Re: stored procedure

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 11 Jun 1999 11:11:48 GMT
Message-ID: <3760eae3.1329521@newshost.us.oracle.com>

A copy of this was sent to "Gennady" <gbesfamilny_at_home.com> (if that email address didn't require changing) On Fri, 11 Jun 1999 02:56:08 GMT, you wrote:

>Hello,
>What is wrong with this stored procedure?
>
>create procedure GB.sp_empty
>as begin
>spool D:\project\DBdata\y.sql
>select 'drop ' || object_type || ' ' || owner || '.' || object_name || ';'
>from all_objects
>where owner='GB'
>and object_type in ('TABLE','VIEW')
>spool off
>@D:\project\DBdata\y.sql;
>end;
>
>How could I fix it?
>
>Gennady
>
>

You are mixing DDL with SQLPLUS commands. It looks like you are trying to write a procedure to drop all tables and views for a user. 2 problems

• you are combining DDL (create) with SQLPLUS (spool) commands.
• doing ddl in a procedure requires you use dynamic sql.

If you are trying to write a SQLplus script to drop all tables/views from a schema, something like:

set heading off
set feedback off
set linesize 100
set trimspool on
set echo off
spool tmp.sql

select 'drop ' || object_type || ' ' || owner || '.' || object_name ||

       decode( object_type, 'TABLE', ' cascade constraints;', ';' )   from all_objects
 where owner='TKYTE'
   and object_type in ('TABLE','VIEW')
/

spool off
set echo on
set heading on
set feedback on
@tmp

will do it (no stored procedures, just a temporary script). If you wanted to do this in a stored procedure, the procedure might look like:

SQL> create or replace procedure drop_tables_views( p_owner in varchar2 )   2 as

  3      exec_cursor     integer default dbms_sql.open_cursor;
  4      rows_processed  number  default 0;
  5          sql_stmt                varchar2(1024);
  6  begin
  7          for x in ( select object_name, object_type,
  8                   decode(object_type,'TABLE',' cascade constraints','') c
  9                                   from all_objects
 10                                  where owner = upper(p_owner)
 11                                    and object_type in ( 'TABLE', 'VIEW' ) )
 12      loop
 13                  sql_stmt := 'drop ' || x.object_type || ' ' || p_owner ||
 14                               '.' ||  x.object_name || x.c;
 15                  dbms_output.put_line( sql_stmt );
 16          dbms_sql.parse(exec_cursor, sql_stmt, dbms_sql.native );
 17          rows_processed := dbms_sql.execute(exec_cursor);
 18          end loop;
 19          dbms_sql.close_cursor( exec_cursor );

 20 end;
 21 /

Procedure created.

SQL> set serveroutput on
SQL> exec drop_tables_views( 'GB' )

See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'... Mirrored (and more current) at http://govt.us.oracle.com/~tkyte/

Current article is "Fine Grained Access Control", added June 8'th  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

--
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Jun 11 1999 - 06:11:48 CDT