Re: Pro C 7.3.4 String limits

A copy of this was sent to Eugene Katzman <genek_at_erols.com> (if that email address didn't require changing) On Sat, 21 Aug 1999 08:02:16 -0500, you wrote:

>I am running 7.3.4 and have an application trying to pass a string to a
>stored procedure. I am using a varchar [1900] and I crash if I send
>more than 246 characters but pass the data ok if I send 226 chars. Is
>this a formal limit. Will version 8 allow passing 2000 chars.
>
>Gene Katzman (301)681-9412

the limit for varchars is actually 32k in 7.x.

There is a bug somewhere in the application -- post a really small example that demonstrates the error. for example:

Oracle7 Server Release 7.3.4.0.1 - Production With the distributed, replication, parallel query and Spatial Data options PL/SQL Release 2.3.4.0.0 - Production

SQL> create or replace procedure demo_proc( str in out varchar2 )   2 as
  3 n number default length(str);
  4 begin
  5 str := 'the string you sent was of length = ' || n;   6 end;
  7 /

Procedure created.

And then use PRO*C like:

main( argc, argv )
int argc;
char * argv[];
{
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR oracleid[250];
VARCHAR my_big_string[32000];
EXEC SQL END DECLARE SECTION;     strcpy( oracleid.arr, "scott/tiger" );     oracleid.len = strlen( oracleid.arr );     exec sql whenever sqlerror do sqlerror_hard();

    EXEC SQL CONNECT :oracleid;
    printf("\nConnected to ORACLE as user: %s %d\n\n", oracleid.arr,sqlca.sqlcode);

    memset( my_big_string.arr, 'a', 29123 );     my_big_string.len = 29123;

    exec sql execute
    begin

        demo_proc( :my_big_string );
    end;
    end-exec;

    printf( "%.*s\n", my_big_string.len, my_big_string.arr );

    /* Disconnect from ORACLE. */
    EXEC SQL COMMIT WORK RELEASE;
    exit(0);
}

which displays for me:

Connected to ORACLE as user: scott/tiger 0

the string you sent was of length = 29123

showing that passing a really big string is OK.

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