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

unknown (Mike) wrote:

>Hi!
>I'm working on a data link between Visual Basic Client-programs
>through an Oracle (7.1) Data Base to a Unisys Mapper-system.
>This involves stored procedures in Oracle communicating with
>a C Batch program through DBMS_PIPE.
>However, since I´m not a very experienced Oracle User I have some
>problems with the stored procedures.
>
>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??
>
>Grateful for answers since at the moment I seem to be stucked.
>
>Michael Stenfelt
>Sweden
>

I can't add much to the advice you've gotten already, but you may want to keep in mind that issuing ANY DDL inside a stored procedure immediately performs an IMPLICIT commit. Any open tranactions (cursors) will be closed. All you have to do is place the DDL statements at the beginning of your procedures to avoid this.

For example, don't open a cursor then issue a create table statement. It'll really screw up the works.

Jim Joslin
BellSouth Telecommunications Received on Mon Jul 01 1996 - 00:00:00 CEST