jorgeani
asked on
Error: ORA-00604: error produced at level 1 of recursivo - SQL ORA-01400: an insertion NULL cannot be made in ("SYS"."obj$"."name")
I have this pl/sql function:
------------------
CREATE OR REPLACE
FUNCTION GET_ISSUER (CpyCode VARCHAR2, ItemId VARCHAR2, TitleType VARCHAR2, InvEntity VARCHAR2)
RETURN VARCHAR2
IS
type issuer_type is ref cursor;
c_issuer issuer_type;
v_issuer ISSUER_CFG%ROWTYPE;
type issuer_rec is record
(num_errors ISSUER_CFG.ACEPTED%TYPE,
issuer_id ISSUER_CFG.ISSUER_ID%TYPE) ;
type v_issuers is table of issuer_rec
index by binary_integer;
v_iss v_issuers;
i binary_integer := 0;
aux1 varchar2(12);
aux2 number(1);
sSQL varchar2(2000);
BEGIN
sSQL :=
'SELECT CPY_CODE, ISSUER_ID, TITLE_TYPE, INV_ENTITY, NAME, ACEPTED ' ||
'FROM ISSUER_CFG ' ||
'WHERE ' ||
' CPY_CODE = "' || CpyCode || '" AND TITLE_TYPE = "' || TitleType || '"';
IF InvEntity != null AND trim(InvEntity) != "" THEN
sSQL := sSQL || ' AND INV_ENTITY = "' || InvEntity || '"';
END IF;
OPEN c_issuer FOR sSQL;
-- get cursor
LOOP
FETCH cur_issuer INTO v_issuer;
EXIT WHEN cur_issuer%NOTFOUND;
i := i + 1;
v_iss(i).issuer_id := v_issuer.ISSUER_ID;
-- get errors number
v_iss(i).num_errors := GET_COINCIDENCES(ItemId, v_iss(i).issuer_id);
END LOOP;
-- order issuers by less number errors
FOR j IN 1 .. v_iss.COUNT-1 LOOP
IF v_iss(j).num_errors > v_iss(j+1).num_errors THEN
aux1 := v_iss(j).num_errors;
aux2 := v_iss(j).issuer_id;
v_iss(j).num_errors := v_iss(j+1).num_errors;
v_iss(j).issuer_id := v_iss(j+1).issuer_id;
v_iss(j+1).num_errors := aux1;
v_iss(j+1).issuer_id := aux2;
END IF;
END LOOP;
IF v_iss.COUNT = 0 OR v_iss(1).num_errors = length(ItemId) THEN
RETURN '0';
ELSE
RETURN v_iss(1).issuer_id;
END IF;
END;
------------------
When I compile it gives me this error:
Error: ORA-00604: error produced at level 1 of recursive SQL
ORA-01400: an insertion NULL cannot be made in ("SYS"."obj$"."name")
What can I do?
Help me plase
------------------
CREATE OR REPLACE
FUNCTION GET_ISSUER (CpyCode VARCHAR2, ItemId VARCHAR2, TitleType VARCHAR2, InvEntity VARCHAR2)
RETURN VARCHAR2
IS
type issuer_type is ref cursor;
c_issuer issuer_type;
v_issuer ISSUER_CFG%ROWTYPE;
type issuer_rec is record
(num_errors ISSUER_CFG.ACEPTED%TYPE,
issuer_id ISSUER_CFG.ISSUER_ID%TYPE)
type v_issuers is table of issuer_rec
index by binary_integer;
v_iss v_issuers;
i binary_integer := 0;
aux1 varchar2(12);
aux2 number(1);
sSQL varchar2(2000);
BEGIN
sSQL :=
'SELECT CPY_CODE, ISSUER_ID, TITLE_TYPE, INV_ENTITY, NAME, ACEPTED ' ||
'FROM ISSUER_CFG ' ||
'WHERE ' ||
' CPY_CODE = "' || CpyCode || '" AND TITLE_TYPE = "' || TitleType || '"';
IF InvEntity != null AND trim(InvEntity) != "" THEN
sSQL := sSQL || ' AND INV_ENTITY = "' || InvEntity || '"';
END IF;
OPEN c_issuer FOR sSQL;
-- get cursor
LOOP
FETCH cur_issuer INTO v_issuer;
EXIT WHEN cur_issuer%NOTFOUND;
i := i + 1;
v_iss(i).issuer_id := v_issuer.ISSUER_ID;
-- get errors number
v_iss(i).num_errors := GET_COINCIDENCES(ItemId, v_iss(i).issuer_id);
END LOOP;
-- order issuers by less number errors
FOR j IN 1 .. v_iss.COUNT-1 LOOP
IF v_iss(j).num_errors > v_iss(j+1).num_errors THEN
aux1 := v_iss(j).num_errors;
aux2 := v_iss(j).issuer_id;
v_iss(j).num_errors := v_iss(j+1).num_errors;
v_iss(j).issuer_id := v_iss(j+1).issuer_id;
v_iss(j+1).num_errors := aux1;
v_iss(j+1).issuer_id := aux2;
END IF;
END LOOP;
IF v_iss.COUNT = 0 OR v_iss(1).num_errors = length(ItemId) THEN
RETURN '0';
ELSE
RETURN v_iss(1).issuer_id;
END IF;
END;
------------------
When I compile it gives me this error:
Error: ORA-00604: error produced at level 1 of recursive SQL
ORA-01400: an insertion NULL cannot be made in ("SYS"."obj$"."name")
What can I do?
Help me plase
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ok, my error was in this line...
IF InvEntity != null AND trim(InvEntity) != "" THEN
I replaced by:
IF InvEntity is not null and trim(InvEntity) != '' THEN
thanks.
IF InvEntity != null AND trim(InvEntity) != "" THEN
I replaced by:
IF InvEntity is not null and trim(InvEntity) != '' THEN
thanks.
ASKER
Error: ORA-00604: error produced at level 1 of recursive SQL
ORA-01400: an insertion NULL cannot be made in ("SYS"."obj$"."name")
What does it mean? What can I do?