Home » SQL & PL/SQL » SQL & PL/SQL » how could this piece of code not working
how could this piece of code not working [message #254114] Wed, 25 July 2007 15:39 Go to next message
dpong
Messages: 73
Registered: January 2007
Member

got no errors. THere're data (from the table MANUAL_PULL_rundate) for region = 'EMEA'. But it's just that it is not generating the table as expected.

Please help!


SET SERVEROUTPUT ON
DECLARE 
rundate VARCHAR2(8) ;

CURSOR manual_cur (region_n IN CHAR)
	   IS 
	   	  SELECT * FROM MANUAL_PULL_Template
		  WHERE REGION = region_n
		  ;
BEGIN 
SELECT Var_Value INTO rundate
  FROM OUTPUT_PROCESS_VARIABLES
 WHERE Var_Name = 'rundate';

OPEN manual_cur ('EMEA') ;
	 If manual_cur%FOUND
	 THEN
 		 drop_table('MP_EMEA_' || rundate);
 	     EXECUTE IMMEDIATE 'CREATE TABLE MP_EMEA_' || rundate || ' 
		 NOLOGGING AS SELECT * FROM MANUAL_PULL_' || rundate || ' WHERE region = ''EMEA''' ;
         DBMS_OUTPUT.PUT_LINE ( 'TABLE MP_EMEA_' || rundate || 'created' );
	 END IF;   

CLOSE manual_cur;
END;
Re: how could this piece of code not working [message #254117 is a reply to message #254114] Wed, 25 July 2007 15:47 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
where's the fetch ?
Re: how could this piece of code not working [message #254120 is a reply to message #254117] Wed, 25 July 2007 15:54 Go to previous message
dpong
Messages: 73
Registered: January 2007
Member
I just realized just like 5 mins ago. Thanks!

Now it worked.
DECLARE 
rundate VARCHAR2(8) ;
row_manual MANUAL_PULL_Template%ROWTYPE;

CURSOR manual_cur (region_n IN CHAR)
	   IS 
	   	  SELECT * FROM MANUAL_PULL_Template
		  WHERE REGION = region_n
		  ;
BEGIN 
SELECT Var_Value INTO rundate
  FROM OUTPUT_PROCESS_VARIABLES
 WHERE Var_Name = 'rundate';

OPEN manual_cur ('EMEA') ;
    FETCH manual_cur INTO row_manual;
	 If manual_cur%FOUND
	 THEN
 		 drop_table('MP_EMEA_' || rundate);
 	     EXECUTE IMMEDIATE 'CREATE TABLE MP_EMEA_' || rundate || ' 
		 NOLOGGING AS SELECT * FROM MANUAL_PULL_' || rundate || ' WHERE region = ''EMEA''' ;
         DBMS_OUTPUT.PUT_LINE ( 'TABLE MP_EMEA_' || rundate || 'created' );
	 END IF;   

CLOSE manual_cur;
END;
Previous Topic: opening n ending balance
Next Topic: Bulk delete
Goto Forum:
  


Current Time: Fri Dec 02 16:57:23 CST 2016

Total time taken to generate the page: 0.29761 seconds