keerthikannan v

• Jan 23rd, 2012

 

Following Step need to setup before executing the Conversion Script


Step 1: Logon to the Database server and connect to the database.

Step 2: Run the below query and it should return five records:

Code
SELECT COMP_ID FROM DBA_REGISTRY WHERE COMP_ID IN (CATALOG,CATPROC,CATJAVA,JAVAVM,XML);


 


COMP_ID


------------------------------


CATALOG


CATPROC


CATJAVA


JAVAVM


XML

Step 3: If it doesnt you need to install those base Oracle Packages.

Code
JAVAVM: ${ORACLE_HOME}/javavm/install/initjvm


CATJAVA: ${ORACLE_HOME}/rdbms/admin/catjava.sql 


    ${ORACLE_HOME}/rdbms/admin/initdbj.sql


XML: ${ORACLE_HOME}/rdbms/admin/initxml.sql

Step 4: Copy the attached jxl.jar file to $ORACLE_HOME/jdk location on the database server.

Step 5: Upload the jar file using the below command:

Code
 


                    cd $ORACLE_HOME/bin


  loadjava -v -u system -r /u01/oracle/product/10.2.0/jdk/jxl.jar


 

Step 6: Execute below grant permissions:

Code
GRANT JAVAUSERPRIV TO PRT;


exec dbms_java.grant_permission( PUBLIC, SYS:java.lang.RuntimePermission, createClassLoader,  );


exec dbms_java.grant_permission( PUBLIC, SYS:java.lang.RuntimePermission, getClassLoader,  );


exec dbms_java.grant_permission( PUBLIC, SYS:java.util.PropertyPermission, *, READ,WRITE )


exec dbms_java.grant_permission( PUBLIC, SYS:java.lang.RuntimePermission, accessDeclaredMembers,  );


exec dbms_java.grant_permission( PUBLIC, SYS:java.lang.reflect.ReflectPermission, suppressAccessChecks,  );


 


exec dbms_java.grant_permission( PUBLIC, SYS:java.util.PropertyPermission, *, READ,WRITE );


exec dbms_java.grant_permission(PUBLIC, java.io.FilePermission, /home/retek/apps_files/-, READ ,WRITE, execute, DELETE);


exec dbms_java.grant_permission(PUBLIC, SYS:java.lang.RuntimePermission, writeFileDescriptor, );


exec dbms_java.grant_permission(PUBLIC, SYS:java.lang.RuntimePermission, readFileDescriptor, );


 


CREATE OR REPLACE DIRECTORY temp AS /home/retek/apps_files/; 


GRANT READ ON DIRECTORY temp TO user;


GRANT WRITE ON DIRECTORY temp TO user; 


GRANT DELETE ON DIRECTORY temp TO user;


 

Step 7: Compile the Java stored Procedure(gocsv) from sql prompt


place the ExcelFile.xls in /home/retek/apps_files/ path and execute the blow script

Code
SET serverout ON 


declare


Path varchar2(1000) := /home/retek/apps_files/; -- directory with perms 


Fname varchar2(1000) := ExcelFile.xls;


Fine varchar2(1000);


begin 


Fine := nn$gocsv.ConvertCSV(Path,Fname,;);


dbms_output.put_line(substr(Fine,1,255));


end;

The Script will generate the following csv file

ExcelFile.xls.csv