| how could this piece of code not working [message #254114] |
Wed, 25 July 2007 15:39  |
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 #254120 is a reply to message #254117] |
Wed, 25 July 2007 15:54  |
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; |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
