CODEWITHSUNDEEP
oracle-database
suuuch
suuuch

Reputation: 5

Oracle EXECUTE IMMEDIATE error

There is UserA and UserB in my oracle.

This is the package from UserA:

CREATE OR REPLACE PACKAGE BODY pkgA AS
    PROCEDURE procA 
    AS 
      l_sql = 'BEGIN ' || UserB.procB || (:l_v1,:l_v2) END;';
      EXECUTE IMMEDIATE l_sql USING IN l_v1,IN l_v2;
    END;

Thie procB is come from UserB;

When I run this, I get the error:

PLS-00201:IDENTIFIER 'UserB.procB' must be declared;

Upvotes: 0

Views: 197

Answers (2)

Alex Poole
Alex Poole

Reputation: 191265

Unless it's supplied as a parameter, the procedure name needs to be inside the string as a fixed value; you have it outside so it's trying to be interpreted as a variable name, which doesn't exist:

l_sql = 'BEGIN UserB.procB(:l_v1,:l_v2) END;';

But then you wouldn't need to execut it dynamically anyway, you could just do:

PROCEDURE ProcA AS
BEGIN
  UserB.procB(l_v1, l_v2);
END;

If you were passing the procedure as a variable, which would be a little odd, you'd have something like:

PROCEDURE procA (proc_name in varchar2) AS 
BEGIN
  l_sql = 'BEGIN ' || proc_name || '(:l_v1,:l_v2) END;';
  EXECUTE IMMEDIATE l_sql USING IN l_v1,IN l_v2;
END;

... and you'd call that as procA('UserB.procB'). I don't think that's what you're trying to do, but it isn't entirely clear.

In both cases you don't seem to have l_v1 or l_v2 defined, so I guess you've just missed that part of the code out.

Upvotes: 0

schurik
schurik

Reputation: 7928

User A need the EXECUTE right on the userb.procB.

grant the right as User B:

grant execute on UserB.procB to userA;

Upvotes: 1

Related Questions