---

From: Powell, Mark D
Sent: Monday, July 10, 2006 10:58 AM
To: 'xiaoyezi.xyz@163.com'
Subject: RE: help about dynamical sql

How about using PL/SQL IF statement to test parameters passed in and based on the target table and parameters build the SQL statement you wish to execute using a combination of the passed parameters and constants concatenated together.  Then pass the constructed SQL statement to execute immediate.

 

See the PL/SQL manual entry for execute immediate.

 

HTH -- Mark D Powell --

 

 

---

From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of xiaoyan
Sent: Monday, July 10, 2006 10:45 AM
To: oracle-l@freelists.org
Subject: help about dynamical sql

Hi,all:

  In my project:I have to use dynamical sql to create a trigger,

suppose in one of my tests of dynamical sql,date information is like this:

   c_columnname='courses_score';

   :new.courses_score=10;

   fathertable=scores;

   f_columnname=score;

 

Then when executing dynamically the following statement

    select DESCRIPTIONl into variables_pkg.myinfo  from '|| fathertable||'where '|| f_columnname||'=:new.'||c_columnname||';

we will get    

    select DESCRIPTIONl into variables_pkg.myinfo  from  scores where score=:new.courses_score;

But if I want to get the following statement:

    select DESCRIPTIONl into variables_pkg.myinfo  from  scores where score=10;

 

Then  how to construct the dynamical sql?

Any idea?

Thank you in advance!

Best Regards