Thanks for the quick reply! but I am still confused :(
The input arguments will eventually be user-entered data, but the user will only enter one of the four inputs, as they are mutually exclusive. (You could send data for all four to the stored procedure and it would work fine; but functionally it doesn't make sense.) Right now I am just trying to get it working by hard-coding a value for the last input, which is the most often supplied. The stored procedure is used for other things and works fine (also why I can't change it right now) ;)
Also, even if I enter something other than NULL, I still get the same error.
<esql:execute-query>
<!-- Call Stored Procedure to retrieve person data-->
<esql:call needs-query="true">
{? = call get_person(
<esql:parameter direction="In" type="String">'234'</esql:parameter>,
<esql:parameter direction="In" type="String"><xsp:expr>'333'</xsp:expr></esql:parameter>,
<esql:parameter direction="In" type="String"><xsp:expr>'333'</xsp:expr></esql:parameter>,
<esql:parameter direction="In" type="String"><xsp:expr>'46465445'</xsp:expr></esql:parameter>)}
</esql:call>
Any ideas or example would be greatly appreciated !!
Thx,
Beth
-----Original Message-----
From: Paul Gilligan [mailto:[EMAIL PROTECTED]]
Sent: Thursday, May 30, 2002 2:33 PM
To: [EMAIL PROTECTED]
Subject: Re: Stored Procedure with input parameters using esql?
Why are you using NULL??
Does not seem to understand the NULL which is why you get the error.
A better way would be to setup a default NULL argument for the PL/SQL
and then you can call:
{? = call get_person(?)}
..which Oracle will handle for you :)
Naquin, Beth wrote:
> I want to execute a stored procedure that requires input parameters
> using the esql logicsheet, but am having difficulty. I can call stored
> procedures that take no inputs just fine.
>
> Is there a working example/samle code of how to do this? I could only
> find one brief example in the userdocs and it still does not make sense
> to me ;(
>
> I have this stored procedure: {? = call get_person(?,?,?,?)}
>
> The first 3 input parameters can accept NULL and the last one will be an
> actual value.
>
> I have this code using esql logicsheet:
> -----------------------------------------------------------------------------
>
> <esql:execute-query>
> <!-- Call Stored Procedure to retrieve person data-->
> <esql:call needs-query="true" >
> {? = call get_person(
> <esql:parameter direction="In"
> type="String"><xsp:expr>NULL</xsp:expr></esql:parameter>,
> <esql:parameter direction="In"
> type="String"><xsp:expr>NULL</xsp:expr></esql:parameter>,
> <esql:parameter direction="In"
> type="String"><xsp:expr>NULL</xsp:expr></esql:parameter>,
> <esql:parameter direction="In"
> type="String"><xsp:expr>'46465445'</xsp:expr></esql:parameter>)}
> </esql:call>
>
> ... stuff to process results ....
> </esql:execute-query>
> -----------------------------------------------------------------------------
>
> But I receive this error:
>
> type fatal
> message Exception in ServerPagesGenerator.generate()
> description org.apache.cocoon.ProcessingException: Exception in
> ServerPagesGenerator.generate(): java.lang.RuntimeException: Error
> executing prepared statement: {? = call get_person( ?, ?, ?, ?)} :
> java.sql.SQLException: JZ0SA: Prepared Statement: Input parameter not
> set, index: 1.
>
> sender org.apache.cocoon.servlet.CocoonServlet
> source Cocoon servlet
> stack-trace
> org.apache.cocoon.ProcessingException: Exception in
> ServerPagesGenerator.generate(): java.lang.RuntimeException: Error
> executing prepared statement: {? = call
> get_person( ?, ?,
> ?, ?)} : java.sql.SQLException: JZ0SA:
> Prepared Statement: Input parameter not set, index: 1.
>
> at
> org.apache.cocoon.generation.ServerPagesGenerator.generate(ServerPagesGenerator.java:272)
>
> at
> org.apache.cocoon.components.pipeline.CachingEventPipeline.process(CachingEventPipeline.java:251)
>
> at
> org.apache.cocoon.components.source.SitemapSource.toSAX(SitemapSource.java:351)
>
> at
> org.apache.cocoon.sitemap.ContentAggregator.generate(ContentAggregator.java:148)
>
> at
> org.apache.cocoon.components.pipeline.CachingEventPipeline.process(CachingEventPipeline.java:251)
>
> at
> org.apache.cocoon.components.pipeline.CachingStreamPipeline.process(CachingStreamPipeline.java:399)
>
> at
> org.apache.cocoon.www.otda_1.sitemap_xmap.matchN100FD(C:\Program
> Files\Apache Tomcat
> 4.0\work\localhost\cocoon\cocoon-files\org/apache/cocoon/www/otda_1\sitemap_xmap.java:1344)
>
> at org.apache.cocoon.www.otda_1.sitemap_xmap.process(C:\Program
> Files\Apache Tomcat
> 4.0\work\localhost\cocoon\cocoon-files\org/apache/cocoon/www/otda_1\sitemap_xmap.java:727)
>
> at org.apache.cocoon.www.otda_1.sitemap_xmap.process(C:\Program
> Files\Apache Tomcat
> 4.0\work\localhost\cocoon\cocoon-files\org/apache/cocoon/www/otda_1\sitemap_xmap.java:634)
>
> at org.apache.cocoon.sitemap.Handler.process(Handler.java:222)
> at org.apache.cocoon.sitemap.Manager.invoke(Manager.java:179)
> at org.apache.cocoon.www.sitemap_xmap.matchN109E1(C:\Program
> Files\Apache Tomcat
> 4.0\work\localhost\cocoon\cocoon-files\org/apache/cocoon/www\sitemap_xmap.java:12247)
>
> at org.apache.cocoon.www.sitemap_xmap.process(C:\Program
> Files\Apache Tomcat
> 4.0\work\localhost\cocoon\cocoon-files\org/apache/cocoon/www\sitemap_xmap.java:3870)
>
> at org.apache.cocoon.www.sitemap_xmap.process(C:\Program
> Files\Apache Tomcat
> 4.0\work\localhost\cocoon\cocoon-files\org/apache/cocoon/www\sitemap_xmap.java:3127)
>
> at org.apache.cocoon.sitemap.Handler.process(Handler.java:222)
> at org.apache.cocoon.sitemap.Manager.invoke(Manager.java:179)
> at
> org.apache.cocoon.sitemap.SitemapManager.process(SitemapManager.java:154)
> at org.apache.cocoon.Cocoon.process(Cocoon.java:575)
> at
> org.apache.cocoon.servlet.CocoonServlet.service(CocoonServlet.java:998)
> at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
> at
> org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:247)
>
> at
> org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:193)
>
> at
> org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:243)
>
> at
> org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566)
>
> at
> org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472)
> at
> org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943)
> at
> org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:201)
>
> at
> org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566)
>
> at
> org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472)
> at
> org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943)
> at
> org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2344)
> at
> org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:164)
>
> at
> org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566)
>
> at
> org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.java:170)
>
> at
> org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564)
>
> at
> org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:170)
>
> at
> org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564)
>
> at
> org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:462)
> at
> org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564)
>
> at
> org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472)
> at
> org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943)
> at
> org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:163)
>
> at
> org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566)
>
> at
> org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472)
> at
> org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943)
> at
> org.apache.catalina.connector.http.HttpProcessor.process(HttpProcessor.java:1011)
>
> at
> org.apache.catalina.connector.http.HttpProcessor.run(HttpProcessor.java:1106)
>
> at java.lang.Thread.run(Unknown Source)
> java.lang.RuntimeException: Error executing prepared
> statement: {? = call get_person(
> ?, ?, ?,
> ?)} : java.sql.SQLException: JZ0SA: Prepared Statement:
> Input parameter not set, index: 1.
>
> at
> org.apache.cocoon.www.otda_1.documents.home.process_person_data_input_xsp.generate(C:\Program
> Files\Apache Tomcat
> 4.0\work\localhost\cocoon\cocoon-files\org/apache/cocoon/www/otda_1/documents/home\process_person_data_input_xsp.java:607)
>
> at
> org.apache.cocoon.generation.ServerPagesGenerator.generate(ServerPagesGenerator.java:260)
>
> at
> org.apache.cocoon.components.pipeline.CachingEventPipeline.process(CachingEventPipeline.java:251)
>
> at
> org.apache.cocoon.components.source.SitemapSource.toSAX(SitemapSource.java:351)
>
> at
> org.apache.cocoon.sitemap.ContentAggregator.generate(ContentAggregator.java:148)
>
> at
> org.apache.cocoon.components.pipeline.CachingEventPipeline.process(CachingEventPipeline.java:251)
>
> at
> org.apache.cocoon.components.pipeline.CachingStreamPipeline.process(CachingStreamPipeline.java:399)
>
> at
> org.apache.cocoon.www.otda_1.sitemap_xmap.matchN100FD(C:\Program
> Files\Apache Tomcat
> 4.0\work\localhost\cocoon\cocoon-files\org/apache/cocoon/www/otda_1\sitemap_xmap.java:1344)
>
> at org.apache.cocoon.www.otda_1.sitemap_xmap.process(C:\Program
> Files\Apache Tomcat
> 4.0\work\localhost\cocoon\cocoon-files\org/apache/cocoon/www/otda_1\sitemap_xmap.java:727)
>
> at org.apache.cocoon.www.otda_1.sitemap_xmap.process(C:\Program
> Files\Apache Tomcat
> 4.0\work\localhost\cocoon\cocoon-files\org/apache/cocoon/www/otda_1\sitemap_xmap.java:634)
>
> at org.apache.cocoon.sitemap.Handler.process(Handler.java:222)
> at org.apache.cocoon.sitemap.Manager.invoke(Manager.java:179)
> at org.apache.cocoon.www.sitemap_xmap.matchN109E1(C:\Program
> Files\Apache Tomcat
> 4.0\work\localhost\cocoon\cocoon-files\org/apache/cocoon/www\sitemap_xmap.java:12247)
>
> at org.apache.cocoon.www.sitemap_xmap.process(C:\Program
> Files\Apache Tomcat
> 4.0\work\localhost\cocoon\cocoon-files\org/apache/cocoon/www\sitemap_xmap.java:3870)
>
> at org.apache.cocoon.www.sitemap_xmap.process(C:\Program
> Files\Apache Tomcat
> 4.0\work\localhost\cocoon\cocoon-files\org/apache/cocoon/www\sitemap_xmap.java:3127)
>
> at org.apache.cocoon.sitemap.Handler.process(Handler.java:222)
> at org.apache.cocoon.sitemap.Manager.invoke(Manager.java:179)
> at
> org.apache.cocoon.sitemap.SitemapManager.process(SitemapManager.java:154)
> at org.apache.cocoon.Cocoon.process(Cocoon.java:575)
> at
> org.apache.cocoon.servlet.CocoonServlet.service(CocoonServlet.java:998)
> at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
> at
> org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:247)
>
> at
> org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:193)
>
> at
> org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:243)
>
> at
> org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566)
>
> at
> org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472)
> at
> org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943)
> at
> org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:201)
>
> at
> org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566)
>
> at
> org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472)
> at
> org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943)
> at
> org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2344)
> at
> org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:164)
>
> at
> org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566)
>
> at
> org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.java:170)
>
> at
> org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564)
>
> at
> org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:170)
>
> at
> org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564)
>
> at
> org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:462)
> at
> org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564)
>
> at
> org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472)
> at
> org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943)
> at
> org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:163)
>
> at
> org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566)
>
> at
> org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472)
> at
> org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943)
> at
> org.apache.catalina.connector.http.HttpProcessor.process(HttpProcessor.java:1011)
>
> at
> org.apache.catalina.connector.http.HttpProcessor.run(HttpProcessor.java:1106)
>
> at java.lang.Thread.run(Unknown Source)
>
>
>
> Cocoon 2.0.2
> Tomcat 4.0.1
> JDK 1.3.1
> sybase database
>
> Thanks in advance,
>
> Beth Naquin
> SAGEM MORPHO Inc.
> 1145 Broadway Plaza STE 200
> Tacoma, WA 98402
>
> 253-597-8245
>
---------------------------------------------------------------------
Please check that your question has not already been answered in the
FAQ before posting. <http://xml.apache.org/cocoon/faqs.html>
To unsubscribe, e-mail: <[EMAIL PROTECTED]>
For additional commands, e-mail: <[EMAIL PROTECTED]>
