TECH_NET
asked on
What is the equivalent of the following SQL statement
I have a SQL procedure that look like this
declare @sql nvarchar(4000)
SET @sql='SELECT * FROM ' + @TABLE_NAME + ' WHERE DISPLAY_STATUS_ID=1 ORDER BY NAME ASC'
exec sp_executesql @sql
I wish to convert this to ORACLE Query.
declare @sql nvarchar(4000)
SET @sql='SELECT * FROM ' + @TABLE_NAME + ' WHERE DISPLAY_STATUS_ID=1 ORDER BY NAME ASC'
exec sp_executesql @sql
I wish to convert this to ORACLE Query.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
As written your procedure can be executed but it won't do anything in oracle except consume resources because it merely executes a select statement but doesn't use the results.
As noted above, "execute immediate" is sort of the equivalent of sp_executesql. One significant difference is the way parameters are passed. Into the statement.
In your case you have no parameters (table is a substitution).
What are you trying to do? we can help you try to build an oracle procedure that aactually performs a useful operation.
As noted above, "execute immediate" is sort of the equivalent of sp_executesql. One significant difference is the way parameters are passed. Into the statement.
In your case you have no parameters (table is a substitution).
What are you trying to do? we can help you try to build an oracle procedure that aactually performs a useful operation.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@johnsone: dammit, you're right... I should've seen it coming since this is "select * from", which more often than not, is prone to produce more than one row as a result.
Perhaps the snippet below is the closest we can get to copying the behaviour of the original procedure (I did a bit of checking what "sp_executesql" is supposed to return) ... however, as sdstuber pointed out, it's still quite useless because we don't really know *what* was returned from the query.
The actual problem is that we need to know how to get data from the opened ref cursor; hence, we need to know what table we are querying, and what fields that table contains.
Perhaps the snippet below is the closest we can get to copying the behaviour of the original procedure (I did a bit of checking what "sp_executesql" is supposed to return) ... however, as sdstuber pointed out, it's still quite useless because we don't really know *what* was returned from the query.
The actual problem is that we need to know how to get data from the opened ref cursor; hence, we need to know what table we are querying, and what fields that table contains.
declare
v_table_name varchar2(30) /* 30 being the max length for table's name */
v_sql varchar2(4000);
rc sys_refcursor;
begin
v_table_name := 'DUAL'; /* the contents of variable v_table_name */
v_sql := 'SELECT * FROM '||v_table_name||' WHERE DISPLAY_STATUS_ID=1 ORDER BY NAME ASC';
open rc for v_sql;
/* here, we should handle the ref cursor "as we wish"... */
-- Actually, that means the here our problems only begin...
end;
ASKER
sdstuber:
I have a Keyword List table eg KEYWORD_COUNTRIES, KEYWORD_US_STATES etc
I want to retrieve the data for the table passed since the structure of these tables stay the same.
I have a Keyword List table eg KEYWORD_COUNTRIES, KEYWORD_US_STATES etc
I want to retrieve the data for the table passed since the structure of these tables stay the same.
and then what are you doing with that data?
Any of the previous solutions are correct, however each with their own caveat, the biggest being, what are you doing with the data you query.
Processing row-by-row should be avoided if you can.
Any of the previous solutions are correct, however each with their own caveat, the biggest being, what are you doing with the data you query.
Processing row-by-row should be avoided if you can.
Open in new window