Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2007
    Posts
    7

    Unanswered: Procedure getting Compiled but not executing

    Hi 2 all,

    I got a procedure compiled successfully, and when i try to execute it, it was saying like 'invalid column


    The procedure is as follows

    CREATE OR REPLACE
    PROCEDURE OVR_BILLABLEREPORT_COMPCODE(YEAR IN VARCHAR2,
    COMPANYCODE IN VARCHAR2,
    TRIPTYPE IN NUMBER,
    BILLABLECODE IN VARCHAR2,
    P_RECORDSET OUT SYS_REFCURSOR) AS
    tinumberstatus varchar2(10) := 'DRAFT';
    itinerarystatus1 varchar2(20) := 'deleted';
    itinerarystatus2 varchar2(20) := 'ticket_cancelled';
    itinerarystatus3 varchar2(20) := 'cancel_approved';
    yearcode varchar2(20) := 'yyyy';
    BEGIN
    OPEN P_RECORDSET FOR
    'SELECT
    CMTRAVELINSTRUCTION.TINUMBER AS TINUMBER,
    CMTRAVELINSTRUCTION.TRAVELLERID AS TRAVELLERID,
    CMTRAVELINSTRUCTION.TRAVELLERNAME AS TRAVELLERNAME,
    CMTRAVELINSTRUCTION.PAYROLLAREA AS PAYROLLAREA,
    CMTRAVELINSTRUCTION.COMPCODE AS COMPCODE,
    CMTRAVELINSTRUCTION.BUSAREA AS BUSAREA,
    CMTRAVELINSTRUCTION.COSTCENTER AS COSTCENTER,
    CMTRAVELINSTRUCTION.REFERENCENUMBER AS REFERENCENUMBER,
    CMTRAVELINSTRUCTION.STARTDATE AS STARTDATE,
    CMTRAVELINSTRUCTION.ENDDATE AS ENDDATE,
    NVL(CMTRAVELITINERARY.ticketamnt,0) AS TICKETAMOUNT,
    (NVL(CMACCOMODATIONEXPENSE1.totaccmamnt,0)+nvl(CMA CCOMODATIONEXPENSE.accomamnt,0)) + round(nvl(cmaccomodationexpense1.totaccusd,0)*(cmt ravelexpenseusd.CONVERSIONRATE)) as netaccom,
    (NVL(CMFOODEXPENSE1.totfoodamnt,0)+NVL(CMFOODEXPEN SE.foodamnt,0))+round(nvl(cmfoodexpense1.totfoodus d,0)*(cmtravelexpenseusd.CONVERSIONRATE)) as netfood,
    (NVL(CMCONVEYANCEEXPENSE1.totconveyamnt,0)+nvl(cmc onveyanceexpense.conveyamnt,0))+ROUND(NVL(CMCONVEY ANCEEXPENSE1.totconveyusd,0)*nvl(cmtravelexpenseus d.CONVERSIONRATE,0)) as netconvey,
    (NVL(CMOUTOFPOCKETEXPENSE.outofpocketamnt,0)+ROUND (NVL(CMOUTOFPOCKETEXPENSE1.totoutofpocketusd,0)*nv l(cmtravelexpenseusd.CONVERSIONRATE,0))) as netoutofpocket,
    (nvl(CMOTHEREXPENSE1.tototheramnt,0)+nvl(CMOTHEREX PENSE.otheramnt,0))+ROUND(NVL(CMOTHEREXPENSE1.toto therusd,0)*nvl(cmtravelexpenseusd.CONVERSIONRATE,0 )) as netother,
    nvl(cmtravelexpenseusd.totalexpinr,0) as totalexpense,
    nvl(cmtravelexpense2.adjticketexp,0) as adjusttickexp,
    NVL(CMADJUSTEXPENSE.adjustexpenses,0) as adjustexpense,
    nvl(cmadjustexpense.usdexpense,0) as usdexpenses,
    CMTRAVELEXPENSEUSD.CONVERSIONRATE as conversionrate,

    NVL(CMTRAVELEXPENSE1.adjustexpense,0) as adjustexp1,
    (NVL(CMACCOMODATIONEXPENSE1.totaccmamnt,0)+NVL(CMF OODEXPENSE1.totfoodamnt,0)+NVL(CMCONVEYANCEEXPENSE 1.totconveyamnt,0)+NVL(CMOUTOFPOCKETEXPENSE.outofp ocketamnt,0)+NVL(CMOTHEREXPENSE1.tototheramnt,0)) AS adjustexp2,
    (nvl(CMACCOMODATIONEXPENSE.accomamnt,0)+NVL(CMFOOD EXPENSE.foodamnt,0)+nvl(cmconveyanceexpense.convey amnt,0)+nvl(CMOTHEREXPENSE.otheramnt,0)) as adjustexp3,
    CMBILLABLE.TOWHOM AS BILLTO,
    CMBILLABLE.REFERENCE AS BILLREFERENCE,
    CMBILLABLE.BYWHATDATE AS BILLDATE,
    NVL(CMBILLABLE.BILLABLEAMOUNT,0) AS BILLAMOUNT,
    cmtravelexpense.testatus as testatus,
    CMADJUSTEXPENSE.TRAVELEXPENSEID as adjustexpenseid,
    CMTRAVELEXPENSE.TRAVELEXPENSEID as travelexpenseid



    FROM
    CMTRAVELINSTRUCTION

    LEFT OUTER JOIN (select travelinstructionid, sum(nvl(ticketamount,0)) as ticketamnt from cmtravelitinerary where cmtravelitinerary.itinerarystatus is null or cmtravelitinerary.itinerarystatus not in (ITINERARYSTATUS1,ITINERARYSTATUS2,ITINERARYSTATUS 3) group by travelinstructionid)
    CMTRAVELITINERARY ON CMTRAVELITINERARY.TRAVELINSTRUCTIONID = CMTRAVELINSTRUCTION.TRAVELINSTRUCTIONID
    LEFT OUTER JOIN
    CMTRAVELEXPENSE ON CMTRAVELEXPENSE.TRAVELINSTRUCTIONID = CMTRAVELINSTRUCTION.TRAVELINSTRUCTIONID
    LEFT OUTER JOIN (select travelexpenseid,sum(nvl(amount,0)) as foodamnt from cmfoodexpense where cmfoodexpense.totalin = 5000 group by travelexpenseid)
    CMFOODEXPENSE ON CMFOODEXPENSE.TRAVELEXPENSEID = CMTRAVELEXPENSE.TRAVELEXPENSEID
    LEFT OUTER JOIN (select travelexpenseid, sum(nvl(amount,0)) as accomamnt from cmaccomodationexpense where cmaccomodationexpense.totalin = 5000 group by travelexpenseid)
    CMACCOMODATIONEXPENSE ON CMACCOMODATIONEXPENSE.TRAVELEXPENSEID = CMTRAVELEXPENSE.TRAVELEXPENSEID
    LEFT OUTER JOIN (select travelexpenseid, sum(nvl(amount,0)) as conveyamnt from cmconveyanceexpense where cmconveyanceexpense.totalin = 5000 group by travelexpenseid)
    CMCONVEYANCEEXPENSE ON CMCONVEYANCEEXPENSE.TRAVELEXPENSEID = CMTRAVELEXPENSE.TRAVELEXPENSEID
    LEFT OUTER JOIN (select travelexpenseid, sum(nvl(amount,0)) as outofpocketamnt from cmoutofpocketexpense where cmoutofpocketexpense.totalin = 5000 group by travelexpenseid)
    CMOUTOFPOCKETEXPENSE ON CMOUTOFPOCKETEXPENSE.TRAVELEXPENSEID = CMTRAVELEXPENSE.TRAVELEXPENSEID
    LEFT OUTER JOIN (select travelexpenseid, sum(nvl(amount,0)) as otheramnt from cmotherexpense where cmotherexpense.totalin = 5000 group by travelexpenseid)
    CMOTHEREXPENSE ON CMOTHEREXPENSE.TRAVELEXPENSEID = CMTRAVELEXPENSE.TRAVELEXPENSEID



    LEFT OUTER JOIN (select travelexpenseid, sum(nvl(amount,0)) as totaccmamnt,sum(nvl(totalusd,0)) as totaccusd from cmaccomodationexpense where cmaccomodationexpense.totalin = 5001 group by travelexpenseid)
    CMACCOMODATIONEXPENSE1 ON CMACCOMODATIONEXPENSE1.TRAVELEXPENSEID = CMTRAVELEXPENSE.TRAVELEXPENSEID
    LEFT OUTER JOIN (select travelexpenseid, sum(nvl(amount,0)) as totfoodamnt, sum(nvl(totalusd,0)) as totfoodusd from cmfoodexpense where cmfoodexpense.TOTALIN = 5001 group by travelexpenseid)
    CMFOODEXPENSE1 ON CMFOODEXPENSE1.TRAVELEXPENSEID = CMTRAVELEXPENSE.TRAVELEXPENSEID
    LEFT OUTER JOIN (select travelexpenseid, sum(nvl(amount,0)) as totconveyamnt, sum(nvl(totalusd,0)) as totconveyusd from cmconveyanceexpense where cmconveyanceexpense.totalin = 5001 group by travelexpenseid)
    CMCONVEYANCEEXPENSE1 ON CMCONVEYANCEEXPENSE1.TRAVELEXPENSEID = CMTRAVELEXPENSE.TRAVELEXPENSEID
    LEFT OUTER JOIN (select travelexpenseid, sum(nvl(amount,0)) as totoutofpocketusd from cmoutofpocketexpense where cmoutofpocketexpense.totalin = 5001 group by travelexpenseid)
    CMOUTOFPOCKETEXPENSE1 ON CMOUTOFPOCKETEXPENSE1.TRAVELEXPENSEID = CMTRAVELEXPENSE.TRAVELEXPENSEID
    LEFT OUTER JOIN (select travelexpenseid, sum(nvl(amount,0)) as tototheramnt ,sum(nvl(totalusd,0)) as tototherusd from cmotherexpense where cmotherexpense.TOTALIN = 5001 group by travelexpenseid)
    CMOTHEREXPENSE1 ON CMOTHEREXPENSE1.TRAVELEXPENSEID = CMTRAVELEXPENSE.TRAVELEXPENSEID
    LEFT OUTER JOIN CMTRAVELEXPENSEUSD ON CMTRAVELEXPENSEUSD.TRAVELEXPENSEID = CMTRAVELEXPENSE.TRAVELEXPENSEID
    LEFT OUTER JOIN (select travelexpenseid, (nvl(adjustaccomodationexp,0)+nvl(adjustfoodexp,0) +nvl(adjustoutofpocketexp,0)+nvl(adjustconveyancee xp,0)+nvl(adjustotherexp,0))as adjustexpenses,(NVL(CMACCOMODATIONEXPENSE1.totaccu sd,0)+ NVL(CMFOODEXPENSE1.totfoodusd,0)+NVL(CMCONVEYANCEE XPENSE1.totconveyusd,0)+NVL(CMOUTOFPOCKETEXPENSE1. totoutofpocketusd,0)+NVL(CMOTHEREXPENSE1.tototheru sd,0)) as usdexpense, CMTRAVELEXPENSEUSD.CONVERSIONRATE as conversionrate from cmadjustexpense,CMACCOMODATIONEXPENSE1,CMTRAVELEXP ENSEUSD,CMFOODEXPENSE1,CMCONVEYANCEEXPENSE1,CMOUTO FPOCKETEXPENSE1,CMOTHEREXPENSE1 )
    CMADJUSTEXPENSE ON CMADJUSTEXPENSE.TRAVELEXPENSEID = CMTRAVELEXPENSE.TRAVELEXPENSEID
    LEFT OUTER JOIN CMBILLABLE ON CMBILLABLE.TRAVELEXPENSEID = CMTRAVELEXPENSE.TRAVELEXPENSEID

    LEFT OUTER JOIN (select travelinstructionid,nvl(adjustticketexp,0) as adjticketexp from cmtravelexpense where cmtravelexpense.testatus = 1076 )
    CMTRAVELEXPENSE2 ON CMTRAVELEXPENSE2.TRAVELINSTRUCTIONID = CMTRAVELINSTRUCTION.TRAVELINSTRUCTIONID



    LEFT OUTER JOIN (select travelinstructionid,sum(nvl(adjustaccomodationexp, 0)+nvl(adjustfoodexp,0)+nvl(adjustotherexp,0)+nvl( adjustconveyanceexp,0)+nvl(adjustoutofpocketexp,0) ) as adjustexpense from cmtravelexpense where cmtravelexpense.testatus = 1076 group by travelinstructionid)
    CMTRAVELEXPENSE1 ON CMTRAVELEXPENSE1.TRAVELINSTRUCTIONID = CMTRAVELINSTRUCTION.TRAVELINSTRUCTIONID


    WHERE

    CMTRAVELINSTRUCTION.COMPCODE = COMPANYCODE AND
    CMTRAVELINSTRUCTION.OVERSEAS = TRIPTYPE AND
    CMTRAVELINSTRUCTION.BILLABLE = BILLABLECODE AND
    CMTRAVELINSTRUCTION.TINUMBER <> tinumberstatus AND
    CMTRAVELINSTRUCTION.TISTATUS >= 1069 AND
    TO_CHAR(CMTRAVELINSTRUCTION.STARTDATE,yearcode) = YEAR AND
    CMTRAVELINSTRUCTION.TISTATUS >= 1066
    ORDER BY CMTRAVELINSTRUCTION.TRAVELINSTRUCTIONID DESC' ;
    END;
    /
    SHOW ERRORS;


    " I got a msg like procedure created no errors"

    When i try to run it,

    VARIABLE RES REFCURSOR;
    EXEC OVR_BILLABLEREPORT_COMPCODE('2006','0100',1,'1250' ,:RES);
    PRINT RES;

    It was showing error like,

    BEGIN OVR_BILLABLEREPORT_COMPCODE('2006','0100',1,'1250' ,:RES); END;
    *
    ERROR at line 1:
    ORA-00904: invalid column name
    ORA-06512: at "NEWTMS1.OVR_BILLABLEREPORT_COMPCODE", line 12
    ORA-06512: at line 1
    ERROR:
    ORA-24338: statement handle not executed
    SP2-0625: Error printing variable "res"

    Can any one help me in this regard

    Thanks
    Suresh Midde

  2. #2
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    The single quotes around your SELECT tells PLSQL to execute the SELECT dynamically, that is why it only fails at execution time. What you want here is a "static" SQL, so remove these single quotes and you will get the error(s) at compile time. BTW with the single quotes, the PLSQL variable names within your SELECT were not interpreted , without the single quotes they will.

    More generally, you should only use dynamic SQL when you have to : if the table name is not the same for each execution, for example.

    Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  3. #3
    Join Date
    Sep 2007
    Posts
    7
    Hi

    I tried by removing quotes around SELECT query as well as around variable names.

    What I am getting problem is that, I am using alias names for different queries like
    SELECT

    NVL(CMADJUSTEXPENSE.adjustexpenses,0) as adjustexpense,
    nvl(cmadjustexpense.usdexpense,0) as usdexpenses,
    CMTRAVELEXPENSEUSD.CONVERSIONRATE as conversionrate,
    .................................................. ....................

    USING QUERY LIKE

    LEFT OUTER JOIN (select travelexpenseid, (nvl(adjustaccomodationexp,0)+nvl(adjustfoodexp,0) +nvl(adjustoutofpocketexp,0)+nvl(adjustconveyancee xp,0)+nvl(adjustotherexp,0))as adjustexpenses,(NVL(CMACCOMODATIONEXPENSE1.totaccu sd,0)+ NVL(CMFOODEXPENSE1.totfoodusd,0)+NVL(CMCONVEYANCEE XPENSE1.totconveyusd,0)+NVL(CMOUTOFPOCKETEXPENSE1. totoutofpocketusd,0)+NVL(CMOTHEREXPENSE1.tototheru sd,0)) as usdexpense, CMTRAVELEXPENSEUSD.CONVERSIONRATE as conversionrate from cmadjustexpense,CMACCOMODATIONEXPENSE1,CMTRAVELEXP ENSEUSD,CMFOODEXPENSE1,CMCONVEYANCEEXPENSE1,CMOUTO FPOCKETEXPENSE1,CMOTHEREXPENSE1 )
    CMADJUSTEXPENSE ON CMADJUSTEXPENSE.TRAVELEXPENSEID = CMTRAVELEXPENSE.TRAVELEXPENSEID


    so, i will be using the result field names like
    cmadjustexpense.usdexpense where usdexpense is my alias name for the field result.

    I am getting invalid column name, if i wont use dynamic sql i.e., quotes around SELECT statement.

  4. #4
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Quote Originally Posted by sureshmidde
    I am getting invalid column name, if i wont use dynamic sql i.e., quotes around SELECT statement.
    You are just hiding the problem by doing so . Remove these quotes and correct it !

    BTW your code is unreadable, please format it better and use the CODE tag ! (the # symbol)

    Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

Posting Permissions

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