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

    Unanswered: SQLCODE=-518, SQLSTATE=07003 dynamic select

    Hi,

    I am trying to perform dynamic sql in stored procedures.
    This is my code

    CREATE PROCEDURE TR (IN e_method varchar(10))
    DYNAMIC RESULT SETS 1
    P1: BEGIN
    -- Declare cursor
    DECLARE TEXT VARCHAR(32672);
    DECLARE STMT1 VARCHAR(32672);
    DECLARE STMT2 VARCHAR(32672);
    DECLARE STMT3 VARCHAR(32672);
    DECLARE l_col_specific_str1 VARCHAR(1000);
    ---DECLARE CONTINUE HANDLER FOR NOT FOUND
    ---SET exitcode = 1;
    --DECLARE cursor1 CURSOR WITH RETURN for STMT;
    SET TEXT = 'SELECT TR FROM M_L where CODE = ''' || encryption_method ||'''' ;

    PREPARE STMT1 FROM TEXT;
    EXECUTE STMT1 into STMT3;
    ---OPEN cursor1;
    END P1

    error:

    The statement named in the EXECUTE statement is not in a prepared state or is a SELECT or VALUES statement.. SQLCODE=-518, SQLSTATE=07003, DRIVER=4.17.30
    Run of routine failed.
    - Roll back completed successfully.

    I am trying to perform dynamic select query without using multiple cursors. Kindly help me.

  2. #2
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    try this:
    Code:
    --#SET TERMINATOR @
    
    set serveroutput on@
    
    begin
      declare text varchar(128);
      declare stmt3 varchar(128);
      set text='set ? = (select ''hello, world'' from sysibm.sysdummy1)';
      prepare s1 from text;
      execute s1 into stmt3;
      call dbms_output.put_line(stmt3);
    end@
    Regards,
    Mark.

  3. #3
    Join Date
    Jul 2014
    Posts
    294
    ThanQ... I can work now.
    Last edited by HABBIE; 10-15-14 at 11:29.

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
  •