Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18

    Unanswered: SQL0104N using table-function in FOR loop of anonymous block

    Using DB2 v10.1 on Linux (x64) RHEl 6.3

    I want to display contents of all compilation-environments currently in the package-cache.

    To do this I use an anonymous block, iterating over all comp_env_desc values reported by table function MON_GET_PKG_CACHE_STMT,
    and for each of those use table function COMPILATION_ENV to decode the blob into name,value pairs.

    Getting a syntax error SQL0104N when I use a table function in a cursor FOR loop (see code below, works with any database)

    I don't understand the reason for this error, I'm sure I'm doing something silly, suggestions?



    Code:
    --#SET TERMINATOR @
    
    set serveroutput on
    @
    
    begin
       FOR v AS cur1 CURSOR FOR select comp_env_desc from table(MON_GET_PKG_CACHE_STMT(NULL,NULL,NULL,-1))
       DO
           select name,value from table(compilation_env(v.comp_env_desc)) as t;
           call dbms_output.put_line('=====================================================================');
       END FOR;
    end
    @
    This reports:
    SQL0104N An unexpected token "table" was found following "lect name,value
    from". Expected tokens may include: ",". LINE NUMBER=4.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Shouldn't it be "FOR v IN select comp_env_desc from table(MON_GET..."?
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    The "For V IN" syntax is for PL/SQL, but I'm using SQL PL.

    I simplified the query as follows, but it gives the same SQL0104N error, as if the table-function is not valid in this context, though my reading of the documentation suggests that a fullselect is valid inside a for loop of a Compound SQL (compiled).

    Code:
    --#SET TERMINATOR @
    
    begin
       FOR v AS cur1 CURSOR FOR select comp_env_desc 
                            from table(MON_GET_PKG_CACHE_STMT(NULL,NULL,NULL,-1))
       DO
           select name,value from table(compilation_env(v.comp_env_desc)) as t;
       END FOR;
    end
    @

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    In a compound statement you can't just use SELECT - it must be a SELECT INTO, that is, the statement you're executing in the loop is incorrect.
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Doh!
    resolved, works now via an array and put_line(). Thank you n_i.

Posting Permissions

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