For Oracle 8.1.6, I have a few packages in which I have procedures which
issue the following statement:
EXECUTE IMMEDIATE 'TRUNCATE TABLE table_name';
The statement is usually issued in connection with wiping out any old report
contents from a table when a new report is requested.
The packages are created and owned by the schema owner and execute fine.
They can also be executed by our app_user as well by creating public
synonyms for the packages and granting EXECUTE on the package to the
app_user. The app_user then executes with privileges of the package owner
through the application.. Since the app_user does not have any truncate
privileges outside of these packages for the owner schema, app_user cannot
issue a truncate statement through application code. Code must use DELETE
dml statements (as delete has been granted to app_user for tables).
HTH,
Bryan
----- Original Message -----
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Sent: Friday, February 16, 2001 11:55 AM
> No, but you have to do it with dynamic sql like:
>
> PROCEDURE exec_sql(p_string IN VARCHAR2 )
> IS
> l_cursor_name INTEGER;
> l_help INTEGER;
> BEGIN
> l_cursor_name := dbms_sql.open_cursor;
> dbms_sql.parse(l_cursor_name, p_string, DBMS_SQL.V7);
> l_help := dbms_sql.execute(l_cursor_name);
> dbms_sql.close_cursor(l_cursor_name);
> END;
> END coa_sql;
>
> exec_sql('truncate table a_table');
>
> Met vriendelijke groet,
>
> /'^'\
> Arno ( o o )
> -----------------------oOOO--(_)--OOOo----------
>
> Arno Disser
> SFS/Pensioenen en Verzekeringen
> FAD/ICT/DBA
> Postbus 11
> 2280 AA RIJSWIJK The Netherlands
> Tel +31 (0)70 33 66 389
> Fax +31 (0)70 31 93 884
> email <mailto:[EMAIL PROTECTED]>
> ------------------------------------------------
>
> -----Oorspronkelijk bericht-----
> Van: Smith, Ron L. [mailto:[EMAIL PROTECTED]]
> Verzonden: vrijdag 16 februari 2001 16:50
> Aan: Multiple recipients of list ORACLE-L
> Onderwerp: Truncate in a procedure?
>
>
> I have a developer that is trying to truncate a table from within a
> procedure. If he does a delete it works ok. If he tries to issue the
> truncate command he gets errors. He is running the procedure as the
schema
> owner. Is there a problem with issuing a truncate command from within a
> procedure?
>
> Ron Smith
> Database Administration
> [EMAIL PROTECTED]
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Smith, Ron L.
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Disser, Arno
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Bryan Sirtosky
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
