My Oracle Support Banner

IN OUT Char bind data not set inside an EXECUTE IMMEDIATE statement (Doc ID 1214863.1)

Last updated on FEBRUARY 03, 2019

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

To view full details, sign in with your My Oracle Support account.

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


In this Document
  Symptoms
  Cause
  Solution
  References

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.