Trying to get result set from Oracle function in package failing

[paul saunders]

Hi all,

I am new to dbFit and am trying to create a test that executes an Oracle function that is in a package.  I am assigning the result set to a local variable and then querying that variable as stated in the dbFit documentation FAQs.  The Query fails with an exception.  The stack trace contains the message: "Unknown column personId".  I am able to connect to the database and run queries on the same page, so I know that the connection is being made.

My tables looks like this:

!|Execute Procedure |FIND_PATIENT_PKG.FIND_PATIENT|

|in_search_criteria|?|

|LNM=SMITH|>>results|

 

|Query|<<results|

|personId?|lastName?|firstName?|

|12345678|JOHN|SMITH|

The full stack trace looks like this:

java.lang.Exception: Unknown column personid

        at dbfit.fixture.RowSetFixture.findColumn(RowSetFixture.java:40)

        at dbfit.fixture.RowSetFixture.bind(RowSetFixture.java:51)

        at fit.ColumnFixture.doRows(ColumnFixture.java:19)

        at dbfit.fixture.RowSetFixture.doRows(RowSetFixture.java:71)

        at fit.Fixture.doTable(Fixture.java:162)

        at fitlibrary.traverse.AlienTraverseHandler.doTable(AlienTraverseHandler.java:18)

        at fitlibrary.traverse.workflow.DoTraverse.interpretWholeTable(DoTraverse.java:98)

        at fitlibrary.DoFixture.interpretWholeTable(DoFixture.java:63)

        at fitlibrary.suite.InFlowPageRunner.run(InFlowPageRunner.java:39)

        at fitlibrary.DoFixture.interpretTables(DoFixture.java:40)

        at dbfit.DatabaseTest.interpretTables(DatabaseTest.java:24)

        at fit.Fixture.doTables(Fixture.java:75)

        at fit.FitServer.process(FitServer.java:76)

        at fit.FitServer.run(FitServer.java:52)

        at fit.FitServer.main(FitServer.java:43)

Any help with this would be greatly appreciated!

Thank you,
Paul

 

[Gojko Adzic]

the syntax is correct, but the error tells you that the result set
that is returned does not contain a column called "personid". can you
execute that directly from sql+ and see what the column names are? is
the stored proc returning an OUT REF CURSOR output?

see
http://dbfit.svn.sourceforge.net/viewvc/dbfit/trunk/FitNesseRoot/AcceptanceTests/DotNetTests/OracleTests/FlowMode/RefCursorOutput/content.txt?revision=333&view=markup
for more info on how to run a demo test.

Gojko Adzic

[paul saunders]

Hi Gojko,

I executed the function in sqlPlus and saw that the column names were different than what I expected.  I corrected the column names and now it is working!

Thanks so much for the help,
Paul

[Srujana]

Hi All,

You had discussed if Procedure is returning an Output paramater of TYPE CURSOR. But if the proc is returning a type of NUM_ARRAY, then how to handle it in the java class while execution. When I am trying to call proc from JAVA code, itself is failing. Facing below issue.

Java Code: 
		call_stmt.setLong(1,7202602);
		call_stmt.registerOutParameter(2, Types.ARRAY);	
        call_stmt.execute();

java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'PROCEDURE_NAME'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

	at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
	at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
	at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:215)
	at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:954)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1169)
	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3285)
	at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3392)
	at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4223)
	at com.seic.fitnesse.atm.fixtures.CpmNextArrayListProcExecutor.call(CpmNextArrayListProcExecutor.java:123)
	at com.seic.fitnesse.atm.fixtures.CpmNextArrayListProcExecutor.exec(CpmNextArrayListProcExecutor.java:60)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:597)
	at fitlibrary.closure.MethodClosure.invoke(MethodClosure.java:31)
	at fitlibrary.closure.MethodClosure.invokeTyped(MethodClosure.java:24)
	at fitlibrary.closure.CalledMethodTarget.invokeTyped(CalledMethodTarget.java:67)
	at fitlibrary.closure.CalledMethodTarget.invokeTyped(CalledMethodTarget.java:82)
	at fitlibrary.closure.CalledMethodTarget.invokeAndWrap(CalledMethodTarget.java:242)
	at fitlibrary.traverse.workflow.caller.ActionCaller.run(ActionCaller.java:25)
	at fitlibrary.traverse.workflow.DoTraverseInterpreter.interpretRow(DoTraverseInterpreter.java:152)
	at fitlibrary.traverse.workflow.DoTraverseInterpreter.interpretAfterFirstRow(DoTraverseInterpreter.java:64)
	at fitlibrary.traverse.workflow.DoTraverseInterpreter.interpretInFlow(DoTraverseInterpreter.java:132)
	at fitlibrary.DoFixture.interpretAfterFirstRow(DoFixture.java:46)
	at fitlibrary.FitLibraryFixture.doTable(FitLibraryFixture.java:75)
	at fit.Fixture.interpretFollowingTables(Fixture.java:120)
	at fit.Fixture.interpretTables(Fixture.java:107)
	at fit.Fixture.doTables(Fixture.java:80)
	at fit.FitServer.process(FitServer.java:82)
	at fit.FitServer.run(FitServer.java:57)
	at fit.FitServer.main(FitServer.java:42)

[Yavor Nikolov]

Hi Srujana,

Oracle collections are not yet supported for parameters and return values: https://github.com/dbfit/dbfit/issues/57

An idea for workaround so far in case of functions returning collections is to use SELECT ... FROM TABLE( func_name ) notation.

Regards,
Yavor

 

--
You received this message because you are subscribed to the Google Groups "dbfit" group.

To unsubscribe from this group and stop receiving emails from it, send an email to dbfit+un...@googlegroups.com.

To post to this group, send email to db...@googlegroups.com.

Visit this group at http://groups.google.com/group/dbfit.
For more options, visit https://groups.google.com/d/optout.