Oracle: Parametrizing a view using a context

There are several ways to create parametrized view in Oracle. In this blog I’ll show you how to do that using a context. Assume you want to have a view that depends on some where condition:

select * from emp where deptno = ?

The deptno is known at the run time

You can define a context (or namespace) and define an attribute „deptno“ that holds the value of deptno at runtime. The context is like a container for attribute value pairs. The attribute can be defined using „dbms_session.set_context(context_name,attr,value)

To create the context (you should have the system privilege create any context) :

create context mycontext using mypackage accessed globally;


Note that the context is associated with a package. This is for security reasons, Oracle only allows to execute the dbms_session.set_context from within the package associated to the context. Furthermore the package does not have to be present at the point when the context is created.

create or replace package mypackage as
    procedure  def_attr_in_context(attr_nam in varchar2, attr_val in varchar2);
end mypackage;
/

create or replace package body mypackage as
    procedure def_attr_in_context(attr_nam in varchar2, attr_val in varchar2) is
    begin
        dbms_session.set_context('mycontext', attr_nam, attr_val);
    end def_attr_in_context;
end mypackage;

Defining the parametrized view with the where condition deptno = SYS_CONTEXT(‚mycontext‘,’deptno‘)

CREATE OR REPLACE VIEW vw_parametrized_view AS
SELECT * FROM emp WHERE deptno = SYS_CONTEXT('mycontext','deptno')
SELECT * FROM vw_parametrized_view

no rows selected

After setting the defining (setting) the attribute deptno

BEGIN
  mypackage.def_attr_in_context('deptno','20');
END;
SELECT * FROM vw_parametrized_view

7369 SMITH CLERK 7902 17.12.1980 800,00 20
7566 JONES MANAGER 7839 02.04.1981 2975,00 20
7788 SCOTT ANALYST 7566 19.04.1987 3000,00 20
7876 ADAMS CLERK 7788 23.05.1987 1100,00 20
7902 FORD ANALYST 7566 03.12.1981 3000,00 20

Retrieving the column values after DML Statement in Oracle database using Returning Clause

Primary keys play an important role in the database world. Based on them you can clearly identify a record. Most of the time, a primary key is defined as a number, which is filled via a sequence. This can be done automatically in a trigger:

--create a test table
create table TBL_TEST_SEQ
(
  id   NUMBER PRIMARY KEY,
  text VARCHAR2(255)
);
--create a sequence for the PK
create sequence MY_SEQ
minvalue 1
maxvalue 9999999999999999999999999999
start with 1
increment by 1;
-- create the trigger to fill the PK
create or replace trigger tri_tbl_test_seq   before insert  on  TBL_TEST_SEQ 
for each row
begin
  :new.id := my_seq.nextval;
end tri_tbl_test_seq;

Since Oracle 12.1 you do not need to explicitly create a sequence and the trigger. All you have to do is define the Primary Key column with the clause generated as identity in the table DDL (This option has existed for long time in MySQL).

create table TBL_TEST_SEQ
(
  id number generated always as identity,
  text varchar2(255)
)

With the following query you can see, that oracle has automatically created a sequence to fill the primary key:

select * from user_tab_identity_cols where table_name = 'TBL_TEST_IDENTITY'

After an insert, the column is filled implicitly. To still get the generated id, Oracle offers the „returnuning into“ clause:

declare
  v_id number;
begin
  insert into TBL_TEST_SEQ (text) values ('hello world') returning id into v_id;
  dbms_output.put_line(v_id);
end;

This is just one use case. In general you can retrieve all columns of the table:

 
drop table tbl_test purge;
-- create a table tbl_test
create table tbl_test
(
  id number generated always as identity,
  text varchar2(255),
  text2 varchar2(255) default 'text2 default'
);

declare
  v_txt varchar2(255);
begin
  insert into tbl_test(text) values ('hello world') returning text2 into v_txt;
  dbms_output.put_line('Output: ' || v_txt);
