Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Dyn Method 4, Pro*C, Stored Procedures, Host Arrays
A copy of this was sent to Ken Tough <ken_at_objectech.co.uk>
(if that email address didn't require changing)
On Wed, 4 Aug 1999 16:35:58 +0100, you wrote:
>
>[a (slightly diff) reply also posted]
>
>Apparently Thomas Kyte <tkyte_at_us.oracle.com> wrote:
>
>>You would have to use OCI to bind table types.
>
>>To return large amounts of data from a stored procedure to a pro*c application -
>>have you considered ref cursors?
>
>I will have a look at that. I was hoping for a very 'generic'
>solution, where parameters could take/return either single or
>array data, without the caller knowing what it is, other than
>parameter position. [long story]
>
>>Another workaround others have used is to pass easily parsable strings back in
>>32k chunks.
>
>That could be a very good solution, as all parms will be dealt with
>as strings anyway. I suppose manipulation in PL/SQL would use
>things like concatenation [ retbuf = retbuf || '|' || selval ] ?
>
>>OCI is pretty easy to use to do this -- you can leave your entire app in Pro*C
>>except for this one part (executing a procedure with plsql tables). let me know
>>if you need a small example.
>
>That sounds like a reasonable option. The example would certainly
>help to get an idea of how much stuff needs to be done with it..
>
>Thanks again,
Sorry so long -- forgot to followup on this. Here is an example. The plsql is:
create or replace procedure do_something( p_table in out owa.vc_arr )
as
begin
for i in 1 .. p_table.count loop p_table(i) := upper(p_table(i)); end loop; p_table( p_table.count+1 ) := 'New Entry';end;
and the OCI to run that would be:
#include <stdio.h>
#include <oratypes.h> #include <ocidfn.h> #include <ociapr.h> #include <ocidem.h>
static char * USERID = "scott/tiger";
#define SQLCA_INIT
EXEC SQL INCLUDE sqlca;
static void sqlerror_hard()
{
EXEC SQL WHENEVER SQLERROR CONTINUE; printf("\nORACLE error detected:"); printf("\n% .70s \n", sqlca.sqlerrm.sqlerrmc);
EXEC SQL ROLLBACK WORK RELEASE;
exit(1);
}
void oci_error(Lda_Def lda, Cda_Def cda) {
text msg[600];
sword rv;
rv = oerhms(&lda, cda.rc, msg, 600);
printf("\n\n%.*s", rv, msg);
printf("Processing OCI function %s\n", oci_func_tab[cda.fc]);
if (oclose(&cda)) printf("Error closing cursor!\n"); if (ologof(&lda)) printf("Error logging off!\n");exit(1);
#define MAX_TABLE_SIZE 1200
static void process()
{
Lda_Def lda;
Cda_Def cda;
char stmt[255];
char ename[40];
char data[50][255];
short data_l[50]; short data_i[50]; short data_rc[50]; int data_nelements; int i;
sqllda( &lda );
if ( oopen(&cda,&lda,NULL,-1,01,NULL,-1) ) oci_error(lda,cda);
strcpy( stmt, "begin do_something( :p_table ); end;" );
if ( oparse(&cda, stmt, -1, 0, 2 ) ) oci_error(lda,cda);
/* init all array elements to NULL and set their max length */ for( i = 0; i < sizeof(data)/sizeof(data[0]); i++ ) {
data_i[i] = -1; data_l[i] = sizeof(data[0]);
/* for the elements we have data for, fix the NULL indicator and
put some data in there */
for( data_nelements = 0; data_nelements < 15; data_nelements++ )
{
data_i[data_nelements] = 0; sprintf( data[data_nelements], "Some data %d", data_nelements );}
/* Bind it */
if (
obndra( &cda, /* cursor data area */ ":p_table", /* bind variable name */ -1, /* indicates prev. parameter is C String */ (void*)data, /* data array (plsql table) */ sizeof(data[0]), /* sizeof each array element */ STRING_TYPE, /* binding C strings in table */ -1, /* scale (not used) */ data_i, /* indicators for the i'th tbl entry */ data_l, /* lengths of the i'th tbl entry */ data_rc, /* return codes for the i'th tbl entry */ sizeof( data )/sizeof(data[0]), /* MAXsize of array */ &data_nelements, /* CURRENT size of array */ (void*)0, /* not used */ -1, /* not used */ -1 ) /* not used */ ) oci_error(lda,cda);
/* run it */
if( oexec( &cda ) ) oci_error(lda,cda);
/* show it... */
for( i = 0; i < data_nelements; i++ )
printf( "Element(%d), Length = %d ind = %d = %s\n", i, data_l[i], data_i[i], data[i] );}
main( argc, argv )
int argc;
char * argv[];
{
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR oracleid[50];
EXEC SQL END DECLARE SECTION;
/* Connect to ORACLE. */
strcpy( oracleid.arr, USERID );
oracleid.len = strlen(USERID);
EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();
EXEC SQL CONNECT :oracleid;
printf("\nConnected to ORACLE as user: %s\n\n", oracleid.arr);
process();
/* Disconnect from ORACLE. */
EXEC SQL COMMIT WORK RELEASE;
exit(0);
}
--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
Thomas Kyte tkyte_at_us.oracle.com Oracle Service Industries Reston, VA USA
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Aug 18 1999 - 14:19:13 CDT