Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Location
    Bulgaria
    Posts
    6

    Unanswered: Import_Update problem

    Hi everybody,

    I have the following problem: I would like to import data from one table (tela_import_sap) into other (TELA_NEW). If there is exeption in primery key in TELA_NEW (y_nomer) I would like to import into table TELA_IMP_SAP_ERR.
    Then I would like to Update some columns in TELA_NEW with information from tela_import_sap. When I try to compile the procedure I receive the following error:

    "PLS-00201: identifier 'CUR_UPDATE' must be declared"

    Could you help me?
    Thank you in advance.



    Below is the PL/SQL code.

    CREATE OR REPLACE procedure prcTLIMPSAP
    (activeuser in varchar2, res_input out integer, reccount out integer) is


    begin
    declare
    V_Z_NOMER VARCHAR2(12);
    V_Y_NOMER VARCHAR2(12);
    V_DESCRIPT VARCHAR2(50);
    V_DESCRIPT2 VARCHAR2(50);
    V_TYPE VARCHAR2(1);
    V_YESNO VARCHAR2(1);
    V_DATE_INPUT_SAP VARCHAR2(10);
    V_DATE_INPUT_VIQS VARCHAR2(10);
    V_USER_ID VARCHAR2(20);
    RECORD_COUNT integer;

    CURSOR cur_import is
    SELECT z_nomer, y_nomer, z_descript, y_descript, mrp_controler,
    active, date_input_sap
    FROM tela_import_sap
    WHERE active='1';

    CURSOR cur_update is
    SELECT z_nomer, y_nomer, z_descript, y_descript, mrp_controler,
    active, date_input_sap
    FROM tela_import_sap;



    begin


    SELECT count(*) INTO RECORD_COUNT FROM TELA_IMP_SAP_ERR;

    FOR myRecord IN cur_import LOOP

    begin
    INSERT INTO TELA_NEW (Z_NOMER, Y_NOMER,
    DESCRIPT, DESCRIPT2, TYPE, YESNO,
    DATE_INPUT_SAP, DATE_INPUT_VIQS,
    USER_ID)
    values( myRecord.Z_NOMER, myRecord.Y_NOMER, myRecord.Z_DESCRIPT,
    myRecord.Y_DESCRIPT, myRecord.MRP_CONTROLER, myRecord.ACTIVE,
    myRecord.DATE_INPUT_SAP,
    sysdate,
    activeuser);
    COMMIT;
    -- End Import Data into Tela_new



    exception
    when DUP_VAL_ON_INDEX then
    --raise_application_error (-20001,'????????');
    INSERT INTO TELA_IMP_SAP_ERR (Z_NOMER, Y_NOMER,
    Z_DESCRIPT, Y_DESCRIPT,
    DATE_INPUT_SAP,
    MRP_CONTROLER)
    VALUES (myRecord.Z_NOMER, myRecord.Y_NOMER, myRecord.Z_DESCRIPT,
    myRecord.Y_DESCRIPT, myRecord.DATE_INPUT_SAP, myRecord.MRP_CONTROLER);
    end;
    COMMIT;
    END LOOP;

    SELECT count(*) INTO reccount FROM TELA_IMP_SAP_ERR;
    IF reccount>0 THEN
    res_input:=1;
    ELSE
    res_input:=0;
    END IF;
    end;

    begin



    FOR myRecord_update IN cur_update LOOP

    update tela_new t1
    set t1.TYPE=

    (select t2.MRP_CONTROLER
    from tela_import_sap t2 , tela_new t1
    where
    t1.y_nomer=t2.y_nomer
    AND t1.Y_NOMER=cur_update.y_nomer)
    WHERE t1.Y_NOMER=cur_update.y_nomer;
    COMMIT;
    END LOOP;

    -- End Update Data into Tela_new

    DELETE FROM Tela_Import_Sap;
    end;

    COMMIT;
    end prcTLIMPSAP;
    /

    Best regards
    Detelin Nedev

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    AFAIK, it is not valid syntax to have two "begin" statements for a single procedure.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    Please read the documentation on creating procedures:
    http://download-west.oracle.com/docs...8_subs.htm#525

    Specifically, a sub-programs declare section is implied by the "create ..." line you do not need to specify "declare". In PL/SQL in general your declares will always go before the first begin. You would help us all help you a lot be simply formatting your code (use the constructs). Check out using VB codes http://www.dbforums.com/misc.php?do=bbcode.

    As a follow on to other comments it is okay to have multiple "begin" statements as long as you have matching "end" statements.
    NOTE: Please disregard the label "Senior Member".

Posting Permissions

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