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

From: Ivan Samuelson <bolski_at_indy.net>
Date: 1996/07/03
Message-ID: <4rea65$e6e_at_news.indy.net>#1/1

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 doesn´t 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 wholeheartidly.  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 Wed Jul 03 1996 - 00:00:00 CEST