Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2002
    Location
    India
    Posts
    6

    Question Unanswered: calling a procedure

    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

  2. #2
    Join Date
    Aug 2002
    Location
    Belgium
    Posts
    534

    Re: calling a procedure

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

  3. #3
    Join Date
    Oct 2002
    Location
    India
    Posts
    6

    Re: calling a procedure

    Originally posted by Roelwe
    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

  4. #4
    Join Date
    Aug 2002
    Location
    Belgium
    Posts
    534
    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
    rws

  5. #5
    Join Date
    Oct 2002
    Location
    India
    Posts
    6
    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 ???

  6. #6
    Join Date
    Aug 2002
    Location
    Belgium
    Posts
    534
    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.
    rws

  7. #7
    Join Date
    Oct 2002
    Location
    India
    Posts
    6

    Thumbs up

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





    Originally posted by Roelwe
    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.

Posting Permissions

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