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

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

ORACLE 7.1 support dynamic SQL. There is package called dbms_sql, which will allow you to create/drop/truncate tables, Moreover it also allows you to create sql statements dynamically, meaning your mytable can hold any tablename.

Here is an example:

CREATE OR REPLACE procedure sql_exec(string IN VARCHAR2) AS

  CURSOR INTEGER;
  RETURN INTEGER; BEGIN   cursor := dbms_sql.open_cursor;
  dbms_sql.parse(cursor,string,dbms_sql.v7);   return := dbms_sql.execute(cursor);
  dbms_sql.close_cursor(cursor);

END;
/

SQL>declare

        tab_name varchar2(30);
     begin
         tab_name := 'TEMP';
         sql_exec('truncate table '||tab_name);
     end;

HTH Sanjay D. S.
Oracle Consultant
Prudential Securities, Inc. Received on Mon Jun 24 1996 - 00:00:00 CEST