Oracle Package Procedure and Function

[Former Member]

Hi everybody,

I'm trying to use an oracle procedure via Visual Composer 2004s.

The procedure is stored into a package.

I've created two systems connected to the Oracle database a "normal" JDBC system using sap oracle driver and a BI JDBC system using Oracle thin.

If I try to get the package using the BI_JDBC I can't find the package at all.

If I use the SAP JDBC I can find the package with its procedure but I can't drag it into the iView in the storyboard.

If I extract the procedure from the package I can use them without problems.

Is it possible to use the Oracle packages?

Thanks a lot

Massimiliano

[Former Member]

In this how to guide, I talk about how to setup the oracle jdbc driver and test it out.

https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/docs/library/uuid/92d80512-0a01-0010-32a3-cd3...

Does the test succeed as outlined in this guide?

[Former Member]

Hi,

the connection test works.

I've tested my connection with

/TestJDBC_Web/TestJDBCPage2.jsp?sys_jdbc=MyDB

as well and it works!

I can browse the system in VC without any problems.

My problem is that with BI JDBC I can get the packages at all.

With normal JDBC connection I can browse the packages but I can't drag any function into the iView.

Thanks

Massimiliano

[Former Member]

I don't understand what you are calling BI JDBC vs normal JDBC. JDBC is for java drivers. Period. The driver you load is all that matters. Which driver are you using and which database are you connecting to? You should only have one system for your database, correct?

[Former Member]

BI JDBC connector: returns you tables & views

Portal JDBC connector: returns you stored procedures

You can use the same driver for both. And of course, the driver also limits what you can do (performance wise, memory wise, search capabilities...)

[Former Member]

OK.

I'm going to explain.....

I've created three systems:

<b>Portal JDBC</b>

ConnectionFactoryClass:JDBCFactory

DriverClassName:com.sap.portals.jdbc.oracle.OracleDriver

ConnectionURL:jdbc:sap:oracle://<<MyServer>>:1525;sid=MyDB

ConnectionTest=OK

VC Browse=OK

I can get procedures but I cannot use them in Iviews

<b>BI JDBC</b>

com.sap.portal.reserved.system.ConnectionFactoryClass:MyDB

//I have created MyDB connector with oracle classes12 driver. Connection works ok.//

DriverClassName:oracle.jdbc.driver.OracleDriver

ConnectionURL:jdbc:oracle:thin:@<<MyServer>>:1525:MyDB

ConnectionTest=OK

VC Browse=OK

I can get and use tables and views.

<b>Portal JDBC</b>

ConnectionFactoryClass:JDBCFactory

DriverClassName:oracle.jdbc.driver.OracleDriver

ConnectionURL:jdbc:oracle:thin:@<<MyServer>>:1525:MyDB

<b>ConnectionTest=FAIL!!!</b>

Thanks!

[Former Member]

Sorry..

with my Portal JDBC (the first one)

using VC I can't use Store Procedures if functions are in a package.

If I try to search a function that is in a package I can find it but I can't drag it into the Iview.

regards

[Former Member]

Have you specified your user management for the Portal JDBC connection test?