We help IT Professionals succeed at work.

What is the equivalent of the following SQL statement

TECH_NET
TECH_NET asked
on
1,675 Views
Last Modified: 2013-12-19
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.
Comment
Watch Question

This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Commented:

declare 
  sql varchar2(4000);
begin
  sql='SELECT *  FROM '||TABLE_NAME||' WHERE DISPLAY_STATUS_ID=1 ORDER BY NAME ASC';
  execute immediate sql;
end;

Open in new window

Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
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.
johnsoneSenior Oracle DBA
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
@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.
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;

Open in new window

Author

Commented:
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.
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
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.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.