Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2012
    Location
    Bucks, UK
    Posts
    13

    Unanswered: Use Temporary table in Stored Proc

    Hi
    I'm trying to find a way of allowing our users to define SPs that process an array. I can see that the PL/SQL features can accomplish this, and I've found much acrimonious debate in the forums about whether this is a good idea or not. The DB2 workaround seems to be to declare a temporary table, populate that and then call the SP to work throught the data stored. But when I've declared a DGTT and then tried to define an SP to reference it, I get the SQL0526N message which is pretty explicitly saying you can't do this.

    Is the solution just to define a 'real' table temporarily and reference that or is there some cleverer option available? We're currently LUW V9.7
    Cheers
    Mark

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I do not know if this will work or not, but with V9.7 you have the feature of CGTT. It is basically a DGTT that is declared in the catalog. You can try that:

    IBM DB2 9.7 Information Center for Linux, UNIX, and Windows

    Andy

  3. #3
    Join Date
    Feb 2012
    Location
    Bucks, UK
    Posts
    13

    Use Temporary table in Stored Proc

    Thanks Andy, that seems to have done the trick

    All I need to do now is to get the SP working, but at least it is accepting that CGTT as input and has removed the SQL0526N.....

Posting Permissions

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