Install JDK in your system

After installation set the path and class path in command prompt as follows:--

 set path=%path%;e:\j2sdk 1.4.2\bin;
set class path=%class path%;c:\j2sdk 1.4.2\lib;

j2sdk contain java compiler , interpreter ,JRE and other tools.

Java program is secure and portable: --- java compiler generate byte code instance of executable code(as in c/c++) this byte code is run(interpreted) by JVM.JVM is  a set of instruction which is used to run  byte code. Also we can say that JVM is interpreter for byte code.


E:---j2sdk 1.4.2:----bin---*.exe(javac.exe/java.exe/jar.exe)

E:---j2sdk 1.4.2:----lib---*.jar


Path:--------- %path%;e:/j2sdk1.4.2_04\bin;
Classpath:---- %classpath%;e:\j2sdk1.4.2_04\lib;

The example is given below to read and import CSV file (comma delimited) in PLSQL. There is a package in Oracle forms "D2k_Delimited_String" we use to read and import comma (or any delimiter) delimited file, I have written a post also on Oracle Forms to read delimited files with this package, the following is the link http://www.foxinfotech.in/2013/02/reading-and-importing-comma-delimited.html

The same package I converted to database package to read CSV file in PLSQL efficiently, I re-named the package to "Plsql_Delimited_String". 

The below is an example given to import csv file in PL SQL with the help of this package function Get_String, the following is the usage of this function:

Plsql_Delimited_String.Getstring ([string containing delimited text, the whole row],
                                              [occurance],
                                              [unterminated boolean default false],
                                              [delimeter]);

In the following procedure user will pass a file name which should be in the directory location created in Oracle directory object, in this example directory object 'YOUR_DIRECTORY' is used, change it with your directory object and copy the delimited file in that location then pass the file name to this procedure.

CREATE OR REPLACE PROCEDURE Import_Emp_File (P_FILENAME   IN     VARCHAR2,
                                             o_msg           OUT VARCHAR2)
IS
   Infile                UTL_FILE.File_Type;
   Linebuf               VARCHAR2 (4000);
   V_Getstring           VARCHAR2 (100);

   -- Field Values Array
   TYPE Fieldvalue IS TABLE OF VARCHAR2 (100)
                         INDEX BY BINARY_INTEGER;

   Field_Position        Fieldvalue;

   Total_Rec_Count       NUMBER := 0;
   Total_Rec_Processed   NUMBER := 0;
BEGIN
   Infile := UTL_FILE.Fopen ('YOUR_DIRECTORY', P_FILENAME, 'R');

   LOOP
      ---
      UTL_FILE.Get_Line (Infile, Linebuf);
      -- adding extra pipe in end of line to read all fields

      Linebuf := Linebuf || '|';

      -- Suppose the file is containing Six delimited strings with pipe (|)
      FOR I IN 1 .. 6
      LOOP
         Field_Position (I) :=
            Plsql_Delimited_String.Getstring (Linebuf,
                                              I,
                                              FALSE,
                                              '|');
      END LOOP;

      BEGIN
         Total_Rec_Count := Total_Rec_Count + 1;

         -- an example table
         INSERT INTO EMP (EMPLOYEE_NUMBER,
                          FIRST_NAME,
                          LAST_NAME,
                          DATE_OF_JOIN,
                          EMP_TYPE,
                          DATE_OF_REGN)
             VALUES (field_position (1),
                     field_position (2),
                     field_position (3),
                     field_position (4),
                     field_position (5),
                     field_position (6));

         Total_Rec_Processed := Total_Rec_Processed + 1;
      EXCEPTION
         WHEN OTHERS
         THEN
            -- ignoring error during database insertion
            NULL;
      END;
   END LOOP;

   IF UTL_FILE.is_open (infile)
   THEN
      UTL_FILE.Fclose (Infile);
   END IF;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      IF UTL_FILE.is_open (infile)
      THEN
         UTL_FILE.Fclose (Infile);
      END IF;

      IF total_rec_processed > 0
      THEN
         COMMIT;
      END IF;
   WHEN OTHERS
   THEN
      IF UTL_FILE.is_open (infile)
      THEN
         UTL_FILE.Fclose (Infile);
      END IF;

      o_msg := SQLERRM;
END;
/

The following is the package source of PLSQL_DELIMITED_STRING used in this example, install this package in your schema. The package contains many other utility functions which can be used to read the csv file using PLSQL:

CREATE OR REPLACE PACKAGE plsql_Delimited_String IS

    function  Counter(    Source_string     in varchar2,
                 UnTerminated    in Boolean  default FALSE,
                 Delimiter     in varchar2 default ',') return number;
    Procedure PutString(    Source_string     in out varchar2,
                 String_to_add     in varchar2,
                 Field_position     in number,
                 UnTerminated    in Boolean  default FALSE,
                 Delimiter     in varchar2 default ',');
    Procedure PutNumber(    Source_string     in out varchar2,
                 number_to_add     in number,
                 Field_position     in number,
                 UnTerminated    in Boolean  default FALSE,
                 Delimiter     in varchar2 default ',');
    Procedure PutDate(    Source_string     in out varchar2,
                 Date_to_add     in date,
                 Field_position     in number,
                 UnTerminated    in Boolean  default FALSE,
                 Delimiter     in varchar2 default ',');
    function  GetString(    Source_string    in varchar2,
                 Field_position     in number,
                 UnTerminated    in Boolean  default FALSE,
                 Delimiter     in varchar2 default ',') return varchar2;
    function  GetNumber(    Source_string    in varchar2,
                 Field_position in number,
                 UnTerminated    in Boolean  default FALSE,
                 Delimiter     in varchar2 default ',') return number;
    function  GetDate(    Source_string    in varchar2,
                 Field_position in number,
                 UnTerminated    in Boolean  default FALSE,
                 Delimiter     in varchar2 default ',') return date;
    function  Locate(    Source_string    in varchar2,
                 Search_String    in varchar2,
                 UnTerminated    in Boolean  default FALSE,
                 Delimiter    in varchar2 default ',') return number;
    function  Locate(    Source_string    in varchar2,
                 Search_date    in date,
                 UnTerminated    in Boolean  default FALSE,
                 Delimiter    in varchar2 default ',') return number;
    function  Locate(    Source_string    in varchar2,
                 Search_number    in number,
                 UnTerminated    in Boolean  default FALSE,
                 Delimiter    in varchar2 default ',') return number;
END plsql_Delimited_String;
/

