Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2010
    Posts
    8

    Unanswered: Regarding Execute Immediate

    Hello All,
    I am using database version Oracle Database 10g Release 10.2.0.1.0 - Production. I have multiple schema's in my database and want to read the data from multiple schema's and have to insert into a single table. For that i was thinking to pass the schema name as parameter and fetch data accordingly. Yet i haven't got any result on this, so might be i am wrong some way.

    ORA-00936: missing expression
    ORA-06512: at "INTERNATIONAL.MPLS_PROC_TEST";, line 66
    ORA-06512: at line 15

    CREATE OR REPLACE PROCEDURE MPLS_PROC_TEST (
    P_CLIENT_CODE IN VARCHAR2,
    P_VARIANT_CODE IN VARCHAR2,
    P_START_DATE IN DATE,
    P_END_DATE IN DATE,
    P_MEDIA_CODE IN VARCHAR2,
    P_SCHEMA IN VARCHAR2
    )
    IS

    TYPE MY_CUR IS REF CURSOR;

    CUR MY_CUR;

    TYPE CUR_REC IS RECORD (
    V_COUNTRY VARCHAR2 (60),
    V_MEDIA_CODE VARCHAR2 (20),
    V_CLIENT_NAME VARCHAR2 (60),
    V_VARIANT_CODE VARCHAR2 (10),
    V_VARIANT_NAME VARCHAR2 (60),
    V_CAMPAIGN_NAME VARCHAR2 (60),
    V_ESTIMATE_NO VARCHAR2 (40),
    V_RO_NO VARCHAR2 (40),
    V_SUPPLIER_CODE VARCHAR2 (10),
    V_SUPPLIER_NAME VARCHAR2 (60),
    V_SUPPLIER_CURRENCY VARCHAR2 (10),
    V_PUBLICATION_NAME VARCHAR2 (60),
    V_AD_DURATION NUMBER (20),
    V_BILL_PAID VARCHAR2 (10),
    V_INVOICE_NO VARCHAR2 (40),
    V_INVOICE_DATE DATE,
    V_COMPANY_CODE VARCHAR2 (10),
    V_COMPANY_NAME VARCHAR2 (60),
    );

    ER CUR_REC;
    RESOURCE_BUSY EXCEPTION;
    PRAGMA EXCEPTION_INIT (RESOURCE_BUSY, -54);
    BEGIN
    IF P_MEDIA_CODE = 'PR'
    THEN
    LOCK TABLE MPLS_DATA IN SHARE UPDATE MODE NOWAIT;

    EXECUTE IMMEDIATE ( 'TRUNCATE TABLE '
    || P_SCHEMA
    || '.MPLS_DATA');

    OPEN CUR FOR 'SELECT Z.COUNTRY_DESC COUNTRY, D.MEDIA_CODE MEDIA_CODE,A.CLIENT_NAME CLIENT_NAME, B.VARIANT_CODE VARIANT_CODE,B.VARIANT_NAME VARIANT_NAME, F.CAMPAIGN_NAME CAMPAIGN_NAME,C.MEDIA_ESTIMATE_NO ESTIMATE_NO, D.RO_NO RO_NO,K.SUPPLIER_CODE SUPPLIER_CODE, K.SUPPLIER_NAME SUPPLIER_NAME,D.SUPPLIER_CURRENCY_CODE SUPPLIER_CURRENCY,H.PUBLICATION_NAME PUBLICATION_NAME, NVL (0, 0) AD_DURATION,C.SCHEDULE_DATE SCH_DATE,DECODE (E.PAID_BONUS_FLAG,"Y", 0,DECODE (C.FINAL_EDITION_STATUS, "4", 0, M.PO_VALUE)) GROSS_COST,E.PAID_BONUS_FLAG BONUS_FLAG,DECODE (C.FINAL_EDITION_STATUS,"0", "No","1", "Yes","2", "Missing","3", "Disputed","4", "MakeGood","6", "Cancelled","7", "Yes") MONITORED,DECODE (C.FINAL_EDITION_STATUS,"4", "Makegood",DECODE (C.PERCENT_DN_GENERATED,NULL, "NOT GENERATED","GENERATED")) DN_STATUS,DECODE (D.RO_PRINTED_FLAG, "N", "No", "Yes") PO_PRINTED,DECODE (Q.BILL_STATUS, "L", "YES", "NO") BILL_LOGGED,DECODE (Q.BILL_STATUS, "B", "YES", "NO") BILL_BOOKED,DECODE (R.SETTLED_AMOUNT, NULL, "NO", "YES") BILL_PAID,N.DN_NO INVOICE_NO, O.DN_DATE INVOICE_DATE,P.COMPANY_CODE COMPANY_CODE, P.COMPANY_NAME COMPANY_NAME,Q.BILL_NO BILL_NO, R.SUPPLIER_BILL_NO SUPPLIER_BILL_NO,R.BILL_DATE BILL_DATE, D.RO_REF_NO RO_REF_NO,( NVL (K.AGENCY_DISCOUNT, T.FEE_COMM_VALUE) - DECODE (U.DISCOUNT_RATE,0, 0,DECODE (SIGN ( NVL (K.AGENCY_DISCOUNT, T.FEE_COMM_VALUE) - NVL (V.COMMISSION_PERCENTAGE, 0)),0, 0,-1, 0,NVL (K.AGENCY_DISCOUNT, T.FEE_COMM_VALUE) - NVL (V.COMMISSION_PERCENTAGE, 0)))) ACTUAL_AGN_COMM,ROUND( DECODE (E.PAID_BONUS_FLAG,"Y", 0,DECODE (C.FINAL_EDITION_STATUS, "4", 0, M.PO_VALUE)) * ( NVL (K.AGENCY_DISCOUNT, T.FEE_COMM_VALUE) - DECODE (U.DISCOUNT_RATE,0, 0,DECODE (SIGN ( NVL (K.AGENCY_DISCOUNT,T.FEE_COMM_VALUE) - NVL (V.COMMISSION_PERCENTAGE, 0)),0, 0,-1, 0,NVL (K.AGENCY_DISCOUNT, T.FEE_COMM_VALUE) - NVL (V.COMMISSION_PERCENTAGE, 0))))/ 100,2) AGN_COMM_AMT_EX_VAT,NVL (W.VOLUME_DISCOUNT_RATE, 0) AVDS_PERC,ROUND ( DECODE (E.PAID_BONUS_FLAG,"Y", 0,DECODE (C.FINAL_EDITION_STATUS, "4", 0, M.PO_VALUE)) * NVL (W.VOLUME_DISCOUNT_RATE, 0)/ 100,2) AVDS_AMT,X.CLIENT_PO_NO CLIENT_PO_NO,E.COMBINATION_DESCRIPTION COMBINATION_DESCRIPTION,Q.BILL_INSERTION_AMOUNT BILL_LOGGED_AMT FROM '
    || P_SCHEMA
    || '.G_CLIENT_MSTR A,'
    || P_SCHEMA
    || '.G_VARIANT_MSTR B,'
    || P_SCHEMA
    || '.M_PR_RO_EDITION_DTL C,'
    || P_SCHEMA
    || '.M_RO_HDR D,'
    || P_SCHEMA
    || '.M_FLOW_PLAN_DETAILS E,'
    || P_SCHEMA
    || '.M_FLOW_PLAN_MSTR X,'
    || P_SCHEMA
    || '.G_CAMPAIGN_MSTR F,'
    || P_SCHEMA
    || '.M_ESTIMATE_HDR G,'
    || P_SCHEMA
    || '.M_PUBLICATION_MSTR H,'
    || P_SCHEMA
    || '.G_SUPPLIER_MSTR K,'
    || P_SCHEMA
    || '.M_PR_ESTIMATE_COMBO_DTL M,'
    || P_SCHEMA
    || '.M_PR_DN_COMBO_DTL N,'
    || P_SCHEMA
    || '.M_DN_HDR O,'
    || P_SCHEMA
    || '.G_COMPANY_MSTR P,'
    || P_SCHEMA
    || '.M_PR_BILL_DTL Q,'
    || P_SCHEMA
    || '.M_BILL_HDR R,'
    || P_SCHEMA
    || '.G_VARIANT_MEDIA_PROFILE_MSTR T,'
    || P_SCHEMA
    || '.G_CLIENT_MONITORING_COST U,'
    || P_SCHEMA
    || '.M_VARIANT_DN_PROFILE_MSTR V,'
    || P_SCHEMA
    || '.M_SUPP_SRVTX_MSTR W,'
    || P_SCHEMA
    || '.COUNTRY_CODES Z WHERE A.CLIENT_CODE = D.CLIENT_CODE AND D.MEDIA_ESTIMATE_NO = M.MEDIA_ESTIMATE_NO AND E.FLOW_PLAN_NO ||"/"|| E.MEDIA_CODE = M.MEDIA_ESTIMATE_NO AND C.MEDIA_ESTIMATE_NO = M.MEDIA_ESTIMATE_NO AND C.SERIAL_NO = M.SERIAL_NO AND C.COMBO_CODE = M.COMBO_CODE AND X.FLOW_PLAN_NO = E.FLOW_PLAN_NO AND X.CLIENT_CODE = E.CLIENT_CODE AND X.VARIANT_CODE = E.VARIANT_CODE AND X.COUNTRY_CODE = Z.COUNTRY_CODE AND E.SERIAL_NO = M.SERIAL_NO AND E.BASE_COMBO_PARAMETER_CODE = M.BASE_COMBO_PARAMETER_CODE AND E.COMBINATION_CODE = M.COMBO_CODE AND B.VARIANT_CODE = D.VARIANT_CODE AND B.CLIENT_CODE = D.CLIENT_CODE AND F.CAMPAIGN_CODE = G.CAMPAIGN_CODE AND G.MEDIA_ESTIMATE_NO = D.MEDIA_ESTIMATE_NO AND D.SUPPLIER_CODE = K.SUPPLIER_CODE AND DECODE (D.RO_TYPE, "M", D.RO_REF_NO, D.RO_NO) = C.RO_NO AND H.PUBLICATION_CODE = C.PUBLICATION_CODE AND C.SCHEDULE_DATE BETWEEN '
    || P_START_DATE
    || ' AND '
    || P_END_DATE
    || ' AND UPPER (A.CLIENT_CODE) = UPPER (DECODE ('
    || P_CLIENT_CODE
    || ', "ALL", A.CLIENT_CODE, '
    || P_CLIENT_CODE
    || ')) AND UPPER (B.VARIANT_CODE) = UPPER (DECODE ('
    || P_VARIANT_CODE
    || ', "ALL", B.VARIANT_CODE,'
    || P_VARIANT_CODE
    || ')) AND E.PUBLICATION_CHANNEL_CODE = C.PUBLICATION_CODE AND E.COMBINATION_CODE = C.COMBO_CODE AND E.FLOW_PLAN_NO || "/" || E.MEDIA_CODE = C.MEDIA_ESTIMATE_NO AND E.SERIAL_NO = C.SERIAL_NO AND C.BILL_BOOKED_FLAG IN ("N", "Y") AND C.RO_NO = N.RO_NO(+) AND C.SERIAL_NO = N.SERIAL_NO(+) AND C.SCHEDULE_SERIAL_NO = N.SCHEDULE_SERIAL_NO(+) AND N.DN_NO = O.DN_NO(+) AND D.COMPANY_CODE = P.COMPANY_CODE AND C.RO_NO = Q.RO_NO(+) AND C.SERIAL_NO = Q.SERIAL_NO(+) AND C.SCHEDULE_SERIAL_NO = Q.SCHEDULE_SERIAL_NO(+) AND Q.BILL_NO = R.BILL_NO(+) AND D.MEDIA_CODE = T.MEDIA_CODE AND D.VARIANT_CODE = T.VARIANT_CODE AND D.CLIENT_CODE = U.CLIENT_CODE AND D.MEDIA_CODE = U.MEDIA_CODE AND T.MEDIA_CODE = V.MEDIA_CODE AND T.VARIANT_CODE = V.VARIANT_CODE AND D.SUPPLIER_CODE = W.SUPPLIER_CODE AND D.MEDIA_CODE = W.MEDIA_CODE';

    FETCH CUR
    INTO ER;

    LOOP
    EXECUTE IMMEDIATE ( 'INSERT INTO '
    || P_SCHEMA
    || '.MPLS_DATA (COUNTRY, MEDIA_CODE, CLIENT_NAME,VARIANT_CODE, VARIANT_NAME,CAMPAIGN_NAME, ESTIMATE_NO,RO_NO, SUPPLIER_CODE,SUPPLIER_NAME, SUPPLIER_CURRENCY,PUBLICATION_NAME, AD_DURATION,SCH_DATE, GROSS_COST, BONUS_FLAG,MONITORED, DN_STATUS, PO_PRINTED,BILL_LOGGED, BILL_BOOKED,BILL_PAID, INVOICE_NO,INVOICE_DATE, COMPANY_CODE,COMPANY_NAME, BILL_NO,SUPPLIER_BILL_NO, BILL_DATE,RO_REF_NO, ACTUAL_AGN_COMM,AGN_COMM_AMT_EX_VAT, AVDS_PERC,AVDS_AMT, CLIENT_PO_NO,COMBINATION_DESCRIPTION, BILL_LOGGED_AMT) VALUES (ER.V_COUNTRY,ER.V_MEDIA_CODE,ER.V_CLIENT_NAME,ER. V_VARIANT_CODE,ER.V_VARIANT_NAME,ER.V_CAMPAIGN_NAM E,ER.V_ESTIMATE_NO,ER.V_RO_NO,ER.V_SUPPLIER_CODE,E R.V_SUPPLIER_NAME,ER.V_SUPPLIER_CURRENCY,ER.V_PUBL ICATION_NAME,ER.V_AD_DURATION,ER.V_SCH_DATE,ER.V_G ROSS_COST,ER.V_BONUS_FLAG,ER.V_MONITORED,ER.V_DN_S TATUS,ER.V_PO_PRINTED,ER.V_BILL_LOGGED,ER.V_BILL_B OOKED,ER.V_BILL_PAID,ER.V_INVOICE_NO,ER.V_INVOICE_ DATE,ER.V_COMPANY_CODE,ER.V_COMPANY_NAME,ER.V_BILL _NO,ER.V_SUPPLIER_BILL_NO,ER.V_BILL_DATE,ER.V_RO_R EF_NO,ER.V_ACTUAL_AGN_COMM,ER.V_AGN_COMM_AMT_EX_VA T,ER.V_AVDS_PERC,ER.V_AVDS_AMT,ER.V_CLIENT_PO_NO,E R.V_COMBINATION_DESCRIPTION,ER.V_BILL_LOGGED_AMT)'
    );

    EXIT WHEN CUR%NOTFOUND;
    END LOOP;

    CLOSE CUR;

    COMMIT;
    END IF;
    EXCEPTION
    WHEN RESOURCE_BUSY
    THEN
    RAISE_APPLICATION_ERROR
    (-20001,
    XXXXXX'
    );
    END;
    Waiting for your response.

    Thanks

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Not necessarily the only errors, but I see these:
    Code:
    ...AND C.SCHEDULE_DATE BETWEEN '
    || P_START_DATE
    || ' AND '
    || P_END_DATE
    etc.

    That will evaluate to something like:

    Code:
    AND C.SCHEDULE_DATE BETWEEN 01-JAN-2010 AND 31-JAN-2010
    which is invalid. You would do better to use bind values for these (and other data values):

    Code:
    AND C.SCHEDULE_DATE BETWEEN :START_DATE AND :END_DATE
    Then bind the values:

    Code:
    OPEN CUR FOR ... USING p_start_date, p_end_date;
    It is also a good tip always to store your dynamic SQL text in a variable so that you can print it out and eyeball it for mistakes like this:

    Code:
    v_sql := 'SELECT Z.COUNTRY_DESC COUNTRY...';
    DBMS_OUTPUT.PUT_LINE(v_sql);
    OPEN CUR FOR v_sql USING p_start_date, p_end_date;

  3. #3
    Join Date
    Feb 2010
    Posts
    8
    Thanks Tony,

    Thank you very much for your reply. I have changed my code according to you and even there were some other issues which are now clear and sorted out.

    So now its working fine.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •