Re: Help needed on DBMS_SQL

From: Jonathan Gennick <gennick_at_worldnet.att.net>
Date: 14 Aug 1998 02:52:27 GMT
Message-ID: <35d9314d.14495983@netnews.worldnet.att.net>

Two things I can think of. When you create a stored

function, that function executes as the "owner" of the
function, not as the "caller". Thus if USERA owns the
function, then it is USERA that is creating the synonym,

even though it may be USERB that is calling the function.

The other thing to look at is to be sure that the function owner has EXECUTE privs to the DBMS_SQL package.

On 12 Aug 1998 14:42:11 GMT, "Maarten Scharroo" <m.scharroo_at_elsevier.nl> wrote:

>Hi,
>
>I try to use dbms_sql to create a synonym.
>
>Code:
>
>DECLARE
>
> C1 INTEGER;
> Dummy INTEGER;
> CommandStr VARCHAR2(240);
>
>
>BEGIN
> C1 := dbms_sql.open_cursor;
> CommandStr := 'create synonym elap_actions_current for
>elap_actions_sep';
> dbms_sql.parse( C1, CommandStr, dbms_sql.v7 );
> dbms_sql.execute( C1 );
> dbms_sql.close_cursor(C1);
>END;
>/
>
>This works....
>
>Put the same code in a packaged function yields....
>
>ORA-01031: insufficient privileges
>
>The user has the CREATE SYNONYM privilege granted.........
>
>Thanks in advance
>
>Maarten
Received on Thu Aug 13 1998 - 21:52:27 CDT