CREATE OR REPLACE PACKAGE BODY plsql_Delimited_String
IS
   FUNCTION Counter (Source_string   IN VARCHAR2,
                     UnTerminated    IN BOOLEAN DEFAULT FALSE,
                     Delimiter       IN VARCHAR2 DEFAULT ',')
      RETURN NUMBER
   IS
      iModifier   PLS_INTEGER := 0;
      iOldSize    PLS_INTEGER := LENGTH (Source_string);
   BEGIN
      IF Unterminated
      THEN
         iModifier := 1;
      END IF;

      RETURN (iOldSize - LENGTH (REPLACE (Source_string, Delimiter)))
             + iModifier;
   END Counter;

   PROCEDURE PutString (Source_string    IN OUT VARCHAR2,
                        String_to_add    IN     VARCHAR2,
                        Field_position   IN     NUMBER,
                        UnTerminated     IN     BOOLEAN DEFAULT FALSE,
                        Delimiter        IN     VARCHAR2 DEFAULT ',')
   IS
      iStrLen       PLS_INTEGER := LENGTH (Source_String);
      iStrFragLen   PLS_INTEGER := LENGTH (String_to_add);
      iPtrStart     PLS_INTEGER := 0;
      iPtrEnd       PLS_INTEGER := 0;
      vcSepBuffer   VARCHAR2 (2000);
      iCounter      PLS_INTEGER;
   BEGIN
      -- 1. is the string Zero Length?
      IF iStrLen = 0
      THEN
         IF Field_Position > 1
         THEN
            FOR iCounter IN 2 .. Field_Position
            LOOP
               vcSepBuffer := vcSepBuffer || Delimiter;
            END LOOP;
         END IF;

         Source_string := vcSepBuffer || String_to_add;

         IF NOT UnTerminated
         THEN
            Source_string := Source_String || Delimiter;
            iStrLen := LENGTH (Source_string);
         END IF;
      ELSE
         -- 2. Cope with unterminated strings
         IF UnTerminated
         THEN
            Source_string := Source_string || Delimiter;
         END IF;

         -- 3. Locate the nth-1 separator
         IF Field_Position > 1
         THEN
            LOOP
               iPtrStart :=
                  (INSTR (Source_string || vcSepBuffer,
                          Delimiter,
                          1,
                          Field_Position - 1)
                   - 1)
                  + LENGTH (Delimiter);
               EXIT WHEN iPtrStart > 0;
               vcSepBuffer := vcSepBuffer || Delimiter;
            END LOOP;

            IF vcSepBuffer IS NOT NULL
            THEN
               iPtrEnd := iStrLen;
            ELSE
               iPtrEnd :=
                  INSTR (Source_string,
                         Delimiter,
                         1,
                         Field_Position);

               IF iPtrEnd = 0
               THEN
                  iPtrEnd := iStrLen;
               END IF;
            END IF;
         ELSE
            iPtrStart := 0;
            iPtrEnd :=
               INSTR (Source_string,
                      Delimiter,
                      1,
                      Field_Position);

            IF iPtrEnd = 0
            THEN
               iPtrEnd := iStrLen;
            END IF;
         END IF;

         -- 3. Rebuild the string
         Source_string :=
               SUBSTR (Source_string, 1, iPtrStart)
            || vcSepBuffer
            || String_to_add
            || Delimiter
            || SUBSTR (Source_string, iPtrEnd + LENGTH (Delimiter));

         -- 4. Sort out termination
         IF UnTerminated
         THEN
            Source_string :=
               SUBSTR (Source_String,
                       1,
                       (LENGTH (Source_string) - LENGTH (Delimiter)));
         END IF;
      END IF;
   END PutString;

   PROCEDURE PutNumber (Source_string    IN OUT VARCHAR2,
                        number_to_add    IN     NUMBER,
                        Field_position   IN     NUMBER,
                        UnTerminated     IN     BOOLEAN DEFAULT FALSE,
                        Delimiter        IN     VARCHAR2 DEFAULT ',')
   IS
   BEGIN
      -- 1. Just do datatype conversions and call the varchar2 varsion of put..
      plsql_Delimited_String.PutString (Source_string,
                                      TO_CHAR (number_to_add),
                                      Field_position,
                                      UnTerminated,
                                      Delimiter);
   END PutNumber;

   PROCEDURE PutDate (Source_string    IN OUT VARCHAR2,
                      Date_to_add      IN     DATE,
                      Field_position   IN     NUMBER,
                      UnTerminated     IN     BOOLEAN DEFAULT FALSE,
                      Delimiter        IN     VARCHAR2 DEFAULT ',')
   IS
   BEGIN
      -- 1. Just do datatype conversions and call the varchar2 varsion of put..
      plsql_Delimited_String.
       PutString (Source_string,
                  TO_CHAR (date_to_add, 'DD-MON-YYYY HH24:MI:SS'),
                  Field_position,
                  UnTerminated,
                  Delimiter);
   END PutDate;


   FUNCTION GetString (Source_string    IN VARCHAR2,
                       Field_position   IN NUMBER,
                       UnTerminated     IN BOOLEAN DEFAULT FALSE,
                       Delimiter        IN VARCHAR2 DEFAULT ',')
      RETURN VARCHAR2
   IS
      iPtrEnd           PLS_INTEGER := 0;
      iPtrStart         PLS_INTEGER := 0;
      vcSourceStrCopy   VARCHAR2 (2000) := Source_string;
   BEGIN
      IF UnTerminated
      THEN
         vcSourceStrCopy := vcSourceStrCopy || Delimiter;
      END IF;

      IF Field_Position > 1
      THEN
         iPtrStart :=
            INSTR (vcSourceStrCopy,
                   Delimiter,
                   1,
                   Field_Position - 1)
            + LENGTH (Delimiter);
      ELSE
         iPtrStart := 1;
      END IF;

      iPtrEnd :=
         INSTR (vcSourceStrCopy,
                Delimiter,
                1,
                Field_Position);
      RETURN SUBSTR (vcSourceStrCopy, iPtrStart, (iPtrEnd - iPtrStart));
   END GetString;                                         /* String Version */


   FUNCTION GetNumber (Source_string    IN VARCHAR2,
                       Field_position   IN NUMBER,
                       UnTerminated     IN BOOLEAN DEFAULT FALSE,
                       Delimiter        IN VARCHAR2 DEFAULT ',')
      RETURN NUMBER
   IS
      iRc   PLS_INTEGER;
   BEGIN
      RETURN TO_NUMBER (GetString (Source_string,
                                   Field_Position,
                                   UnTerminated,
                                   Delimiter));
   END GetNumber;                                         /* Number Version */

   FUNCTION GetDate (Source_string    IN VARCHAR2,
                     Field_position   IN NUMBER,
                     UnTerminated     IN BOOLEAN DEFAULT FALSE,
                     Delimiter        IN VARCHAR2 DEFAULT ',')
      RETURN DATE
   IS
   BEGIN
      RETURN TO_DATE (GetString (Source_string,
                                 Field_Position,
                                 UnTerminated,
                                 Delimiter), 'DD-MON-YYYY HH24:MI:SS');
   END GetDate;                                             /* Date Version */

   FUNCTION Locate (Source_string   IN VARCHAR2,
                    Search_String   IN VARCHAR2,
                    UnTerminated    IN BOOLEAN DEFAULT FALSE,
                    Delimiter       IN VARCHAR2 DEFAULT ',')
      RETURN NUMBER
   IS
      iHit        PLS_INTEGER := 0;
      iCounter    PLS_INTEGER;
      vcCompare   VARCHAR2 (2000);
   BEGIN
      FOR iCounter IN 1 .. Counter (Source_String, UnTerminated, Delimiter)
      LOOP
         IF GetString (Source_String,
                       iCounter,
                       UnTerminated,
                       Delimiter) = Search_String
         THEN
            iHit := iCounter;
            EXIT;
         END IF;
      END LOOP;

      RETURN iHit;
   END Locate;

   FUNCTION Locate (Source_string   IN VARCHAR2,
                    Search_date     IN DATE,
                    UnTerminated    IN BOOLEAN DEFAULT FALSE,
                    Delimiter       IN VARCHAR2 DEFAULT ',')
      RETURN NUMBER
   IS
   BEGIN
      RETURN Locate (Source_string,
                     TO_CHAR (Search_date, 'DD-MON-YYYY HH24:MI:SS'),
                     UnTerminated,
                     Delimiter);
   END Locate;                                              /* Date Version */

   FUNCTION Locate (Source_string   IN VARCHAR2,
                    Search_number   IN NUMBER,
                    UnTerminated    IN BOOLEAN DEFAULT FALSE,
                    Delimiter       IN VARCHAR2 DEFAULT ',')
      RETURN NUMBER
   IS
   BEGIN
      RETURN Locate (Source_string,
                     TO_CHAR (Search_number),
                     UnTerminated,
                     Delimiter);
   END Locate;                                            /* Number Version */
