Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Stored Procedures Variable arguments

756344Feb 26 2010
I am working on a project of migration where I need to have Stored Procedures for DB access routines cursor - Declare, Open, Fetch etc.
I need to use Dynamic SQL, so I have written the routines as External Procedures in ProC and they work fine.
The Select statement for Cursor in Declare routine is different every time & so the Fetch receives variable arguments. In External Procedures it is done by using va_list, va_arg .....
All the Calling programs in C/Fortran call the Stored Procedures.

I have 2 problems -
1. how to pass the variable arguments to the Stored Procedure Fetch. Is there any way to declare parameters for Stored Procedure that are variable
2. can i have out parameters that have more than one value or out parameters that take address to an array because Fetch needs to pass the address of the first element of the arrays

Calling Program -
CALL DECLARE(VCURS, SELECT_STMT)
CALL OPEN(VCURS)
CALL FETCH(VCURS, 2, CVAL1, CVAL2)
where VCURS is the cursor name
SELECt_STMT is the Select statement
CVAL1 is a float array
CVAL2 is an integer array

Declare Stored Procedure -
create or replace procedure "DECLARE"
(cursorname IN OUT VARCHAR2,
selectstmt IN VARCHAR2)
is
EXTERNAL LIBRARY externProcedures
NAME "declarerout"
LANGUAGE C
PARAMETERS (
cursorName char,
selectstmt char );

Declare External Procedure -
void declarerout(char* cursorName, char* selectStr)
{
......... }

Fetch Stored Procedure -
create or replace procedure "FETCH"
( ??????? )
is
EXTERNAL LIBRARY externProcedures
NAME "fetchrout"
LANGUAGE C;

Fetch External Procedure -
char fetchrout(char *cursorName, int numArgs, ...)
{
............}

Any help or suggestion is highly appreciated. Thank you in advance.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 26 2010
Added on Feb 26 2010
0 comments
551 views