cocoon-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Paul Gilligan <pdgilli...@oyap.net>
Subject Re: Stored Procedure with input parameters using esql?
Date Thu, 30 May 2002 21:55:40 GMT
Ok,


Take this example:

---------------------------------------------------

PROCEDURE create_dept (
     new_dname VARCHAR2 DEFAULT 'TEMP',
     new_loc   VARCHAR2 DEFAULT 'TEMP') IS
BEGIN
    INSERT INTO dept
       VALUES (deptno_seq.NEXTVAL, new_dname, new_loc);
    ...
END;

---------------------------------------------------

Here note the keyword default in the definition of create_dept.

so now we can call any one of these and Oracle will handle the default 
parameters in sequence.

exec create_dept();
exec create_dept('ACCOUNTS');
exec create_dept('ACCOUNTS', 'PUTNEY');


So from the external "calling" language such as java you do not need the 
handle default conditions in the actual java code, let Oracle handle it 
at the database end.

Paul



Naquin, Beth wrote:
> 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:pdgilligan@oyap.net]
> Sent: Thursday, May 30, 2002 2:33 PM
> To: cocoon-users@xml.apache.org
> 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: <cocoon-users-unsubscribe@xml.apache.org>
> For additional commands, e-mail: <cocoon-users-help@xml.apache.org>
> 



---------------------------------------------------------------------
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: <cocoon-users-unsubscribe@xml.apache.org>
For additional commands, e-mail: <cocoon-users-help@xml.apache.org>


Mime
View raw message