Re: CREATE/TRUNCATE in PL/SQL???

Using the truncate in PL/SQL using dynamic sql works fine for me. Rich

In <4rea65$e6e_at_news.indy.net>, bolski_at_indy.net (Ivan Samuelson) writes:
>Hi Mike.
>
>Mike (unknown) wrote:
>
>: 1. I need to store data in an Oracle Table with the (new) name
>: sent to the procedure as a parameter. But it seems I can't use
>: neither CREATE nor TRUNCATE in PL/SQL. I can use them in SQLPlus
>: but that doesn4t help me very much. What do I do?
 

>: 2. How do use a table name in a variable in for example a SELECT.
>: i.e. select * from mytable
>: where mytable is the table with the name contained in the variable
>: mytable??
>
>Both these problems can be solved using the DBMS_SQL database package.
>This package allows you to create "dynamic SQL" statements within PL/SQL.
>PL/SQL cannot use DDL (data definition language) commands such as CREATE,
>DROP, etc. I'm NOT sure about TRUNCATE working. I've not been able to do
>this successfully with PL/SQL. Has anyone else gotten the DBMS_SQL
>package to use TRUNCATE? It never seems to work for me.
>
>Also, using a variable to store a table name can also be used with the
>DBMS_SQL package. Item 1 can be accomplished as follows:
>
>PROCEDURE foo (p_table_name IN varchar2) IS
> cursor_handle INTEGER;
>
>BEGIN
> /* create a cursor to use for the synamic SQL */
> cursor_handle := DBS_SQL.OPEN_CURSOR;
>
> DBMS_SQL.PARSE (cursor_handle, 'CREATE TABLE ' || p_table_name ||
> '(col1 varchar2(1), col2 number(7))',
> DBMS_SQL.V7);
>END ;
>
>You're best bet would be to get the O'Reilly & Associates book Oracle
>PL/SQL Programming written by Steven Feuerstein. It is an EXCELLENT
>source for PL/SQL programmers and has helped me with many problems as
>well as teaching me a few new tricks. THis is a book I recommend whole-
>heartidly. And no, I don't work for O'Reilly. ;-)
>
>The ISBN for the book is 1-56592-142-9 if that helps.
>
>Good luck!
>
>--
>-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
>Ivan Samuelson, Consultant * Blue Ribbon Campaign Supporter
>Profound Consulting * bolski_at_indy.net
>http://indy.net/~profound * http://chaos.taylored.com/home/bolski/
>-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
>
Received on Tue Jul 09 1996 - 00:00:00 CEST