Hitachi Vantara Pentaho Community Forums
Results 1 to 2 of 2

Thread: Call Stored Procedure to insert data into a table

  1. #1
    Join Date
    Sep 2013
    Posts
    3

    Default Call Stored Procedure to insert data into a table

    Hi All,

    I am calling a stored procedure from report designer as follows:

    {call invoice_email_table_update(${email},${dealer},${period})}

    This stored procedure essentially inputs the data from the variables into a table in the database. This stored procedure works fine if I call it in SQL Developer.

    But every time I run it in report designer, it gives an error but yet inserts the data into the table:


    org.pentaho.reporting.engine.classic.core.ReportDataFactoryException: Failed at query: {call invoice_email_table_update(${email},${dealer},${period})}




    at org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.SimpleSQLReportDataFactory.queryData(SimpleSQLReportDataFactory.java:210)
    at org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.SQLReportDataFactory.queryData(SQLReportDataFactory.java:162)
    at org.pentaho.reporting.engine.classic.core.CompoundDataFactory.queryStatic(CompoundDataFactory.java:125)
    at org.pentaho.reporting.engine.classic.core.cache.CachingDataFactory.queryInternal(CachingDataFactory.java:429)
    at org.pentaho.reporting.engine.classic.core.cache.CachingDataFactory.queryStatic(CachingDataFactory.java:178)
    at org.pentaho.reporting.engine.classic.core.CompoundDataFactory.queryStatic(CompoundDataFactory.java:120)
    at org.pentaho.reporting.engine.classic.core.CompoundDataFactory.queryData(CompoundDataFactory.java:75)
    at org.pentaho.reporting.engine.classic.core.states.datarow.DefaultFlowController.performQueryData(DefaultFlowController.java:221)
    at org.pentaho.reporting.engine.classic.core.states.datarow.DefaultFlowController.performQuery(DefaultFlowController.java:188)
    at org.pentaho.reporting.engine.classic.core.states.process.ProcessState.initializeForMasterReport(ProcessState.java:393)
    at org.pentaho.reporting.engine.classic.core.layout.output.AbstractReportProcessor.prepareReportProcessing(AbstractReportProcessor.java:468)
    at org.pentaho.reporting.engine.classic.core.layout.output.AbstractReportProcessor.processReport(AbstractReportProcessor.java:1666)
    at org.pentaho.reporting.designer.core.actions.report.preview.PreviewHtmlAction$ExportTask.run(PreviewHtmlAction.java:137)
    at java.lang.Thread.run(Unknown Source)
    Caused by: java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement


    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:389)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:382)
    at oracle.jdbc.driver.T4CTTIfun.processError(T4CTTIfun.java:655)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:505)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:223)
    at oracle.jdbc.driver.T4C8Odscrarr.doODNY(T4C8Odscrarr.java:98)
    at oracle.jdbc.driver.T4CCallableStatement.doDescribe(T4CCallableStatement.java:813)
    at oracle.jdbc.driver.OracleStatement.describe(OracleStatement.java:3984)
    at oracle.jdbc.driver.OracleResultSetMetaData.<init>(OracleResultSetMetaData.java:55)
    at oracle.jdbc.driver.OracleResultSetImpl.getMetaData(OracleResultSetImpl.java:180)
    at org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.ResultSetTableModelFactory.generateDefaultTableModel(ResultSetTableModelFactory.java:259)
    at org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.ResultSetTableModelFactory.createTableModel(ResultSetTableModelFactory.java:126)
    at org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.SimpleSQLReportDataFactory.parametrizeAndQuery(SimpleSQLReportDataFactory.java:395)
    at org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.SimpleSQLReportDataFactory.queryData(SimpleSQLReportDataFactory.java:206)
    ... 13 more




    Any help will be appreciated!

  2. #2
    Join Date
    Aug 2006
    Posts
    287

    Default

    Any particular reason to use a report to write back to a database? With all due respect I do not think that is the appropriate use of a report. If anything you may want to look into using an xaction or even better a transformation within PDI

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.