Run a function with a table of User Defined Type (UDT) as a parameter using SQLPlus

If you want to send multiple records to an oracle function, a common practice is to create the function with one or more parameters as a table of an object type.

In the function itself, you can loop thru the array or select from it using to TABLE command.

Another option to send an array of multiple records is to use XML (xmltype) as the function parameter but this will be addressed in a separate post.

While I use this method for years, for some reason, many times I find myself struggling with the relatively simple syntax. This post will serve me (and maybe some of you) as a short, very simplistic template to copy from.

Basically, what you need to do is the create a user defined object type (UDT)

CREATE OR REPLACE TYPE order_type AS object (
  num_of_units NUMBER
, cost_of_unit NUMBER)

Then you create a table of this user defined type

CREATE OR REPLACE TYPE order_tbl AS  TABLE OF order_type

A simple function

CREATE OR REPLACE FUNCTION calc_total (i_orders order_tbl) RETURN NUMBER IS
 
res NUMBER;
 
BEGIN
 
 
   SELECT SUM(num_of_units * cost_of_unit)
   INTO res
   FROM TABLE (i_orders);
 
 
 
   RETURN res;
 
END;

Testing this function with SQLPlus is a little challenging. The simple way to send a table of UDT type to a function is to cast it to the table of the user defined type.

SQL>   SELECT  calc_total(
  2                      CAST(MULTISET(
  3                        SELECT * FROM
  4                        (
  5                        SELECT 9 num_of_units, 2 cost_of_unit FROM dual UNION ALL
  6                        SELECT 6, 3  FROM dual
  7                        ) ) AS order_tbl   ))
  8    FROM dual;
 
