Home » SQL & PL/SQL » SQL & PL/SQL » Stored procedures variable arguments
Stored procedures variable arguments [message #445168] Fri, 26 February 2010 05:23 Go to next message
saracooper
Messages: 15
Registered: February 2010
Junior Member
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.
Re: Stored procedures variable arguments [message #445170 is a reply to message #445168] Fri, 26 February 2010 05:31 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
You can't have a variable number of parameters, but you can use overloading

As for getting variable result sets back - use ref cursors
Re: Stored procedures variable arguments [message #445171 is a reply to message #445168] Fri, 26 February 2010 05:37 Go to previous messageGo to next message
Michel Cadot
Messages: 63805
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't know why you want to use external procedure to do job that can be done inside the database server as it is made for this (and anyway you will call it to get the data).
In addition, it is a BIG hole in security and reliability.

Here's how you can do variable number of parameters in PL/SQL:
SQL> create or replace procedure p (
  2    p1 varchar2 default null,
  3    p2 varchar2 default null,
  4    p3 varchar2 default null,
  5    p4 varchar2 default null,
  6    p5 varchar2 default null
  7  ) 
  8  is
  9  begin
 10    null;
 11  end;
 12  /

Procedure created.

SQL> exec p ('A');

PL/SQL procedure successfully completed.

SQL> exec p('A','B');

PL/SQL procedure successfully completed.

SQL> exec p('A','B','C');

PL/SQL procedure successfully completed.

SQL> exec p(p3=>'C', p5=>'E');

PL/SQL procedure successfully completed.

Regards
Michel

Re: Stored procedures variable arguments [message #445176 is a reply to message #445171] Fri, 26 February 2010 05:43 Go to previous messageGo to next message
saracooper
Messages: 15
Registered: February 2010
Junior Member
Thank u for your replies, great help. I need to use External Procedures because I need SQLDA, Dynamic SQL.
Re: Stored procedures variable arguments [message #445178 is a reply to message #445168] Fri, 26 February 2010 05:50 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
You can use dynamic sql in stored procedures in the db.
Re: Stored procedures variable arguments [message #445179 is a reply to message #445178] Fri, 26 February 2010 05:56 Go to previous messageGo to next message
saracooper
Messages: 15
Registered: February 2010
Junior Member
OK. But I dont think u can use SQLDA - Descriptor Area in Stored Procedures. I did a lot of research before I decided to use ProC.
Re: Stored procedures variable arguments [message #445181 is a reply to message #445176] Fri, 26 February 2010 05:57 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You'd be much better off doing this in the database. It'll be much easier to debug, and much easier for the next developer to understand what's going on.

here is the EXECUTE IMMEDIATE documentation, which will do 95% of everything you can think of, and here is the DBMS_SQL documentation, which will make you reconsider whether you actually needed to do the other 5% anyway.
Previous Topic: logic and cosed
Next Topic: fatch column value on basis condition
Goto Forum:
  


Current Time: Tue Sep 27 00:46:52 CDT 2016

Total time taken to generate the page: 0.10989 seconds