UCCX 10 integrate with Orcale DB

[helawamry]

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,

[Gergely Szabo]

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.

[helawamry]

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

[Gergely Szabo · Accepted solution]

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.

[helawamry]

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.

[Gergely Szabo]

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.

[helawamry]

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

[Gergely Szabo]

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

G.

[helawamry]

Premium.

[Gergely Szabo]

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.

[helawamry]

Hi Gergely,

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

Thanks

[Gergely Szabo]

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.

[helawamry]

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.

[Gergely Szabo]

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.

[helawamry]

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