On dynamic sql statement *urgent*

[yukeshean]

Dear all,
Can i build an sql statement dynamically?
For instance, sub_acc is my table name, and....I need some sql statement (simplified) like this:
'select * from sub_acc where <select-condition>' and the <select-condition> is a long string like 'columnA= valueA and columnB = valueB'. It means that the <select-condition> varies in different situation. Can it be done????
This is becoz i need it in a search account scenario, where i have five condition for the user to choose to search.
If the user search by condition 1 and 3, my sql statement...the <select-condition> will be something like 'column1=valueabc and column3=valuebcd' (valueabc, and valuebcd is passed in as ijn parameter).
Can you get it?
Do you have other approach on doing this???
Plaese do give me some advice asap, ok? Thanks very much!

yukeshean

 

[lewisp]

You need to use PL/SQL and the DBMS_SQL package which allows you to use dynamic sql.

 

[DoubleH]

Hai,
If you are working i Oracle 8i,then this will be an easier implementation.This implementation is called as NDS method.
step 1 : Declare a ref cursor => ref_cur
step 2 : create a type for that ref cursor => ref_type
step 2b: store the where cond in a variable => where_var
step 3 : Open ref_type into select ......|| where_var
step 4 : loop
step 5 : fetch
step 6 : end loop
For more information on this, just check out the documentation.
Note: This will work only in oracle 8i.

Thanks&Regards,
DoubleH

 

[jee]

BTW: NDS stands for Native Dynamic SQL (will make it easier for you to lookup).