why my dynamic sql statement failed?

From: <zlmei_at_hotmail.com>
Date: 15 Feb 2006 13:32:08 -0800
Message-ID: <1140039127.992566.106590@z14g2000cwz.googlegroups.com>

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?

BTW, I did grant the select and update priv. explicity.

Thanks.

Guang

• test1:

SQL> connect username/password_at_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_at_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 Received on Wed Feb 15 2006 - 15:32:08 CST