Results 1 to 2 of 2

Thread: SP error..

  1. #1
    Join Date
    May 2013
    Posts
    3

    Unanswered: SP error..

    Hi All

    can anybody look into the below issue...

    When i ran the below SP getting the error..Please check it and suggest me.

    SP:
    ================================================== ====
    CREATE PROCEDURE sp_delete_ods_error(v_segment VARCHAR2(50))
    LANGUAGE SQL
    P1: BEGIN
    DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
    DECLARE v_ERR_ID BIGINT;
    DECLARE cur1 CURSOR FOR SELECT ERR_ID FROM ODS_ETL.ODS_STAGING_ERR WHERE RCRD_TYPE_CD = v_segment;
    OPEN cur1;
    FETCH FROM cur1 INTO v_ERR_ID;
    WHILE(SQLSTATE = '00000') DO
    DELETE FROM ODS_ETL.ODS_STAGING_ERR
    WHERE ERR_ID = v_ERR_ID;
    COMMIT;

    FETCH FROM cur1 INTO v_ERR_ID;
    END WHILE;
    CLOSE cur1;
    END P1;
    ===============================================

    Error :


    Error: com.ibm.db2.jcc.b.SqlException: DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: END-OF-STATEMENT;R(5) DEFAULT '00000';<psm_semicolon>, SQL State: 42601, Error Code: -104
    Error occured in:
    CREATE PROCEDURE sp_delete_ods_error(v_segment VARCHAR2(50))
    LANGUAGE SQL
    P1: BEGIN
    DECLARE SQLSTATE CHAR(5) DEFAULT '00000'

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    many times repeated question...
    search for delimiter
    db2 -td@ -vf ....
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

Posting Permissions

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