Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2003
    Posts
    76

    Red face Unanswered: bad bind or define context Error

    Hi Folks, need a little help here. I have been at it for a bit now but can't seem to pin this.

    I wrote this procedure using bulk collect and forall. I keep getting the BAD BIND or DEFINE CONTEXT error for this. I have 5 other procedures for other table loads which are exactly setup the same way and methods (the cursor collection may be different for each), but this is the only one which gives this error.

    No I checked the ABC_ITEM_SUPPLIERS table and it had a unique constraint. It disabled it and removed the DUP_VAL_INDEX exception also. But still no luck. Not sure if it even matters. But the error prevails and I havent found much on the net except the standard definition. Can anyone help?

    I am putting the complete code for this proc down in its entirety. I have to admit I am developing after many years so feel free to pick on any other inconsistancies you see.
    Thank you.
    ******************

    CREATE OR REPLACE PROCEDURE ABC.ABC_ITEM_SUPP
    iS
    Total_records NUMBER := 0;
    i NUMBER := 1;

    CURSOR isupp_cur
    IS
    SELECT PROCESSING_LEAD_TIME,
    MINIMUM_ORDER_QUANTITY,
    FIXED_LOT_MULTIPLE,
    VENDOR_NAME,
    VENDOR_SITE_CODE,
    DELETED_FLAG,
    PURCHASING_UNIT_OF_MEASURE,
    ITEM_PRICE,
    ITEM_NAME,
    ORGANIZATION_CODE,
    SR_INSTANCE_CODE,
    PROCESS_FLAG,
    PLANNER_CODE,
    ASL_LEVEL,
    ATTRIBUTE1,
    ATTRIBUTE3,
    last_update_date,
    last_updated_by,
    Creation_date,
    created_by
    FROM (SELECT DISTINCT
    SOI.PROCESSING_LEAD_TIME,
    CID.QTY_MIN AS MINIMUM_ORDER_QUANTITY,
    ASI.nvaps_supp_pack_size AS FIXED_LOT_MULTIPLE,
    CID.VENDOR_ID AS VENDOR_NAME,
    CID.VNDR_LOC AS VENDOR_SITE_CODE,
    '2' AS DELETED_FLAG,
    'EA' AS PURCHASING_UNIT_OF_MEASURE,
    CID.PRICE_CNTRCT_BASE AS ITEM_PRICE,
    COI.ITEM_NAME AS ITEM_NAME,
    COI.ORGANIZATION_CODE AS ORGANIZATION_CODE,
    'LEG' AS SR_INSTANCE_CODE,
    '1' AS PROCESS_FLAG,
    ASI.planner_code AS PLANNER_CODE,
    '2' AS ASL_LEVEL, -- 1 Global, 2 Local
    CID.CNTRCT_ID AS ATTRIBUTE1,
    CID.CNTRCT_LINE_NBR AS ATTRIBUTE3,
    SYSDATE AS last_update_date,
    '' AS last_updated_by,
    SYSDATE AS Creation_date,
    '' AS created_by
    FROM ABC_CONTRACT_ITEMS_DTL CID,
    ABC_SP_CTRCT_ORG_ITEM COI,
    ABC_SP_SUPLR_ORG_ITEM SOI,
    ABC_system_items ASI
    WHERE COI.ITEM_NAME = SOI.ITEM_NAME
    AND COI.ORGANIZATION_CODE = SOI.ORGANIZATION_CODE
    AND CID.INV_ITEM_ID = SOI.ITEM_NAME
    AND CID.INV_ITEM_ID = COI.ITEM_NAME
    AND CID.CNTRCT_ID = COI.CONTRACT_ID
    AND ASI.item_name = COI.Item_name
    AND ASI.ORGANIZATION_CODE = COI.ORGANIZATION_CODE);

    TYPE isupp_tab IS TABLE OF isupp_cur%ROWTYPE;

    isupp_rec isupp_tab;
    start_time NUMBER;
    end_time NUMBER;

    BEGIN
    start_time := DBMS_UTILITY.get_time;

    EXECUTE IMMEDIATE 'Truncate table ABC.ABC_ITEM_SUPPLIERS';

    DBMS_OUTPUT.PUT_LINE ('Table ABC_ITEM_SUPPLIERS truncated');
    DBMS_OUTPUT.ENABLE (1000000);

    OPEN isupp_cur;



    LOOP
    FETCH isupp_cur BULK COLLECT INTO isupp_rec LIMIT 3000;

    FORALL j IN isupp_rec.FIRST .. isupp_rec.LAST --(errors out here)
    SAVE EXCEPTIONS
    INSERT INTO ( SELECT PROCESSING_LEAD_TIME,
    MINIMUM_ORDER_QUANTITY,
    FIXED_LOT_MULTIPLE,
    VENDOR_NAME,
    VENDOR_SITE_CODE,
    DELETED_FLAG,
    PURCHASING_UNIT_OF_MEASURE,
    ITEM_PRICE,
    ITEM_NAME,
    ORGANIZATION_CODE,
    SR_INSTANCE_CODE,
    PROCESS_FLAG,
    PLANNER_CODE,
    ASL_LEVEL,
    ATTRIBUTE1,
    ATTRIBUTE3,
    last_update_date,
    last_updated_by,
    Creation_date,
    created_by
    FROM ABC.ABC_ITEM_SUPPLIERS)
    VALUES isupp_rec (j);

    i := i + 1;

    IF MOD (i, 1000) = 0
    THEN -- Commit every 1000 records
    COMMIT;
    END IF;

    EXIT WHEN isupp_cur%NOTFOUND;
    END LOOP;

    CLOSE isupp_cur; --(is this really needed here?)

    Total_records := isupp_cur%ROWCOUNT;

    DBMS_OUTPUT.put_line (
    'Total number of rows inserted was = ' || Total_records
    );



    end_time := DBMS_UTILITY.get_time;
    DBMS_OUTPUT.put_line( 'Total time to load the ABC_ITEM_SUPPLIERS was '
    || ROUND ( (end_time - start_time) / 100, 2)
    || 'seconds');
    EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
    DBMS_OUTPUT.put_line ('No data found in source tables');
    WHEN DUP_VAL_ON_INDEX
    THEN
    DBMS_OUTPUT.put_line ('We have duplicate data');
    WHEN CURSOR_ALREADY_OPEN
    THEN
    DBMS_OUTPUT.put_line ('The cursor is already open');
    WHEN INVALID_CURSOR
    THEN
    DBMS_OUTPUT.put_line ('Trying to close a cursur which is not open');
    WHEN INVALID_NUMBER
    THEN
    DBMS_OUTPUT.put_line (
    'Invalid datatype for string/number value already declared'
    );
    WHEN TOO_MANY_ROWS
    THEN
    DBMS_OUTPUT.put_line ('too many rows are returned by select');
    WHEN STORAGE_ERROR
    THEN
    DBMS_OUTPUT.put_line (
    'Memory error, not enough memory to perform the operation'
    );
    WHEN PROGRAM_ERROR
    THEN
    DBMS_OUTPUT.put_line ('Internal error: PL/SQL parsing error');
    WHEN LOGIN_DENIED
    THEN
    DBMS_OUTPUT.put_line ('invalid user name or password');
    WHEN OTHERS
    THEN
    RAISE;
    END ABC_ITEM_SUPP;

  2. #2
    Join Date
    Nov 2003
    Posts
    76
    it looks like this proc works if I disable the constraint and drop the indexes on the table. Its hardly a solution though. But is this it than, is the cursor fetching duplicate rows on indexed columns really what causes this error?

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >it looks like this proc works if I disable the constraint and drop the indexes on the table.
    We don't know which constraint was dropped, or which indexes were dropped and to which table you refer.

    >But is this it than, is the cursor fetching duplicate rows on indexed columns really what causes this error?
    We never saw any actual error.

    I am glad you solved 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
  •