Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2010
    Posts
    31

    Unanswered: Dynamic Open Cursor statement

    Hi,

    I have a stored procedure which calls other 3 stored procedures the logic is like

    Stored Procedure - Main

    declare cursor
    select ... from the table.

    open cursor
    fetch into the fields
    while at_end = 0
    call insert_procedure (table_name);
    call update_procedure (table_name);
    call delete_procedure (table_name);

    fetch into the fields
    end while
    close cursor.

    I have to use open/fetch/close cursor statements in insert_procedure, update_procedure, and delete_procedure;

    But the problem is when main procedure calls those 3 stored procedures, sometimes the open/fetch/close cursor statements work and sometime these statements do not work.

    But If I call these 3 stored procedures individually with the parameter values, these stored procedures work fine.

    I tried to use execute immediate command to open and close the cursors but this command seems not working.

    Can please someone help me as I can not combine these 3 stored procedures into one stored procedure because of the complexity of the logic.

    Naveed

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Quote Originally Posted by navch View Post
    But the problem is when main procedure calls those 3 stored procedures, sometimes the open/fetch/close cursor statements work and sometime these statements do not work.
    whats the error? any description of "do not work" will be useful.

    I tried to use execute immediate command to open and close the cursors but this command seems not working.

    Naveed
    What did you try and "not working" ?

    Also, do not forget to mention the db2 version and platform.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Jan 2010
    Posts
    31
    We are using DB2 V9.5 on Linux Red Hat Enterprise Linux Server release 3.9.

    The following is the error message when I run the stored procedure using the execute immediate statement to open/close cursor.


    LAS.TEST_LAS_UPDATE_STMT - Run started.
    LAS.TEST_LAS_UPDATE_STMT - Exception occurred while running:
    A database manager error occurred.[IBM][CLI Driver][DB2/LINUXX8664] SQL0104N An unexpected token "END-OF-STATEMENT" was found following "en las_update_cursor". Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601

    LAS.TEST_LAS_UPDATE_STMT - Roll back completed successfully.
    LAS.TEST_LAS_UPDATE_STMT - Run failed.

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Clearly, You have a syntax error.

    Fixing it should solve the problem
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Jan 2010
    Posts
    31
    I do not think there is any syntax error. Below is my coding.


    CREATE PROCEDURE las.test_las_update_stmt (in p_table_name varchar(50))

    SPECIFIC las.test_las_update_stmt
    DYNAMIC RESULT SETS 1
    LANGUAGE SQL
    NOT DETERMINISTIC
    CALLED ON NULL INPUT
    MODIFIES SQL DATA
    INHERIT SPECIAL REGISTERS
    ------------------------------------------------------------------------
    -- SQL Stored Procedure
    ------------------------------------------------------------------------
    P1: BEGIN

    declare sfildname varchar(50); -- field name
    declare sdatatype varchar(50); -- data type
    declare sprimarykey varchar(3);
    declare sql_stmt varchar(9999);
    declare sql_cursor varchar(9999);

    declare sql_1 varchar(3000);
    declare sql_2 varchar(3000);
    declare sql_3 varchar(3000);
    declare sql_4 varchar(3000);
    declare sql_5 varchar(3000);
    declare sql_6 varchar(15000);

    declare at_end int default 0;

    declare las_update_cursor cursor for
    select distinct columnname, dattype, primarykey
    from las.las_table_info
    where columnname not in ('ETL_CREATE_DT', 'ETL_LAST_UPDT_DT')
    with ur;

    -- Declare handler
    declare continue handler for not found
    set at_end = 1;

    begin

    set sql_1 = '';
    set sql_2 = '';
    set sql_3 = '';
    set sql_4 = '';
    set sql_5 = '';
    set sql_6 = '';
    set at_end = 0;

    delete from naveed.table_name with ur;

    set sql_stmt = 'insert into naveed.table_name select distinct name from app.las_xml_stage, xmltable(''$c/dataChanges/table'' passing XML_DATA as "c" ' ||
    'COLUMNS name varchar(30) path ''@name'') where xmlexists (''$c/dataChanges/table[@name="' || p_table_name ||
    '"]/row[@type="U"]'' passing XML_DATA as "c")' ;

    execute immediate sql_stmt;

    set sql_3 = ' from app.las_xml_stage' || ' , xmltable(''$c/dataChanges/table[@name="' || rtrim(p_table_name) ||
    '"]/row[@type="U"]'' passing XML_DATA as "c" COLUMNS ';


    set sql_cursor = 'open las_update_cursor';
    execute immediate sql_cursor;

    -- open las_update_cursor;

    fetch las_update_cursor into sfildname, sdatatype, sprimarykey;

    while at_end = 0 do

    insert into naveed.table_name values ('TABLE WHILE');


    set sql_1 = sql_1 || sfildname || ',';

    set sql_3 = sql_3 || sfildname || ' ' || sdatatype || ' path ''' || sfildname || ''',';

    if sprimarykey = 'YES' then
    set sql_4 = sql_4 || 'base.' || sfildname || ' = stage.' || sfildname || ' and ';
    end if;

    if sprimarykey = 'NO' then
    set sql_5 = sql_5 || 'base.' || sfildname || ' = stage.' || sfildname || ',';
    end if;

    fetch las_update_cursor into sfildname, sdatatype, sprimarykey;

    end while;

    --close las_update_cursor;

    set sql_cursor = 'close las_update_cursor';
    execute immediate sql_cursor;


    set sql_1 = substr(sql_1, 1, length(sql_1) - 1);

    set sql_2 = 'merge into (select * from las.' || p_table_name || ') base using (select ' || sql_1 || '' ;

    set sql_6 = sql_2 || substr(sql_3, 1, length(sql_3) - 1) || ') ) as stage on ' ||
    substr(sql_4, 1, length(sql_4) - 5) || ' when matched then update
    set base.etl_last_updt_dt = current date, ' || substr(sql_5, 1, length(sql_5) -1) || ' with ur';

    delete from las.las_insert_stmt;

    if exists (select * from naveed.table_name) then
    insert into las.las_insert_stmt (tablename,
    tbaction,
    sqlstatement
    )

    values (p_table_name, 'UPDATE', sql_6);
    end if;
    end;

    END P1

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by RTFM
    OPEN statement

    ...

    this statement can only be embedded within an application program. It is an executable statement that cannot be dynamically prepared.
    The message you have entered is too short. Please lengthen your message to at least 10 characters.

  7. #7
    Join Date
    Jan 2010
    Posts
    31
    Even, I have modified these 3 stored procedures to be run independent without embedded in the Main stored procedure.

    But it seems like the call insert stored procedure being mentioned the first procedure to be executed does not return any values. It looks like the cursor is not opened.

    But if I compile this procedure and then call it, it works for one time only and when I run it again, it does not return anything.

    Nav

Posting Permissions

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