IN OUT Char bind data not set inside an EXECUTE IMMEDIATE statement

(Doc ID 1214863.1)

Last updated on SEPTEMBER 23, 2010

Applies to:

PL/SQL - Version: 10.1.0.2 to 11.2.0.1 - Release: 10.1 to 11.2
Information in this document applies to any platform.

Symptoms


=== ODM Issue Clarification ===
Assigning a value to a CHAR IN OUT bind variable inside a block executed by
an EXECUTE IMMEDIATE statement does not work if the bind has not been set to
a value before executing the block.

The same works fine on 9.2.0.8.0 but gives null value on 10g and later versions.


11.2.0.1.0

SQL> create table test1 (col1 char(1), col2 varchar2(1000));

Table created.

SQL> insert into test1 values ('F', 'testing');

1 row created.

SQL> commit;

Commit complete.

SQL> CREATE OR REPLACE FUNCTION Test_Function RETURN CHAR
2 IS
3 BL_YES CHAR(1) := 'T';
4 v_result CHAR(1);
5 BEGIN
6 SELECT BL_YES
7 INTO v_result
8 FROM test1
9 WHERE col2 = 'testing';
10 DBMS_OUTPUT.PUT_LINE( 'Test_Function v_result=>' ||v_result);
11 RETURN v_result;
12 END Test_Function;
13 /

Function created.

SQL> SET SERVEROUTPUT ON
DECLARE
V_RESULT CHAR(1);
BEGIN
EXECUTE IMMEDIATE 'BEGIN :V_RESULT := Test_Function; END;' USING IN OUT V_RESULT;
DBMS_OUTPUT.PUT_LINE( 'Caller V_RESULT=>'||V_RESULT );
END;SQL> 2 3 4 5 6
7 /

Test_Function v_result=>T
Caller V_RESULT=>

PL/SQL procedure successfully completed.


9.2.0.8.0

SQL> create table test1 (col1 char(1), col2 varchar2(1000));

Table created.

SQL> insert into test1 values ('F', 'testing');

1 row created.

SQL> commit;

Commit complete. 

SQL> CREATE OR REPLACE FUNCTION Test_Function RETURN CHAR
2 IS
3 BL_YES CHAR(1) := 'T';
4 v_result CHAR(1);
5 BEGIN
6 SELECT BL_YES
7 INTO v_result
8 FROM test1
9 WHERE col2 = 'testing';
10 DBMS_OUTPUT.PUT_LINE( 'Test_Function v_result=>' ||v_result);
11 RETURN v_result;
12 END Test_Function;
13 /

Function created.

SQL> SET SERVEROUTPUT ON
DECLARE
V_RESULT CHAR(1);
BEGIN
EXECUTE IMMEDIATE 'BEGIN :V_RESULT := Test_Function; END;' USING IN OUT V_R ESULT;
DBMS_OUTPUT.PUT_LINE( 'Caller V_RESULT=>'||V_RESULT );
END;SQL> 2 3 4 5 6
7 /

Test_Function v_result=>T
Caller V_RESULT=>T

PL/SQL procedure successfully completed.


The output is incorrect in 10g and 11g.



Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms