Re: Passing parameter from C to stored procedure.

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 30 Nov 1999 15:06:38 -0500
Message-ID: <vbb84scoav6t7mdsqfnic55oolqi9f5cab@4ax.com>

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 Received on Tue Nov 30 1999 - 14:06:38 CST