Re: Passing parameter from C to stored procedure.

A copy of this was sent to jeffwyant_at_my-deja.com (if that email address didn't require changing) On Thu, 02 Dec 1999 14:44:31 GMT, you wrote:

>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.
>

then there must be a bug in your stored procedure -- lets see that code.

All I did was change my stored procedure to:

create or replace procedure stored_procedure( s in varchar2, n out number ) as

    some_other_string varchar2(4000); begin

    some_other_string := substr( s, 1, 500 );     n := length(some_other_string);
end;
/

and when you rerun the example program below, it prints out that the input buffer was 1000 bytes and the length of the substring'ed string was 500.

Lets see the procedure

>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.

--
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 Received on Thu Dec 02 1999 - 09:03:55 CST