Re: PL/SQL Catch*22?

From: Matthias Gresz <GreMa_at_t-online.de>
Date: 1997/11/07
Message-ID: <63ufq8$alq$1@news02.btx.dtag.de>#1/1

Jerry Apfelbaum wrote:
>
> Can anyone solve this conundrum?
>
> In a PL/SQL script, I want to create a temporary table, do some SQL
> operations on that table, then drop the table.
>
> Unfortunately, when Oracle goes to compile the PL/SQL, the table doesn't
> exist yet so I get a compile-time error and cannot run the script.
> Creating the table ahead of time defeats the purpose here; ie, I don't
> really want an unnecessary table hanging around. Also, using dynamic
> SQL for the DML is a bit of a crude workaround as I think that would
> make the usual PL/SQL DML operations rather cumbersome or even
> unworkable.
>
> Is this a Catch*22? Anyone got any ideas?
>
> Thanks in advance.
>
> --
> =================================================
> Jerry Apfelbaum email: japfelba_at_ican.ca
> Eastern Sun Group Inc. phone: 416.240.9695
> Toronto, Canada

Hi,

you've no choice than to use dynamic sql.

I wrote a procedure that has the SQL-Statement as input-parameter. It is based on a function from Thomas Kyte an oracle employee from US that I try to restorwe in suite.

procedure execute_immediate(stmt varchar2) is

	exec_cursor	integer default dbms_sql.open_cursor;
	rows_processed	number default 0;
begin
	dbms_sql.parse(exec_cursor, stmt, dbms_sql.native);
	rows_processed := dbms_sql.execute(exec_cursor);
	dbms_sql.close_cursor(exec_cursor);
exception
	when others then
	if dbms_sql.is_open(exec_cursor) then
		dbms_sql.close_cursor(exec_cursor);
	end if;
  	raise;

end execute_immediate
;

function execute_immediate(stmt varchar2) is

	exec_cursor	integer default dbms_sql.open_cursor;
	rows_processed	number default 0;
begin
	dbms_sql.parse(exec_cursor, stmt, dbms_sql.native);
	rows_processed := dbms_sql.execute(exec_cursor);
	dbms_sql.close_cursor(exec_cursor);
	return rows_processed;			-- I think it was this way.
exception
	when others then
	if dbms_sql.is_open(exec_cursor) then
		dbms_sql.close_cursor(exec_cursor);
	end if;
  	raise;

end execute_immediate
;

-- 
Regards

M.Gresz    :-)

Received on Fri Nov 07 1997 - 00:00:00 CST