Custom element for Oracle 12C.1 database Stored procedures.

[mtnova]

We are trying to connect to an Oracle 12C.1 database and want to execute a stored procedures. From what I read we need to create a custom element. Does anyone have the JAVA code for a element that will do this? We are on CVP10.1

[Gerry O'Rourke]

Mark,

I do not believe a custom element is necessary. I have used the standard database element to run store procedures on an Microsoft SQL database in the past and doing similar with an Oracle database should be possible.

I would look to see if you can set up the JNDI resources correctly for an Oracle database and use the standard DB element.

See below for a reference on how to setup the JNDI resources for a Microsoft SQL database.

I wish Cisco documented these, but if you get it working can you post the steps on setting up JNDI for an Oracle?

Details Steps (I documented this ages ago) for Cisco CVP with Microsoft SQL:

Unified CVP VXML Server JNDI Resources - DocWiki

Gerry

[mtnova]

Gerry,

We use stored procedures in other CVP applications using MS SQL. In the application that we are having a problem with we can execute DB queries to the Oracle DB without any problems using the DB element. We have one stored procedure that when executed returns nothing. The is our JNDI resource configuration that we are using:

<Resource

name="jdbc/TRSD"

auth="Container"

type="javax.sql.DataSource"

username="XXXXXX"

password="XXXXX"

maxActive="300"

maxIdle="30"

maxWait="10000"

removeAbandoned="true"

removeAbandonedTimeout="300"

testOnBorrow="true"

validationQuery="select * from trsadm.TAXFEE"   

driverClassName="oracle.jdbc.OracleDriver"       

url="jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=audadb31)(PORT=1531)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME= TRSDEV)))"

/>

f you are able to use the DB element to execute a Oracle Stored Procedure, can you provide instruction on how that is done?

[Gerry O'Rourke]

Mark,

I have not done so. But I would not see why it could or should not work.

Have you checked the that the user has rights to run the store procedure?

Its the only thing I can think of.

I am sure you have, so if anyone else can help out.....

Gerry

[janinegraves]

I was told years ago (by someone at a Cisco ATP Partner) that while you

'can' execute MySql and SqlServer stored procedures,using the Studio DB

element, you can NOT exec oracle stored procedures

I've never tested this, but it's probably why Mark is receiving the

error below.

[Gerry O'Rourke]

Janine,

That is really interesting. I need to do something similar in the near future and this is a pain if true.

I would classify this as a bug if it is still the case.

Mark - if you cannot get anywhere, I would open a TAC case and query if this is still valid or not.

And If so what is the root cause and when a ES (fix) could be made available.

Keep up posted!

Gerry

[mtnova]

I did open a case with TAC, and they do not provide help customizing elements, but pointed me to DEVNET for help.

We are using this link for guidance:

https://communities.cisco.com/docs/DOC-42921

it is a few years old, and we are trying to create a custom element using the JAVA code on that link, but are running into error compiling it. We are reaching out to a JAVA programmer that work for us to help.

Janine we have check out the other web site that discuss this, but the code there does not create an element for us.

Could this just be that we are using CVP10.0, JRE 1.6, which is a little out of date. Can we upgrade CVP to use JRE 1.7?

The instruction that Cisco does provide, do not match what programs are in the upgrade tool they suggest using.

[janinegraves]

CVP 10.0 was Java 1.6 and you can't change your version of Java to 1.7

until you upgrade to CVP 10.5 or higher.

But you should be able to create an element to exec an oracle stored

proc in any version of java - I've created DB elements, but not for oracle.

What kind of compile error are you getting? Maybe you're missing a

driver (jar file) or imported a class from an incorrect package.

Are you using JNDI to connect?

What does your stored procedure call look like (just need to know in

params, out params, inout params, Is all the data being returned in

params? how many rows are being returned?

If you want to post or send your java code, I can take a look. And the

error you're getting.

[mtnova]

I have some good news, our JAVA programmer was able to clean up our code and have it compile correctly. Tomorrow we will put it is a script and see if it works. if it does I will post the code here. Wish us luck.

[Gerry O'Rourke]

Mark,

Did you get it working?

Are you able to post the code of the custom element here?

Gerry

[Gerry O'Rourke]

Mark,

TAC will not help you with customizing a element.

But the standard database element should  work for store procedures on Oracle, hence TAC should review and confirm why the default database element does not work correctly.

If we knew why the next step would be to see if Cisco could correct it.

P.S. Could you post the corrected Java code as well, which now compiles?

Gerry

[janinegraves]

Mark, you might just google how to execute an oracle stored proc using

java if no one on the devnet provides anything.