Call stored procedure that has an input parameter that's a pl/sql table from OO4O - OO4O and Wizards(Archived)

We use Oracle Objects for OLE (OO4O) to call stored procedures from our Visual Basic client applications. I have a need to basically build a cursor on the client and then pass it as a parameter to a stored procedure. The problem is that cursor parameters in OO4O are read only, so I can't use that approach. My second approach is to define an Oracle object. I then build a PL/SQL table of that object and then pass that as a parameter to a stored procedure. In theory that should work. I've written a stored procedure that accepts as input a PL/SQL table of object. In the client application, I'm able to build a PL/SQL table of object. However, when I call the stored procedure, I get a ORA-03113 end-of-file on communication channel error. I have been able to call a stored procedure and pass a parameter that's a PL/SQL table of a non-object like VARCHAR2.
So, the question is: Can I call a stored procedure that has an input parameter that's a PL/SQL table of object from OO4O?
Any info would be greatly appreciated.
Thanks

Related

HOW TO EXECUTE A STORE PROCEDURE THAT RETURN MULTIPLE ROWS FROM A QUERY

I NEED TO CREATE AND USE A STORE PROCEDURE THAT IS GOING TO DO A SELECT STATEMENT AN THE RESULT OF THE SELECT STATEMENT IS RETURN IN SOME WAY TO THE REQUESTER.
THIS CALL IS MADE BY AN EXTERNAL LANGUAGE, NOT PL/SQL OR FORMS APPLICATION. USING FOR EXAMPLE ODBC AND VISUAL BASIC. WHAT I NEED TO DO IS ADD A DATA ACCESS LAYER TO MY APPLICATION AND I ALREADY HAVE IT DONE FOR MS SQL SERVER, BUT I NEED THE SAME FUNCTIONALITY ACCESSING AN ORACLE DATABASE.
FLOW:
1. VB CREATE A ODBC CONNECTION TO A ORACLE DATABASE
2. VB EXECUTE A STORE PROCEDURE
3. THE STORE PROCEDURE RETURNS TO THE VB APPLICATION THE RESULT OF THE QUERY THAT IS INSIDE OF THE STORE PROCEDURE.(I.E. THE STORE PROCEDURE IS A BASIC SELECT, NOTHING COMPLEX)
4. VB DISPLAY THE RESULT IN A GRID
FOR SURE I CAN DO THE SELECT DIRECTLY TO ORACLE, BUT FOR PERFORMANCE REASONS AND SCALABILITY, I'LL LIKE IT TO DO IT USING A STORE PROCUDURES
IS THIS POSIBLE?, HOW?
THANKS 
Certainly, it's possible. First, define a stored procedure that includes an OUT parameter which is a REF CURSOR. Then, call the stored procedure via ODBC omitting the OUT parameter from the list of parameters. IT will automatically be returned as a result set. Syntax for both is below...
CREATE PROCEDURE foo (inParam in varchar2, resultSet OUT REF CURSOR )
In ODBC:
{call foo( 'someData' )}
Justin

Oracle/VB/ADO/OO4O

Problem with Using ADO to access Oracle stored procedures:
Actually we want to pass an array of parameters to the Oracle stored procedure from VB code. But the problem we are facing is that the ADO datatypes do not map to the Oracle table datatypes and it becomes a problem when adding a parameter to the command object. The next problem is that we want to return a cursor or record type data from the oracle stored procedure to the VB front end. This also gives problems. Is there any method in ADO that can solve this problem?
Problem with OO4O in accessing Oracle stored procedure:
After a bit of Search we found that there is an object called OO4O Oracle object for OLE that can take an array as a parameter from VB. But even then it does not take any multidimensional array or array of records as an input. It has no problem in returning a cursor to the VB front end, but we don't know how to simultaneously pass an array as an input parameter and get a cursor as an output parameter from the Stored procedure. I tried the Addtable method to add an array of parameters to the Oracle stored procedure and also added a parameter of cursor data type to the Object. But when I tried to execute the stored procedure it is giving error messages. What can be the solution for this?
null

Passing an array to a stored procedure.

I'm trying to pass a 2 dimensional array from VB6 to a stored procedure
in Oracle.
What is the parameter syntax for accepting the 2 dimensional array in the
procedure.
Jean-Marc Morandnull 
U can create a package with a user defined table variable (array) similar to the structure of ur recordset and then use this variable as an input to ur procedure.
for the syntax of making a call to this package in vb can be obtained from the microsoft technet site.

INSERT'ing using Stored Procedure

I want to make my VB.NET application performing INSERT's by calling a Stored Procedure. How do I supply the column values (without using numerous IN parameters) ?
E.g.
create procedure my_ins(my_row my_tab%rowtype)
is
...
How do I call this procedure from VB.NET ?
Any help appreciated!
Runar Emgård
runaremg#online.no
What is the data type of my_tab%rowtype? 
It contains multiple datatypes, according to the columns of the underlying table "my_tab".

Pass a result set to stored procedure

Hello
Can i pass a result set to a stored procedure? How do i do this?
My requirement is that from .NET i will have a dataset which has some records inserted to it, this has to be passed to the stored procedure and insert the records from the SP.
Let me know if this is achievable.
Thanks
PL/SQL does not support the functionality of passing a recordset from the client side to a stored procedure. However, you can pass a REF Cursor within a PL/SQL block to another stored procedure.

Categories

Resources