why my dynamic sql statement failed?

From: "Guang Mei" <GMei@xxxxxx>
To: "Oracle-L (E-mail)" <Oracle-L@xxxxxxxxxxxxx>
Date: Wed, 15 Feb 2006 14:21:19 -0500
Oracle 9i.  I am running an update statement from one schema to update some 
data in another schema. The update statement in pl/sql block was successful 
(test1). But if I use dynamic sql (test2) to do the something it failed. Anyone 
know why?

Thanks.

Guang


-- test1:

SQL> connect username/password@qa9i
Connected.
SQL> select  ISDISABLED from users
  2  WHERE RID = (
  3            SELECT u.RID FROM Users u, Customers c
  4            WHERE u.ID = 'USER1'
  5            AND c.ID = 'ENRCUSTIRACHWIRE'
  6            AND u.customerkey = c.RID)
  7        AND isdisabled = 1;

ISDISABLED
----------
         1

-- direct update statement is successful:

SQL> set serveroutput on
SQL> declare 
  2  begin
  3         UPDATE Users SET isdisabled = 0 
  4          WHERE RID = (
  5              SELECT u.RID FROM Users u, Customers c
  6              WHERE u.ID = 'USER1'
  7              AND c.ID = 'ENRCUSTIRACHWIRE'
  8              AND u.customerkey = c.RID)
  9          AND isdisabled = 1;
 10       dbms_output.put_line('sql%rowcount='|| sql%rowcount);
 11  end;
 12  /
sql%rowcount=1

PL/SQL procedure successfully completed.

SQL> select  ISDISABLED from users
  2  WHERE RID = (
  3            SELECT u.RID FROM Users u, Customers c
  4            WHERE u.ID = 'USER1'
  5            AND c.ID = 'ENRCUSTIRACHWIRE'
  6            AND u.customerkey = c.RID)
  7        AND isdisabled = 1;

no rows selected

SQL> rollback;

Rollback complete.

SQL>  select  ISDISABLED from users
  2   WHERE RID = (
  3             SELECT u.RID FROM Users u, Customers c
  4             WHERE u.ID = 'USER1'
  5             AND c.ID = 'ENRCUSTIRACHWIRE'
  6             AND u.customerkey = c.RID)
  7         AND isdisabled = 1;

ISDISABLED
----------
         1


-- test2:

SQL> connect username/password@qa9i
Connected.
SQL> select  ISDISABLED from users
  2  WHERE RID = (
  3            SELECT u.RID FROM Users u, Customers c
  4            WHERE u.ID = 'USER1'
  5            AND c.ID = 'ENRCUSTIRACHWIRE'
  6            AND u.customerkey = c.RID)
  7        AND isdisabled = 1;

ISDISABLED
----------
         1

-- try to do update through dynamic sql, it failed:

SQL> set serveroutput on
SQL> declare
  2      l_User_SName  VARCHAR2(20);
  3      l_Org_SName   VARCHAR2(20);
  4      l_PR_user_rid  NUMBER :=-999;   
  5  begin
  6      l_User_SName :='USER1';
  7      l_Org_SName:=   'ENRCUSTIRACHWIRE';
  8          EXECUTE IMMEDIATE '
  9          UPDATE Users SET isdisabled = 0 
 10          WHERE RID = (
 11              SELECT u.RID FROM Users u, Customers c
 12              WHERE u.ID = :User_SName
 13              AND c.ID = :Org_SName
 14              AND u.customerkey = c.RID)
 15          AND isdisabled = 1
 16          returning  RID into :rid'
 17          USING IN l_User_SName, IN l_Org_SName
 18          returning into l_PR_user_rid  ;
 19       dbms_output.put_line('sql%rowcount='|| sql%rowcount);
 20       dbms_output.put_line('l_PR_user_rid ='|| l_PR_user_rid  );
 21  end;
 22  /
sql%rowcount=0
l_PR_user_rid =-999

PL/SQL procedure successfully completed.

SQL> select  ISDISABLED from users
  2  WHERE RID = (
  3            SELECT u.RID FROM Users u, Customers c
  4            WHERE u.ID = 'USER1'
  5            AND c.ID = 'ENRCUSTIRACHWIRE'
  6            AND u.customerkey = c.RID)
  7        AND isdisabled = 1;

ISDISABLED
----------
         1
why my dynamic sql statement failed?

Other related posts:
