Thread: bad bind or define context Error
06-04-09, 14:13 #1Registered User
- Join Date
- Nov 2003
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.
CREATE OR REPLACE PROCEDURE ABC.ABC_ITEM_SUPP
Total_records NUMBER := 0;
i NUMBER := 1;
FROM (SELECT DISTINCT
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,
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;
start_time := DBMS_UTILITY.get_time;
EXECUTE IMMEDIATE 'Truncate table ABC.ABC_ITEM_SUPPLIERS';
DBMS_OUTPUT.PUT_LINE ('Table ABC_ITEM_SUPPLIERS truncated');
FETCH isupp_cur BULK COLLECT INTO isupp_rec LIMIT 3000;
FORALL j IN isupp_rec.FIRST .. isupp_rec.LAST --(errors out here)
INSERT INTO ( SELECT PROCESSING_LEAD_TIME,
VALUES isupp_rec (j);
i := i + 1;
IF MOD (i, 1000) = 0
THEN -- Commit every 1000 records
EXIT WHEN isupp_cur%NOTFOUND;
CLOSE isupp_cur; --(is this really needed here?)
Total_records := isupp_cur%ROWCOUNT;
'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)
DBMS_OUTPUT.put_line ('No data found in source tables');
DBMS_OUTPUT.put_line ('We have duplicate data');
DBMS_OUTPUT.put_line ('The cursor is already open');
DBMS_OUTPUT.put_line ('Trying to close a cursur which is not open');
'Invalid datatype for string/number value already declared'
DBMS_OUTPUT.put_line ('too many rows are returned by select');
'Memory error, not enough memory to perform the operation'
DBMS_OUTPUT.put_line ('Internal error: PL/SQL parsing error');
DBMS_OUTPUT.put_line ('invalid user name or password');
06-04-09, 14:42 #2Registered User
- Join Date
- Nov 2003
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?
06-04-09, 15:25 #3Registered User
Provided Answers: 1
- Join Date
- Aug 2003
- Where the Surf Meets the Turf @Del Mar, CA
>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.