Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2013
    Posts
    1

    Unanswered: Dynamic Select for Cursor -> %Rowtype not working

    Hello,

    I'd like to write a procedure, which processes actions (mainly insert/delete), which an autocommit after a defined amount of actions.


    Therefore I've created a procedure, which takes a parameter and opens it in a cursor - something like this:

    Code:
    CREATE OR REPLACE procedure doSomething
    (IN sqlstring varchar(200))
    LANGUAGE SQL
    BEGIN
       DECLARE c1 weakCursorType;
       DECLARE myVar jurlvRow;
    
       DECLARE lvid varchar(20);
    
       DECLARE SQL_CURS1 CHAR(100) ;
       --DECLARE STMT_CURS1 CHAR(100); 
       
     
       DECLARE CURS2 CURSOR WITH RETURN FOR STMT_CURS1;    
    
       
       PREPARE STMT_CURS1 FROM sqlstring ;
    
       OPEN CURS2;
    
       FETCH CURS2 INTO myVar;
    
       CALL DBMS_OUTPUT.PUT_LINE(myVar.lvid || '   ' || myVar.bearbid );
    
       close CURS2; 
       
       RETURN 0;   
    END
    @
    
    SET SERVEROUTPUT ON@
    
    call doSomething('SELECT * from TABLENAME')@
    This technically works, but I've to predefine for each table the Type with

    Code:
    CREATE or replace TYPE jurlvRow AS ROW ANCHOR ROW OF TABLENAME@
    I found in various documentations of DB2 (we are using DB2 9.7 on Windows) that there is an equivalent for %ROWTYPE of Oracle - but it's usually used in the block between IS and BEGIN.
    I'm not familiar with DB2, but somehow this version doesn't recognise this syntax?

    Another question is - is there a possibility to insert the values, which were assigned to a %rowtype-like structure to another table, or is it necessary to do this manually?

    Thanks in advance,

    Best regards Martin

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by somebody View Post
    I found in various documentations of DB2 (we are using DB2 9.7 on Windows) that there is an equivalent for %ROWTYPE of Oracle - but it's usually used in the block between IS and BEGIN.
    I'm not familiar with DB2, but somehow this version doesn't recognise this syntax?
    You will need to create the database in the Oracle compatibility mode for DB2 to recognize the Oracle-like syntax constructs. DB2 compatibility features introduction

    Quote Originally Posted by somebody View Post
    Another question is - is there a possibility to insert the values, which were assigned to a %rowtype-like structure to another table, or is it necessary to do this manually?
    Yes.
    Features of the row data type
    INSERT
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

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