Re: creating synonyms with pl/sql
Date: 1997/02/22
Message-ID: <330e5431.2893380_at_nntp.mediasoft.net>#1/1
On Fri, 21 Feb 1997 12:29:56 +0100, Corrado Campigotto <corrado_at_kom.id.ethz.ch> wrote:
>I've got a problem while creating a synonym in
>a pl/sql script. How can I set 'create synonym' in
>an pl/sql program ? Could you please tell me all
>the possible usages of the 'create' statement in
>pl/sql ?
>
>corrado
>
>corrado_at_kom.id.ethz.ch
I use a small procedure such as:
create or replace
function execute_immediate( stmt in varchar2 )
return number
as
exec_cursor integer default dbms_sql.open_cursor;
rows_processed number default 0;
begin
dbms_sql.parse(exec_cursor, stmt, dbms_sql.native );
rows_processed := dbms_sql.execute(exec_cursor);
dbms_sql.close_cursor( exec_cursor );
return rows_processed;
exception
when others then
if dbms_sql.is_open(exec_cursor) then
dbms_sql.close_cursor(exec_cursor);
end if;
raise;
end;
/
to do dynamic DDL and Insert/Update/Delete type statements. for example, you'll be able to :
create procedure foo
as
n number;
begin
n := execute_immedate( 'create synonym bar for foobar' );
end;
/
NOTE: when you execute a procedure NO ROLES are ever enabled. The OWNER of the above procedure must have the privelege to create the synonym . You should test your privelege set by issuing "set role none" to see if you can do what you need to do in sql*plus first. For example:
SQL> set role none;
Role set.
SQL> create synonym foo for all_objects;
create synonym foo for all_objects
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> set role all;
Role set.
SQL> grant create synonym to tkyte;
Grant succeeded.
SQL> set role none;
Role set.
SQL> create synonym foo for all_objects; Synonym created.
As you can see, you will need the create synonym privelege granted to the OWNER
of the procedure....
Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com
- Check out Oracle Governments web site! -----
Follow the link to "Tech Center"
and then downloadable Utilities for some free software...
statements and opinions are mine and do not necessarily reflect the opinions of Oracle Corporation Received on Sat Feb 22 1997 - 00:00:00 CET