Re: dbms_sql vs. plsql

From: Gerard M. Averill <e-mail.address_at_my.sig>
Date: Fri, 07 May 99 18:53:16 GMT
Message-ID: <7gvci7$clk$1@news.doit.wisc.edu>

In article <373208DB.D14D85F7_at_fastx.com>, Paul Postel <paulp_at_fastx.com> wrote:

<snip>

>/*
> * Bind a varchar2 variable to a varchar2 function's result,
> * this fails with:
> *
> * ORA-06502: PL/SQL: numeric or value error
> * ORA-06512: at line 3
> * ORA-06512: at "SYS.DBMS_SYS_SQL", line 348
> * ORA-06512: at "SYS.DBMS_SQL", line 141
> * ORA-06512: at line 14
> */
>declare
> sql_block varchar2(255);
> curs integer;
> my_result varchar2(20);
> rc integer;
>begin
> sql_block := '
> begin
> :out_val := fn_varchar2;
> end;';
> curs := dbms_sql.open_cursor;
> dbms_sql.parse(curs, sql_block, DBMS_SQL.NATIVE);
> dbms_sql.bind_variable(curs, ':out_val', my_result);
> rc := dbms_sql.execute(curs);
> dbms_sql.variable_value(curs, ':out_val', my_result);
> dbms_output.put_line('my_result = ' || my_result);
> dbms_sql.close_cursor(curs);
>end;
>/

Paul,

For VARCHAR2 parameters use the overloaded version of BIND_VARIABLE that takes an additional (4th) argument, the maximum expected size of the output value. If not present, the current value of the bind variable, in this case "my_result", is used -- in your example "my_result" is uninitialized (null) so it's size is null (or maybe considered 0). The function result, '999', is larger than this and so it "overflows" the size of the result buffer that DBMS_SQL has reserved for it.

Simply changing your code to:

        ...
        dbms_sql.bind_variable(curs, ':out_val', my_result, 20);
        ...

does the trick. Hope this helps.
Gerard

---

Gerard M. Averill, Researcher
CHSRA, University of Wisconsin - Madison GAverill<at>chsra<dot>wisc<dot>edu Received on Fri May 07 1999 - 13:53:16 CDT