Using dynamic SQL in Oracle 8i

Hello,

I'm a complete Oracle newbie, I'm trying to translate a T-SQL (the SQL dialect of MS SQL Server) into PL/SQL, and I'd really like some guidance.

What I'm trying to achieve is to write a stored procedure that takes a single VARCHAR2 parameter--which contains a SQL statement--and 'executes' it. In T-SQL it would look something like this:

• 'text' is the T-SQL version of Oracle's CLOB data type

create procedure spExecuteSQL ( @SQL text ) as

    execute ( @SQL )

That's it. The keyword 'execute' is overloaded to either execute a compile time resolvable statement...or a string whose contents can be resolved only at runtime.

Of course, @SQL caould contain a resultset-returning statement...but for the Oracle translation I'm willing to forego that. @SQL will be just 'inserts' or 'updates'. Important, though, @SQL could be a 'batch' (what Oracle calls a 'script'?), may declare variables and the like, and include calls to other stored procedures.

So, back to the question, could somebody tell me the text of an Oracle stored procedure that accepts a string and executes it?

Thanx

--A
Received on Wed Oct 18 2000 - 15:05:30 CDT