Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1277
  • Last Modified:

Uploading files in Oracle

Hi
I want to upload files (through Forms) from my machine(Windows 2000) to the Unix Server.This is where Oracle Database is.Is it possible?What are the commands to be used?
0
sindhuanand
Asked:
sindhuanand
1 Solution
 
schwertnerCommented:
In Forms you have to use the package TEXT_IO to read the data.
To store the files on the server use a stored procedure on the server (pass line by line the read file as parameter to the procedure) using the package UTL_FILE.
There are many examples here how to use this package.

Another idea is to store the file as BFILE from forms on the server. Use UTL_FILE to write the lines on the erver and DBMS_LOB to read the BFILE.

 
0
 
sindhuanandAuthor Commented:
Can you quote me an example as to how to do it?
0
 
helpneedCommented:
0
 
radhika1306Commented:
try to use the orale SQL* loader functionality
it's there in utilities for oracle 8i and 9i
it's a simple way to load files.
0
 
schwertnerCommented:
It depends on the task.
If the files are small use TEXT_IO.
If the files are big use SQL*Loader.
If the files are unstructured (pictures, Word or Excell docs) use LOBs.


Importing a Text File into a Block
==================================
Importing from a text file is similar to the above, however, you must  
parse the comma-delimited files and retrieve the data bit by bit. For  
that you use a helper function, TOKEN, show below.
 
          FUNCTION TOKEN (LINEBUF IN OUT VARCHAR2) RETURN VARCHAR2 IS
            /* Returns the first token from the comma-delimited line    */
            /* passed as linebuf.  Linebuf then has that token stripped */
            /* so that subsequent calls return the second, third, etc.  */
 
            FIRST_TOKEN   VARCHAR2(40);
            COMMA_POS     NUMBER;
          BEGIN
            COMMA_POS := INSTR(LINEBUF, ',');
            IF COMMA_POS = 0 THEN
              FIRST_TOKEN := LINEBUF;
              LINEBUF := null;
            ELSE
              FIRST_TOKEN := SUBSTR(LINEBUF, 1, COMMA_POS - 1);
              LINEBUF := SUBSTR(LINEBUF, COMMA_POS + 1);
            END IF;
            RETURN FIRST_TOKEN;
          END;
 
 
          PROCEDURE READ_DEPT_BLOCK (FILENAME IN VARCHAR2) IS
     R1     MYFILE   TEXT_IO.FILE_TYPE;  
     R1     LINEBUF  VARCHAR(255);                                        
          BEGIN
            GO_BLOCK('DEPT');
            CLEAR_BLOCK;
     R2     MYFILE := TEXT_IO.FOPEN(FILENAME, 'R');
            LOOP
     R3       TEXT_IO.GET_LINE(MYFILE, LINEBUF);
     R4       :DEPT.DEPTNO := TO_NUMBER(TOKEN(LINEBUF));
     R4       :DEPT.DNAME := TOKEN(LINEBUF);
     R4       :DEPT.LOC := TOKEN(LINEBUF);
              CREATE_RECORD;
            END LOOP;  
          EXCEPTION
     R5     WHEN NO_DATA_FOUND THEN
     R6       TEXT_IO.FCLOSE(MYFILE);
            END IF;
          END;
 
 
Importing a Text File into a Multi-Line Text Item
=================================================
The following procedure reads the contents of a plain text file into
a multi-line text item called Document.  The file is assumed to be  
composed of 80-character lines terminated by a new line character.
 
  PROCEDURE READ_INTO_MULTI (FILENAME IN VARCHAR2) IS
    MYFILE   TEXT_IO.FILE_TYPE;
    LINEBUF  VARCHAR2(80);
  BEGIN
    MYFILE := TEXT_IO.FOPEN(FILENAME, 'R');
    LOOP
      TEXT_IO.GET_LINE(MYFILE, LINEBUF);
      :DOCUMENT := :DOCUMENT || LINEBUF || CHR(10);
    END LOOP;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      TEXT_IO.FCLOSE(MYFILE);
  END;
 
The CHR(10) keeps the data in distinct lines in the multi-line output.  This
routine should be used only for small to medium documents (that is, less  
than 32K). For importing larger documents, you should use SQL*Loader or  
custom user exits.

0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now