Error in Execute Procedure (Error # 674)

Posted in 2005

Summary

A user porting an Oracle procedure with an OUT parameter wrote an Informix SPL routine as CREATE PROCEDURE test_proc(val1 INT, OUT r_cnt INT), then got error -674 "Routine (test_proc) cannot be resolved" when calling EXECUTE PROCEDURE test_proc(3), since the routine was defined with two parameters. Respondents explained that SPL returns values via a RETURNING/RETURNS clause, not OUT parameters (OUT applies only to external C/Java UDFs), and that the routine should be written as CREATE FUNCTION test_proc(val1 INT) RETURNING INT with a RETURN statement.

Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.

Topics: Stored Procedures & SPL

Prashant Shirude — Thu, 13 Jan 2005 22:52:53 — source: IIUG Forums & Mailing Lists

Hi All, Here is the procedure: create procedure test_proc(val1 INT, OUT r_cnt INT) define x char(10); define r_cnt INT; LET r_cnt = 0; begin set debug file to "test_proc.out"; trace "begin trace"; trace on; IF val1 = 3 THEN LET x = 'True-3'; LET r_cnt = val1; ELSE LET x = 'False-3'; LET r_cnt = 0; END IF; trace off; end end procedure; => EXECUTE PROCEDURE test_proc(3); Error: (674) Routine (test_proc) can not be resolved. Is anything wrong in Execute Procedure statement? Help needed. Prashant -----Original Message----- From: June C. Hunt [mailto:june_c_hunt@hotmail.com] Sent: Thursday, January 13, 2005 4:43 AM To: ids@iiug.org Cc: Prashant Shirude Subject: RE: How to execute procedure [3999] "Prashant Sh...." <PShirude@manh.com> wrote: > >CREATE PROCEDURE AUTO_APPROVE_SHIPMENT > (vshpid LIKE SHIPMENT.SHIPMENT_ID,> OUT vPassFail INT > ) > >How to execute above procedure when there is one Input and one out >parameter. Please see the IBM Informix Guide to SQL: Tutorial. You will find it here, if you don't already have it - http://www-306.ibm.com/software/data/informix/pubs/library/list2.html#G There is an entire chapter devoted to creating and using SPL routines. You'll want to review the format for creating a procedure (function) that returns a value, and there are several ways to execute a procedure or function. You haven't provided enough information about how you will be using the procedure to give you the syntax for execution. -- June Hunt

↪ replying to Prashant Shirude

Gregory S. — Thu, 13 Jan 2005 23:25:36 — source: IIUG Forums & Mailing Lists

You seem to have created a proecure with two paramaters, although the syntax is odd enough I wonder what is really going on ... The correct syntax for returning something from a procedure is something like: CREATE FUNCTION upd_billing_job (p_jobid INTEGER, p_early_time DATETIME YEAR TO SECOND, p_last_time DATETIME YEAR TO SECOND) RETURNING INTEGER;DEFINE someval INTEGER; <DEFINES, including > <CODE> RETURN(some_val); END FUNCTION; Your procedure lack a proper RETURNING clause ... as it is you need to give the procedure two integers in your call ... HTH, Greg Williamson DBA GlobeXplorer LLC -----Original Message----- From: Prashant Sh.... [mailto:PShirude@manh.com] Sent: Thu 1/13/2005 2:52 PM To: ids@iiug.org Cc: Subject: Error in Execute Procedure (Error # 674) [4008] Hi All, Here is the procedure: create procedure test_proc(val1 INT, OUT r_cnt INT) define x char(10); define r_cnt INT; LET r_cnt = 0; begin set debug file to "test_proc.out"; trace "begin trace"; trace on; IF val1 = 3 THEN LET x = 'True-3'; LET r_cnt = val1; ELSE LET x = 'False-3'; LET r_cnt = 0; END IF; trace off; end end procedure; => EXECUTE PROCEDURE test_proc(3); Error: (674) Routine (test_proc) can not be resolved. Is anything wrong in Execute Procedure statement? Help needed. Prashant -----Original Message----- From: June C. Hunt [mailto:june_c_hunt@hotmail.com] Sent: Thursday, January 13, 2005 4:43 AM To: ids@iiug.org Cc: Prashant Shirude Subject: RE: How to execute procedure [3999] "Prashant Sh...." <PShirude@manh.com> wrote: > >CREATE PROCEDURE AUTO_APPROVE_SHIPMENT > (vshpid LIKE SHIPMENT.SHIPMENT_ID,> OUT vPassFail INT > ) > >How to execute above procedure when there is one Input and one out >parameter. Please see the IBM Informix Guide to SQL: Tutorial. You will find it here, if you don't already have it - http://www-306.ibm.com/software/data/informix/pubs/library/list2.html#G There is an entire chapter devoted to creating and using SPL routines. You'll want to review the format for creating a procedure (function) that returns a value, and there are several ways to execute a procedure or function. You haven't provided enough information about how you will be using the procedure to give you the syntax for execution. -- June Hunt

↪ replying to Prashant Shirude

Obnoxio The Chav — Fri, 14 Jan 2005 01:16:52 — source: IIUG Forums & Mailing Lists

Prashant Sh.... said: > create procedure test_proc(val1 INT, OUT r_cnt INT) What are you doing with r_cnt? > define r_cnt INT; Are you perhaps looking for CREATE FUNCTION test_proc(val1 int) returning int; ??? -- Bye now, Obnoxio "C'est pas parce qu'on n'a rien à dire qu'il faut fermer sa gueule" - Coluche "I'm trying to see things your way, but I can't get my head up my ass" - JCH "Ogni uomo mi guarda come se fossi una testa di cazzo" - Marco If anyone told you to be yourself they would be giving you bad advice. I went to the airport to check in and they asked what I did because I looked like a terrorist. I said I was a comedian. They said, "Say something funny then." I told them I had just graduated from flying school. -- Ahmed Ahmed

↪ replying to Prashant Shirude

Prashant Shirude — Fri, 14 Jan 2005 04:41:50 — source: IIUG Forums & Mailing Lists

Actually I was converting Oracle Procedure into Informix. In Oracle: CREATE OR REPLACE PROCEDURE test_proc(val1 in NUMBER, r_cnt OUT NUMBER); I think, in Oracle r_cnt OUT returns the value from where it called. Converted in Informix: Create procedure test_proc(val1 INT, OUT r_cnt INT) Informix SPL doesn't support to return value. I think, I may need to write function. Prashant -----Original Message----- From: Gregory S. Williamson [mailto:gsw@globexplorer.com] Sent: Thursday, January 13, 2005 6:21 PM To: Prashant Shirude; ids@iiug.org Subject: RE: Error in Execute Procedure (Error # 674) [4008] You seem to have created a proecure with two paramaters, although the syntax is odd enough I wonder what is really going on ... The correct syntax for returning something from a procedure is something like: CREATE FUNCTION upd_billing_job (p_jobid INTEGER, p_early_time DATETIME YEAR TO SECOND, p_last_time DATETIME YEAR TO SECOND) RETURNING INTEGER; DEFINE someval INTEGER; <DEFINES, including > <CODE> RETURN(some_val); END FUNCTION; Your procedure lack a proper RETURNING clause ... as it is you need to give the procedure two integers in your call ... HTH, Greg Williamson DBA GlobeXplorer LLC -----Original Message----- From: Prashant Sh.... [mailto:PShirude@manh.com] Sent: Thu 1/13/2005 2:52 PM To: ids@iiug.org Cc: Subject: Error in Execute Procedure (Error # 674) [4008] Hi All, Here is the procedure: create procedure test_proc(val1 INT, OUT r_cnt INT) define x char(10); define r_cnt INT; LET r_cnt = 0; begin set debug file to "test_proc.out"; trace "begin trace"; trace on; IF val1 = 3 THEN LET x = 'True-3'; LET r_cnt = val1; ELSE LET x = 'False-3'; LET r_cnt = 0; END IF; trace off; end end procedure; => EXECUTE PROCEDURE test_proc(3); Error: (674) Routine (test_proc) can not be resolved. Is anything wrong in Execute Procedure statement? Help needed. Prashant -----Original Message----- From: June C. Hunt [mailto:june_c_hunt@hotmail.com] Sent: Thursday, January 13, 2005 4:43 AM To: ids@iiug.org Cc: Prashant Shirude Subject: RE: How to execute procedure [3999] "Prashant Sh...." <PShirude@manh.com> wrote: > >CREATE PROCEDURE AUTO_APPROVE_SHIPMENT > (vshpid LIKE SHIPMENT.SHIPMENT_ID, > OUT vPassFail INT > ) > >How to execute above procedure when there is one Input and one out >parameter. Please see the IBM Informix Guide to SQL: Tutorial. You will find it here, if you don't already have it - http://www-306.ibm.com/software/data/informix/pubs/library/list2.html#G There is an entire chapter devoted to creating and using SPL routines. You'll want to review the format for creating a procedure (function) that returns a value, and there are several ways to execute a procedure or function. You haven't provided enough information about how you will be using the procedure to give you the syntax for execution. -- June Hunt ------_=_NextPart_001_01C4F9F2.98346CB8 Content-Type: text/html Content-Transfer-Encoding: quoted-printable <html xmlns:o=3D"urn:schemas-microsoft-com:office:office" = xmlns:w=3D"urn:schemas-microsoft-com:office:word" = xmlns:st1=3D"urn:schemas-microsoft-com:office:smarttags" = xmlns=3D"http://www.w3.org/TR/REC-html40">; <head> <META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; = charset=3Dus-ascii"> <meta name=3DGenerator content=3D"Microsoft Word 11 (filtered medium)"> <o:SmartTagType = namespaceuri=3D"urn:schemas-microsoft-com:office:smarttags" name=3D"PersonName"/>  <style>  </style> </head> <body lang=3DEN-US link=3Dblue vlink=3Dpurple> <div class=3DSection1> Actually I was converting Oracle Procedure into = Informix.<o:p></o:p> <o:p> </o:p> In Oracle: = <o:p></o:p> CREATE OR REPLACE PROCEDURE test_proc(val1 in NUMBER, r_cnt OUT = NUMBER);<o:p></o:p> <o:p> </o:p> I think, in Oracle r_cnt = OUT returns the value from where it called.<o:p></o:p> <o:p> </o:p> Converted in = Informix: Create procedure test_proc(val1 INT, OUT r_cnt INT)<o:p></o:p> <o:p> </o:p> Informix SPL doesn't support to return value. I think, I may = need to write function.<o:p></o:p> <o:p> </o:p> Prashant <o:p></o:p><

↪ replying to Prashant Shirude

Obnoxio The Chav — Fri, 14 Jan 2005 08:25:46 — source: IIUG Forums & Mailing Lists

Prashant Sh.... said: > Informix SPL doesn't support to return value. I think, I may need to write > function. Procedures don't return values, functions do. -- Bye now, Obnoxio "C'est pas parce qu'on n'a rien à dire qu'il faut fermer sa gueule" - Coluche "I'm trying to see things your way, but I can't get my head up my ass" - JCH "Ogni uomo mi guarda come se fossi una testa di cazzo" - Marco If anyone told you to be yourself they would be giving you bad advice. I went to the airport to check in and they asked what I did because I looked like a terrorist. I said I was a comedian. They said, "Say something funny then." I told them I had just graduated from flying school. -- Ahmed Ahmed

↪ replying to Prashant Shirude

ART KAGEL, — Fri, 14 Jan 2005 13:41:59 — source: IIUG Forums & Mailing Lists

Informix DOES indeed support return values from procedures, just not with the same syntax and new procedures returning values should use the keyword FUNCTION instead of PROCEDURE (though the older keyword is still supported). The OUT parameter type is ONLY for external UDFs created using Java or C not for SPL routines which must use the RETURNING (or RETURNS) clause. The syntax would be: CREATE FUNCTION test_proc( call INT ) RETURNING INT;DEFINE retvar INT; .. RETURN retvar; END FUNCTION; Art S. Kagel ----- Original Message ----- From: Prashant Sh.... <PShirude@manh.com> At: 1/13 23:47 > Actually I was converting Oracle Procedure into Informix. > > > > In Oracle: > > CREATE OR REPLACE PROCEDURE test_proc(val1 in NUMBER, r_cnt OUT NUMBER); > > > > I think, in Oracle r_cnt OUT returns the value from where it called. > > > > Converted in Informix: Create procedure test_proc(val1 INT, OUT r_cnt INT) > > > > Informix SPL doesn't support to return value. I think, I may need to write > function. > > > > Prashant > > > > -----Original Message----- > From: Gregory S. Williamson [mailto:gsw@globexplorer.com] > Sent: Thursday, January 13, 2005 6:21 PM > To: Prashant Shirude; ids@iiug.org > Subject: RE: Error in Execute Procedure (Error # 674) [4008] > > > > > > You seem to have created a proecure with two paramaters, although the syntax > is odd enough I wonder what is really going on ... > > > > The correct syntax for returning something from a procedure is something > like: > > CREATE FUNCTION upd_billing_job (p_jobid INTEGER, p_early_time DATETIME YEAR > TO SECOND, p_last_time DATETIME YEAR TO SECOND)> > RETURNING INTEGER; > > DEFINE someval INTEGER; > > <DEFINES, including > > > > > <CODE> > > RETURN(some_val); > > END FUNCTION; > > > > Your procedure lack a proper RETURNING clause ... as it is you need to give > the procedure two integers in your call ... > > > > HTH, > > Greg Williamson > > DBA > > GlobeXplorer LLC > > > > -----Original Message----- > > From: Prashant Sh.... [mailto:PShirude@manh.com] > > Sent: Thu 1/13/2005 2:52 PM > > To: ids@iiug.org > > Cc: > > Subject: Error in Execute Procedure (Error # 674) [4008] > > Hi All, > > > > Here is the procedure: > > > > create procedure test_proc(val1 INT, OUT r_cnt INT)> > > > define x char(10); > > define r_cnt INT; > > LET r_cnt = 0; > > > > begin > > set debug file to "test_proc.out"; > > trace "begin trace"; > > trace on; > > > > IF val1 = 3 THEN > > LET x = 'True-3'; > > LET r_cnt = val1; > > ELSE > > LET x = 'False-3'; > > LET r_cnt = 0; > > END IF; > > > > trace off; > > end > > end procedure; > > > > => EXECUTE PROCEDURE test_proc(3); > > Error: (674) Routine (test_proc) can not be resolved. > > > > Is anything wrong in Execute Procedure statement? Help needed. > > > > Prashant > > > > -----Original Message----- > > From: June C. Hunt [mailto:june_c_hunt@hotmail.com] > > Sent: Thursday, January 13, 2005 4:43 AM > > To: ids@iiug.org > > Cc: Prashant Shirude > > Subject: RE: How to execute procedure [3999] > > > > "Prashant Sh...." <PShirude@manh.com> wrote: > > > > > >CREATE PROCEDURE AUTO_APPROVE_SHIPMENT> > > (vshpid LIKE SHIPMENT.SHIPMENT_ID, > > > OUT vPassFail INT > > > ) > > > > > >How to execute above procedure when there is one Input and one out > > >parameter. > > > > Please see the IBM Informix Guide to SQL: Tutorial. You will find it here, > > if you don't already have it - > > http://www-306.ibm.com/software/data/informix/pubs/library/list2.html#G > > > > There is an entire chapter devoted to creating and using SPL routines. > > You'll want to review the format for creating a procedure (function) that > > returns a value, and there are several ways to execute a procedure or > > function. You haven't provided enough information about how you will be > > using the procedure to give you the syntax for execution. > > > > -- > > June Hunt > > > > > > > > > > > ------_=_NextPart_001_01C4F9F2.98346CB8 > Content-Type: text/html > Content-Transfer-Encoding: quoted-printable > > <html xmlns:o=3D"urn:schemas-microsoft-com:office:office" = > xmlns:w=3D"urn:schemas-microsoft-com:office:word" = > xmlns:st1=3D"urn:schemas-microsoft-com:office:smarttags" = > xmlns=3D"http://www.w3.org/TR/REC-html40">; > > <head> > <META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; = > charset=3Dus-ascii"> > > > <meta name=3DGenerator content=3D"Microsoft Word 11 (filtered medium)"> > <o:SmartTagType = > namespaceuri=3D"urn:schemas-microsoft-com:office:smarttags" > name=3D"PersonName"/> >  > <style> >  > </style> > > </head> > > <body lang=3DEN-US link=3Dblue vlink=3Dpurple> > > <div class=3DSection1> > > style=3D'font-size: > 10.0pt'>Actually I was converting Oracle Procedure into = > Informix.<o:p></o:p> > > style=3D'font-size: > 10.0pt'><o:p> </o:p> > > style=3D'font-size:10.0pt;font-weight:bold'>In Oracle: = > <o:p></o:p> > > style=3D'font-size: > 10.0pt'>CREATE OR REPLACE PROCEDURE test_proc(val1 in NUMBER, r_cnt OUT = > NUMBER);<o:p></o:p> > > style=3D'font-size

Related threads

Glossary

SPL (Stored Procedure Language)