1. What is Dynamic PSP?
2. What are the differences between Oracle PSP and Dynamic PSP?
3. Why not use JSP?
4. Why Oracle still supports PL/SQL when they integrated Java into Oracle RDBMS?
5. How To Secure Dynamic PSP Development Interface?
6. How to enable native PL/SQL compilation in 9i Release 2 on Windows?
7. I am unable to login into Unit Commander using default account.
8. What is WebDAV?
9. How to avoid redirection from index.html to DPSP unit?
10. Java class to convert xls to csv or from MS Excel to CSV file into PL/SQL and upload data from .xls file to Oracle
11. Java class to convert from DBF (DB2) to CSV file within Oracle PL/SQL

Java class to convert xls to csv or from MS Excel to CSV file into PL/SQL and upload data from .xls file to Oracle

* gocsv.ConvertCSV Version 2.00
*
* Java class to convert from MS Excel to CSV file into PL/SQL
* and upload data from .csv file to Oracle Database
* Copyright(c) 2000-2006 by HitMedia. All rights reserved.
* Written by Andrew A. Toropov
*
1. How to Install
>loadjava -v -u user/pass@[TNSNAME] -r jxl.jar
>sqlplus user/pass@[TNSNAME] @gocsv.sql
2. How to Run
>sqlplus user/pass@[TNSNAME]
sql>
set serverout on
declare
Path varchar2(1000) := '/temp/'; -- 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;
/
File -/temp/ ExcelFile.xls.csv will be created
Warning! Grant Oracle Java permissions to USER.
> sqlplus "sys/[password]@[TNSNAME] as sysdba"
sql>
begin
dbms_java.grant_permission('USER',
'SYS:java.io.FilePermission',
'/temp/-',
'read,write,delete');
end;
CREATE OR REPLACE DIRECTORY temp AS '/temp/';
GRANT READ ON DIRECTORY temp TO user;
GRANT WRITE ON DIRECTORY temp TO user;
GRANT DELETE ON DIRECTORY temp TO user;
/

3. Now you can upload data from .csv file to an Oracle Database

>sqlplus user/pass@[TNSNAME]
sql>
CREATE TABLE ExcelFile
(p1 VARCHAR2(4000)
,p2 VARCHAR2(4000)
,p3 VARCHAR2(4000)
,p4 VARCHAR2(4000)
,p5 VARCHAR2(4000))
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER
DEFAULT DIRECTORY temp /**/
ACCESS PARAMETERS(
RECORDS DELIMITED BY NEWLINE
NOBADFILE NODISCARDFILE NOLOGFILE
FIELDS TERMINATED BY ';'
OPTIONALLY ENCLOSED BY '"'
MISSING FIELD VALUES ARE NULL
(p1,p2,p3,p4,p5))
LOCATION ('ExcelFile.xls.csv')
) PARALLEL 5 REJECT LIMIT UNLIMITED;


4. Now you can fetch data from Oracle external table created based on Excel file

>sqlplus user/pass@[TNSNAME]
sql>
select * from ExcelFile;

5. JSP Page to convert .xls to .csv file
<%@ page contentType="text/html;charset=UTF-8" import="java.sql.* ,java.io.*,java.util.*, java.lang.*"%>
<jsp:useBean id="gocsv" class="gocsv.ConvertCSV"/>
<%
String Fine = "";
String Fname = "";
String Path = "";
String tType = "";
String Guid = "";
if (request.getParameter("t$type") != null) tType = request.getParameter("t$type");
if (request.getParameter("guid") != null) Guid = request.getParameter("guid");
if (request.getParameter("path") != null) {
Path = request.getParameter("path");
if (request.getParameter("fname") != null) {
Fname = request.getParameter("fname");
Fine = gocsv.convert(Path,Fname,";");
out.println("ConvertCSV:"+gocsv.convert(Path,Fname,";"));
out.println("Fname:"+Fname+"<br>");
out.println("Path:"+Path+"<br>"); } }
out.println("Fine");
%>

6. We are providing a Java class only if you want a source code, purchase it from here.
Here you can download a Java class.