Re: Using dynamic SQL in Oracle 8i

Hi

For 8i then the equivalent is Execute immediate. For prior versions you'll need to look at dynamic sql and the dbms_sql package.

hth

--
Niall Litchfield
Oracle DBA
Audit Commission UK
"ADP" <panakal_at_bigfoot.com> wrote in message
news:8skvq7$488_at_news.or.intel.com...


> 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

>

>