Problem in Dynamic Sql
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Problem in Dynamic Sql

  1. #1
    Join Date
    Jan 2001
    Posts
    50

    Problem in Dynamic Sql

    We have a function "test_dyanamic_before" in which we are dynamically calling another function "TEST_CHILD". I am pasting the sample codes but while running the test_dyanamic_before the flow always goes inside exception (see DBMS_OUTPUT.PUT_LINE('INSIDE EXCEPTION') in test_dyanamic_before how can i avoid this and what are we doing wrong.

    Select test_dyanamic_before(‘test_child’) from dual ;

    Its giving a error

    ORA-20000: ORA-06502: PL/SQL: numeric or value error: character string buffer too small ERRRRRRRRRRRROOOORRRRR ... Line: 29

    The samples are as follows
    /**********************************************/
    TEST_CHILD
    /**********************************************/
    CREATE OR REPLACE FUNCTION test_child(var_1 VARCHAR2,var_2 OUT VARCHAR2 )
    RETURN NUMBER AS
    var_3 VARCHAR2(10) := 'AAA';
    BEGIN
    var_2 := var_3;
    --DBMS_OUTPUT.PUT_LINE('inside etst_child');
    RETURN length(var_2);
    --RETURN 0;
    END;


    /**********************************************/
    TEST_DYNAMIC BEFORE
    /**********************************************/
    CREATE OR REPLACE FUNCTION TEST_DYNAMIC_BEFORE(VAR_PROC VARCHAR2)
    RETURN NUMBER
    AS

    VAR_CMD VARCHAR2(2000);
    V_CURSOR_ID INTEGER;
    VAR_L_RETURN NUMBER;
    VAR_INPUT VARCHAR2(10) := 'TES';
    VAR_OUTPUT VARCHAR2(1000);
    DUMMY NUMBER;

    BEGIN

    VAR_CMD := ' BEGIN '||
    ' :RET_CODE :=' || VAR_PROC || '(' ||
    ' :VAR_1,' || ':VAR_2' ||
    '); END;';

    --------------------------------------------------------------------------------------------
    BEGIN
    --------------------------------------------------------------------------------------------
    -- FOR TESTING DYNAMIC SQL

    V_CURSOR_ID := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE (V_CURSOR_ID,VAR_CMD,DBMS_SQL.V7 );
    DBMS_SQL.BIND_VARIABLE (V_CURSOR_ID,':RET_CODE',VAR_L_RETURN);
    DBMS_SQL.BIND_VARIABLE (V_CURSOR_ID,':VAR_1',VAR_INPUT);
    DBMS_SQL.BIND_VARIABLE(V_CURSOR_ID,':VAR_2',VAR_OUTPUT);
    dbms_output.put_line('HERE 1');
    DUMMY := DBMS_SQL.EXECUTE (V_CURSOR_ID);
    dbms_output.put_line('HERE 2');
    DBMS_SQL.VARIABLE_VALUE(V_CURSOR_ID,':VAR_2',VAR_OUTPUT);
    DBMS_SQL.CLOSE_CURSOR (V_CURSOR_ID);
    DBMS_OUTPUT.PUT_LINE('inside MAIN' || VAR_OUTPUT);
    --VAR_L_RETURN := TEST_CHILD(VAR_INPUT,VAR_OUTPUT);
    --------------------------------------------------------------------------------------------
    IF (VAR_L_RETURN != 0) THEN
    BEGIN
    ORA_RAISERROR(29, 'EXECUTION OF PENALTY ROUTINE FAILED ...', 29);
    RETURN (VAR_L_RETURN);
    END;
    END IF;
    EXCEPTION
    WHEN OTHERS THEN
    IF DBMS_SQL.IS_OPEN(V_CURSOR_ID) THEN
    DBMS_SQL.CLOSE_CURSOR(V_CURSOR_ID);
    --RETURN 0;
    END IF;
    DBMS_OUTPUT.PUT_LINE('INSIDE EXCEPTION');
    --ORA_RAISERROR(SQLCODE, 'ERRRRRRRRRRRROOOORRRRR ...', 29);
    END;
    RETURN 80;
    END;


    PLz help


    Regds
    Aniruddha
    Aniruddha Gupta

  2. #2
    Join Date
    Jan 2004
    Posts
    162
    Pass an integer value to the DBMS_SQL.BIND_VARIABLE call for the OUT parameter size, e.g.
    Code:
    DBMS_SQL.BIND_VARIABLE (v_cursor_id, ':VAR_2', var_output, 10);
    You do know all this would be trivial if you used EXECUTE IMMEDIATE don't you?

  3. #3
    Join Date
    Jan 2001
    Posts
    50
    Hi Padds,
    Thnnks for your reply and its working fine , I know about Execute Immediate but then while making a dynamic call to an finction (test_child) in our case it does not work.
    /*********************************************
    TEST_DYNAMIC_BEFORE using Execute Immediate
    *********************************************/
    CREATE OR REPLACE FUNCTION TEST_DYNAMIC_AFTER(VAR_PROC VARCHAR2)
    RETURN NUMBER
    AS

    VAR_CMD VARCHAR2(2000);
    V_CURSOR_ID INTEGER;
    VAR_L_RETURN NUMBER;
    VAR_INPUT VARCHAR2(10) ;
    VAR_OUTPUT VARCHAR2(1000);
    DUMMY INTEGER;

    BEGIN

    VAR_CMD := ' BEGIN '||
    ' :ret_code :=' || VAR_PROC || '(' ||
    ' :var_1,' || ':var_2' ||
    '); END;';

    -- VAR_CMD := VAR_PROC || '(?,?)' ;
    dbms_output.put_line(VAR_CMD);
    --------------------------------------------------------------------------------------------
    BEGIN
    --------------------------------------------------------------------------------------------
    -- FOR TESTING DYNAMIC SQL
    /*
    V_CURSOR_ID := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE (V_CURSOR_ID,VAR_CMD,DBMS_SQL.NATIVE );
    DBMS_SQL.BIND_VARIABLE (V_CURSOR_ID,':ret_code',VAR_L_RETURN);
    DBMS_SQL.BIND_VARIABLE (V_CURSOR_ID,':var_1',VAR_INPUT);
    DBMS_SQL.BIND_VARIABLE (V_CURSOR_ID,':var_2',VAR_OUTPUT,10);
    dbms_output.put_line('HERE 1:' || TO_CHAR(V_CURSOR_ID));
    DUMMY := DBMS_SQL.EXECUTE (V_CURSOR_ID);
    dbms_output.put_line('HERE 2');
    DBMS_SQL.VARIABLE_VALUE(V_CURSOR_ID,':var_2',VAR_OUTPUT);
    DBMS_SQL.CLOSE_CURSOR (V_CURSOR_ID);
    DBMS_OUTPUT.PUT_LINE('inside MAIN' || VAR_OUTPUT);
    */
    --DBMS_OUTPUT.PUT_LINE('inside MAIN' || VAR_CMD);
    EXECUTE IMMEDIATE VAR_CMD USING VAR_L_RETURN,VAR_INPUT, VAR_OUTPUT;
    --------------------------------------------------------------------------------------------
    IF (VAR_L_RETURN != 0) THEN
    BEGIN
    ORA_RAISERROR(29, 'EXECUTION OF PENALTY ROUTINE FAILED ...', 29);
    RETURN (VAR_L_RETURN);
    END;
    END IF;
    EXCEPTION
    WHEN OTHERS THEN
    /*
    IF DBMS_SQL.IS_OPEN(V_CURSOR_ID) THEN
    DBMS_SQL.CLOSE_CURSOR(V_CURSOR_ID);
    --RETURN 0;
    END IF;
    */
    DBMS_OUTPUT.PUT_LINE('INSIDE EXCEPTION');
    ORA_RAISERROR(SQLCODE, 'ERRRRRRRRRRRROOOORRRRR ...', 29);
    END;
    RETURN 80;
    END;
    /

    Select test_dynamic_before('test_child') from dual;
    After making a call and passing the TEST_CHILD to this function this gives an error "ORA-20000: ORA-06536: IN bind variable bound to an OUT position ERRRRRRRRRRRROOOORRRRR ... Line: 29"

    Can u plz tell me how to use Execute Immediate in such cases.
    Aniruddha Gupta

  4. #4
    Join Date
    Jan 2003
    Location
    Hull, UK
    Posts
    220
    Code:
    Select test_dynamic_before('test_child') from dual;
    If you want to execute using Execute immediate the above statement, it can be done like this...

    Code:
    Declare
             l_var varchar2(20);
    begin
    Execute Immediate 'select test_dynamic_before('test_child') from dual'   into l_var;
    
    dbms_output.put_line(l_var);
    Hope this is wht u want...

    Srini

  5. #5
    Join Date
    Jan 2004
    Posts
    162
    No offence but reading the manual might help. USING clause syntax allows you to specify IN (the default) / OUT / IN OUT for the parameters you are binding. For example...
    Code:
    Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
    JServer Release 9.2.0.6.0 - Production
    
    SQL> CREATE OR REPLACE FUNCTION test_child (
      2     var_1         VARCHAR2,
      3     var_2   OUT   VARCHAR2)
      4     RETURN NUMBER
      5  AS
      6     var_3 VARCHAR2 (10) := 'AAA';
      7  BEGIN
      8     var_2 := var_3;
      9     RETURN LENGTH (var_2);
     10  END;
     11  /
    
    Function created.
    
    SQL> SET SERVEROUTPUT ON;
    SQL> DECLARE
      2    v_par_1 VARCHAR2 (10) := 'PAR1';
      3    v_par_2 VARCHAR2 (10) := 'PAR2';
      4    v_rtn NUMBER;
      5  BEGIN
      6    EXECUTE IMMEDIATE 
      7      'BEGIN :1 := test_child (:2, :3); END;' 
      8   USING OUT v_rtn, IN v_par_1, OUT v_par_2;
      9   
     10    DBMS_OUTPUT.PUT_LINE ('v_par_1: ' || v_par_1);
     11    DBMS_OUTPUT.PUT_LINE ('v_par_2: ' || v_par_1);
     12    DBMS_OUTPUT.PUT_LINE ('v_rtn: ' || v_rtn);
     13  END;
     14  /
    v_par_1: PAR1
    v_par_2: PAR1
    v_rtn: 3
    
    PL/SQL procedure successfully completed.
    
    SQL>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width