end;

Output: text2 default

Another interesting use case is after a delete or update statement:

declare
  v_txt varchar2(255);
begin
  delete from tbl_test where id = 1 returning text into v_txt;
  dbms_output.put_line(v_txt);
end;

If multiple records are affected by insert or update, you can retrieve the column values into collection:

eclare
  v_txts sys.odcivarchar2list ;
begin
  delete from tbl_test returning text bulk collect into v_txts;
  for i in 1..v_txts.count loop
    dbms_output.put_line(v_txts(i));
  end loop;
end;

Returning bulk collect into for Insert Statement:

declare
  v_ids sys.odcinumberlist;
begin
  forall i in 1..10
    insert into tbl_test (text) values( 'hello welt ') 
    returning id bulk collect into v_ids;
  for i in 1..v_ids.count loop
    dbms_output.put_line(v_ids(i));
  end loop;
end;

But what I miss is when using „insert + select from„. It seems that something like this is not implemented for some reason. Of course, there are other restrictions. For example, this does not work with parallel processing.

Can’t execute jar- file: „no main manifest attribute“

This error occurs when trying to execute a jar file that has no information about the main class in its manifest file. When executing a Jar file, the Java Virtual Machine needs to know which is the main class to begin with it. This information is defined as attribute in a text file named MANIFEST.MF with in the jar file. This file will be generated automatically, when you create the jar file using the command jar -cf. The file contains a lot of useful information needed by the JVM. Such as the main class in the jar file.

You should explicitly specify which Class in the jar file is the main Class. You can achieve this by using the flag „e“ and the fully qualified class name of the main Class, when creating the jar file:

jar -cfe MyApp.jar com.example.App com/example/*

Or in hindsight, if you can not recompile the jar file. In this case, you can enter the info manually in the manifest file:

Manifest-Version: 1.0

Created-By: 1.8.0_211 (Oracle Corporation)

Main-Class: com.example.App

MANIFEST.MF

Let us look at this with an example

We create a java class App in the com/example directory
mkdir -p com/example
vi com/example/App.java

package com.example;
public class App {
  public static void main(String[] args){
    System.out.println("Hello world");
  }
}

We compile the source file and we create the jar file using the „e“ flag

javac com/example/App.java -d target

jar -cfe MyApp.jar com.example.App com/example/*

#the jar file is now executable 
java -jar MyApp.jar
Hello world

If you use Maven to build your Jar:

In Maven you have to add this information in the maven-jar-plugin configuration in the POM File like the following:

<build>
  <plugins>
    <plugin>
      <groupId>org.apache.maven.plugins</groupId>
        <artifactId>maven-jar-plugin</artifactId>
	      <configuration>
	        <archive>
			  <manifest>
			    <mainClass>com.example.App</mainClass>
			  </manifest>
			</archive>
		  </configuration>
	</plugin>
  </plugins>
</build>

Spring Boot: Hello World

Spring, unlike EJB, offers a simple way to implement JEE applications. Thanks to DI and AOP programming, one only has to deal with POJOs.
However, this simplicity is associated with a large configuration effort: Enabling certain Spring or third-party library features (Spring MVC, transaction management,…) requires explicit configuration.
Mostly such configurations are the same in many different projects and they have nothing to do with the business logic.
Spring Boot is the answer to this problem. Spring Boot allows you to focus on the most important. Let us look at this with an example

First of all you should install Spring Boot CLI. One possibility to achieve this is to install it manually, you have only to download it, unzip it and add its bin directory to your path variable.

To find the last release:

https://repo.spring.io/release/org/springframework/boot/spring-boot-cli/


Check the CLI Version

d:\tmp>spring --version
Spring CLI v2.1.8.RELEASE
d:\tmp>

Write the Controller (Business logic)

@RestController
class HelloController {
  @RequestMapping("/")
  String hello() {
    return "Hello World"
  }
}

Run the application

d:\tmp>spring run HelloController.java

The above command achieves at least this tasks:

  • Create a project structure with a Maven or Gradle build file + all required dependencies + Compilation + Packaging
  • A Spring configuration that enables Spring MVC
  • Application Server (Tomcat) + Deploying the application

Calling the application in the browser:

Java Code Warm up (my Kata)

Exciting projects and challenges keep you up to date and completely fit.
As a full-stack freelance, you always want to stay fit, even in languages or technologies that are not used in the current project.
For example, between database development and Java development, it is worlds apart.
In this blog, I’ll show some simple methods that I practice several times a week to stay fit in Java. Especially when I am deep down somewhere else.
It’s all about simple things: manipulating strings, sorting, etc. But practicing them helps a lot to stay fit.

The complete Code is to find under:

https://github.com/afikri-code/java-warmup

Let’s start

Generating n-length array of integer (and distinct values):

Sometimes when we want to test some java code, we need to generate arrays or lists to use them as input for our tests. To achieve this I use the class Random from the package java.util. The following are two methods to generate n-length arrays. In the second method, the values of the arrays are distinct.

To generate n-length array, in which it does not matter if the values are distinct or not:

        /**
	 * @param dim, Array dimension
	 * @param bound maxvalue (arr[i] <= bound)
	 * @return
	 */
	public int[] generateIntArr(int dim, int bound) {
		int[] arr = new int[dim];
		Random random = new Random();
		for(int i=0;i<dim;i++) {
			arr[i] = random.nextInt(bound);
		}
		return arr;		
	}

