Error while Shell executing from Oracle 11g Using java proc

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

Error while Shell executing from Oracle 11g Using java proc

Postby OraNew » Fri Apr 20, 2012 8:35 am

Hi,

I'm using one generic code that is available here to execute shell from Oracle procedure.

The generic procedure are as follows -

Code: Select all
create or replace and compile java source named host as
import java.io.*;
public class Host {
  public static void executeCommand(String command) {
    try {
      String[] finalCommand;
      if (isWindows()) {
        finalCommand = new String[4];
        finalCommand[0] = "C:\\windows\\system32\\cmd.exe";  // Windows XP/2003
        finalCommand[1] = "/y";
        finalCommand[2] = "/c";
        finalCommand[3] = command;
      }
      else {
        finalCommand = new String[3];
        finalCommand[0] = "/bin/bash";
        finalCommand[1] = "-c";
        finalCommand[2] = command;
      }
 
      final Process pr = Runtime.getRuntime().exec(finalCommand);
      pr.waitFor();
 
      new Thread(new Runnable(){
        public void run() {
          BufferedReader br_in = null;
          try {
            br_in = new BufferedReader(new InputStreamReader(pr.getInputStream()));
            String buff = null;
            while ((buff = br_in.readLine()) != null) {
              System.out.println("Process out :" + buff);
              try {Thread.sleep(100); } catch(Exception e) {}
            }
            br_in.close();
          }
          catch (IOException ioe) {
            System.out.println("Failed to print.");
            ioe.printStackTrace();
          }
          finally {
            try {
              br_in.close();
            } catch (Exception ex) {}
          }
        }
      }).start();
 
      new Thread(new Runnable(){
        public void run() {
          BufferedReader br_err = null;
          try {
            br_err = new BufferedReader(new InputStreamReader(pr.getErrorStream()));
            String buff = null;
            while ((buff = br_err.readLine()) != null) {
              System.out.println("Process err :" + buff);
              try {Thread.sleep(100); } catch(Exception e) {}
            }
            br_err.close();
          }
          catch (IOException ioe) {
            System.out.println("Process error.");
            ioe.printStackTrace();
          }
          finally {
            try {
              br_err.close();
            } catch (Exception ex) {}
          }
        }
      }).start();
    }
    catch (Exception ex) {
      System.out.println(ex.getLocalizedMessage());
    }
  }
 
  public static boolean isWindows() {
    if (System.getProperty("os.name").toLowerCase().indexOf("windows") != -1)
      return true;
    else
      return false;
  }
 
};


Oracle version -

Code: Select all
SQL>
SQL> select * from v$version;
 
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
 
Elapsed: 00:00:00.01
SQL>
SQL>


Linux Version -

Code: Select all
[oracle@bpcatapult loader_command]$ cat /proc/version
Linux version 2.6.18-274.7.1.el5PAE (mockbuild@x86-007.build.bos.redhat.com) (gcc version 4.1.2 20080704 (Red Hat 4.1.2-50)) #1 SMP Mon Oct 17 12:05:46 EDT 2011


And, the problem I'm facing -

Case 1: [Directly running from shell prompt]

Code: Select all
[oracle@pult loader_command]$ ./TG_Supply.sh
Commit point reached - logical record count 100
Commit point reached - logical record count 200
Commit point reached - logical record count 300
.
.
.
.
Commit point reached - logical record count 53051
Commit point reached - logical record count 53082
Commit point reached - logical record count 53113
Commit point reached - logical record count 53144
Commit point reached - logical record count 53175
Commit point reached - logical record count 53206
Commit point reached - logical record count 53237
Commit point reached - logical record count 53268
Commit point reached - logical record count 53299
Commit point reached - logical record count 53330
Commit point reached - logical record count 53640


And, the count it shows -

Code: Select all
SQL>
SQL> select count(*) from tg_sup;
 
  COUNT(*)
----------
     53342
 
Elapsed: 00:00:00.03
SQL>


