| Procedure_urgent [message #230672] |
Thu, 12 April 2007 10:06  |
Priya_reddy
Messages: 11
Registered: April 2007
|
Junior Member |
|
|
I have to write procedure .
The procedure should have logic to produce a generic message written to the same target as the difference reports, when the reference table name does not have a corresponding procedure for refresh in Oracle
procedure for each table that will compare the data of the non-prefixed table to the RL prefixed table.
Please give me some clue.
Priya
|
|
|
|
|
|
| Re: Procedure_urgent [message #230688 is a reply to message #230672] |
Thu, 12 April 2007 10:38  |
Priya_reddy
Messages: 11
Registered: April 2007
|
Junior Member |
|
|
Will you give me some clue how can I solve it.
So I have to write procedure that procedure should have logic to produce a generic message written to the same target as the difference reports, when the reference table name does not have a corresponding procedure for refresh in Oracle.
Below is the list of reference tables under the GTN_MCC schema. In the same schema with RL_ prefixing the table names are the master reference tables that will be periodically refreshed for comparison to the list of tables below.
After the refresh of the RL_ table occurs a process in Teradata will invoke the Oracle stored procedure to do the comparison. There are several dozen tables in Teradata and only the nine tables listed below in Oracle.
Oracle Oracle
Schema: GTN_MCC Schema: GTN_MCC
Reference Tables Refresh Master Tables
CTRY RL_CTRY
ST_PRVNC RL_ST_PRVNC
TRNSPRN_EVNT_CD RL_TRNSPRN_EVNT_CD
CTRCT_TRM RL_CTRCT_TRM
STD_TRNST_TM RL_STD_TRNST_TM
HLDY RL_HLDY
|
|
|
|
| Re: Procedure_urgent [message #230695 is a reply to message #230672] |
Thu, 12 April 2007 10:49 |
Priya_reddy
Messages: 11
Registered: April 2007
|
Junior Member |
|
|
Reference Tables Refresh Master Tables
CTEM RL_CTEM
ST_PRVNC RL_ST_PRVNC
Tllm_EVNT_Cl RL_Tllm_EVNT_Cl
CTRCT_TRM RL_CTRCT_TRM
Above is the list of reference tables under the LTB_CCC schema. In the same schema with RL_ prefixing the table names are the master reference tables that will be periodically refreshed for comparison to the list of tables below. After the refresh of the RL_ table occurs a process in Teradata will invoke the Oracle stored procedure to do the comparison.
So I have to write procedure that will receive the table name as an input parameter and will invoke a similarly named procedure. The procedure should have logic to produce a generic message written to the same target as the difference reports, when the reference table name does not have a corresponding procedure for refresh in Oracle
Please give me some hints how I will write
Priya
|
|
|
|
| Re: Procedure_urgent [message #230697 is a reply to message #230695] |
Thu, 12 April 2007 10:53 |
Priya_reddy
Messages: 11
Registered: April 2007
|
Junior Member |
|
|
| Priya_reddy wrote on Thu, 12 April 2007 10:49 |
Reference Tables
MTTN
ST_llp
Refresh Master Tables
RL_MTTN
ST_IIP
Above is the list of reference tables under the LTB_CCC schema. In the same schema with RL_ prefixing the table names are the master reference tables that will be periodically refreshed for comparison to the list of tables below. After the refresh of the RL_ table occurs a process in Teradata will invoke the Oracle stored procedure to do the comparison.
So I have to write procedure that will receive the table name as an input parameter and will invoke a similarly named procedure. The procedure should have logic to produce a generic message written to the same target as the difference reports, when the reference table name does not have a corresponding procedure for refresh in Oracle
Please give me some hints how I will write
Priya
|
|
|
|
|
| Re: Procedure_urgent [message #230721 is a reply to message #230688] |
Thu, 12 April 2007 11:37 |
Priya_reddy
Messages: 11
Registered: April 2007
|
Junior Member |
|
|
| Priya_reddy wrote on Thu, 12 April 2007 10:38 | Will you give me some clue how can I solve it.
So I have to write procedure that procedure should have logic to produce a generic message written to the same target as the difference reports, when the reference table name does not have a corresponding procedure for refresh in Oracle.
Below is the list of reference tables under the GTN_MCC schema. In the same schema with RL_ prefixing the table names are the master reference tables that will be periodically refreshed for comparison to the list of tables below.
After the refresh of the RL_ table occurs a process in Teradata will invoke the Oracle stored procedure to do the comparison. There are several dozen tables in Teradata and only the nine tables listed below in Oracle.
Oracle Oracle
Schema: GTN_MCC Schema: GTN_MCC
Reference Tables Refresh Master Tables
CTRY RL_CTRY
ST_PRVNC RL_ST_PRVNC
TRNSPRN_EVNT_CD RL_TRNSPRN_EVNT_CD
CTRCT_TRM RL_CTRCT_TRM
STD_TRNST_TM RL_STD_TRNST_TM
HLDY RL_HLDY
|
|
|
|
|
|
|
|
|
| Re: Procedure_urgent [message #230753 is a reply to message #230750] |
Thu, 12 April 2007 13:03 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
Maybe you should post it in all capitals.
If people don't understand you, simply repeat what you just said. If they still don't get you, shout.
|
|
|
|
|
|
| Re: Procedure_urgent [message #232737 is a reply to message #230672] |
Mon, 23 April 2007 02:38 |
michael_bialik
Messages: 621
Registered: July 2006
|
Senior Member |
|
|
I think I understood it:
CREATE OR REPLACE FUNCTION GTN_MCC.TEST_TABLE ( pTableName VARCHAR2 )
RETURN NUMBER IS
i NUMBER;
BEGIN
SELECT 1 INTO i FROM USER_TABLES
WHERE table_name = 'RL_' || pTableName;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 0;
END; |
|
|
|
) 1 Vote
] 
Blog
Search
Help
Members
Register
Login
Home
