Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

FOREACH in a stored porcedure

FOREACH in a stored porcedure

FOREACH in a stored porcedure

I'm runnig 7.3 and am having some problems with syntax.

Here is a foreach in a stored procedure:

FOREACH temp_table FOR
from ..
where ..

How do I access values from the temp_table in another SQL statement in this stored procedure?

I was going to use temporary tables in the stored procedure, but I discovered that this causes many problems when multiple people attempt to called on the same procedure.  I'd use cursors, but apparently the foreach statement is actually an implicit cursor (!).  Also you cannot delcare an explicit cursor in a procedure!  Any help would be great!


RE: FOREACH in a stored porcedure


I'm sorry, but I'm not following the problem you're having with temp tables in stored procedures.

You're right about a foreach being an implicit cursor;  Within a temp table, you can create a temp table, populate it with data with DML statements, and access the temp table with a FOREACH statement.

I think you'll have to post more information about the problems you're having.



RE: FOREACH in a stored porcedure


When you create a procedure with the following syntax:

SELECT a, b, c
FROM alphabet
INTO TEMP alpha;

{Select from the temp table within the procedure.}
SELECT a, b, c, 1 sort
FROM alpha

it creates a temp table called alpha that is stored in the database.  Unfortunately if a number of users execute the same procedure some users receive ane error like. "Table alpha already exists in the database."

In Oracle, a work around is to use a cursor, which basically stores a temporary table in specified memory address, but not in the database.  So users can execute the procedure without any error messages.  Informix's answer is to create an implicit cursor within a procedure using the FOREACH syntax.  Unfortunately I have no idea how to open the cursor within a stored procedure.  The informix 7.3 manual says you can access a cursor using OPEN name_cursor, but then later it says that OPEN cannot be used within a stored procedure (!).   Thanks for any help that you can offer.   

RE: FOREACH in a stored porcedure


You have found a bug in Informix.  In the early 90's I had a similar problem with Informix 4GL since fixed.  If you have Informix tech support, I'd report it.

In the mean-time, there's two things you can try:

1) Before exiting the stored procedure, drop the temp table.

2) Instead of creating the temp table with the select, create the temp table stand alone, and then populate it with a select .. insert into the temp table.  

(I'd create it with 'no log'.  No sense sending this output to the Logical Logs).

Sorry, I can't be of more help.



Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close