Case 2: [Running from Oracle Procedure]

Code: Select all
SQL> declare
  2    err_cd   number;
  3    err_desc varchar2(500);
  4  begin
  5   dbms_java.set_output(1000000);
  6   host(p_command => '/a/mis/Sqlloader_script/loader_command/ST_det.sh');
  7   dbms_output.put_line('Successfully Executed SQL Loader Command');
  8  exception
  9      when others then
 10        err_cd := 1;
 11        err_desc := substr(sqlerrm,1,500);
 12        dbms_output.put_line(err_desc);
 13  end;
 14  /


And, the output count is -

Code: Select all
SQL>
SQL> select count(*) from tg_sup;
 
  COUNT(*)
----------
     42476
 
Elapsed: 00:00:00.03
SQL>


And, from Unix prompt it shows -

Code: Select all
[oracle@pult loader_command]$ ps -ef|grep -i "ST_det.sh"
oracle   14565 14485  0 11:00 ?        00:00:00 /a/mis/Sqlloader_script/loader_command/ST_det.sh T_det.sh
oracle   14608 13619  0 11:01 pts/1    00:00:00 grep -i st_det.sh


In first case, It loads 53K+ & finished successfully, whereas in the second case it loads 42K+ and then the program seems to be hanged.

One more thing I've noticed now.
Until I killed the session from shell, rest of the records won't insert.

But, SQL*Plus session still remain in freeze state even this forceful methods loads all the data.

What might be the reason for this strange action?

Really looking for some suggestion.

This is working perfectly in Windows server.

Thanks.
OraNew
Member
 
Posts: 5
Joined: Fri Apr 20, 2012 8:18 am

Re: Error while Shell executing from Oracle 11g Using java p

Postby Tim... » Fri Apr 20, 2012 8:43 am

Hi.

First, try adding,

Code: Select all
exit 0


, to the end of your SQL*Loader script.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17933
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Error while Shell executing from Oracle 11g Using java p

Postby OraNew » Fri Apr 20, 2012 9:18 am

Hi Tim,

Thanks for your quick response.

I've already done that. But, I'm afraid - it is not working.

Until, I kill the session loading isn't finishing it's job. Once i killed it from unix prompt - it starts loading rest of the record.
OraNew
Member
 
Posts: 5
Joined: Fri Apr 20, 2012 8:18 am

Re: Error while Shell executing from Oracle 11g Using java p

Postby Tim... » Fri Apr 20, 2012 10:27 am

Hi.

What happens if you exit the SQL*Plus session calling the script?

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17933
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Error while Shell executing from Oracle 11g Using java p

Postby OraNew » Fri Apr 20, 2012 10:36 am

Hi Tim,

I cannot exit normally from sqlplus once i execute my above pl/sql snippet inside from the sqlplus. I've to terminate that by closing the session forcefully. Otherwise, it stays inside the sqlplus session.

Surprisingly, Even if i close the session from sqlplus. It continues to hold it until I killed it from DBA session.
OraNew
Member
 
Posts: 5
Joined: Fri Apr 20, 2012 8:18 am

Re: Error while Shell executing from Oracle 11g Using java p

Postby OraNew » Fri Apr 20, 2012 11:00 am

I've also tried DIRECT=FALSE as suggested in one site. But, so far no luck..... :(
OraNew
Member
 
Posts: 5
Joined: Fri Apr 20, 2012 8:18 am

Re: Error while Shell executing from Oracle 11g Using java p

Postby OraNew » Fri Apr 20, 2012 11:53 am

Hi Tim,

I changed DIRECT=TRUE. And, it worked. :)
OraNew
Member
 
Posts: 5
Joined: Fri Apr 20, 2012 8:18 am

Re: Error while Shell executing from Oracle 11g Using java p

Postby Tim... » Fri Apr 20, 2012 12:57 pm

OK.
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17933
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle SQL and PL/SQL Development

Who is online

Users browsing this forum: No registered users and 5 guests

cron