Home » SQL & PL/SQL » SQL & PL/SQL » reading a file into the database
reading a file into the database [message #294716] Fri, 18 January 2008 10:23 Go to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
Hi all,

I am sorry for posting this question again even though 
several times on this post it has already answered.But,wana end my Friday in a Good mood.so,please any one explain me 

SQL> desc table1;
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------
 F1                                                 VARCHAR2(100)
 F2                                                 VARCHAR2(100)
 F3                                                 VARCHAR2(100)
and a procedure which reads the data from the file into this data_file.txt

[CODE]create or replace PROCEDURE load_example
   IS
      log    VARCHAR2 (100)      := 'username/password';
      v_file     UTL_FILE.file_type;
      err_file   UTL_FILE.file_type;                      
      v_dir      VARCHAR2 (50)      := '/c/';
      l_file     VARCHAR2 (100)     := 'data_file.txt';
      e_file     VARCHAR2 (100)     := 'err_file.txt';
      v_how      VARCHAR2 (1)       := 'r';
      v_text     VARCHAR2 (2000);
      v_size     NUMBER             := 32767;

      TYPE data_rec IS RECORD (
         field1         VARCHAR2 (10),          	    
         field2		VARCHAR2 (100),   	   
         field3		VARCHAR2 (100),              
         val		VARCHAR2 (100),
         rst		VARCHAR2 (2000),
         cnt		NUMBER
      );

      l          data_rec;
   BEGIN
      DBMS_OUTPUT.ENABLE (1000000);

      DBMS_APPLICATION_INFO.set_client_info ('1');

      v_file := UTL_FILE.fopen (v_dir, l_file, v_how, v_size);  
      err_file := UTL_FILE.fopen (v_dir, e_file, 'w', v_size);

      BEGIN
         LOOP
            l := NULL;
            l.cnt := 0;

            BEGIN
               UTL_FILE.get_line (v_file, v_text, v_size);
            EXCEPTION
               WHEN NO_DATA_FOUND
               THEN
                  EXIT;
            END;

            v_text :=
               REPLACE (REPLACE (REPLACE (v_text, CHR (13)), CHR (10)),
                        CHR (9)
                       );
            l.rst := v_text;

            WHILE LENGTH (l.rst) > 0
            LOOP
               BEGIN
                  l.cnt := l.cnt + 1;
                  l.val := SUBSTR (l.rst, 1, INSTR (l.rst, ',', 1, 1) - 1);
                  l.rst := SUBSTR (l.rst, INSTR (l.rst, ',', 1, 1) + 1);

                  IF l.cnt = 1
                  THEN
                     BEGIN
                        l.field1 := l.val;
                     EXCEPTION
                        WHEN OTHERS
                        THEN
                           UTL_FILE.put_line
                                            (err_file,
                                                'Err while processing field1: '
                                             || v_text
                                             || ':'
                                             || SQLERRM
                                            );
                           EXIT;
                     END;
                  ELSIF l.cnt = 2
                  THEN
                     BEGIN
                        l.field2 := l.val;
                     EXCEPTION
                        WHEN OTHERS
                        THEN
                           UTL_FILE.put_line
                                      (err_file,
                                          'Err while processing field2: '
                                       || v_text
                                       || ':'
                                       || SQLERRM
                                      );
                           EXIT;
                     END;
                  ELSIF l.cnt = 3
                  THEN
                     BEGIN
                        l.field3 := l.val;
                     EXCEPTION
                        WHEN OTHERS
                        THEN
                           UTL_FILE.put_line
                                           (err_file,
                                               'Err while processing field3: '
                                            || v_text
                                            || ':'
                                            || SQLERRM
                                           );
                           EXIT;
                     END;
                  END IF;

                  IF INSTR (l.rst, ',', 1, 1) = 0
                  THEN
                     BEGIN
                        l.field3 := l.rst;
                        EXIT;
                     EXCEPTION
                        WHEN OTHERS
                        THEN
                           UTL_FILE.put_line
                                           (err_file,
                                               'Err while processing field3: '
                                            || v_text
                                            || ':'
                                            || SQLERRM
                                           );
                           EXIT;
                     END;
                  END IF;
               EXCEPTION
                  WHEN OTHERS
                  THEN
                     UTL_FILE.put_line (err_file,
                                           'Err while processing line: '
                                        || v_text
                                        || ':'
                                        || SQLERRM
                                       );
                     EXIT;
               END;
            END LOOP;

            l.field1 := UPPER (LTRIM (RTRIM (l.field1)));
            l.field2 := UPPER (LTRIM (RTRIM (l.field2)));
            l.field3 := UPPER (LTRIM (RTRIM (l.field3)));


            BEGIN
               INSERT INTO table1 VALUES (l.field1, l.field2, l.field3 );
	     
            EXCEPTION
               WHEN OTHERS
               THEN
                  UTL_FILE.put_line (err_file,
                                        'Err while inserting/updating: '
                                     || l.field1
                                     || ':'
                                     || l.field2
                                     || ':'
                                     || l.field3
                                     || ':'
                                     || SQLERRM
                                    ); 
            END;
         END LOOP;

         COMMIT;
      EXCEPTION
         WHEN OTHERS
         THEN
            UTL_FILE.put_line (err_file, 'Err : ' || SQLERRM);
      END;

      UTL_FILE.fclose (v_file);                                  -- Close File
      UTL_FILE.fclose (err_file);
   EXCEPTION
      WHEN UTL_FILE.invalid_path
      THEN
         DBMS_OUTPUT.put_line ('Invalid Path');
         UTL_FILE.fclose (v_file);                              -- Close File
         UTL_FILE.fclose (err_file);
      WHEN UTL_FILE.invalid_mode
      THEN
         DBMS_OUTPUT.put_line ('Invalid Mode');
         UTL_FILE.fclose (v_file);                              -- Close File
         UTL_FILE.fclose (err_file);
      WHEN UTL_FILE.write_error
      THEN
         DBMS_OUTPUT.put_line ('Write Error');
         UTL_FILE.fclose (v_file);                              -- Close File
         UTL_FILE.fclose (err_file);
      WHEN UTL_FILE.invalid_operation
      THEN
         DBMS_OUTPUT.put_line ('Invalid Operation');
         UTL_FILE.fclose (v_file);                              -- Close File
         UTL_FILE.fclose (err_file);
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line (SQLCODE || ': ' || SQLERRM);
         UTL_FILE.fclose (v_file);                              -- Close File
         UTL_FILE.fclose (err_file);
   END load_example;[/CODE]

The above Procedure is successully compiled.

Questions: First of all how to set the v_dir ?
it can be any directory on the disk like C:\ where i stored this data_file.txt ?
2)Please let me know how can i execute this.
AS I said earlier this is not my requirement ,but i want to learn and end-up the week in high-end.

