Re: what is wrong with my PL/SQL program?

The Grant statement is considered a DDL, and DDL statements cannot be used in PL/SQL blocks because at compile time, Oracle has no guarantee that the objects referenced by the DDL statement will continue to exist at execution time. You can get around that problem by using the DBMS_SQL dynamic SQL package which defers parsing of any SQL statements until execution time.

Not sure what your second question is, but for other users to use stored procedures, you must grant execute on the procedure to all the other users (or to public) and if you don't wish to have to qualify the stored procedure with the owners' name, you have to create synonyms for all the other users.

...Ru

Johnson wrote
>Hi,;
> I have made a stored procedure for some work. For public use, I
have to
>grant some rights to the person who execute the sql script. So I
wrote the
>codes below:
>
>declare
>username varchar2;
>begin
>select user into username from dual; -- get the current user name
>grant alter user to username; -- grant the right to the user ***
>end;
>
>when I run the script, I was always told there was something wrong
with the
>grant sentence. So what is wrong with it?
>
>Also, I have another question about the stored procedure and
function.
>I created a stored procedure under UserA, now I would like to call
it from
>UserB, so I wrote like this in the program. "
UserA.procedure_name", but
>I was always told not be able to find the program, so I have to
create a
>public synonym for the stored procedure, and then use this synonym
instead
>of the original procedure name. But I can directly use the above
>"UserA.procedure_name" manner to call another user's procedure in
SQLPlus,
>why can't I do it in Forms?
>
>Thanks in advance.
>Johnson Chao
>zhao_at_ctc-g.co.jp
>
Received on Mon Mar 29 1999 - 14:25:12 CST