FW: help about dynamical sql

From: Powell, Mark D <mark.powell_at_eds.com>
Date: Mon, 10 Jul 2006 10:58:32 -0400
Message-ID: <5A14AF34CFF8AD44A44891F7C9FF410507D30FE7@usahm236.amer.corp.eds.com>

Forgot to copy the list.

---

	From: Powell, Mark D 
	Sent: Monday, July 10, 2006 10:58 AM
	To: 'xiaoyezi.xyz_at_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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of xiaoyan

		Sent: Monday, July 10, 2006 10:45 AM
		To: oracle-l_at_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
		

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Jul 10 2006 - 09:58:32 CDT