Re: Can some body help me with changing to the oracle sp.

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Thu, 03 Nov 2005 21:07:14 +0100
Message-ID: <dkdqla$8l3$00$1@news.t-online.com>

Frank van Bortel schrieb:

> Vinay Bhushan wrote:
>

>>I need help in reconstructing the oracle stored procedure as this
>>involves lot of global temporaty tables can any one help me with the
>>code.
>>

>>sqlStmt:= 'CREATE GLOBAL TEMPORARY TABLE DATA_VIEW ' ;
>>sqlStmt:= sqlStmt ||'ON COMMIT DELETE ROWS ' ;
>>sqlStmt:= sqlStmt ||'AS SELECT * FROM :VIEW_NAME ';
>>sqlStmt:= sqlStmt ||'WHERE ID =: PFILTER_TXT ';
>>
>> FOR i IN 0..(LENGTH(sqlStmt)-1)/80 LOOP
>> DBMS_OUTPUT.PUT_LINE(SUBSTR(sqlStmt, (i*80) + 1, 80));
>> END LOOP;
>>
>>EXECUTE IMMEDIATE sqlStmt USING VIEW_NAME,PFILTER_TXT ;
>>
>>but this dosnt work can any one help regarding this.
>>

> 
> 
> Sjees - who wrote that?!?
>

> What does "does not work" mean?

I believe you are right in the suggestion - this wasn't manually checked and "but this dosnt work" relates to the fact , that this "code" produces syntactically wrong SQL which is tried to be executed immediately.

To OP:
From
http://download-uk.oracle.com/docs/cd/B19306_01/appdev.102/b14261/dynamic.htm#i14257 <quote>
You can only use placeholders in places where you can substitute variables in the SQL statement, such as conditional tests in WHERE clauses. You cannot use placeholders for the names of schema objects. For the right way, see "Passing Schema Object Names As Parameters". </quote>

It seems to me, however, to write appropriate Oracle procedure from scratch requires much less efforts than this attempt to adopt a SQL Server stored procedure.

Best regards

Maxim Received on Thu Nov 03 2005 - 14:07:14 CST