Re: owner name as parameter in a stored proc?dure

Originally posted by Ferret
> Hello,
>
> I would like to pass the name on a owner as parameter in a stored
> procedure written in PL/SQL. How can I do that.
>
> Thanks for your help
>
> Pascal Ferret.

PROCEDURE p ( p_owner VARCHAR2 ) IS ...

Now what? Probably you want to SELECT from some table owned by that owner, or something like that. You need to use dynamic SQL. For example:

PROCEDURE p ( p_owner VARCHAR2 ) IS
  v_count NUMBER;
BEGIN
  EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM '||p_owner||'.emp' INTO   v_count;
  DBMS_OUTPUT.PUT_LINE(p_owner||'.emp contains '||v_count||' rows'); END;
/

SQL> exec p('TANDREWS')
TANDREWS.emp contains 14 rows

PL/SQL procedure successfully completed.

--
Posted via http://dbforums.com