SQL>
SQL> --connect as system to grant permisiions to scott
SQL> --java classes
SQL>
SQL> ---------------------------------------------------------------------
SQL> connect system/manager
Connected.
SQL>
SQL> begin
2 dbms_java.grant_permission
3 ('SCOTT',
4 'java.io.FilePermission',
5 '*',
6 'read');
7 dbms_java.grant_permission
8 ('SCOTT',
9 'java.io.FilePermission',
10 '*',
11 'execute');
12 dbms_java.grant_permission
13 ('SCOTT',
14 'java.lang.RuntimePermission',
15 '*',
16 'writerFileDescriptor');
17 end;
18 /
PL/SQL procedure successfully completed.
SQL> -----------------------------------------------------------------------
SQL>
SQL> -- now connect as LOADER
SQL>
SQL> connect scott/tiger
Connected.
SQL>
SQL> -- create a TABLES for SQLLoader
SQL>
SQL> ----------------------------------------------------------------------
SQL>
SQL> drop table dir_list;
Table dropped.
SQL>
SQL> create table dir_list
2 (filename varchar2(255))
3 ;
Table created.
SQL>
SQL>
SQL> DROP TABLE TAB_LOAD_DATA_DETAIL CASCADE CONSTRAINTS ;
Table dropped.
SQL>
SQL> CREATE TABLE TAB_LOAD_DATA_DETAIL (
2 DATETIME DATE DEFAULT SYSDATE,
3 TABLE_NAME VARCHAR2(31),
4 FILE_NAME VARCHAR2(255))
5 TABLESPACE USERS PCTUSED 40 PCTFREE 10
6 STORAGE(INITIAL 131072 NEXT 131072 PCTINCREASE 0 )
7 PARALLEL (DEGREE 1 INSTANCES 1) NOCACHE;
Table created.
SQL>
SQL> DROP TABLE TAB_LOAD_FAIL_DETAIL CASCADE CONSTRAINTS ;
Table dropped.
SQL>
SQL> CREATE TABLE TAB_LOAD_FAIL_DETAIL (
2 DATETIME DATE NOT NULL,
3 RESULT VARCHAR2(20) NOT NULL,
4 DISCARD_DIR VARCHAR2(255),
5 BAD_DIR VARCHAR2(255),
6 LOG_DIR VARCHAR2(255))
7 TABLESPACE USERS PCTUSED 40 PCTFREE 10
8 STORAGE(INITIAL 16384 NEXT 16384 PCTINCREASE 10 )
9 PARALLEL (DEGREE 1 INSTANCES 1) NOCACHE;
Table created.
SQL>
SQL>
SQL> --create java source
SQL>
SQL> drop java source "DirList";
Java dropped.
SQL>
SQL>
SQL> create or replace
2 and resolve java source named "DirList"
3 as
4 import java.io.*;
5 import java.sql.*;
6
7 public class DirList
8 {
9 public static void getList(String directory)
10 throws SQLException
11 {
12 File path = new File(directory);
13 String[] list = path.list();
14 String element;
15
16 for (int i=0; i < list.length; i++)
17 {
18 element = list[i];
19 #sql { insert into dir_list(filename)
20 values(:element)
21 };
22 }
23 }
24
25 }
26 /
Java created.
SQL>
SQL>
SQL> -- create procedure
SQL>
SQL>
SQL> ---------------------------------------------------------------------
SQL>
SQL>
SQL> drop procedure get_dir_list;
Procedure dropped.
SQL>
SQL> create or replace procedure get_dir_list(p_directory in varchar2)
2 as
3 language java
4 name 'DirList.getList( java.lang.String)';
5 /
Procedure created.
SQL>
SQL>
SQL> -- execute the proc
SQL>
SQL>
SQL> ----------------------------------------------------------------------
SQL>
SQL>
SQL> exec get_dir_list('h:\oracle\loader_scripts');
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> -- select the data from dirlist temporary table to make sure that data is there
SQL>
SQL> select * from dir_list;
FILENAME
--------------------------------------------------------------------------------
ALL_SOURCES.sql
compare_file_name.sql
dirlist.log
emp.dat
get_tab_ddl.sql
get_tab_script.doc
SqlLoader.sql
utl_java_source.sql
what_I_did.log
9 rows selected.
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
Redo thread mounted by this instance: 1
Oracle process number: 15
Windows thread id: 2008, image: ORACLE.EXE
*** SESSION ID:(12.17) 2002-06-12 17:11:37.045
java.io.IOException
at oracle.aurora.java.lang.OracleProcess.create(OracleProcess.java)
at oracle.aurora.java.lang.OracleProcess.construct(OracleProcess.java)
at java.lang.Runtime.execInternal(Runtime.java)
at java.lang.Runtime.exec(Runtime.java:272)
at java.lang.Runtime.exec(Runtime.java:195)
at java.lang.Runtime.exec(Runtime.java:152)
at Util.RunThis(Util:12)
=============================================================
how to haldle those exceptions can some body help me.........