To generate n-length array of distinct integer:

        /**
	 * @param dim , Array dimension
	 * @param bound maxvalue (arr[i] <= bound)
	 * @return
	 */
	public int[] generateIntArrDistinctVals(int dim, int bound) {
		int[] arr = new int[dim];
		int tmpArrSize = 100*dim;
		int currentSize = 0;
		int tmp,tmpMod;
		int[] tmpArr = new int[tmpArrSize];
		Random random = new Random();
		while(currentSize<dim) {
		  tmp = random.nextInt(bound);
		  tmpMod = tmp%tmpArrSize;
		  if(tmpArr[tmpMod]!=tmpMod) {
			  tmpArr[tmpMod] = tmpMod;
			  arr[currentSize++]=tmp;
		  }
		}		
		return arr;
	}

Note: We do not use the Set Data structure to ensure that the array values are distinct. Instead, we use a simple hash function and a temporary array.

Check if the values of an array are distinct: To achieve this we use a Set data structure.

/**
 * @param arr
 * @return
 */
public boolean isArrDistinct(int[] arr) {
    Set<Integer> tmpSet = new HashSet<>();
    for(int i=0;i<arr.length;i++) {
        //if an element already exist, the add function
        // return false
        if(!tmpSet.add(arr[i]))
            return false;
    }
    return true;

}

Check if array is sorted:

        /**
	 * @param arr
	 * @return
	 */
	public boolean isArrSorted(int[] arr) {
		for(int i = 1;i< arr.length;i++) {
			if(arr[i]<arr[i-1])
				return false;
		}
		return true;
	}

Merge Sort:

        /**
	 * @param left
	 * @param right
	 * @return
	 */
	int[] mergeArr(int[] left, int[] right) {
		int[] arr = new int[left.length+right.length];
		int currentSize =0;
		int j=0;
		for(int i=0;i<left.length;i++) {
			while(j<right.length && right[j]<left[i]) {
				arr[currentSize++] = right[j++];
			}
			arr[currentSize++] = left[i];
		}
		for(int i=j;i<right.length;i++) {
			arr[currentSize++]=right[i];
		}
		
		return arr;
	}
	
	/**
	 * @param arr
	 * @return
	 */
	public int[] sortMerge(int[] arr) {
		if(arr.length == 1) {
			return arr;
		}
		int[] left = new int[arr.length/2];
		int[] right = new int[arr.length - left.length];
		for(int i=0;i<left.length;i++) {
			left[i] = arr[i];
		}
		for(int i=0;i<right.length;i++) {
			right[i] = arr[i+left.length];
		}
		left = sortMerge(left);
		right = sortMerge(right);
		return mergeArr(left,right);
	}

