Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2014
    Posts
    294

    Unanswered: Create stored procedure returns SQLCODE: -104, SQLSTATE: 42601

    Hi,

    I am facing few issues on my procedure, as I am new to these unable to figure it out. Kindly correct me if I am wrong.
    Below is my code & error:


    CREATE OR REPLACE PROCEDURE DUMMY2(IN TB_NAME VARCHAR(1024))
    AS:
    --LANGUAGE SQL--
    --DYNAMIC RESULT SETS 1--

    BEGIN
    ---DECLARE VAR1 VARCHAR(1024);--
    CALL DBMS_OUTPUT.PUT_LINE(TB_NAME);

    END@

    CREATE OR REPLACE PROCEDURE DUMMY1 ()
    LANGUAGE SQL
    DYNAMIC RESULT SETS 1

    P1: BEGIN
    DECLARE TB_NAME VARCHAR(1024);
    DECLARE EXITCODE INTEGER DEFAULT 0;
    -- Declare cursor
    DECLARE cursor1 CURSOR WITH RETURN TO CALLER for

    SELECT TBNAME FROM META_DISTINCT;
    DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET EXITCODE=1;

    -- Cursor left open for client application
    OPEN cursor1;
    L1:
    LOOP
    FETCH FROM cursor1 INTO TB_NAME;
    CALL DUMMY2(TB_NAME);
    IF EXITCODE=1 THEN
    LEAVE L1;
    END IF;
    ----EXIT WHEN cursor1 %NOTFOUND;----
    END LOOP L1;
    CLOSE cursor1;

    END P1

    Error:
    Deploy VENKATASD.DUMMY2(VARCHAR(1024))

    Running
    VENKATASD.DUMMY2 - Deploy started.
    Create stored procedure returns SQLCODE: -104, SQLSTATE: 42601.
    VENKATASD.DUMMY2: 10: An unexpected token "CREATE OR REPLACE PROCEDURE DUMMY2(IN TB_NA" was found following "BEGIN-OF-STATEMENT". Expected tokens may include: "<space>".. SQLCODE=-104, SQLSTATE=42601, DRIVER=3.67.28
    An unexpected token "CREATE OR REPLACE PROCEDURE DUMMY2(IN TB_NA" was found following "BEGIN-OF-STATEMENT". Expected tokens may include: "<space>".. SQLCODE=-104, SQLSTATE=42601, DRIVER=3.67.28
    VENKATASD.DUMMY2 - Deploy failed.
    VENKATASD.DUMMY2 - Roll back completed successfully.

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    if you had show the complete command to create the procedure ..
    probably not as
    db2 -td@ -vf xxxx
    because the default terminator ; can not be used...
    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

  3. #3
    Join Date
    Jul 2014
    Posts
    294

    Create stored procedure returns SQLCODE: -104, SQLSTATE: 42601

    @przytula_guy

    Could u please some what specific. I am unable to get u. I have provided the complete Procedure code.This is my entire procedure.
    Last edited by HABBIE; 07-30-14 at 07:30. Reason: Few things added

  4. #4
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    yes I could
    have a look at infocenter and try to understand how to create an sp from command window with specific delimiter or creating from a tool as datastudio and specifying default delimiter....
    People that do not know the basics of DB2 should not be allowed to touch the db in any kind....
    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

Tags for this Thread

Posting Permissions

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