END;                                                                /* Body */
/




Suppose you have a requirement to round any decimal numeric value to nearest 0.25 in Oracle SQL, then you have to find some other way to round because in Oracle there is no such parameters in Round or Trunc function to round a value to nearest 0.25.

In my case, I was dealing with time data in 100 format mean 15 minutest is equal to 0.25 and there was a need to round that time to nearest 15 minutes, examples are given below:

0.01 to 0.25 = 0.25  (15 minutes)
0.26 to 0.50 = 0.50  (30 minutes)
0.51 to 0.75 = 0.75  (45 minutes)
0.76 to 1       = 1       (1 hours) 

You can complete this task by using following formula in SQL:

Select CEIL (0.01 / 0.25) * 0.25 Nearest_25 from dual;

NEAREST_25
----------
       .25

Select CEIL (0.26 / 0.25) * 0.25 Nearest_25 from dual;

NEAREST_25
----------
        .5

Select CEIL (0.51 / 0.25) * 0.25 Nearest_25 from dual;

NEAREST_25
----------
       .75

Select CEIL (0.76 / 0.25) * 0.25 Nearest_25 from dual;

NEAREST_25
----------
         1


More Topics

V. Kapoor

{picture#https://3.bp.blogspot.com/-q3wCssWUHo8/ViDFfCpPEuI/AAAAAAAAD0M/MHPvgnl3T3U/s1600/authpic.JPG} Vinish Kapoor is a Software Consultant, currently working in a reputed IT company in Noida, India and doing blogging on Oracle Forms, SQL and PLSQL topics. He is an author of an eBook Oracle Forms Recipes, which is available on Google play. {facebook#https://www.facebook.com/foxinfotech2014} {twitter#https://twitter.com/foxinfotech} {google#https://plus.google.com/u/0/+VKapoor/posts}
Fox Infotech. Powered by Blogger.