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