Re: Does anyone help me?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 06 Aug 1999 14:10:29 GMT
Message-ID: <37b2ec74.8125614@newshost.us.oracle.com>

A copy of this was sent to sakshart_at_thaimail.com (if that email address didn't require changing) On Fri, 06 Aug 1999 11:07:36 GMT, you wrote:

>Problem: I want this procedure
>procedure create_table( str in varchar2)
>begin
> .... ( I want to know how to do this )
>end create_table;
>That works like this..
>sQL>create_table('create table t1 (n number, d number)');
>It pass parameter create table command as string and It creates
>table that correspond to its parameter..
>Does anyone can solve this problem?
>It's cool....
>Please keep me up to date...
>sakshart_at_thaimail.com
>
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.

create or replace procedure execute_immediate( sql_stmt in varchar2 ) as

    exec_cursor integer default dbms_sql.open_cursor;     rows_processed number default 0;
begin

    dbms_sql.parse(exec_cursor, sql_stmt, dbms_sql.native );
    rows_processed := dbms_sql.execute(exec_cursor);
    dbms_sql.close_cursor( exec_cursor );

end;
/

begin

    execute_immediate( 'create table scott.t ( x int primary key )' ); end;
/

but bear in mind that roles are never enabled during the execution of a procedure except in the special case of Invokers Rights which is a new feature in Oracle8i, release 8.1.

This fact is documented application developers guide:

<quote>
Privileges Required to Create Procedures and Functions

To create a stand-alone procedure or function, or package specification or body, you must meet the following prerequisites:

• You must have the CREATE PROCEDURE system privilege to create a procedure or package in your schema, or the CREATE ANY PROCEDURE system privilege to create a procedure or package in another user’s schema.

Attention: To create without errors, that is, to compile the procedure or package successfully, requires the following additional privileges: The owner of the procedure or package must have been explicitly granted the necessary object privileges for all objects referenced within the body of the code; the owner cannot have obtained required privileges through roles.

If the privileges of a procedure’s or package’s owner change, the procedure must be reauthenticated before it is executed. If a necessary privilege to a referenced object is revoked from the owner of the procedure (or package), the procedure cannot be executed.
</quote>

Try this:

SQL> set role none;
SQL> "statement you want to test to see if it'll work in a procedure"

If you can do it in plus with no roles you can do it in a procedure. If you can't, you must have the privelege from a role and hence won't be able to do it in a procedure.

If you get an error, you probably have the privelege to do what you are trying to do in the procedure via a role. Grant the privelege directly to the owner of the procedure and it'll work.

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Aug 06 1999 - 09:10:29 CDT