Cisco Support Community
cancel
Showing results for 
Search instead for 
Did you mean: 
Announcements

Welcome to Cisco Support Community. We would love to have your feedback.

For an introduction to the new site, click here. If you'd prefer to explore, try our test area to get started. And see here for current known issues.

New Member

UCCX 10 integrate with Orcale DB

Hi,

We have UCCX 10 integrating with Oracle DB and the customer wants to run certain SQL statement at DB read step and add the result it directly in string variable on UCCX without using DB get step because the result of this SQL statement is not searching in table, it acts as equation and returns digits into string.

Can this be done or I need other way for doing this.

 

Thanks
Haitham,

 

1 ACCEPTED SOLUTION

Accepted Solutions

I see. You have the following

I see. You have the following options then:

1. Create a database table with one column ("bal") with the correct datatype. Feed it to the Get DB Read step.

2. Use a HTTP-to-DB proxy service, as described here.

3. Upload the Oracle JDBC driver to the UCCX server and use a Java code block in a Set/Do step.

G.

24 REPLIES

Hi,I don't get this. You say

Hi,

I don't get this. You say the query returns something ("into string"). Now why can't you use the DB Get step then? Can you tell me what sort of request is that? A stored procedure perhaps?

G.

New Member

Hi Gergely, The SQL statement

Hi Gergely,

 

The SQL statement in DB read step doesn't returns rows because this statement is not searching the database table so, there is no tables found in DB get set.
 

the SQL statement as below, we need the result of it(bal) to be filled in UCCX string variable directly.

select ivr_package.ivr_cust_balance ($ivr_cust_id ) bal from dual

 

Thanks

I see. You have the following

I see. You have the following options then:

1. Create a database table with one column ("bal") with the correct datatype. Feed it to the Get DB Read step.

2. Use a HTTP-to-DB proxy service, as described here.

3. Upload the Oracle JDBC driver to the UCCX server and use a Java code block in a Set/Do step.

G.

New Member

Hi Gergely, Thank you for

Hi Gergely,

 

Thank you for your support.

Regarding the first choice, i have suggested it to the customer but he refused it.

I think 3rd choice is the best one but it needs Java developer for making Java code in Set step.

Hi,actually, it's not that

Hi,

actually, it's not that difficult. What is the data type of the value returned by the database server? Also, can you please tell me the version and license type of your UCCX?

G.

New Member

Hi, The returned value is

Hi,

 

The returned value is digits and UCCX version 10.5.
If you can help me for the knowing the Java code at Set step will be great.

 

Thanks

Could you check the UCCX

Could you check the UCCX license for me please (Standard, Enhanced, Premium or IP IVR).

G.

New Member

Premium.

Premium.

Okay.Have you downloaded the

Okay.

Have you downloaded the Oracle JDBC Driver package yet and if not, do you have access to the Oracle Developer Network? If not, please register and download it from here.

Also, please make sure you have all the necessary information such as the SID, the username, the password and the schema name.

G.

New Member

Hi Gergely,I have integrated

Hi Gergely,

I have integrated with oracle DB with JDBC driver from Oracle already and i can get information from the tables.

 

Thanks

Hi,okay, so you need to

Hi,

okay, so you need to upload the driver jar file to the Classpath of the UCCX server (check the documentation) and restart UCCX.

You did not say too much about that stored function, so I just created a simplistic one, like this:

[greg@ooangdb ~]$ sqlplus
SQL*Plus: Release 11.2.0.2.0 Production on Mon Nov 3 19:10:29 2014
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Enter user-name: ooang
Enter password:
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL> create or replace function ivr_cust_balance(ivr_cust_id IN VARCHAR)
  2  return
  3  varchar
  4  is
  5  begin
  6  return '12345';
  7  end;
  8  /
Function created.
SQL> commit;
Commit complete.
SQL>

 

Then I added the following variables to my UCCX script:

  • input - type String - initial value: ""
  • jdbcPass - type String - initial value: the password
  • jdbcStatement - type String - initial value: "{ ? = call ivr_cust_balance(?)}"
  • jdbcUrl - type String - initial value: the JDBC URL
  • jdbcUser - type String - initial value: tthe JDBC user
  • output - type String - initial value: ""

Or, if you prefer a screenshot:

Then I just inserted a Set step (setting the value of the variable input to "33333"), followed by yet another Set step which assigns the value of output and contains the following code block:

{
Class.forName("oracle.jdbc.driver.OracleDriver");
java.sql.Connection connection = java.sql.DriverManager.getConnection(jdbcUrl, jdbcUser, jdbcPass);
java.sql.CallableStatement callableStatement = connection.prepareCall(jdbcStatement);
callableStatement.setQueryTimeout(5000);
callableStatement.registerOutParameter(1, java.sql.Types.VARCHAR);
callableStatement.setString(2, input);
callableStatement.executeQuery();
String output =callableStatement.getString (1);
return output;
}

 

Naturally, you may enhance this with an exception handling mechanism.

Now if you run this and the DB server is available, after the second Set step the value of the output variable is "12345":

 

 

G.

 

 

 

New Member

Hi Gergely,Actually, I didn't

Hi Gergely,

Actually, I didn't understand where I will configure the below part, knowing that UCCX is integrating with Oracle DB 11g release 2 and the SQL statement for retrieving  select ivr_package.ivr_cust_balance ($ivr_cust_id ) bal from dual,
this statement should return the balance of the customer by his id.

At jdbcstatement string, Shall i add the previous SQL statement to get the balance

[greg@ooangdb ~]$ sqlplus
SQL*Plus: Release 11.2.0.2.0 Production on Mon Nov 3 19:10:29 2014
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Enter user-name: ooang
Enter password:
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL> create or replace function ivr_cust_balance(ivr_cust_id IN VARCHAR)
  2  return
  3  varchar
  4  is
  5  begin
  6  return '12345';
  7  end;
  8  /
Function created.
SQL> commit;
Commit complete.
SQL>
 
Thank you so much.

Hi,er... you wrote you had

Hi,

er... you wrote you had already done some integration with JDBC.

Anyway, to answer your question, no, the above stuff with the SQL create or replace function is just my pretend DB function to demonstrate that the Java code in my UCCX script works.

I assume your DB function takes one parameter and returns one value - is this correct? If so, you need to change the initial value of jdbcStatement to

"{ ? = ivr_package.ivr_cust_balance(?) }"

And if the input parameter value is stored in the local variable named ivr_cust_id then the code block of the Set step would be:

{
Class.forName("oracle.jdbc.driver.OracleDriver");
java.sql.Connection connection = java.sql.DriverManager.getConnection(jdbcUrl, jdbcUser, jdbcPass);
java.sql.CallableStatement callableStatement = connection.prepareCall(jdbcStatement);
callableStatement.setQueryTimeout(5000);
callableStatement.registerOutParameter(1, java.sql.Types.VARCHAR);
callableStatement.setString(2, ivr_cust_id);
callableStatement.executeQuery();
String output =callableStatement.getString (1);
return output;
}

I hope that helps.

G.

New Member

Hi Gergely,Thank you so much

Hi Gergely,

Thank you so much for your help, I will try the java code.

regarding your question:

I assume your DB function takes one parameter and returns one value - is this correct?
yes, the input is the customer id.

 

Thanks

New Member

Hi Gergely, Thank you for

Hi Gergely,

 

Thank you for cooperation, I faced error in the code as below.
I have uploaded the odbc jar file to use it in the scripts.


sorry for interruption and thanks for your help.