Quicksort

        /**
	 * @param arr
	 * @param begin
	 * @param end
	 * @return
	 */
	int partitionForQuickSort(int[] arr,int begin, int end) {
		int pi = arr[(begin+end)/2];
		int i=begin-1;
		int j=end+1;
		int tmp;
		while(true) {
			do {
				i++;
			}while(arr[i]<pi);
			do {
				j--;
			}while(arr[j]> pi);

			if(i<j) {
				tmp = arr[i];
				arr[i]=arr[j];
				arr[j]=tmp;
			}else {
				return j;
			}
		}

	}
	
	public void quickSort(int[] arr, int begin,int end) {
		if(begin<end) {
			int q = partitionForQuickSort(arr,begin,end);
			quickSort(arr,begin,q);
			quickSort(arr,q+1,end);
		}
		
	}

Find Longest Common Sequence

/**
	 * @param s1
	 * @param s2
	 * @return
	 */
	public String findLongestCommonSequence(String s1, String s2) {
		String longStr;
		String shortStr;
		String tmp = null;
		if(s1.length() > s2.length()) {
			longStr = s1;
			shortStr = s2;
		}else {
			longStr = s2;
			shortStr = s1;
		}
		
		int length = shortStr.length();
		
		for(int i=length;i>0;i--) {
			int begin = 0;
			while(begin+i<=length) {
			   tmp = shortStr.substring(begin, i+begin++);
			   if(longStr.contains(tmp)) {
				   System.out.println(tmp);
				   return tmp;
			   }
			}
		}
		
		
		return null;
	}

Find Longest Palindrome

        /**
	 * @param word
	 * @return
	 */
	public String findLongestPalindrome(String word) {
		int length = word.length();
		int j ;
		for(int l=length;l>0;l--) {
			j=0;
			while(j+l<=length) {
				char[] a = word.substring(j, j+l).toCharArray();
				int i=a.length/2+1;
				
				while(i-->0 && a[i]==a[a.length - 1-i]);
				if(i==-1) {
					return new String(a);
				}
				
				j++;
			}
			
		}
		return "";
	}

Reverse an Integer value

        /**
	 * e.g. input = 54321 output = 12345
	 * @param input
	 * @return
	 */
	//Reverse an Integer value
	public int reverseIneger(int input) {
		int output = 0;		
		do {
		//be aware if 10*output > Integer.max_value!!
		  output = 10*output+input%10;
		  input /=10;
		}while(input>0);
		return output;
	}

ORA-22288: file or LOB operation FILEOPEN failed soft link in path

On a 12c database when trying to load a file as a CLOB, I got the following error:

ORA-22288: file or LOB operation FILEOPEN failed
soft link in path

As if oracle checks if there is a soft link in the path of a directory! Since when does Oracle do that and why?

Here is an example to reproduce this on 12.2 version.

Let’s create a soft link:

[oracle@kirux ~]$ cd
[oracle@kirux ~]$ ln -s /tmp softlink
[oracle@kirux ~]$ ls -ltr
lrwxrwxrwx. 1 oracle oinstall          4 Aug  6 20:52 softlink -> /tmp

let’s create a database directory:

create or replace directory dir_with_softlink 
as '/home/oracle/softlink'

let’s create a file „file.txt“ in /home/oracle/softlink

[oracle@kirux ~]$ cat /tmp/file.txt
line1 test
line2 test2
[oracle@kirux ~]$

Following PL / SQL block to load the file into a CLOB:

