|
|
|
|
|
|
|
|
| Re: Passing arrays as an argument for pl/sql procedures [message #37027 is a reply to message #37005] |
Thu, 10 January 2002 19:31   |
sulinder singh
Messages: 10
Registered: January 2002
|
Junior Member |
|
|
Thanks everyone,
Andrew thats the way i am currently doing (but this is a workaround there is problem when i have to accept dates or numbers with decimal as valid data for array)
what i wanted to know was is there anyway that i can make a front end like pb/vb pass array as an argument to the stored procedure, I guess what suresh said is what i think i will stick to
as about making a package with the type table which is passed to the procedure
It didnt work i had tried it... maybe i should relook into it once again.
Thanks a lot Everyone
Sulinder Singh
|
|
|
|
| Re: Passing arrays as an argument for pl/sql procedures [message #38135 is a reply to message #37005] |
Sun, 24 March 2002 22:38 |
ashok
Messages: 32
Registered: July 2000
|
Member |
|
|
Hi Sulinder,
I am stuck at the same place where I require to pass Arrays from VB to Oracle Stored Procedures. Did you arrive at any solution for your problem. If yes, it will be really great if you can send me the piece of code...
Please treat this as urgent. Any answer, positive or negative will be greatly appreciated.
Regards,
Ashok
|
|
|
|
| Re: Passing arrays as an argument for pl/sql procedures [message #39733 is a reply to message #37005] |
Tue, 06 August 2002 02:16 |
Madhavan Lakshmi Narasimh
Messages: 2
Registered: August 2002
|
Junior Member |
|
|
|
You can pass a array to oracle procedures through Oracle Objects for OLE(OO4O). oo4o can be implemented in VB. Defin the array 'IN' parameters of the Stored procedures in TABLE TYPE. I know Iam replying rather late. But I had the same problem 4 weeks back and found this solution. May be this will help future web searchers for this problem.
|
|
|
|
| Re: Passing arrays as an argument for pl/sql procedures [message #39775 is a reply to message #37005] |
Thu, 08 August 2002 23:07 |
Madhavan Lakshmi Narasimh
Messages: 2
Registered: August 2002
|
Junior Member |
|
|
Yes you can, but using,
Oracle Objects for OLE (OO4O).
create a stored procedure with the in parameters and out of Table Type.
using the OO4O pass them. You can install the OO4O form the oracel client for windows disk. You can even do connection pooling with OO4O for effecient performance.
My suggestion is that you use Microsoft OLEDB provider for Oracle for all other operations and use OO4O where ever you want to pass arrays to stored procedures. This way you can have mix and match of both the worlds and getting the best.
check this following code out for oracle side. This has been written for the age old EMP table.
create or replace package tt1 as
type ENOARRAY is table of emp.empno%type index by BINARY_INTEGER;
type ENAMEARRAY is table of emp.ename%type index by BINARY_INTEGER;
type JOBARRAY is table of emp.job%type index by BINARY_INTEGER;
procedure tt2(UBOUND in number, ENO in ENOARRAY, ENM out ENAMEARRAY, EJB out JOBARRAY, RESULTSMESSAGE out VARCHAR2);
end tt1;
/
create or replace package body tt1 as
procedure tt2(UBOUND in number, ENO in ENOARRAY, ENM out ENAMEARRAY, EJB out JOBARRAY, RESULTSMESSAGE OUT VARCHAR2) is
I number(22);
BEGIN
FOR I in 1..ArraySize loop
SELECT ENAME, JOB INTO ENM(I), EJB(I) from EMP WHERE EMPNO = ENO(I);
END LOOP;
RESULTSMESSAGE:='SUCCESSFUL';
eXCEPTION
WHEN OTHERS THEN
RESULTSMESSAGE:=SQLERRM;
END;
END;
check this following code out for VB side. This has been written for the age old EMP table.
Dim oSession As OracleInProcServer.OraSessionClass
Dim oDb As OracleInProcServer.OraDatabase
Dim userPass As String
Dim empno As OracleInProcServer.OraParamArray
Dim ename As OracleInProcServer.OraParamArray
Dim job As OracleInProcServer.OraParamArray
Dim sqlStatement As String
Dim oSql As OracleInProcServer.OraSqlStmt
Dim st As OracleInProcServer.OraParameters
Dim st1 As OracleInProcServer.OraParameter
Dim counter As Integer
Private Sub Command1_Click()
Set oSession = CreateObject("OracleInProcServer.XOraSession")
userPass = "SYSTEM/MANAGER"
Set oDb = oSession.OpenDatabase("VENKAT16", userPass, 1)
oDb.Parameters.Add "UBOUND", 3, ORAPARM_INPUT
oDb.Parameters("UBOUND").serverType = ORATYPE_NUMBER
oDb.Parameters.AddTable "ENO", CLng(1), 2, CLng(3)
oDb.Parameters("ENO").put_Value 7369, 0
oDb.Parameters("ENO").put_Value 7499, 1
oDb.Parameters("ENO").put_Value 7521, 2
oDb.Parameters.AddTable "ENM", ORAPARM_OUTPUT, ORATYPE_VARCHAR2, 3, 10
oDb.Parameters.AddTable "EJB", ORAPARM_OUTPUT, ORATYPE_VARCHAR2, 3, 9
oDb.Parameters.Add "RESULTSMESSAGE", "OUT", ORAPARM_OUTPUT
oDb.Parameters("RESULTSMESSAGE").serverType = ORATYPE_VARCHAR2
sqlStatement = "Begin TT1.TT2(:UBOUND, :ENO, :ENM, :EJB, :RESULTSMESSAGE); End;"
Set oSql = oDb.CreateSql(sqlStatement, 0)
oSql.Refresh
MsgBox oDb.Parameters("RESULTSMESSAGE")
For counter = 0 To 2
MsgBox oDb.Parameters("ENM").get_Value(counter)
MsgBox oDb.Parameters("EJB").get_Value(counter)
Next
Set oSql = Nothing
Set empname = Nothing
Set empno = Nothing
Set job = Nothing
Set oDb = Nothing
Set oSession = Nothing
End Sub
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
