Problem in Dynamic Sql

[ag_201074]

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

[padders]

Pass an integer value to the DBMS_SQL.BIND_VARIABLE call for the OUT parameter size, e.g.

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?

[ag_201074]

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.

[Srinivas_Sharma]

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

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

[padders]

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

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>