Thanks in advance. 
Re: reading a file into the database [message #294718 is a reply to message #294716] Fri, 18 January 2008 10:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I already said you in your previous post:
Quote:
Don't put the whole post inside code tags, just the code part. Also make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button to verify.

Edit your post and follow this.

Regards
Michel
Re: reading a file into the database [message #294719 is a reply to message #294716] Fri, 18 January 2008 10:46 Go to previous messageGo to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
Sorry again,

Created directory as 

sql>CREATE DIRECTORY BDUMP_DIR 
    AS '/c';
Directory created.

SQL> GRANT READ ON DIRECTORY BDUMP_DIR TO PUBLIC ;

Grant succeeded.

SQL>  @c:\utl_file.sql

Procedure created.

SQL> set serverout on
SQL> exec load_example;
Invalid Path
Invalid Path
Invalid Path
Invalid Path

Questions: 
1)First of all how to set the v_dir in the below procedure?
Is It can be any directory on the disk like C:\ where i stored this data_file.txt ?
If not please let me know how to do it?

[Updated on: Fri, 18 January 2008 11:01]

Report message to a moderator

Re: reading a file into the database [message #294721 is a reply to message #294719] Fri, 18 January 2008 11:07 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Sorry again,

Really? You still post the whole between code tags and not only the code part.

At least the followings are wrong.

v_dir VARCHAR2 (50) := '/c/';
It should contain the name of your Oracle directory (see below).

CREATE DIRECTORY BDUMP_DIR AS '/c';
Do you really have an OS directory named '/c'?

Regards
Michel

Previous Topic: Problem writing a sql query for a select list item in a form
Next Topic: Change CLOB column to VARCHAR2 data type
Goto Forum:
  


Current Time: Thu Dec 08 16:24:15 CST 2016

Total time taken to generate the page: 0.10440 seconds