Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2009
    Posts
    3

    Unanswered: ORA-06553: PLS-320: the declaration of the type of this expression is incomplete or m

    Hi,
    below is my stored procedure and it gives the following error:
    PL/SQL: ORA-06552: PL/SQL: Compilation unit analysis terminated
    ORA-06553: PLS-320: the declaration of the type of this expression is incomplete or malformed

    The query when run on its own seems fine and gives the correct results.
    but when it is embedded in the stored procedure it throws the error as above.
    Can someone please help!!!!!

    Cheers,
    Parul



    CREATE OR REPLACE PROCEDURE REPORTING.testing (

    p_Customer IN varchar,
    p_Product_By_WH_And_Loc_temp OUT Reporting.rpt_cursor_package.rpt_cursor

    )

    AS

    /************************************************** ****************************
    NAME: NZPost_Product_By_WH_And_Loc
    PURPOSE:

    REVISIONS:
    Ver Date Author Description
    --------- ---------- --------------- ------------------------------------
    1.0 04/02/2009 Ashish Sharma 1. Created this procedure.
    1.1 20/05/2009 Parul G Added ITEM_CODE not in 'ZZZ%' to
    exclude the unreal itemcodes.

    NOTES:
    Original code was prepared by Brian Reid from some crystal report.

    ************************************************** ****************************/

    V_Company INT;


    BEGIN

    SElect CompanyID into V_Company
    From Customers
    Where Customer = P_Customer
    AND ROWNUM = 1;



    open p_Product_By_WH_And_Loc_temp for
    SELECT * FROM
    (SELECT STOCK_DATA_BY_LOCATION_AND_LOT.WAREHOUSE,
    STOCK_DATA_BY_LOCATION_AND_LOT.LOCATION,
    Locations.DESCRIPTION as Location_Description,
    STOCK_DATA_BY_LOCATION_AND_LOT.ITEM_CODE,
    ITEM_DATA.DESCRIPTION,
    STOCK_DATA_BY_LOCATION_AND_LOT.STOCK_STOCK_UNIT,
    -- STOCK_DATA_BY_LOCATION_AND_LOT.ALLOCATED_STOCK_STO CK_UNIT,
    (STOCK_DATA_BY_LOCATION_AND_LOT.STOCK_STOCK_UNIT * 100 / ITEM_DATA.STOCK_ON_HAND) as ALLOCATED_STOCK_STOCK_UNIT,
    STOCK_DATA_BY_LOCATION_AND_LOT.LAST_TRANSACTION_DA TE,
    ITEM_DATA.ITEM_OWNERSHIP,
    ITEM_DATA.STOCK_ON_HAND
    --TEXTS.TEXT_LINE


    FROM REPORTING.STOCK_DATA_BY_LOCATION_AND_LOT STOCK_DATA_BY_LOCATION_AND_LOT
    INNER JOIN REPORTING.ITEM_DATA ITEM_DATA ON STOCK_DATA_BY_LOCATION_AND_LOT.ITEM_CODE=ITEM_DATA .ITEM_CODE

    LEFT OUTER JOIN REPORTING.STOCK_TRANSACTIONS_BY_LOC_HIST ON STOCK_DATA_BY_LOCATION_AND_LOT.ITEM_CODE = STOCK_TRANSACTIONS_BY_LOC_HIST.ITEM_CODE
    AND STOCK_TRANSACTIONS_BY_LOC_HIST.ORDER_BATCH_ID =STOCK_DATA_BY_LOCATION_AND_LOT.ORDER_BATCH_ID
    AND STOCK_TRANSACTIONS_BY_LOC_HIST.ORDER_TYPE =16
    AND STOCK_TRANSACTIONS_BY_LOC_HIST.TRANSACTION_DATE >= STOCK_DATA_BY_LOCATION_AND_LOT.LAST_TRANSACTION_DA TE

    LEFT OUTER JOIN REPORTING.STOCK_TRANSACTIONS_BY_ITEM STOCK_TRANSACTIONS_BY_ITEM ON STOCK_DATA_BY_LOCATION_AND_LOT.ITEM_CODE= STOCK_TRANSACTIONS_BY_ITEM.ITEM_CODE
    AND STOCK_TRANSACTIONS_BY_ITEM.WAREHOUSE_ORDER = STOCK_TRANSACTIONS_BY_LOC_HIST.ORDER_BATCH_ID

    LEFT OUTER JOIN REPORTING.TEXTS TEXTS ON STOCK_TRANSACTIONS_BY_ITEM.TEXT = TEXTS.TEXT_NUMBER

    INNER JOIN REPORTING.Locations Locations ON Locations.LOCATION = STOCK_DATA_BY_LOCATION_AND_LOT.LOCATION
    AND Locations.CompanyID = 300
    AND Locations.Warehouse = STOCK_DATA_BY_LOCATION_AND_LOT.WAREHOUSE



    WHERE ( ITEM_DATA.ITEM_OWNERSHIP=' NZP' AND STOCK_DATA_BY_LOCATION_AND_LOT.WAREHOUSE='HLD'

    OR ITEM_DATA.ITEM_OWNERSHIP=' NZP' AND STOCK_DATA_BY_LOCATION_AND_LOT.WAREHOUSE='AK1'
    AND (STOCK_DATA_BY_LOCATION_AND_LOT.LOCATION IN (' Z777777', ' Z888888', ' Z999999', ' X999999'))) AND (ITEM_DATA.ITEM_CODE NOT LIKE 'ZZZ%')

    ORDER BY STOCK_DATA_BY_LOCATION_AND_LOT.WAREHOUSE, STOCK_DATA_BY_LOCATION_AND_LOT.LOCATION, STOCK_DATA_BY_LOCATION_AND_LOT.ITEM_CODE

    );


    END testing;
    /

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    privileges acquired via ROLE do not apply within PL/SQL procedures.

    You can test using sqlplus by first
    SQL> SET ROLE NONE
    -- now try to run your code & note where error happens
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jun 2009
    Posts
    3
    i use TOAD. not SQLPLUS. but thinking about previliges let me try something.

  4. #4
    Join Date
    Jun 2009
    Posts
    3
    no, i do have all the previleges.

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >no, i do have all the previleges.
    Then, all is good.

    You have provided nothing that allows anyone to reproduce your problem.

    Enjoy your mystery.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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