Home » Developer & Programmer » Application Express, ORDS & MOD_PLSQL » Shell Script in HTMLDB
Shell Script in HTMLDB [message #254019] Wed, 25 July 2007 09:29 Go to next message
rtantrav
Messages: 2
Registered: July 2007
Location: Hyderabad, India
Junior Member
I've a requirement to validate the existance of filenames given in htmldb form. The files would be residing on a different linux server. Hence the html db should connect to the linux server and then check for the file existance. Is it possible in htmldb? Please let me know if there are any other approaches.
Re: Shell Script in HTMLDB [message #254104 is a reply to message #254019] Wed, 25 July 2007 14:22 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
sure many ways:
1.) assuming remote server has Oracle running and you can create a DB link to that DB, then see XUTL_FINDFILES, XUTL_FTP:
http://www.chrispoole.co.uk/index.htm
2.) you can run an OS command to list the files and read the result back into pl/sql. On Windows it is something like this:

--grant create any job, create external job, execute on DBMS_SCHEDULER to the user
--   DBMS_SCHEDULER.create_job
--      (job_name        => 'testjob',
--       job_type        => 'EXECUTABLE',
--       job_action      => 'c:\windows\system32\cmd.exe /c c:\my_script.bat',
--       enabled         => false
--      );

DBMS_SCHEDULER.run_job
   (job_name        => 'testjob',
    use_current_session =>TRUE);



If you have an nfsmount of the remote box onto the local one, then the local machine can see the remote files. On the remote machine, you can add your local machine to the .rhosts file and then the local machine can do a remsh (or whatever Linux uses) to do an ls of the remote files without a passwd. If you redirect your output from the script to a log, you can read that log back in using utl_file or simply an external table.

      -- grant CREATE EXTERNAL JOB to the user
      -- grant create any directory to the user
      -- create or replace directory LOG_DIR as 'c:\abc\logs';

   --CREATE TABLE EXT_LOG_TAB
   --(LINE     VARCHAR2(4000))
   --ORGANIZATION EXTERNAL
   --  (  TYPE ORACLE_LOADER
   --     DEFAULT DIRECTORY LOG_DIR
   --     ACCESS PARAMETERS
   --       ( RECORDS DELIMITED BY NEWLINE
   --      NOBADFILE
   --      NODISCARDFILE
   --      NOLOGFILE
   --      DATE_CACHE 0
   --      FIELDS
   --         MISSING FIELD VALUES ARE NULL
   --         REJECT ROWS WITH ALL NULL FIELDS
   --         ( LINE          CHAR (4000)) )
   --     LOCATION ('my_log.log')
   --  )
   --REJECT LIMIT UNLIMITED;

select * from EXT_LOG_TAB;
Re: Shell Script in HTMLDB [message #254154 is a reply to message #254104] Thu, 26 July 2007 00:02 Go to previous messageGo to next message
rtantrav
Messages: 2
Registered: July 2007
Location: Hyderabad, India
Junior Member
Hi,
Thanks for the reply. But I've some queries regarding the dbms_scheduler approach.
1. The linux server on which the files reside doesn't have a database running.
2. Is it something like the script my_script.bat exists on the db server, and this script is used to ssh to the linux server?
3. Since the htmldb application is used simultaneously by different users, how does the dbms_scheduler works in this scenario?
4. Also how do we get the response from the linux server, that the file exists in the given directory or not?

Please suggest me if I can accomplish the above using this approach.
Re: Shell Script in HTMLDB [message #254442 is a reply to message #254154] Thu, 26 July 2007 14:35 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Yes, my_script.bat exists on the DB host. You can try passing a unique spool name as a parameter to the script based on user IP address, session ID or whatever. sys_context('userenv', 'SESSIONID') or sys_context('userenv', 'IP_ADDRESS'). If dbms_scheduler wont let you do that - just create the script on the fly (also not great for concurrent access like you are trying to avoid).

As long as you aren't using XE, you can run Java commands too and you can definitely add command line parameters. Example of capturing DBMS_OUTPUT from Java (without using a log file).
delete t; 

DECLARE 
   v_line     VARCHAR2 (200); 
   v_status   NUMBER; 
   n          NUMBER         := 1; 
BEGIN 
   DBMS_OUTPUT.ENABLE (1000000); 
   dbms_java.set_output (1000000); 
   exec_j ('/usr/bin/sh -c /usr/bin/ls -ltr initdw*.ora'); 

   LOOP 
      DBMS_OUTPUT.get_line (v_line, v_status); 

      IF v_status = 0 
      THEN 
         insert into v values (n, v_line); 
         n    := n + 1; 
      ELSE 
         EXIT; 
      END IF; 
   END LOOP; 
END; 
/ 

select * from t; 

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:952229840241

The output from the remote host command should come back to the local DB host (try it at commandline) but beware - you can't rely on the exit code becuase it will be from the remsh command itself - not the remote command.
Re: Shell Script in HTMLDB [message #254706 is a reply to message #254442] Fri, 27 July 2007 10:56 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
One more point - an easy way to avoid multiple executions of the script running similtaneously is to monitor session activity like this.

declare
 v_busy_XYZ_processes number;
begin
 for i in 1..100 loop
   -- check if script from another session currenlty running
   select count(*) into v_busy_XYZ_processes 
   from v$session where user_info = 'XYZ script';

   if v_busy_email_processes = 0
   then
     -- No running copy of this code, so set signature 
     -- on this session to show we are busy running script
     dbms_application_info.set_client_info( 'XYZ script' ); 
    
     --==================
     -- run script now
     --==================
     ...

     -- remove signature - or if this code only runs
     -- in dbms_job, then it'll end naturally
     dbms_application_info.set_client_info( 'done' ); 

     exit;  -- script complete, so exit loop
   else
     -- Sleep 1 sec - hopefully blocking session will be done
     dbms_lock.sleep(1); 
   end if;
 end loop;
end;
/
Previous Topic: How do I run a Form from local host?
Next Topic: Dynamic Input Fields
Goto Forum:
  


Current Time: Thu Mar 28 06:42:55 CDT 2024