literal does not match format string

Dear All,

I executed the package body (for which I have given the coding below) in the following way :

exec Appts_Dss_Delta.appt_load

and Oracle says :

SQL> exec Appts_Dss_Delta.appt_load
appts_dss_agents,agents_load 17-SEP-03 -1861 ORA-01861: literal does not match format string Oracle
BEGIN Appts_Dss_Delta.appt_load; END;

*
ERROR at line 1:
ORA-01861: literal does not match format string
ORA-06512: at "DSS.APPTS_DSS_DELTA", line 204
ORA-06512: at line 1


Can anyone clarify where Iam wrong?? Just glance thru and give me a hint...


Regards,

K.Diwakar










CREATE OR REPLACE PACKAGE Appts_Dss_Delta AS
temprec_agent EAPP_AGENTS_TEMP%ROWTYPE;
temprec_contract EAPP_CONTRACT_TEMP%ROWTYPE;
temprec_appt_lic EAPP_LIC_TEMP%ROWTYPE;
PROCEDURE appt_load;
END Appts_Dss_Delta;
/
CREATE OR REPLACE PACKAGE BODY Appts_Dss_Delta AS
CURSOR tempcur_appt_lic
IS SELECT * FROM EAPP_LIC_TEMP;

CURSOR tempcur_agent
IS SELECT * FROM EAPP_AGENTS_TEMP;

CURSOR tempcur_contract
IS SELECT * FROM EAPP_CONTRACT_TEMP;


CURSOR extcur_agents (agent_tax_id_in IN VARCHAR2,company_Code_in IN VARCHAR2)
IS
SELECT agent_code,company_Code
FROM EAPP_DSS_AGENTS
WHERE agent_tax_id = agent_tax_id_in AND company_Code=company_Code_in;


PROCEDURE appt_load
IS
v_company_code VARCHAR2(3);
v_agent_code VARCHAR2(15);
v_agency_number VARCHAR2(10);
v_agent_tax_id VARCHAR2(15);
v_contract_type VARCHAR2(10);
v_lob_area VARCHAR2(3);
v_license_number VARCHAR2(16);
extrec_agent VARCHAR2(100);
v_cnt_agent NUMBER := 0;
lob_count NUMBER := 0;
lob_factor NUMBER := 0;
i_lob_position NUMBER := 0;
v_cnt_contract NUMBER := 0;
v_cnt_license NUMBER := 0;
v_cnt_appt_lic NUMBER := 0;
v_ErrorNumber NUMBER;
v_ErrorText VARCHAR2(200);
BEGIN



FOR temprec_agent IN tempcur_agent
LOOP
v_agent_code:=LTRIM(RTRIM(temprec_agent.agent_code));
v_agency_number:=LTRIM(RTRIM(temprec_agent.agency_number));
v_company_code:=LTRIM(RTRIM(temprec_agent.company_code));
v_agent_tax_id:=LTRIM(RTRIM(temprec_agent.agent_tax_id));
IF temprec_agent.action_indicator='A'
THEN
/*insert into eapp_dss_agents*/
INSERT INTO EAPP_DSS_AGENTS
(company_code,agent_code,agent_name,address_line_1,address_line_2,address_line_3,address_line_4,
address_line_5,state_code,zip_postal_code,country_code,tel_number,fax_number,agent_tax_id,agency_num ber,
agency_name,created_date)
VALUES
(v_company_code,v_agent_code,temprec_agent.agent_name,temprec_agent.address_line_1,
temprec_agent.address_line_2,temprec_agent.address_line_3,temprec_agent.address_line_4,
temprec_agent.address_line_5,temprec_agent.state_code,temprec_agent.zip_postal_code,temprec_agent.co untry_code,
temprec_agent.tel_number,temprec_agent.fax_number,v_agent_tax_id,v_agency_number,temprec_agent.agenc y_name,SYSDATE);

ELSIF temprec_agent.action_indicator='C'
THEN
/*update eapp_dss_agent*/
UPDATE EAPP_DSS_AGENTS SET
company_code=v_company_code,
agent_code=v_agent_code,
agent_name=temprec_agent.agent_name,
address_line_1=temprec_agent.address_line_1,
address_line_2=temprec_agent.address_line_2,
address_line_3=temprec_agent.address_line_3,
address_line_4=temprec_agent.address_line_4,
address_line_5=temprec_agent.address_line_5,
state_code=temprec_agent.state_code,
zip_postal_code=temprec_agent.zip_postal_code,
country_code=temprec_agent.country_code,
tel_number=temprec_agent.tel_number,
fax_number=temprec_agent.fax_number,
agent_tax_id=v_agent_tax_id,
agency_number=v_agency_number,
agency_name=temprec_agent.agency_name,
last_modified_date=SYSDATE
WHERE
agent_Code=v_agent_code AND company_code=v_company_code;
ELSE
/*delete eapp_dss_agent*/
DELETE FROM EAPP_DSS_AGENTS
WHERE agent_Code=v_agent_code AND company_code=v_company_code;
END IF;

v_cnt_agent := v_cnt_agent + 1;

IF MOD(v_cnt_agent,500) = 0 THEN
COMMIT;
END IF;

