SQL>
SQL>
SQL> this java source
SQL> --------------------------------------------------------------------
SQL>
SQL> create or replace and resolve
2 java source named "Util"
3 as
4 import java.io.*;
5 import java.lang.*;
6
7 public class Util extends Object
8 {
9 public static int RunThis(String[] args)
10 {
11 Runtime rt = Runtime.getRuntime();
12 int rc = -1;
13 try
14 {
15 Process p = rt.exec(args[0]);
16
17 int bufSize = 4096;
18 BufferedInputStream bis =
19 new BufferedInputStream(p.getInputStream(), bufSize);
20 int len;
21 byte buffer[]=new byte[bufSize];
22
23 while((len=bis.read(buffer,0,bufSize)) != -1)
24 System.out.write(buffer,0,len);
25 rc=p.waitFor();
26 }
27
28 catch (Exception e)
29
30 {
31 e.printStackTrace();
32 rc=-1;
33 }
34
35 finally
36
37 {
38 return rc;
39 }
40 }
41 }
42 /
Java created.
SQL>
SQL>
SQL> --------------------------------------------------------------------
SQL>
SQL> rem now create a function
SQL>
SQL> connect scott/tiger@srinivas
Connected.
SQL>
SQL> create or replace function run_cmd(p_cmd in varchar2) return number
2 as
3 language java
4 name 'Util.RunThis(java.lang.String[]) return integer';
5 /
Function created.
SQL>
SQL> ---------------------------------------------------------------------
SQL> rem create this procedure as LOADER
SQL>
SQL>
SQL>
SQL> create or replace procedure Exec_Cmd(p_cmd in varchar2)
2 as
3 x number;
4 v_date_format varchar2(255);
5
6
7 begin
8
9 v_date_format:='mon-dd-yyyy hh24:mi:ss';
10 Execute immediate ' alter session set nls_date_format ='''|| v_date_format||'''';
11 x:=run_cmd(p_cmd);
12 if x = 0 then
13 INSERT INTO TAB_LOAD_FAIL_DETAIL (
14 DATETIME,
15 RESULT,
16 DISCARD_DIR,
17 BAD_DIR,
18 LOG_DIR
19 )
20 VALUES (
21 sysdate,
22 'Succeeded',
23 'H:\Oracle\ldr_discard\',
24 'H:\Oracle\ldr_bad\',
25 'H:\Oracle\ldr_log\'
26 );
27 commit;
28 elsif x <> 0 then
29 INSERT INTO TAB_LOAD_FAIL_DETAIL (
30 DATETIME,
31 RESULT,
32 DISCARD_DIR,
33 BAD_DIR,
34 LOG_DIR
35 )
36 VALUES (
37 sysdate,
38 'Failed',
39 'H:\Oracle\ldr_discard\',
40 'H:\Oracle\ldr_bad\',
41 'H:\Oracle\ldr_log\'
42 );
43 commit;
44 end if;
45 end;
46 /
Procedure created.
SQL>
SQL> --
SQL>
SQL> CREATE OR REPLACE PROCEDURE compare_file_name
2 as
3
4
5 cursor tab is select table_name from dba_tables where owner =user;
6
7 v_file varchar2(255);
8 v_date_format varchar2(255);
9 v_cmd_string varchar2(255);
10
11
12 begin
13
14 Execute immediate' delete from dir_list';
15
16 get_dir_list('H:\Oracle\loader_scripts');
17
18 v_date_format:='mon-dd-yyyy hh24:mi:ss';
19
20 Execute immediate ' alter session set nls_date_format ='''|| v_date_format||'''';
21 for tab_rec in tab loop
22 --dbms_output.put_line(tab_rec.table_name);
23 for x in (select upper(substr(filename,1,instr(filename,'.')-1)) filename from dir_list)
24 loop
25 v_file := x.filename;
26
27 if tab_rec.table_name = v_file then
28
29 INSERT INTO TAB_LOAD_DATA_DETAIL
30 VALUES ( sysdate,tab_rec.table_name,'H:\Oracle\loader_scripts\'||v_file||'.dat');
31 commit;
32 dbms_output.put_line(tab_rec.table_name ||' '||v_file||' '||' You Got that');
33
34 Exec_Cmd(chr(39)||'sqlldr parfile=H:\Oracle\ldr_par_files\'||tab_rec.table_name||'.ctl'||chr(39));
35
36
37 end if;
38
39 end loop;
40 end loop;
41 end;
42 /
Procedure created.
SQL>
SQL>
SQL>
SQL> EXEC COMPARE_FILE_NAME;
PL/SQL procedure successfully completed.
SQL>
SQL> spool off
the dump file generated has the following errors messages
========================================================
Dump file H:\Oracle\admin\srinivas\udump\ORA02008.TRC
Wed Jun 12 17:11:37 2002
ORACLE V8.1.6.3.0 - Production vsnsta=0
vsnsql=e vsnxtr=3
Windows 2000 Version 5.0 Service Pack 2, CPU type 586
Oracle8i Enterprise Edition Release 8.1.6.3.0 - Production
With the Partitioning option
JServer Release 8.1.6.3.0 - Production
Windows 2000 Version 5.0 Service Pack 2, CPU type 586
Instance name: srinivas
Reply With Quote
Bookmarks