Hi,
1.
I want to call a procedure from a procedure..
The calling procedure returns a result set.
How do I proceed and what is the way of doing it.
I am a novice to Informix and migrating a stored procedure from oracle.
Could any one help me in solving this issue.
2.
I get an error saying "460: Statement length exceeds maximum."
When I execute a procedure. The total length of the procedure is around 70KB. what should I do in order to increase the maximum length..

thanks and Regards
Arvind

I Think we need more input here:
Can you post the two procedures?
When do you get the error? On the execute procedure statement?

CREATE PROCEDURE ONE ( id int)
RETURNING VARCHAR(10),VARCHAR(10);
DEFINE v_name;
DEFINE v_add;
If (id =2 )
FOREACH Outputcur FOR
SELECT Ename, Address INTO v_name, v_add FROM Employee
RETURN v_name, v_add WITH RESUME;
END FOREACH
ELSE
CALL PROCEDURE TWO(id int);
END PROCEDURE;

Now if the condition does not match and goes to the else part ..then what about the return value of the ONE procedure. How this takes place How should i proceed..


For 2nd question.
There was a mistake it was not happening while executing the procedure it was happening while CREATING the procedure.

Thanks for the immediate reply

Why don't you crate a third (central) procedure:

CREATE PROCEDURE TEST (int id)
If (id =2 )
CALL PROCEDURE ONE;
ELSE
CALL PROCEDURE TWO;
END IF
END PROCEDURE

CREATE PROCEDURE ONE
RETURNING VARCHAR(10),VARCHAR(10);
DEFINE v_name;
DEFINE v_add;
FOREACH Outputcur FOR
SELECT Ename, Address INTO v_name, v_add FROM Employee
RETURN v_name, v_add WITH RESUME;
END FOREACH
END PROCEDURE

By doing so As the calling procedure (one, two) returns more than one value errors will be arising..
I want to call a procedure inside a procedure which will written set of rows...is it possible in informix ???

Fro a procedure returning more than one row you need:

fareach execute procedure one into ..., ...

...

end foreach;

Error -460:

The statement text in this PREPARE, DECLARE, or EXECUTE IMMEDIATE statement is longer than the database server can handle. The actual limit differs with different implementations, but it is always generous, in most cases up to 32,000 characters. Review the program logic to ensure that an error has not caused it to present a string that is longer than intended (for example, by overlaying the null string terminator byte in memory). If the text has the intended length, revise the program to present fewer statements at a time.

I think you might want to dig deeper in yr code.


The WITH RESUME option.

It will continue to return rows until no more rows are to be returned.

Thanks alot Roelwe
It really helped me solve a big issue
thanks once again