declare
 l_bfile  BFILE;
  l_clob   CLOB;
  l_dest_offset   INTEGER := 1;
  l_src_offset    INTEGER := 1;
  l_bfile_csid    NUMBER  := 0;
  l_lang_context  INTEGER := 0;
  l_warning       INTEGER := 0;
begin
  l_bfile := BFILENAME('DIR_WITH_SOFTLINK', 'file.txt');
  DBMS_LOB.fileopen(l_bfile, DBMS_LOB.file_readonly);
  dbms_output.put_line('file opned');
  DBMS_LOB.loadclobfromfile (
    dest_lob      => l_clob,
    src_bfile     => l_bfile,
    amount        => DBMS_LOB.lobmaxsize,
    dest_offset   => l_dest_offset,
    src_offset    => l_src_offset,
    bfile_csid    => l_bfile_csid ,
    lang_context  => l_lang_context,
    warning       => l_warning);
  DBMS_LOB.fileclose(l_bfile);
exception 
  when others then
    dbms_output.put_line(sqlerrm);
end;

This error occurs when the above block is executed :

ORA-22288: file or LOB operation FILEOPEN failed
soft link in path

When trying to load the file as an external table it works:

create table
   ext_tbl_softlink ( msg varchar2(1024) )
organization external (
   type oracle_loader
   default directory dir_with_softlink
   access parameters (
      records delimited by newline
   )
   location('file.txt')
)
reject limit 100000

select * from ext_tbl_softlink -- this works


It appears that the check only affects the function DBMS_LOB.fileopen

To solve (go around) the problem I used the following undocumented parameter. (The instance should be restarted):

alter system set "_disable_directory_link_check" = true scope=spfile;
And
ALTER SYSTEM SET "_kolfuseslf" = TRUE SCOPE=SPFILE;

Note: On 11.2.0.4 it doesn’t matter whether there is a soft link in the path. It looks like this check was added in the version 12c.

Oracle Performance issues by using sequence in parallel high insert DML

Sequences are good choice to generate unique integers for populating primary key and foreign key columns. By using them it is guaranteed that two sessions do not pull the same value. Ensuring this could have a big price in terms of performance. Indeed a sequence is just a row in the seq$ table and every time a session tries to get the next value of the sequence, it should increment the highwater value. This means updating highwater column for the row of the sequence in the seq$ table. For that, the session needs to request a row lock. In parallel processing, this leads to poor performance.
But also in serial processing (Even if the session may only request the lock once for the whole work), the act to update the seq$ table for each used value of the sequence is very expensive. Therefore you should cache the sequence.

Cache specifies how many sequence numbers to preallocate and keep in memory.
Only when all values from the cache are used, the table seq$ is updated. This means the larger is the cache the fewer updates on the seq$ table take place. So if you want to insert millions of rows the cache should be at least 1000.
In my point of view, there is no problem with caching of 5000 values or more. Because only in event of an instance failure (power failure, shutdown abort), any unused values still in memory are lost.

Caching has also a positive impact on parallel processing. Even significantly better than serial processing.

By using the technique of directly calling the sequence in the DML statements, do not forget to increase the cache explicitly and reset it to the default value after the DML is finished.

Let see some examples:

--we create a sequence with no cache
create sequence my_seq increment by 1 nocache;
--we create two table a and b. Both with 1Mio records
create table a as select rownum id, decode(mod(rownum,2),0,null,rownum) id2 from (
(select * from dual connect by level < 1001 ) cross join (select * from dual connect by level < 1001));

create table b as select rownum id from (
(select * from dual connect by level < 1001 ) cross join (select * from dual connect by level < 1001));

-- we create an empty table
create table tbl_test_seq (id number,  text varchar2(300));
-- we insert 1 Mio records in serial processing
-- and we enable event 10046
ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
insert /*+ append gather_plan_statistics */ into tbl_test_seq t
select  nvl(a.id2,my_seq.nextval) ,lpad('xx',200,'y') text from a  join b
on a.id = b.id;
commit;
ALTER SESSION SET EVENTS '10046 trace name context off';

