Home » SQL & PL/SQL » SQL & PL/SQL » Any way to parameterize the following code
Any way to parameterize the following code [message #247420] Mon, 25 June 2007 18:15 Go to next message
dpong
Messages: 73
Registered: January 2007
Member
Any way to parameterize the following code to execute the CREATE TABLE only when there're records in the table.

BEGIN
...
		drop_table('MANUAL_EMEA_TECH_' || rundate);
		EXECUTE IMMEDIATE 'CREATE TABLE MANUAL_EMEA_TECH_' || rundate || ' 
		 NOLOGGING AS SELECT * FROM MANUAL_PULL_' || rundate || ' WHERE region = ''EMEA''
		 AND PRODUCT_CATEGORY = ''TECH'' AND (COUNTRY_ID NOT IN (38, 225) OR 
		 INSTR(DISPOSITION,''SIEBEL'')>0)';

...
END;
Re: Any way to parameterize the following code [message #247422 is a reply to message #247420] Mon, 25 June 2007 18:53 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Yes, but this is really, really, REALLY bad idea for any number of reason.
IMO, you would really, really benefit from learning how to design tables into Third Normal Form.
Even if/when you succeed in creating tables on the fly, you'll seriously regret doing later on while trying to maintain & expand the application.
By doing so you are FORCING the application to ALWAYS resort to EXECUTE IMMEDIATE which forces HARD parses & precludes use of bind variables.

Bad, Bad, BAD, HORRIBLE idea!

[Updated on: Mon, 25 June 2007 19:00] by Moderator

Report message to a moderator

Re: Any way to parameterize the following code [message #247581 is a reply to message #247420] Tue, 26 June 2007 07:40 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
What anacedent said !!

Just DON'T do it
Re: Any way to parameterize the following code [message #247645 is a reply to message #247581] Tue, 26 June 2007 11:32 Go to previous messageGo to next message
dpong
Messages: 73
Registered: January 2007
Member
Okay, let me change the way I'm asking about this.

Could I use cursor attribute on cusor with one parameter being passed in ?



DECLARE 
rundate VARCHAR2(9) DEFAULT '000000';
rundate_t VARCHAR2(9) DEFAULT '000000';
--v_rec MANUAL_PULL_Template%ROWTYPE;  
--v_rec2 MANUAL_PULL_TEMPLATE%ROWTYPE;
v_rc   Sys_RefCursor;

CURSOR manual_tech_cur (region_n IN CHAR)
	   IS 
	   	  SELECT * FROM MANUAL_PULL_Template
		  WHERE REGION = region_n
		  AND PRODUCT_CATEGORY = 'TECH'
		  AND (
		  	   COUNTRY_ID NOT IN (38, 225) OR 
		  	   INSTR(DISPOSITION,'SIEBEL')>0
			  );
			    
CURSOR manual_apps_cur (region_n IN CHAR)
	   IS 
	   	  SELECT * FROM MANUAL_PULL_Template
		  WHERE REGION = region_n
		  AND PRODUCT_CATEGORY = 'APPS'
		  AND (
		  	   COUNTRY_ID NOT IN (38, 225) OR 
		  	   INSTR(DISPOSITION,'SIEBEL')>0
			  );
BEGIN
SELECT Var_Value INTO rundate
  FROM OUTPUT_PROCESS_VARIABLES
 WHERE Var_Name = 'rundate';
SELECT Var_Value INTO rundate_t
  FROM OUTPUT_PROCESS_VARIABLES
 WHERE Var_Name = 'RUNDATE_T';

OPEN manual_tech_cur ('EMEA') ;
	 If manual_tech_cur ('EMEA')%ROWCOUNT > 0
	 THEN
 		 drop_table('MANUAL_EMEA_TECH_' || rundate);
 	     EXECUTE IMMEDIATE 'CREATE TABLE MANUAL_EMEA_TECH_' || rundate || ' 
		 NOLOGGING AS SELECT * FROM MANUAL_PULL_' || rundate || ' WHERE region = ''EMEA''
		 AND PRODUCT_CATEGORY = ''TECH'' AND (COUNTRY_ID NOT IN (38, 225) OR 
		 INSTR(DISPOSITION,''SIEBEL'')>0)';
	 END IF;   

CLOSE manual_tech_cur ;
END;

[Updated on: Tue, 26 June 2007 11:37]

Report message to a moderator

Re: Any way to parameterize the following code [message #247648 is a reply to message #247420] Tue, 26 June 2007 11:58 Go to previous message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2444907911913
Describes bind variable usage & benefits as opposed to brute force SQL
Previous Topic: Need distinct on 1 table
Next Topic: Deadlock on delete from same table
Goto Forum:
  


Current Time: Sat Dec 10 11:07:43 CST 2016

Total time taken to generate the page: 0.25452 seconds