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

Before I could do anything about it, unknown (Mike) wrote:

>Hi!

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?
Well, you need to be using version 7.1.3 of higher to do this: call the DBMS_SQL package to format a DDL command from inside your stored procedure. This will allow you to pass the name of your table as a parameter.

You could also do this in a Pro*C program.

>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??
The only way to accomplish this is also from the DBMS_SQL package or by using the Pro*C program.

If you opt for the DBMS_SQL package, keep in mind that dynamic SQL is a really fun and usefulthing, but really confusing at times as well. I suggest a good bit of experimentation before you trust a live app with your skills.

>Grateful for answers since at the moment I seem to be stucked.
Hope this was helpful. If you have any other questions, you can respond via e-mail and I will do my best to help.

Jonathan
jingram_at_teleport.com
jonathan.ingram_at_attws.com Received on Sat Jun 22 1996 - 00:00:00 CEST