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>