Oracle DBA Forums > Execute command line program within Java within Oracle

Full Version: Execute command line program within Java within Oracle

Oracle DBA Forums > Oracle > Oracle Forum

AndreCrash3r

Jan 10 2012, 08:53 AM

Hi all,

I've created a Java class in my Oracle DB that calls a Visual Basic program to convert a XLS file into a CSV file in order to load it into an external table. The problem that I have is that when I call the Visual Basic program from the Java class, nothing happens. I had the same problem with a Python program, and I thought that the problem was from Python, but now with Visual Basic the problem remains, both aren't executed.

The strange thing is that when I call the same Java class outside Oracle, directly from a command line, it executes both Python and Visual Basic programs.

Here is the Java class defined in Oracle:

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "OSCommand" AS
import java.io.*;
public class OSCommand{
public static void Run(){
try
{
Runtime r = Runtime.getRuntime();

Process p = r.exec("cmd /c C:\\Ficheiros\\SAP\\Novos\\XlsToCsv.vbs C:\\Ficheiros\\SAP\\Novos\\SAP.xls C:\\Ficheiros\\SAP\\Novos\\SAP.csv");
}
catch (Exception e)
{
e.printStackTrace();
}
}
}
/

And here is the procedure that calls the Java class:

create or replace
procedure run_os_command
as language java
name 'OSCommand.Run()';

Can anyone help?

Thanks,
André

burleson

Jan 10 2012, 11:43 AM

Hi André,

This is a DBA forum, but I may be able to help you!

>> I've created a Java class in my Oracle DB that calls a Visual Basic program to convert a XLS file into a CSV file in order to load it into an external table.

It's more elegant to just write a quick PL/SQL to stuff commas in between the results . . .

Here is one:

http://www.google.com/search?sourceid=ie7&...024&bih=527

CODE

create or replace procedure dump_table_to_csv( p_tname in varchar2,
                                                p_dir   in varchar2,
                                                p_filename in varchar2 )
is
     l_output       utl_file.file_type;
    l_theCursor     integer default dbms_sql.open_cursor;
    l_columnValue   varchar2(4000);
    l_status        integer;
    l_query         varchar2(1000)
                     default 'select * from ' || p_tname;
     l_colCnt        number := 0;
     l_separator     varchar2(1);
     l_descTbl       dbms_sql.desc_tab;
  begin
  l_output := utl_file.fopen( p_dir, p_filename, 'w' );
   execute immediate 'alter session set nls_date_format=''dd-mon-yyyy hh24:mi:ss''';
     dbms_sql.parse(  l_theCursor,  l_query, dbms_sql.native );
     dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );

     for i in 1 .. l_colCnt loop
     utl_file.put( l_output, l_separator || '"' || l_descTbl(i).col_name || '"'
);
          dbms_sql.define_column( l_theCursor, i, l_columnValue, 4000 );
         l_separator := ',';
       end loop;
       utl_file.new_line( l_output );

       l_status := dbms_sql.execute(l_theCursor);

       while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
          l_separator := '';
           for i in 1 .. l_colCnt loop
               dbms_sql.column_value( l_theCursor, i, l_columnValue );
               utl_file.put( l_output, l_separator || l_columnValue );
               l_separator := ',';
           end loop;
           utl_file.new_line( l_output );
       end loop;
       dbms_sql.close_cursor(l_theCursor);
       utl_file.fclose( l_output );

       execute immediate 'alter session set nls_date_format=''dd-MON-yy'' ';
   exception
       when others then
          execute immediate 'alter session set nls_date_format=''dd-MON-yy'' ';
          raise;
   end;
   /

See these notes on using Oracle with Java

http://dba-oracle.com/googlesearchsite_pro...+oracle+command

****************************************
>> The problem that I have is that when I call the Visual Basic program from the Java class, nothing happens.

Salt it with displays so you can see if it reaches the Java class:

Read this carefully:

http://www.dba-oracle.com/t_introduction_java_debugging.htm

In any case, I would put the convert to csv in Oracle, where it belongs!

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.