| OO4o and Paging [message #1022] |
Thu, 21 March 2002 08:30  |
Mike
Messages: 417
Registered: September 1998
|
Senior Member |
|
|
Thanks in advance!
I am using the OO4O method in an ASP application and I need help paging the cursors I have obtained via a Stored Procedure. Can someone please give me more info on this matter. My code is as follows.
Set OraDatabase = OraSession.GetDatabaseFromPool(10)
OraDatabase.parameters.remove "nUId"
OraDatabase.parameters.remove "nCId"
OraDatabase.parameters.remove "cursor1"
'last two : value,direction:1:in 2:out
OraDatabase.parameters.add "nUId",spUser_Id,ORAPARM_INPUT
oradatabase.parameters("nUId").servertype= ORATYPE_NUMBER
OraDatabase.parameters.add "nCId",spCust_Id,ORAPARM_INPUT
oradatabase.parameters("nCId").servertype= ORATYPE_NUMBER
OraDatabase.parameters.add "cursor1","",ORAPARM_OUTPUT
oradatabase.parameters("cursor1").servertype= ORATYPE_CURSOR
set cTest = OraDatabase.createsql("begin deploy.spourcustprodusers(:nUId,:nCId,:cursor1); end;",ORASQL_FAILEXEC)
Set rsCust = OraDatabase.parameters("cursor1").value
|
|
|
|
| Re: OO4o and Paging [message #1025 is a reply to message #1022] |
Thu, 21 March 2002 10:11  |
Hersel Ahdout
Messages: 8
Registered: March 2002
|
Junior Member |
|
|
Why don't you use thr follwoing:
Set OraDatabase = OraSession.GetDatabaseFromPool(10)
Dim cTest As Object
OraDatabase.parameters.remove "nUId"
OraDatabase.parameters.remove "nCId"
OraDatabase.parameters.remove "cursor1"
'last two : value,direction:1:in 2:out
OraDatabase.parameters.add "nUId",spUser_Id,ORAPARM_INPUT
oradatabase.parameters("nUId").servertype= ORATYPE_NUMBER
OraDatabase.parameters.add "nCId",spCust_Id,ORAPARM_INPUT
oradatabase.parameters("nCId").servertype= ORATYPE_NUMBER
'OraDatabase.parameters.add "cursor1","",ORAPARM_OUTPUT
'oradatabase.parameters("cursor1").servertype= ORATYPE_CURSOR
'set cTest = OraDatabase.createsql("begin deploy.spourcustprodusers(:nUId,:nCId,:cursor1); end;",ORASQL_FAILEXEC)
set cTest = OraDatabase.CreatePlsqlDynaset("begin deploy.spourcustprodusers(:nUId,:nCId,:cursor1); end;", "cursor1", 0&)
'Set rsCust = OraDatabase.parameters("cursor1").value
'Now you have a cursor that you can recurse through
field_count = cTest.Fields.Count
For x = 0 To field_count
print cTest.FieldName(x)
Next
While cTest.EOF = False
For j = 0 To field_count
print cTest.Fields(j)
Next
cTest.MoveNext
Wend
OraDatabase.Parameters.Remove ("nCId")
OraDatabase.Parameters.Remove ("nUId")
cTest.Close
|
|
|
|
| Re: OO4o and Paging [message #1028 is a reply to message #1025] |
Thu, 21 March 2002 16:56 |
Hersel Ahdout
Messages: 8
Registered: March 2002
|
Junior Member |
|
|
This example demonstrates the use of OraParameter object supporting PL/SQL cursor This example returns PL/SQL cursor as a dynaset for the different values of Customer name parameter.
'Declare variables as OLE Objects.
Dim MySession as OraSession
Dim MyDatabase as OraDatabase
Dim OrderDynaset As OraDynaset
Dim SalesDynaset As OraDynaset
'Create the OraSession Object.
Set MySession = CreateObject("OracleInProcServer.XOraSession")
'Create the OraDatabase Object by opening a connection to Oracle.
Set MyDatabase = MySession.OpenDatabase("ExampleDb", "scott/tiger", 0&)
'Create the Deptno parameter
MyDatabase.Parameters.Add "NAME", "JOCKSPORTS", ORAPARM_INPUT
MyDatabase.Parameters("NAME").ServerType = ORATYPE_VARCHAR2
MyDatabase.Parameters.Add "ORDCURSOR", 0, ORAPARM_OUTPUT
MyDatabase.Parameters("ORDCURSOR").ServerType = ORATYPE_CURSOR
MyDatabase.Parameters.Add "SALESCURSOR", 0, ORAPARM_OUTPUT
MyDatabase.Parameters("SALESCURSOR").ServerType = ORATYPE_CURSOR
Set OraSQLStmt = MyDatabase.CreateSql("Begin Customers.GetCutomerSalesOrder(:Name,:OrdCursor,:SalesCursor);end;" ,ORASQL_FAILEXEC)
Set OrderDynaset = MyDatabase.Parameters("ORDCURSOR").Value
Set SalesDynaset = MyDatabase.Parameters("SALESCURSOR").Value
'Now display the Dynaset's field value
MsgBox "Order Details are " & OrderDynaset.fields("ordid").Value & " " & OrderDynaset.fields("orderDate").Value
MsgBox "Sales Details are " & SalesDynaset.fields("Prodid").Value & " " & SalesDynaset.fields("ProdName").Value
'Now Change the customer name to VOLLYRITE
MyDatabase.Parameters("NAME").Value = "VOLLYRITE"
'Now refreshes the SQLStmt object
OraSQLStmt.Refresh
'Now display the Dynaset's field value
MsgBox "Order Details are " & OrderDynaset.fields("ordid").Value & " " & OrderDynaset.fields("orderDate").Value
MsgBox "Sales Details are " & SalesDynaset.fields("Prodid").Value & " " & SalesDynaset.fields("ProdName").Value
'Now remove the Parameters object
'MUST BE CALLED for OraParameter of type ORATYPE_CURSOR
MyDatabase.Parameters.Remove ("ORDCURSOR")
MyDatabase.Parameters.Remove ("SALESCURSOR")
MyDatabase.Parameters.Remove ("NAME")
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