Insert of 1Mio records in serial processing

Insert 1Mio records in dop = 8. Trace for one process slave:

After caching the sequence, the insert works very efficiently.

A remark about the consistency check when deleting from a table in Oracle.

When deleting data from a table, Oracle checks for each deleted row whether there are other rows that depend on the deleted one. So oracle will scan (or use indexes if they exist) all tables that have a foreign key pointing on the target table, to see whether
the deleted row has child rows. So Deleting must not cause orphans.
Oracle will do a full scan on the child tables if no indexes are defined on the foreign keys columns. This is of course very inefficient if the child tables are big.

But what if you just want to delete data from the parent table that does not exist in the child tables? Like this „delete from tbl_parent where not exists (select * from tbl_child)„.
You would expect Oracle to not recheck that the deleted records does not have any dependent records in the child tables.

Let’s illustrate this with an example

CREATE TABLE tbl_parent (ID NUMBER PRIMARY KEY) ;

CREATE TABLE tbl_child (ID NUMBER , id_parent NUMBER, CONSTRAINT fk_tbl_child_parent FOREIGN KEY (id_parent) REFERENCES tbl_parent(ID)) ;

INSERT INTO tbl_parent SELECT ROWNUM FROM dual CONNECT BY LEVEL < 101;
INSERT   INTO tbl_child SELECT ROWNUM, ROWNUM + 50 FROM dual CONNECT BY LEVEL < 51;
COMMIT;

And we try to delete all rows from the parent table that does not exist in the child table. We enable the event 10046 and analyze the trace file.

ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';

delete from tbl_parent where id not in (select id_parent from tbl_child);
commit;
ALTER SESSION SET EVENTS '10046 trace name context off';

In the trace file:

SQL ID: 8uucqtmzuqnhz Plan Hash: 2719173255
delete from tbl_parent
where
id not in (select id_parent from tbl_child)
call count cpu elapsed disk query current rows
——- —— ——– ———- ———- ———- ———- ———-
Parse 1 0.00 0.00 0 4 2 0
Execute 1 0.00 0.00 0 11 256 50
Fetch 0 0.00 0.00 0 0 0 0
——- —— ——– ———- ———- ———- ———- ———-
total 2 0.01 0.00 0 15 258 50
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 107
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
———- ———- ———- —————————————————
0 0 0 DELETE TBL_PARENT (cr=361 pr=0 pw=0 time=9025 us starts=1)
50 50 50 HASH JOIN ANTI NA (cr=11 pr=0 pw=0 time=285 us starts=1 cost=5 size=2600 card=100)
100 100 100 INDEX FAST FULL SCAN SYS_C0021393 (cr=4 pr=0 pw=0 time=24 us starts=1 cost=2 size=1300 card=100)(object id 100084)
50 50 50 TABLE ACCESS FULL TBL_CHILD (cr=7 pr=0 pw=0 time=17 us starts=1 cost=3 size=650 card=50)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
—————————————- Waited ———- ————
Disk file operations I/O 1 0.00 0.00
log file sync 1 0.00 0.00
PGA memory operation 1 0.00 0.00
SQLNet message to client 1 0.00 0.00 SQLNet message from client 1 0.00 0.00

