Re: PL/SQL : Truncate Table
Date: Sat, 07 Aug 1999 14:54:35 GMT
Message-ID: <37ad4893.2755802_at_newshost.us.oracle.com>
A copy of this was sent to Syed Salman Shakir <ssalman_at_cyber.net.pk> (if that email address didn't require changing) On Sat, 07 Aug 1999 16:42:16 +0500, you wrote:
>Hi all,
>We use the command
>
> SQL> truncate table <table_name>
>
>at the SQL prompt to delete the values from the table and automatically
>commit it.
>
>Now I want to use the same command in PL/SQL block but it does not work.
>
>It gives the following error.
>
> ORA-06550: line 4, column 12:
> PLS-00103: Encountered the symbol "TABLE" when expecting one of
>the following:
> := . ( _at_ % ;
>
>I know there is a package we use for the alternate of TRUNCATE TABLE
>command that
>can be use in the PL/SQL block.
>
>Kindly help me.
>
>Thanks in advance.
>
>Regards,
>Salman
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( 'truncate table t' );
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 CorporationReceived on Sat Aug 07 1999 - 16:54:35 CEST