Re: Passing parameter from C to stored procedure.

Upon futher investigation I have learned more about the problem. It seems the full length of the string is getting into my Oracle stored procedure, but as soon as the stored procedure attempts to use the substr function on the string, the string is limited to 255 characters. The input variable I am working with is defined as varchar2.

In article <vbb84scoav6t7mdsqfnic55oolqi9f5cab_at_4ax.com>,   tkyte_at_us.oracle.com wrote:
> A copy of this was sent to jeffwyant_at_my-deja.com
> (if that email address didn't require changing)
> On Tue, 30 Nov 1999 19:28:36 GMT, you wrote:
>
> >I am having trouble passing a string from C to an Oracle stored
> >procedure. I am using the Pro*c pre-compiler, but I am unable to pass
> >more than 255 characters to the Oracle stored procedure. Is this the
> >limit or is there a way to pass more than 255 characters. Below is a
> >sample of my code.
> >
> >
>
> Here is my snippet of code:
> #include <stdio.h>
> #include <string.h>
>
> static char * USERID = "tkyte/tkyte";
>
> #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);
> }
>
> static int process( char * input )
> {
> EXEC SQL BEGIN DECLARE SECTION;
> int n;
> char buffer[4000];
> EXEC SQL END DECLARE SECTION;
>
> memset( buffer, 0, sizeof(buffer) );
> strcpy( buffer, input );
> n = 0;
>
> EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();
> EXEC SQL EXECUTE
> begin
> stored_procedure( :buffer, :n );
> end;
> END-EXEC;
>
> printf( "Strlen buffer = %d, N = %d\n", strlen(buffer), n );
> }
>
> main( argc, argv )
> int argc;
> char * argv[];
> {
> EXEC SQL BEGIN DECLARE SECTION;
> VARCHAR oracleid[50];
> EXEC SQL END DECLARE SECTION;
> char data[40000];
>
> strcpy( oracleid.arr, USERID );
> oracleid.len = strlen( oracleid.arr );
> EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();
> EXEC SQL CONNECT :oracleid;
> printf("\nConnected to ORACLE as user: %s\n\n", oracleid.arr);
>
> memset( data, 'A', 1000 );
> data[1000] = 0;
>
> process(data);
>
> /* Disconnect from ORACLE. */
> EXEC SQL COMMIT WORK RELEASE;
> exit(0);
> }
>
> that when run with a stored procedure:
>
> create or replace procedure stored_procedure( s in varchar2, n out
number )
> as
> begin
> n := length(s);
> end;
> /
>
> produces the output:
>
> Connected to ORACLE as user: tkyte/tkyte
>
> Strlen buffer = 1000, N = 1000
>
> showing that >255 can be done.
>
> [snip]
>
> when you say "I am unable" does that mean you have an error, you only
get 255
> passed, ???
>
> what version?
>
> what platform?
>
> what does the call to your insert_routine look like (perhaps your
input data is
> wrong -- has a \0 in it somewhere? )
>
> >
> >
> >
> >Sent via Deja.com http://www.deja.com/
> >Before you buy.
>
> --
> See http://osi.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
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Dec 02 1999 - 08:44:31 CST