Overloaded procedure - ORA-06502: PL/SQL: numeric or value error: character to number conversion error

[jagad...@gmail.com]

Hi,

I am using DbFit and trying to create a test case for a procedure. It is a overloaded procedure and does have in out parameter. I keep getting 
"PL/SQL: numeric or value error: character to number conversion error". 

If I rename the procedure to a different name (so it is not overloaded) the test runs fine. I am not sure why this is happening when it is overloaded.

Ouput Error
java.sql.SQLException: ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 1

	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:395)
	at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:802)
	at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:436)
	at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186)
	at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:521)
	at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:202)
	at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:1005)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1307)
	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3449)
	at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3550)
	at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4710)
	at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1374)
	at dbfit.fixture.StatementExecution.run(StatementExecution.java:75)
	at dbfit.fixture.DbObjectExecutionFixture.executeStatementAndEvaluateOutputs(DbObjectExecutionFixture.java:176)
	at dbfit.fixture.DbObjectExecutionFixture.runRow(DbObjectExecutionFixture.java:147)
	at dbfit.fixture.DbObjectExecutionFixture.doRows(DbObjectExecutionFixture.java:76)
	at fit.Fixture.doTable(Fixture.java:154)
	at fitlibrary.traverse.AlienTraverseHandler.doTable(AlienTraverseHandler.java:18)
	at fitlibrary.traverse.workflow.DoTraverseInterpreter.interpretWholeTable(DoTraverseInterpreter.java:99)
	at fitlibrary.traverse.workflow.DoTraverseInterpreter.interpretWholeTable(DoTraverseInterpreter.java:87)
	at fitlibrary.DoFixture.interpretWholeTable(DoFixture.java:69)
	at fitlibrary.suite.InFlowPageRunner.run(InFlowPageRunner.java:34)
	at fitlibrary.DoFixture.interpretTables(DoFixture.java:42)
	at dbfit.DatabaseTest.interpretTables(DatabaseTest.java:26)
	at fit.Fixture.doTables(Fixture.java:80)
	at fit.FitServer.process(FitServer.java:81)
	at fit.FitServer.run(FitServer.java:56)
	at fit.FitServer.main(FitServer.java:41)

[Yavor Nikolov]

Hi,

Currently DbFit resolves stored procedures by its name only - so it doesn't work fine if a stored procedure is overloaded.
The workaround at the moment is similar to what you already did: define a uniquely-named wrapper on top of the original stored procedure.

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.

[jagad...@gmail.com]

Thanks Yavor. Appreciate all the hard work put into DbFit.

[jagad...@gmail.com]

Hi Yavor,

I am running the test in FlowMode and creating the wrapper procedure during Setup and Dropping it at the end of test case. But I noticed data is not being rolled back. Is this a known issue?

[Yavor Nikolov]

Hi Jagad,

Yes, this is known behaviour. Oracle performs implicit commit on DDL statements (create/drop/alter). You can either create the wrapper in some other way; or you can add execute rollback before your tear down.

(Obviously, things will a bit more complicated If you have commits within your stored procedures, but that's another story).

Yavor

[jagad...@gmail.com]

Hi Yavor,

As suggested I created a wrapper script and had moved ahead. Are there any plans to enhance DbFit so that it can handle overloaded procedures correctly?

Thanks
Jagad

[jagad...@gmail.com]

Hi Yavor, Jake,

I was looking at the source for a fix for the overloaded procedure. I was looking at OracleEnvironment.java and I see that it queries the all_arguments table to get all arguments. Can we put in a where clause for 'OVERLOAD' (a column in the table) to identify the exact procedure. Overload value can be passed in the name itself in parenthesis. [e.g. USR.PKG_NM.PRC_NM(2) ]
This is probably not ideal solution but a work around. But something is better than nothing

Let me know your thoughts on it.

Thanks
Jagad

[Yavor Nikolov]

Hi Jagad,

Thank you for looking into that issue!
Indeed, specifying the OVERLOAD index in such a will probably allow us to identify the exact stored procedure which we want.

As you already observed - OVERLOAD column is just a numeric index based on the order of procedure's definition in the source code. It's a bit obscure and brittle to rely on such an index (one may rearrange the code; or add new overload procedures not at the end)...

BTW, there there are some bits of discussions and code about the same topic. I'm sending reference to them:

A Github issue: https://github.com/dbfit/dbfit/issues/96

Two (closed unmerged) pull requests for it:
https://github.com/dbfit/dbfit/issues/118
https://github.com/dbfit/dbfit/issues/127

(The 2nd PR is superseding the 1st but still there may have some usable comments there too).

Regards,

Yavor