Re: Using dynamic SQL in Oracle 8i

In article <8skvq7$488_at_news.or.intel.com>,   "ADP" <panakal_at_bigfoot.com> wrote:
> 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
>
>

Yes I could
create or replace procedure rtfm (sqlstr in varchar2 -- no other datatypes available)
begin
execute immediate :sqlstr;
end;
/
I assume my 'hint' is clear enough.
Question: why do almost all sqlserver types refuse to read any manual?

Regards,

--
Sybrand Bakker, Oracle DBA

All standard disclaimers apply
------------------------------------------------------------------------


Sent via Deja.com http://www.deja.com/
Before you buy.