Jump to content
Changes to the Jaspersoft community edition download ×

Error while executing stored procedure from JasperStudio


gtamang47

Recommended Posts

Stored Procedure,

CREATE OR REPLACE PACKAGE CUSTOM.test_PACK AS
PROCEDURE test_PROC (        inp_set_id         IN  tbaadm.sst.set_id%type,
                                        out_retcode     OUT NUMBER  ,
                                        out_rec         OUT VARCHAR2);
END test_PACK;
/


CREATE OR REPLACE PACKAGE BODY CUSTOM.test_PACK AS

                 outArr                  TBAADM.basp0099.ArrayType;

                v_set_id                TBAADM.sst.SET_ID%type;
                v_acct_name     varchar2(300);
                v_foracid       varchar2(100);

                v_sol_id                varchar2(100);

           
         
                ------------ Declaration of Cursor ------------

CURSOR c1( v_set_id     TBAADM.gam.SOL_ID%type ) is
                        select sol_id, acct_name, foracid from tbaadm.gam
                        where sol_id in (select sol_id from tbaadm.sst where set_id = v_set_id);

                                ----------------
                                --PROCEDURE
                                ----------------
PROCEDURE  test_PROC (inp_set_id in tbaadm.sst.set_id%type,out_retCode OUT number,out_rec OUT varchar2) AS
        BEGIN
        --{

                out_retcode     :=0;
                v_set_id := inp_set_id;

                IF(NOT c1%ISOPEN) THEN

                        OPEN c1( v_set_id);

                END IF;

                IF(c1%ISOPEN) THEN

                FETCH c1
                        INTO
                               v_sol_id,
                               v_acct_name,
                                v_foracid;
                                END IF;

                        ------------ Output Section ------------

        IF( c1%found) THEN

                        dbms_output.enable (buffer_size => 1000000);

                out_rec :=(
                                v_sol_id                ||'|'||
                                 v_acct_name            ||'|'||
                                 v_foracid
                                );


                ELSE
                          close c1;
                          out_retcode := 1;
                          return;

                END IF;
END test_PROC;

END test_PACK;
/
 

executing procedure from jasper studio as: {call CUSTOM.test_PACK.test_PROC( $P{p_v_set_id}  , $P{FIN_OUT_RETCODE} , $P{FIN_OUT_REC} ) }

error 

 

net.sf.jasperreports.engine.JRException: Error executing SQL statement for: proctest.
    at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:347)
    at com.jaspersoft.studio.data.jdbc.JDBCFieldsProvider.getFields(JDBCFieldsProvider.java:68)
    at com.jaspersoft.studio.data.jdbc.JDBCDataAdapterDescriptor.getFields(JDBCDataAdapterDescriptor.java:70)
    at com.jaspersoft.studio.property.dataset.dialog.DataQueryAdapters.doGetFields(DataQueryAdapters.java:486)
    at com.jaspersoft.studio.data.designer.AQueryDesignerContainer$1.run(AQueryDesignerContainer.java:49)
    at org.eclipse.jface.operation.ModalContext$ModalContextThread.run(ModalContext.java:119)
Caused by: java.sql.SQLException: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "CUSTOM.TEST_PACK", line 68
ORA-06512: at line 1

    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:951)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:513)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:227)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
    at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:205)
    at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:1043)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1336)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3613)
    at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3714)
    at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4755)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1378)
    at net.sf.jasperreports.engine.query.OracleProcedureCallHandler.execute(OracleProcedureCallHandler.java:209)
    at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:306)
    ... 5 more
 

Link to comment
Share on other sites

  • Replies 1
  • Created
  • Last Reply

Top Posters In This Topic

Popular Days

Top Posters In This Topic

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...