Home » SQL & PL/SQL » SQL & PL/SQL » Problem ORA-01422
Problem ORA-01422 [message #38833] Sun, 19 May 2002 19:59 Go to next message
welly
Messages: 3
Registered: May 2002
Junior Member
Hello all

i have a problem in my procedure (PL/SQL)
my problem is

ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "ACTIVA.WELLY_TESTING", line 38
ORA-06512: at line 2

I don't understaind.... what can i do...

btw : this my pl/sql :

************************************************
CREATE OR REPLACE PROCEDURE WELLY_TESTING (
parm_pertama activa.wellytest.PERTAMA%TYPE
) AS

-- variable declarations
welly_count NUMBER;
HasilROW_NUMBER NUMBER(10);
HasilM25_MODEL_ID VARCHAR2(5);
HasilM18_BOR_TYPE_ID VARCHAR2(10);
HasilO06_MASTER_LIST_SETS_ID VARCHAR2(5);
HasilO09_TYPE_CODE VARCHAR2(1);
HasilO09_RESOURCE_ID VARCHAR2(40);
HasilM33_SEQ_NBR NUMBER(5,1);
HasilM33_UNIT_WELLY NUMBER(21,10);
HasilM33_COST_OBJECT_REF_QUANT NUMBER(17,10);
HasilM33_UNIT_YIELD NUMBER(5,4);
HasilECO_NUMBER VARCHAR2(10);
HasilBOM_LINE VARCHAR2(15);
HasilEFFECTIVE_BEGIN_DATE DATE;
HasilEFFECTIVE_END_DATE DATE;
HasilMAINT_USER_ID VARCHAR2(11);
HasilMAINT_EFFECT_CODE VARCHAR2(1);
HasilMAINT_TIMESTAMP DATE;
HasilDFK_M25_MODEL_ID VARCHAR2(5);
HasilDFK_M18_BOR_TYPE_ID VARCHAR2(10);
HasilDFK_O06_MASTER_LIST VARCHAR2(5);
HasilDFK_O09_TYPE_CODE VARCHAR2(1);
HasilDFK_O09_RESOURCE_ID VARCHAR2(40);
HasilDFK_I01_DRIVER_SET_ID VARCHAR2(10);
HasilDFK_I02_DRIVER_SET_ID VARCHAR2(10);
HasilDFK_M14_ACT_DRV_SET_WL DATE;
HasilVALUED_FLAG VARCHAR2(1);
BEGIN
SELECT COUNT (*)
INTO welly_count FROM activa.activa_m33_bill_of_res_line_s;
IF welly_count > 1 THEN
-- variable select activa m33
SELECT ROW_NUMBER,M25_MODEL_ID,M18_BOR_TYPE_ID,O06_MASTER_LIST_SETS_ID,O09_TYPE_CODE,O09_RESOURCE_ID,
M33_SEQ_NBR,M33_UNIT_CONSUMPTION_QUANT,M33_COST_OBJECT_REF_QUANT,M33_UNIT_YIELD_FACTOR_QUANT,
ECO_NUMBER,BOM_LINE,EFFECTIVE_BEGIN_DATE,EFFECTIVE_END_DATE,MAINT_USER_ID,MAINT_EFFECT_CODE,
MAINT_TIMESTAMP,DFK_M25_MODEL_ID,DFK_M18_BOR_TYPE_ID,DFK_O06_MASTER_LIST_SETS_ID,DFK_O09_TYPE_CODE,
DFK_O09_RESOURCE_ID,DFK_I01_DRIVER_SET_ID,DFK_I02_DRIVER_SET_ID,DFK_M14_ACT_DRV_SET_BEGIN_DATE,
VALUED_FLAG
INTO
HasilROW_NUMBER, HasilM25_MODEL_ID, HasilM18_BOR_TYPE_ID, HasilO06_MASTER_LIST_SETS_ID,
HasilO09_TYPE_CODE, HasilO09_RESOURCE_ID, HasilM33_SEQ_NBR, HasilM33_UNIT_WELLY,
HasilM33_COST_OBJECT_REF_QUANT, HasilM33_UNIT_YIELD, HasilECO_NUMBER, HasilBOM_LINE,
HasilEFFECTIVE_BEGIN_DATE, HasilEFFECTIVE_END_DATE, HasilMAINT_USER_ID, HasilMAINT_EFFECT_CODE,
HasilMAINT_TIMESTAMP, HasilDFK_M25_MODEL_ID, HasilDFK_M18_BOR_TYPE_ID, HasilDFK_O06_MASTER_LIST,
HasilDFK_O09_TYPE_CODE, HasilDFK_O09_RESOURCE_ID, HasilDFK_I01_DRIVER_SET_ID, HasilDFK_I02_DRIVER_SET_ID,
HasilDFK_M14_ACT_DRV_SET_WL, HasilVALUED_FLAG
FROM activa.activa_m33_bill_of_res_line_s ;
-- variable insert m33
INSERT INTO activa.m33_bill_of_res_line (
M25_MODEL_ID,M18_BOR_TYPE_ID,O06_MASTER_LIST_SETS_ID, O09_TYPE_CODE, O09_RESOURCE_ID,
M33_BOR_LINE_ID_CTR, M33_SEQ_NBR, M33_UNIT_CONSUMPTION_QUANT, M33_COST_OBJECT_REF_QUANT,
M33_UNIT_YIELD_FACTOR_QUANT, ECO_NUMBER, BOM_LINE, EFFECTIVE_BEGIN_DATE, EFFECTIVE_END_DATE,
MAINT_USER_ID, MAINT_EFFECT_CODE, MAINT_TIMESTAMP, DFK_M25_MODEL_ID, DFK_M18_BOR_TYPE_ID,
DFK_O06_MASTER_LIST_SETS_ID, DFK_O09_TYPE_CODE, DFK_O09_RESOURCE_ID, DFK_I01_DRIVER_SET_ID,
DFK_I02_DRIVER_SET_ID, DFK_M14_ACT_DRV_SET_BEGIN_DATE, VALUED_FLAG)
VALUES (
HasilM25_MODEL_ID, HasilM18_BOR_TYPE_ID, HasilO06_MASTER_LIST_SETS_ID,HasilO09_TYPE_CODE,
HasilO09_RESOURCE_ID,HasilROW_NUMBER,HasilM33_SEQ_NBR,HasilM33_UNIT_WELLY,
HasilM33_COST_OBJECT_REF_QUANT,HasilM33_UNIT_YIELD,HasilECO_NUMBER,
HasilBOM_LINE,HasilEFFECTIVE_BEGIN_DATE,HasilEFFECTIVE_END_DATE,HasilMAINT_USER_ID,
HasilMAINT_EFFECT_CODE,HasilMAINT_TIMESTAMP, HasilDFK_M25_MODEL_ID,HasilDFK_M18_BOR_TYPE_ID,
HasilDFK_O06_MASTER_LIST,HasilDFK_O09_TYPE_CODE,HasilDFK_O09_RESOURCE_ID,
HasilDFK_I01_DRIVER_SET_ID, HasilDFK_I02_DRIVER_SET_ID,HasilDFK_M14_ACT_DRV_SET_WL,
HasilVALUED_FLAG);
ELSE
SELECT ROW_NUMBER,M25_MODEL_ID,M18_BOR_TYPE_ID,O06_MASTER_LIST_SETS_ID,O09_TYPE_CODE,O09_RESOURCE_ID,
M33_SEQ_NBR,M33_UNIT_CONSUMPTION_QUANT,M33_COST_OBJECT_REF_QUANT,M33_UNIT_YIELD_FACTOR_QUANT,
ECO_NUMBER,BOM_LINE,EFFECTIVE_BEGIN_DATE,EFFECTIVE_END_DATE,MAINT_USER_ID,MAINT_EFFECT_CODE,
MAINT_TIMESTAMP,DFK_M25_MODEL_ID,DFK_M18_BOR_TYPE_ID,DFK_O06_MASTER_LIST_SETS_ID,DFK_O09_TYPE_CODE,
DFK_O09_RESOURCE_ID,DFK_I01_DRIVER_SET_ID,DFK_I02_DRIVER_SET_ID,DFK_M14_ACT_DRV_SET_BEGIN_DATE,
VALUED_FLAG
INTO
HasilROW_NUMBER, HasilM25_MODEL_ID, HasilM18_BOR_TYPE_ID, HasilO06_MASTER_LIST_SETS_ID,
HasilO09_TYPE_CODE, HasilO09_RESOURCE_ID, HasilM33_SEQ_NBR, HasilM33_UNIT_WELLY,
HasilM33_COST_OBJECT_REF_QUANT, HasilM33_UNIT_YIELD, HasilECO_NUMBER, HasilBOM_LINE,
HasilEFFECTIVE_BEGIN_DATE, HasilEFFECTIVE_END_DATE, HasilMAINT_USER_ID, HasilMAINT_EFFECT_CODE,
HasilMAINT_TIMESTAMP, HasilDFK_M25_MODEL_ID, HasilDFK_M18_BOR_TYPE_ID, HasilDFK_O06_MASTER_LIST,
HasilDFK_O09_TYPE_CODE, HasilDFK_O09_RESOURCE_ID, HasilDFK_I01_DRIVER_SET_ID, HasilDFK_I02_DRIVER_SET_ID,
HasilDFK_M14_ACT_DRV_SET_WL, HasilVALUED_FLAG
FROM activa.activa_m33_bill_of_res_line_s ;
-- variable insert m33
INSERT INTO activa.m33_bill_of_res_line (
M25_MODEL_ID,M18_BOR_TYPE_ID,O06_MASTER_LIST_SETS_ID, O09_TYPE_CODE, O09_RESOURCE_ID,
M33_BOR_LINE_ID_CTR, M33_SEQ_NBR, M33_UNIT_CONSUMPTION_QUANT, M33_COST_OBJECT_REF_QUANT,
M33_UNIT_YIELD_FACTOR_QUANT, ECO_NUMBER, BOM_LINE, EFFECTIVE_BEGIN_DATE, EFFECTIVE_END_DATE,
MAINT_USER_ID, MAINT_EFFECT_CODE, MAINT_TIMESTAMP, DFK_M25_MODEL_ID, DFK_M18_BOR_TYPE_ID,
DFK_O06_MASTER_LIST_SETS_ID, DFK_O09_TYPE_CODE, DFK_O09_RESOURCE_ID, DFK_I01_DRIVER_SET_ID,
DFK_I02_DRIVER_SET_ID, DFK_M14_ACT_DRV_SET_BEGIN_DATE, VALUED_FLAG)
VALUES (
HasilM25_MODEL_ID, HasilM18_BOR_TYPE_ID, HasilO06_MASTER_LIST_SETS_ID,HasilO09_TYPE_CODE,
HasilO09_RESOURCE_ID,HasilROW_NUMBER,HasilM33_SEQ_NBR,HasilM33_UNIT_WELLY,
HasilM33_COST_OBJECT_REF_QUANT,HasilM33_UNIT_YIELD,HasilECO_NUMBER,
HasilBOM_LINE,HasilEFFECTIVE_BEGIN_DATE,HasilEFFECTIVE_END_DATE,HasilMAINT_USER_ID,
HasilMAINT_EFFECT_CODE,HasilMAINT_TIMESTAMP, HasilDFK_M25_MODEL_ID,HasilDFK_M18_BOR_TYPE_ID,
HasilDFK_O06_MASTER_LIST,HasilDFK_O09_TYPE_CODE,HasilDFK_O09_RESOURCE_ID,
HasilDFK_I01_DRIVER_SET_ID, HasilDFK_I02_DRIVER_SET_ID,HasilDFK_M14_ACT_DRV_SET_WL,
HasilVALUED_FLAG);
END IF;
commit;
END;
***********************************************

Some body help me plzz :(

tx's

best regard's

-welly-

Report message to a moderator

Re: Problem ORA-01422 [message #38835 is a reply to message #38833] Sun, 19 May 2002 21:49 Go to previous messageGo to next message
HOW TO GET CONTEXT OF AN
Messages: 6
Registered: February 2002
Junior Member
COS THERE IS MORE THAN ONE RECORDS HAD BEEN SELECTED OUT IN YOUR SELECT - SQL .SO ORACLE THINK WHICH VALUE OF THE FIELD SHOULD BEEN STORED INTO THE VARIABLE ?
PLZ EXECUTE IT IN SQL-PLUS FIRST, AND YOU WILL FIND IT RETURN HOW MANY RECORDS .

Report message to a moderator

Re: Problem ORA-01422 [message #38837 is a reply to message #38833] Mon, 20 May 2002 03:23 Go to previous message
welly
Messages: 3
Registered: May 2002
Junior Member
hallo again..
i can solve my problem with
i'm create cursor and Loop at pl/sql Bodys.

tx's very much for all special for alex :)...your my best teacher :)

Best Regard's
-welly-

Report message to a moderator

Previous Topic: ****ORA-01422****Need Help!!
Next Topic: using ref cursor and for .. loop
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Apr 25 09:13:13 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.