Home » SQL & PL/SQL » SQL & PL/SQL » How To identify the tables from a dblinks seeing at a procedure
How To identify the tables from a dblinks seeing at a procedure [message #354148] Thu, 16 October 2008 10:34 Go to next message
shashish
Messages: 2
Registered: October 2008
Junior Member
Sad

 PROCEDURE import
     IS

     vBeginTime  DATE := SYSDATE;
     vStartTime  DATE;
     my_last_update  DATE;

     BEGIN
        SELECT lastrefresh INTO my_last_update
        FROM RefreshSem;

        DBMS_OUTPUT.PUT_LINE('RefreshSem has data.');


        DBMS_OUTPUT.PUT_LINE('STARTED PROCESS (' || TO_CHAR(SYSDATE,
            'mm/dd/yyyy hh24:mi') || ')');
        DBMS_OUTPUT.PUT_LINE(' ');

            vStartTime := SYSDATE;
        importMarkets;
            ShowElapsedTime(vStartTime, 'importMarkets');

            vStartTime := SYSDATE;
        importRatePlans;
            ShowElapsedTime(vStartTime, 'importRatePlans');

            vStartTime := SYSDATE;
        importFeatures;
            ShowElapsedTime(vStartTime, 'importFeatures');

            vStartTime := SYSDATE;
        importRatePlanFeatures;
            ShowElapsedTime(vStartTime, 'importRatePlanFeatures');

            vStartTime := SYSDATE;
        importFeatureRelationships;
        ShowElapsedTime(vStartTime, 'importFeatureRelationships');

-- comment out begin here for commenting out the execution of the importPhoneNumbers procedure
            vStartTime := SYSDATE;
        importPhoneNumbers;
            ShowElapsedTime(vStartTime, 'importPhoneNumbers');
-- comment out end here for commenting out the execution of the importPhoneNumbers procedure

        /**** Changed by Sumeet Prakash for Enabler 4.95 12/07 *****/

        /**** BEGIN ***/
/* jtuck: don't go to enabler anymore
            vStartTime := SYSDATE;
        importSwitchParameters;
            ShowElapsedTime(vStartTime, 'importSwitchParameters');
*/
            vStartTime := SYSDATE;
        updatepromotionalsocs;
            ShowElapsedTime(vStartTime, 'Update Promotional SOC Indicator');

            vStartTime := SYSDATE;
        importrateplanfeaturesbuckets;
            ShowElapsedTime(vStartTime, 'Import RateplanFeatuersBuckets');

            vStartTime := SYSDATE;
        importfeaturebuckets;
            ShowElapsedTime(vStartTime, 'Import FeatureBuckets');

            vStartTime := SYSDATE;
        ImportContractReqFlag;
            ShowElapsedTime(vStartTime, 'Import ImportContractReqFlag from WATSON');

                        vStartTime := SYSDATE;
                UpdatePromotionalRateplans;
            ShowElapsedTime(vStartTime, 'Update Promotional indicator from WATSON ');

        /***** END ****/

        DBMS_OUTPUT.PUT_LINE(' ');
        ShowElapsedTime(vBeginTime, 'PROCESS (' || TO_CHAR(SYSDATE,
            'mm/dd/yyyy hh24:mi') || ')');
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            DBMS_OUTPUT.PUT_LINE('Import didn''t complete.');

        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('ERROR: An error occurred --');
            DBMS_OUTPUT.PUT_LINE(SQLCODE||': '||SUBSTR(SQLERRM,1,100));

    END import;



This is a proc and i would like to identify the tables that are coming from the dblink EREF_TO_REFDB.

Any timely help would be greatll appreciated



[mod-edit: code tags added by bb; next time please add them yourself]

[Updated on: Thu, 16 October 2008 11:44] by Moderator

Report message to a moderator

Re: How To identify the tables from a dblinks seeing at a procedure [message #354163 is a reply to message #354148] Thu, 16 October 2008 13:33 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
What you ask is not easil doable; at least, I have never found an easy way.

Second:

there is no reference to EREF_TO_REFDB in your code. This means either it does not exist, or you have failed to provide all the necessary code for us to see what you are dealing with.

Kevin
Previous Topic: Error in decode function?
Next Topic: Oracle Object type not working in two schemas
Goto Forum:
  


Current Time: Mon Dec 05 19:18:16 CST 2016

Total time taken to generate the page: 0.09162 seconds