literal does not match format string
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: literal does not match format string

Threaded View

  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 02:58 AM.

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