DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: literal does not match format string

  1. #1
    Join Date
    May 2002
    Posts
    193

    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;
    /
    Last edited by diwakar; 09-18-2003 at 01:58 AM.

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    My guess is you have an explict format string (like a to_date or to_char) somewhere that you are passing garbage to.
    Jeff Hunter

  3. #3
    Join Date
    May 2002
    Posts
    193
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width