SQL ID: g59tya9raw21s Plan Hash: 3669815686
select /*+ all_rows */ count(1)
from
„TESTER“.“TBL_CHILD“ where „ID_PARENT“ = :1
call count cpu elapsed disk query current rows
——- —— ——– ———- ———- ———- ———- ———-
Parse 1 0.00 0.00 0 0 0 0
Execute 50 0.00 0.00 0 0 0 0
Fetch 50 0.00 0.00 0 350 0 50
——- —— ——– ———- ———- ———- ———- ———-
total 101 0.00 0.00 0 350 0 50
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS (recursive depth: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
———- ———- ———- —————————————————
1 1 1 SORT AGGREGATE (cr=7 pr=0 pw=0 time=27 us starts=1)
0 0 0 TABLE ACCESS FULL TBL_CHILD (cr=7 pr=0 pw=0 time=23 us starts=1 cost=2 size=13 card=1)

SQL ID: 9pgdf32vu0h3c Plan Hash: 0
commit

So we see that for each deleted row oracle do the following check:

select /*+ all_rows */ count(1)
from
"TESTER"."TBL_CHILD" where "ID_PARENT" = :1

So oracle did not realize that this check is superfluous.

Select from comma-separated list using just the famous „dual“ Table

There are many ways to read comma-separated line into a array: Using PL/SQL Functions, XMLTABLE … However in this example I use only the famous select * from dual
The goal is to transform the String ‚val1,val2,….,val11‘ into varchar2list.

declare
  v_values sys.odcivarchar2list := sys.odcivarchar2list();
begin
  with myline as
   (select 'val1,val2,val3,val4,val5,val6,val7,val8,val9,val10,val11,' text from dual)
  select case
           when a < b then
            substr(text, b)
           else
            substr(text, b, a - b)
         end text
    bulk collect
    into v_values
    from (select rownum n,
                 instr(text, ',', 1, rownum) a,
                 decode(rownum, 1, 1, instr(text, ',', 1, rownum - 1) + 1) b,
                 text
          
            from myline
           where decode(rownum, 1, 1, instr(text, ',', 1, rownum - 1)) > 0
          connect by level < 100);
  for i in 1 .. v_values.count loop
    dbms_output.put_line(v_values(i));
  end loop;
end;

val1
val2
val3
val4
val5
val6
val7
val8
val9
val10
val11

Output

The method can deal also with empty strings.

Executing operating system commands from PL/SQL: As an example, finding differences between two OS files using the diff shell command

In some situations, you need to run programs that are installed on the host machine from an Oracle database. You can do that for example using Java classes. But there is another easy way to achieve this. With the help of the „dbms_scheduler“ package. As Example I write PL/SQL block to get the differences between tow files using the diff shell command.

DECLARE
v_file_left_full_name VARCHAR2(280) := '/tmp/file1';
v_file_right_full_name VARCHAR2(280) := '/tmp/file2';
v_file_out_full_name VARCHAR2(280) := '/tmp/diff_result';
v_script_full_name VARCHAR2(280) := '/tmp/filediff.sh';
v_os_program       VARCHAR2(280) := '/bin/bash';
--
v_job_name VARCHAR2(30) := 'FILE_DIFF';
v_os_credential VARCHAR2(30) := 'OS_CRED';

--====Begin====--
BEGIN
 
  BEGIN
    dbms_scheduler.drop_job(job_name => v_job_name);
  EXCEPTION
    WHEN OTHERS THEN
      IF SQLCODE = -27475 THEN
        dbms_output.put_line('--');
      ELSE
        RAISE;
      END IF;
  END ;
   BEGIN
    DBMS_SCHEDULER.drop_credential(v_os_credential);
  EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line('1- ' || SQLERRM);
      NULL;
  END ;
  DBMS_SCHEDULER.create_credential(v_os_credential,'oracle','oracle');
  DBMS_SCHEDULER.CREATE_JOB(job_name          => v_job_name,
                            job_type          => 'EXECUTABLE',
                            job_action        => v_os_program,
                            number_of_arguments => 4,
                            credential_name=>   v_os_credential,
                            enabled           => FALSE);
  DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(v_job_name,
                                        1,
                                        v_script_full_name);
  DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(v_job_name,
                                        2,
                                        v_file_left_full_name);
  DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(v_job_name,
                                        3,
                                        v_file_right_full_name);
  DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(v_job_name,
                                        4,
                                        v_file_out_full_name);
  dbms_scheduler.run_job(job_name => v_job_name);
END;
#!/bin/bash
diff $1 $2 > $3
exit 0