CALC_TOTAL(CAST(MULTISET(SELEC
------------------------------
                            36

The reason for the additional inline view is the weird oracle bug the cause the function to hang (and sometimes crash with ora-03113 and ora-00600 ) when using cast and multiset together with union. The inline view is fast workaround for this.
Another option to work around this problem is to use multiset union

SQL>    SELECT  calc_total( (CAST(multiset(
  2                               SELECT 9,2 FROM dual
  3                                MULTISET UNION
  4                               SELECT 6,3 FROM dual
  5                           ) AS order_tbl   ))
  6                       )
  7     FROM dual;
 
CALC_TOTAL((CAST(MULTISET(SELE
------------------------------
                            36
Share on RedditEmail this to someoneShare on Google+Share on LinkedInShare on FacebookDigg thisTweet about this on Twitter

A short reminder: Why OSB Database Adapter returns ORA-01403 when the function return results on SQLPlus

In OSB (Oracle Service Bus) a Database Adapter is a great tool for wrapping a database function and using it as part of a complex process or providing it as a web service. A common practice is that the function returns a table of user defined types (UDT) that can can be read by the adapter.

During the creation of the DB adapter, the oracle user that is used by JDeveloper needs to be granted with permissions for the function and the returned user defined types (the object and the table of the object).

However, during runtime, the OSB Database Adapter somtimes fails with BEA-382500 OSB Service Callout action received SOAP Fault response. Inside the results you can find the following line:

” Cause: java.sql.SQLException: ORA-01403: no data found … … nested exception is: BINDING.JCA-11812 or BINDING.JCA-11811

This message can be misleading because many times , running the same function with same user in SQLPlus will return results. Luckily, the solution is simple, in order for the Data Base Adapter to work properly, all you need to do is to grant the user running the db adapter with permission for ALL types used by the function (even internally)
for some reason (maybe a difference between the Oracle Clients), the DB adapter requires permissions on the internal types that are used inside the function while SQLPlus (Toad or plsql Developer) can work without these permissions.

Share on RedditEmail this to someoneShare on Google+Share on LinkedInShare on FacebookDigg thisTweet about this on Twitter

How to schedule a job to run on a different hour on a specific date.

Oracle jobs are a simple way to schedule procedure and processes. However, sometimes the scheduling requirements can get really complicated. A very common request is to run a job on a specific hour on weekdays and not to run it on the weekend. This request can be easily delivered using the “WHAT” parameter of the dbms_job.submit procedure.

BEGIN
  IF to_char(sysdate, 'D') BETWEEN 2 AND 6 THEN
      my_procedure ;
  END IF;
END;

However, what will you do if you need to run the procedure at a specific hour on Monday to Friday but on a different hour on the weekend?
In this case you will be happy to know that you can use CASE on the “INTERVAL” parameter of the dbms_job.submit procedure.

DECLARE
  my_job NUMBER;
BEGIN
  dbms_job.submit(job => my_job, 
    what => 'my_procedure ;',
 
     INTERVAL => 'case
                     when  to_number(to_char(sysdate, ''D'')) between 1 and 5 then 
                        TRUNC(SYSDATE+1) + 16 /24 
                     when  to_number(to_char(sysdate, ''D'')) between 6 and 7 then
                         TRUNC(SYSDATE+1) + 8 /24 
                  end'
 
 
    );
END;

remember that this calculation is for NEXT run not for the current. Therefore, you will need to take into consideration that the timinig for Monday will be calculated on Sunday (I know it sounds obvious but you will be surprised how many time you can do the same mistake).

While the case clause can handle many calculations and complications, there is one caveat. The case statement can handle only 200 characters. This becomes a problem when you try to write a longer statement. For example:

CASE
  WHEN  to_number(to_char(sysdate, 'D')) BETWEEN 1 AND 4 THEN 
      TRUNC(SYSDATE+1) + 16 /24 
  WHEN  to_number(to_char(sysdate, 'D'))= 5 THEN
      TRUNC(SYSDATE+1) + 13 /24     
  WHEN  to_number(to_char(sysdate, 'D'))= 6 THEN
      TRUNC(SYSDATE+2) + 16 /24          
  ELSE
      TRUNC(SYSDATE+1) + 16 /24 
 END

if you try to submit a job with this statement, you will probably get the following error

ORA-12899: VALUE too LARGE FOR COLUMN "SYS"."JOB$"."INTERVAL#" (actual: 257, maximum: 200)
ORA-06512: at "SYS.DBMS_IJOB", line 290
ORA-06512: at "SYS.DBMS_JOB", line 159
ORA-06512: at line 4

You can workaround this problem if you use a function to calculate the INTERVAL value.

CREATE OR REPLACE FUNCTION get_next_run_date RETURN DATE IS
 
res DATE;
BEGIN
SELECT
 
CASE
  WHEN  to_number(to_char(sysdate, 'D')) BETWEEN 1 AND 4 THEN
      TRUNC(SYSDATE+1) + 16 /24
  WHEN  to_number(to_char(sysdate, 'D'))= 5 THEN
      TRUNC(SYSDATE+1) + 13 /24
  WHEN  to_number(to_char(sysdate, 'D'))= 6 THEN
      TRUNC(SYSDATE+2) + 16 /24
  ELSE
      TRUNC(SYSDATE+1) + 16 /24
 END
 INTO res
 FROM dual
  ;
RETURN res;
 
END;

once the function is created, all you need to do is use it in your dbms_job submit under the INTERVAL parameter

DECLARE
  my_job NUMBER;
BEGIN
  dbms_job.submit(job => my_job, 
    what => 'my_procedure ;',
 
    INTERVAL => 'get_next_run_date'
    );
END
Share on RedditEmail this to someoneShare on Google+Share on LinkedInShare on FacebookDigg thisTweet about this on Twitter

A short reminder – SQLException: Unsupported character set (add orai18n.jar to your classpath)

After upgrading one of our testing environments to a newer oracle version, some of our services started to return the following error:

SQLException: Unsupported character set (add orai18n.jar to your classpath)

From the error message wording, one could suspect that this is an character set but after running

SELECT VALUE FROM SYS.NLS_DATABASE_PARAMETERS WHERE PARAMETER = 'NLS_CHARACTERSET';

We found out that both databases return the same results.

Therefore, it is obvious that there is a version mismatch between the client and the server. And this message is Oracle’s way of telling you that you need to upgrade your client. Since we are using WebLogic, the process is more complicated.

As suggested, we tried adding the orai18n.jar to the classpath but to no avail.

In addition we discovered that all the problematic service select xmltype from the Database so we assumed that this issue could be solved by some kind of encoding workaround.
We tried converting the character set (convert(col1,’IW8ISO8859P8′,’UTF-8′); ) or tweaking the NLS settings (alter session set nls_territory) only to discover that the simple solution is to convert the xmltype to string and reconverting it back to xmltype

SELECT XMLELEMENT("RESULTS",
                    XMLELEMENT(  "Field",XMLATTRIBUTES(col_name_1 AS "name") ,col_value_1 ),
                    XMLELEMENT(  "Field",XMLATTRIBUTES(col_name_2 AS "name") ,col_value_2 )
 
                    )
 
INTO xml --  xmltype 
FROM (SELECT     
        'NAME1' AS col_name_1
        ,'VALUE1' AS  col_value_1
        ,'NAME2' AS col_name_2
        ,'VALUE2' AS  col_value_2 
 FROM dual   
        );
 
RETURN xmltype(to_clob(xml.getStringVal()));
Share on RedditEmail this to someoneShare on Google+Share on LinkedInShare on FacebookDigg thisTweet about this on Twitter

Logstash and Oracle – JDBC input settings

Lately, I began experimenting with elastic search, logstash and kibana. As a DBA, the first thing I wanted to try is Data base connectivity. Just like any new emerging technology, Oracle database is not the first priority on their implementation (and of course, documentation). There are much more examples for sexier (and free) databases like Postgresql or MySQL. As a result, there are only few examples and the configuaration is a real PITA.

While it is easy to find mysql database jdbc for logstash examples

INPUT {
  jdbc {
    jdbc_driver_library => "mysql-connector-java-5.1.36-bin.jar"
    jdbc_driver_class => "com.mysql.jdbc.Driver"
    jdbc_connection_string => "jdbc:mysql://localhost:3306/mydb"
    jdbc_user => "mysql"
    parameters => { "favorite_artist" => "Beethoven" }
    schedule => "* * * * *"
    statement => "SELECT * from songs where artist = :favorite_artist"
  }
}

It is much harder to find Oracle database jdbc logstash examples but I found something to start with at https://discuss.elastic.co/t/logstash-jdbc-input-oracle-settings/26996

jdbc {
      # The path TO our downloaded jdbc driver saved mine IN /bin, driver ojdbc6.jar also works. 
      #Best location can be /vendor folder
           jdbc_driver_library => "ojdbc7.jar"
      # ORACLE Driver Class
           jdbc_driver_class => "Java::oracle.jdbc.driver.OracleDriver"
      # ORACLE jdbc connection string TO our DATABASE, ORACLE  jdbc:oracle:thin:@hostname:PORT/SERVICE
           jdbc_connection_string => "jdbc:oracle:thin:@hostname:PORT/SERVICE"
      # The USER AND password we wish TO EXECUTE our statement AS
           jdbc_user => "DBUSERNAME"
           jdbc_password => "DBPASSWORD"
 
    # our SQL query
    statement => "select * from AWESOMETABLE"
 
    #optional extras I USE 
      TYPE => "Database"
      tags => ["awesome", "import"]
 }

Since I did not have ojdbc7.jar on my computer I chose to use ojdbc6.jar so my Oracle database jdbc logstash query came out like this:

INPUT {
jdbc {
    jdbc_connection_string => "jdbc:oracle:thin:@\\my-host:1521:my-sid"
    jdbc_user => "db-user"
    jdbc_password => "db-pwd"
    jdbc_validate_connection => TRUE
    jdbc_driver_library => "C:\path\to\ojdbc6.jar"
    jdbc_driver_class => "Java::oracle.jdbc.driver.OracleDriver"
    statement => "SELECT * from my-table"
}
}
output {
elasticsearch {
INDEX => "contacts"
document_type => "contact"
document_id => "%{uid}"
hosts => "localhost:9200"
}
}

Along the way, I encountered two error messages that are the reason for this post:
The first

” Error: java::oracle.jdbc.driver.OracleDriver not loaded. Are you sure you’ve included the correct jdbc driver in :jdbc_driver_library?
You may be interested in the ‘–configtest’ flag which you can use to validate logstash’s configuration before you choose to restart a running system. ”

Running the query with the ‘–configtest’ flag did not help much but eventually, it turned out to be a simple typo. The oracle driver was not loaded only because the call was
” java::oracle.jdbc.driver.OracleDriver ” instead of ” Java::oracle.jdbc.driver.OracleDriver “.
(this one was really annoying but I hope this will save you time I spent on it ).

Another message I got along the way was

Java::JavaSql::SQLRecoverableException: IO Error: Invalid connection string fo
rmat, a valid format is: “host:port:sid”

apparently, while all examples for the connection string are in the following format:
jdbc:mysql://localhost:3306/mydb
The valid format in my case (oracle client 11 on windows 7) was
jdbc:oracle:thin:@\\my-host:1521:my-sid

This should get you connected but if you still get

Java::JavaSql::SQLRecoverableException: IO Error: The Network Adapter could not establish the connection
Check the following:
The database and the listener are running.
The specified port in the code is the same port that the database is listening .
There is no firewall blocking
Good luck

Share on RedditEmail this to someoneShare on Google+Share on LinkedInShare on FacebookDigg thisTweet about this on Twitter