HELP !!!

For PL/SQL developers. We are looking for a way to accomplish the following:

1. dynamically invoke a PL/SQL Stored Procedure from a client application using dynamic arguements...Input/Output
2. Allow the SP to produce return result groups dynamically to the client application and dynamically allocate the necessary data types to support the data returned...
3. Post Process the dynamic result sets with a series of 'C' api calls that investigate the POOL of data that was produced by the execution of the Stored Procedure allowing the developer to arbitrarily assign the rows to local program variables and iteratively process them with inclusive data conversion support.

An example follows:

        PL/SQL Stored Procedure...

 	if arg2==NULL && arg1 != 0
	   select * from EMP_TABLE
	   select * from SALARY_TABLE where EMP_NBR=arg1
	   return;

	if arg1>5
	   select * from EMP_TABLE where arg1=TABLE.EMPLOYEE_NBR
        else
	   select * from EMP_TABLE where arg1=TABLE.EMP_SOC_SECURITY_NBR
	endif

	=====================
   
Code fragment....
	int  max_cols;
	int  max_rows;
	char *col[max_cols];
	char *row[max_rows];

	char *sql_str="execute procedure ABC _at_arg1=10,_at_arg2=NULL";
	dbexec_procedure( sql_str);

	(api calls needed)

	max_result_sets=get_nbr_of_result_sets(); 
	for(k=max_result_sets; k>0; k--) {
	  max_rows=get_nbr_rows_in_this_set(k);
	  for( j=0; j<max_rows; j++) {
	    max_cols=get_nbr_cols_in_row(k,j);
	    for( i=0; i<max_cols; i++) {
	 	.
	 	. ( Process columns into local 'C' application w/dataconvesrion)
		. 
	    }	
	  }	
	}	

We think there is a fundamental limitation in PL/SQL that won't support THIS!! (i.e. select into .... within Stored Procedures....)

Can anyone shed light on this scenario? It would be greatly appreciated ....

Thanks for the help...

Ron Garton
Director of Product Development
Bluestone Inc.

(609) 727-4600 (Ofc.)
(609) 778-8125 (Fax ) Received on Wed Mar 23 1994 - 21:05:12 CET