END LOOP;
COMMIT;
/* license detials*/
FOR temprec_appt_lic IN tempcur_appt_lic
LOOP
v_agent_tax_id:=LTRIM(RTRIM(temprec_appt_lic.agent_tax_id));
v_company_code:=LTRIM(RTRIM(temprec_appt_lic.company_code));
v_license_number:=LTRIM(RTRIM(temprec_appt_lic.license_number));

IF temprec_appt_lic.action_indicator='A'
THEN
FOR extrec_agent IN extcur_agents(v_agent_tax_id,v_company_code)
LOOP
INSERT INTO APPOINTMENT_LICENCE_DSS
(agent_code,company_code,agent_agency_source_code,lic_product_group_Code,
state_cd,lic_pend_status_cd,license_type,license_number,lic_eff_date,lic_expiration_date,
last_modified_date,agent_tax_id)
VALUES
(LTRIM(RTRIM(extrec_agent.agent_Code)),v_company_Code,'LYN',LTRIM(RTRIM(temprec_appt_lic.lob_area)),
temprec_appt_lic.state_code,temprec_appt_lic.appt_Status,temprec_appt_lic.license_type,
v_license_number,temprec_appt_lic.appt_effective_date,
temprec_appt_lic.appt_renewal_date,temprec_appt_lic.last_status_change_date,
v_agent_tax_id);

END LOOP; /*loop for extcur */

ELSIF temprec_appt_lic.action_indicator='C'
THEN
UPDATE APPOINTMENT_LICENCE_DSS
SET company_Code=v_company_Code,
state_cd=temprec_appt_lic.state_Code,
agent_tax_id=v_agent_tax_id,
lic_product_group_Code=LTRIM(RTRIM(temprec_appt_lic.lob_area)),
lic_pend_status_cd=temprec_appt_lic.appt_Status,
license_type=temprec_appt_lic.license_type,
license_number=v_license_number,
lic_eff_date=temprec_appt_lic.appt_effective_date,
lic_expiration_date=temprec_appt_lic.appt_renewal_date,
last_modified_date=temprec_appt_lic.last_status_change_date
WHERE
agent_tax_id=v_agent_tax_id
AND license_type=temprec_appt_lic.license_type
AND license_number=v_license_number
AND state_Cd=temprec_appt_lic.state_code
AND company_Code=v_company_code;
ELSE
/*delete eapp_dss_contract*/
DELETE FROM APPOINTMENT_LICENCE_DSS
WHERE agent_tax_id=v_agent_tax_id AND license_type=temprec_appt_lic.license_type
AND license_number=v_license_number AND state_Cd=temprec_appt_lic.state_code
AND company_Code=v_company_code;
END IF;
v_cnt_appt_lic := v_cnt_appt_lic + 1;

IF MOD(v_cnt_appt_lic,500) = 0 THEN
COMMIT;
END IF;
END LOOP;
COMMIT;

/*contract info*/
FOR temprec_contract IN tempcur_contract
LOOP
v_agent_code:=LTRIM(RTRIM(temprec_contract.agent_code));
v_company_code:=LTRIM(RTRIM(temprec_contract.company_code));
v_contract_type:=LTRIM(RTRIM(temprec_contract.contract_type));
IF temprec_contract.action_indicator='A'
THEN
/*insert into eapp_dss_contract*/
INSERT INTO EAPP_DSS_CONTRACT
(company_code,agent_code,contract_type,effective_date,created_date)
VALUES
(v_company_code,v_agent_code,
v_contract_type,temprec_contract.effective_date,SYSDATE);

ELSIF temprec_contract.action_indicator='C'
THEN
/*update eapp_dss_contract*/
UPDATE EAPP_DSS_CONTRACT SET
company_code=v_company_code,
agent_code=v_agent_code,
contract_type=v_contract_type,
effective_date=temprec_contract.effective_date,
last_modified_date=SYSDATE
WHERE
agent_Code=v_agent_code AND company_code=v_company_code
AND contract_type=v_contract_type;
ELSE
/*delete eapp_dss_contract*/
DELETE FROM EAPP_DSS_CONTRACT
WHERE agent_Code=v_agent_code AND company_code=v_company_code
AND contract_type=v_contract_type;
END IF;

v_cnt_contract := v_cnt_contract + 1;

IF MOD(v_cnt_contract,500) = 0 THEN
COMMIT;
END IF;

END LOOP;
COMMIT;

EXCEPTION
WHEN OTHERS THEN
v_ErrorNumber := SQLCODE;
v_ErrorText := SUBSTR(SQLERRM, 1, 200);
dbms_output.put_line('appts_dss_agents,agents_load '||SYSDATE||' '||v_ErrorNumber||' '||v_ErrorText||' '||'Oracle erroro ccurred AT ' || TO_CHAR(SYSDATE, 'DD-MON-YY HH24:MI:SS')||' '||v_agent_code||' '||v_agent_tax_id);
RAISE;
END appt_load;
END Appts_Dss_Delta;
/

My guess is you have an explict format string (like a to_date or to_char) somewhere that you are passing garbage to.

Dear Marist,

Could it be this way that the procedure is trying to insert values into the table whose columns are defined as date but the values are not actually in the DATE format?

Regards,

K.Diwakar