You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@cocoon.apache.org by T K <tg...@yahoo.de> on 2003/11/03 14:26:09 UTC
Bug in SQL-Transformer ??
Hi,
I have posted this question on Friday before, but apparently no one seems to know a solution... Therefore I am posting my problem more detailed again, for having solved this problem is really important to me.
I'm using Cocoon2.1, JDK 1.4, Oracle9i and Tomcat 4.0.6. (anything left out ?)
I'm trying to use the SQL-Transformer to execute an stored procedure from an Oracle DB. When using the out-parameter tag I get an "java.sql.SQLException: ORA-01036: Variablenname/-nummer ungültig" (illegal variable name/number). There is no error when executing a "normal" SQL-select-statement via the SQL-Transformer and the stored procedure is reachable and executable successfully within SQL-Plus !
My code in loginAdmin.xsl:
<sql:execute-query xmlns:sql="http://apache.org/cocoon/SQL/2.0">
<sql:query isstoredprocedure="true" name="datesearch">
DECLARE
return_ NUMBER;
outparm NUMBER;
input VARCHAR2(200);
BEGIN
return_ := NULL;
outparm := NULL;
input := 'wa314';
DB_HP_TST.SP_GET_USER_ID (return_, outparm, input);
COMMIT;
END;
</sql:query>
<sql:out-parameter sql:nr="1" sql:name="return_" sql:type="oracle.jdbc.driver.OracleTypes.NUMBER"/>
<sql:out-parameter sql:nr="2" sql:name="outparm" sql:type="oracle.jdbc.driver.OracleTypes.CURSOR"/>
The sitemap's entry:
<map:transform src="hybridProfiles/xsl/loginAdmin.xsl"/>
<map:transform type="sql">
<map:parameter name="use-connection" value="personnel"/>
</map:transform>
I'm not that familiar with the out-parameter syntax. Must the name-attribute point to some variable declared before or can it just be any string ? Must the nr-attribute be congruent to the stored-procedure-syntax ?
To me it's seems to be a bug within the Transformer for I can't see any mistake in my code, but to put the blame on something else is always quite easy, so I'll keep searching the failure on my side, till somebody proved the opposite.
Any solutions, remarks, hints ?
Thanx in advance
Torsten
the complete error-trace (from sitemap.log):
ERROR (2003-11-03) 12:55.55:955 [sitemap.sitemap.transformer.sql] (/cocoon/emma/logAdmin) HttpProcessor[8080][3]/SQLTransformer$Query: Caught a SQLException
java.sql.SQLException: ORA-01036: Variablenname/-nummer ungültig
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.oci8.OCIDBAccess.check_error(OCIDBAccess.java:2321)
at oracle.jdbc.oci8.OCIDBAccess.executeFetch(OCIDBAccess.java:1617)
at oracle.jdbc.oci8.OCIDBAccess.parseExecuteFetch(OCIDBAccess.java:1902)
at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2047)
at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:1940)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2709)
at oracle.jdbc.driver.OraclePreparedStatement.doScrollPstmtExecuteUpdate(OraclePreparedStatement.java:3975)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:623)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:656)
at org.apache.cocoon.transformation.SQLTransformer$Query.execute(SQLTransformer.java:1085)
at org.apache.cocoon.transformation.SQLTransformer.executeQuery(SQLTransformer.java:309)
at org.apache.cocoon.transformation.SQLTransformer.endExecuteQueryElement(SQLTransformer.java:466)
at org.apache.cocoon.transformation.SQLTransformer.endTransformingElement(SQLTransformer.java:747)
at org.apache.cocoon.transformation.AbstractSAXTransformer.endElement(AbstractSAXTransformer.java:358)
at org.apache.xalan.transformer.ResultTreeHandler.endElement(ResultTreeHandler.java:307)
at org.apache.xalan.templates.ElemLiteralResult.execute(ElemLiteralResult.java:684)
at org.apache.xalan.transformer.TransformerImpl.executeChildTemplates(TransformerImpl.java:2182)
at org.apache.xalan.templates.ElemLiteralResult.execute(ElemLiteralResult.java:678)
at org.apache.xalan.transformer.TransformerImpl.executeChildTemplates(TransformerImpl.java:2182)
at org.apache.xalan.transformer.TransformerImpl.applyTemplateToNode(TransformerImpl.java:2008)
at org.apache.xalan.transformer.TransformerImpl.transformNode(TransformerImpl.java:1171)
at org.apache.xalan.transformer.TransformerImpl.run(TransformerImpl.java:3135)
at org.apache.xalan.transformer.TransformerHandlerImpl.endDocument(TransformerHandlerImpl.java:433)
at org.apache.cocoon.xml.AbstractXMLPipe.endDocument(AbstractXMLPipe.java:91)
at org.apache.cocoon.transformation.TraxTransformer.endDocument(TraxTransformer.java:529)
at org.apache.cocoon.xml.AbstractXMLPipe.endDocument(AbstractXMLPipe.java:91)
at org.apache.cocoon.transformation.AbstractSAXTransformer.endDocument(AbstractSAXTransformer.java:312)
at org.apache.cocoon.www.emma.hybridProfiles.xml.loginAdmin_xml.generate(org.apache.cocoon.www.emma.hybridProfiles.xml.loginAdmin_xml:323)
at org.apache.cocoon.generation.ServerPagesGenerator.generate(ServerPagesGenerator.java:260)
at org.apache.cocoon.components.pipeline.AbstractProcessingPipeline.processXMLPipeline(AbstractProcessingPipeline.java:548)
at org.apache.cocoon.components.pipeline.impl.AbstractCachingProcessingPipeline.processXMLPipeline(AbstractCachingProcessingPipeline.java:196)
at org.apache.cocoon.components.pipeline.AbstractProcessingPipeline.process(AbstractProcessingPipeline.java:492)
at org.apache.cocoon.components.treeprocessor.sitemap.SerializeNode.invoke(SerializeNode.java:147)
at org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.invokeNodes(AbstractParentProcessingNode.java:84)
at org.apache.cocoon.components.treeprocessor.sitemap.PreparableMatchNode.invoke(PreparableMatchNode.java:164)
at org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.invokeNodes(AbstractParentProcessingNode.java:108)
at org.apache.cocoon.components.treeprocessor.sitemap.PipelineNode.invoke(PipelineNode.java:163)
at org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.invokeNodes(AbstractParentProcessingNode.java:108)
at org.apache.cocoon.components.treeprocessor.sitemap.PipelinesNode.invoke(PipelinesNode.java:161)
at org.apache.cocoon.components.treeprocessor.TreeProcessor.process(TreeProcessor.java:351)
at org.apache.cocoon.components.treeprocessor.TreeProcessor.process(TreeProcessor.java:304)
at org.apache.cocoon.components.treeprocessor.sitemap.MountNode.invoke(MountNode.java:130)
at org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.invokeNodes(AbstractParentProcessingNode.java:84)
at org.apache.cocoon.components.treeprocessor.sitemap.PreparableMatchNode.invoke(PreparableMatchNode.java:164)
at org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.invokeNodes(AbstractParentProcessingNode.java:108)
at org.apache.cocoon.components.treeprocessor.sitemap.PipelineNode.invoke(PipelineNode.java:163)
at org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.invokeNodes(AbstractParentProcessingNode.java:108)
at org.apache.cocoon.components.treeprocessor.sitemap.PipelinesNode.invoke(PipelinesNode.java:161)
at org.apache.cocoon.components.treeprocessor.TreeProcessor.process(TreeProcessor.java:351)
at org.apache.cocoon.components.treeprocessor.TreeProcessor.process(TreeProcessor.java:304)
at org.apache.cocoon.Cocoon.process(Cocoon.java:640)
at org.apache.cocoon.servlet.CocoonServlet.service(CocoonServlet.java:1104)
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:190)
at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566)
at org.apache.catalina.valves.CertificatesValve.invoke(CertificatesValve.java:246)
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.StandardContext.invoke(StandardContext.java:2347)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:180)
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:468)
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:174)
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:1027)
at org.apache.catalina.connector.http.HttpProcessor.run(HttpProcessor.java:1125)
at java.lang.Thread.run(Thread.java:536)
---------------------------------
Gesendet von http://mail.yahoo.de
Schneller als Mail - der neue Yahoo! Messenger.
Re: Bug in SQL-Transformer ??
Posted by T K <tg...@yahoo.de>.
Thanks a lot, that worked !
Torsten
Luca Morandini <lu...@tin.it> wrote:
T K wrote:
> I have posted this question on Friday before, but apparently no one
> seems to know a solution... Therefore I am posting my problem more
> detailed again, for having solved this problem is really important to me.
>
> I'm using Cocoon2.1, JDK 1.4, Oracle9i and Tomcat 4.0.6. (anything left
> out ?)
Try "DB_HP_TST.SP_GET_USER_ID (?, ?, input);" instead (question marks
used as output parameters' placeholders).
Regards,
-----------------------------------
Luca Morandini - GIS Consultant
lmorandini@ieee.org
http://xoomer.virgilio.it/ukmorand/
-----------------------------------
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@cocoon.apache.org
For additional commands, e-mail: users-help@cocoon.apache.org
---------------------------------
Gesendet von http://mail.yahoo.de
Schneller als Mail - der neue Yahoo! Messenger.
Re: Bug in SQL-Transformer ??
Posted by Luca Morandini <lu...@tin.it>.
T K wrote:
> I have posted this question on Friday before, but apparently no one
> seems to know a solution... Therefore I am posting my problem more
> detailed again, for having solved this problem is really important to me.
>
> I'm using Cocoon2.1, JDK 1.4, Oracle9i and Tomcat 4.0.6. (anything left
> out ?)
Try "DB_HP_TST.SP_GET_USER_ID (?, ?, input);" instead (question marks
used as output parameters' placeholders).
Regards,
-----------------------------------
Luca Morandini - GIS Consultant
lmorandini@ieee.org
http://xoomer.virgilio.it/ukmorand/
-----------------------------------
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@cocoon.apache.org
For additional commands, e-mail: users-help@cocoon.apache.org