Calling os_command.exec from an Oracle stored procedure

Question

I have used os_command.exec to send commands to the Linux shell. I am using Oracle 12c.

Here is an example code which works fine:

select os_command.exec('/home/smucha/app/smucha/product/12.1.0/dbhome_1/bin/sqlldr userid=system/password control=/home/smucha/load_data.cmt')
from dual

I would like to run a similar command in a stored procedure. Is there any way to do this?

I have tried the following but it does not work. My procedure runs without errors but no records are loaded.

execute immediate 'select os_command.exec(''/home/smucha/app/smucha/product/12.1.0/dbhome_1/bin/sqlldr userid=system/acorp56k control=/home/smucha/IZ/load_data.cmt'') from dual';

Answer 1

Your query is never executed. From the documentation:

If dynamic_sql_statement is a SELECT statement, and you omit both into_clause and bulk_collect_into_clause, then execute_immediate_statement never executes.

Your execute immdiate doesn't have an into clause, so it's essentially ignored.

You don't need the query though, you can call the function directly:

procedure foo is
  result pls_integer; -- or whatever type your function actually returns
begin
  result := os_command.exec('/home/smucha/app/smucha/product/12.1.0/dbhome_1/bin/sqlldr userid=system/password control=/home/smucha/load_data.cmt');
  -- do something with the result?
end foo;

As an aside, you might want to consider using an external table rather than a call out to SQL*Loader.