Re: Truncate in a procedure?

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 <ORACLE-L_at_fatcity.com> 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:a.disser_at_sfs.nl>
> ------------------------------------------------
>
> -----Oorspronkelijk bericht-----
> Van: Smith, Ron L. [mailto:rlsmith_at_kmg.com]
> 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
> rlsmith_at_kmg.com
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Smith, Ron L.
> INET: rlsmith_at_kmg.com
>
> 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: ListGuru_at_fatcity.com (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: a.disser_at_sfs.nl
>
> 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: ListGuru_at_fatcity.com (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: freeman_at_iquest.net

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: ListGuru_at_fatcity.com (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).