?
Solved

Uploading files in Oracle

Posted on 2005-04-02
7
Medium Priority
?
1,260 Views
Last Modified: 2008-03-10
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
Comment
Question by:sindhuanand
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 48

Expert Comment

by:schwertner
ID: 13688383
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
 

Author Comment

by:sindhuanand
ID: 13690556
Can you quote me an example as to how to do it?
0
 
LVL 5

Expert Comment

by:helpneed
ID: 13695221
0
 
LVL 1

Expert Comment

by:radhika1306
ID: 13733224
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
 
LVL 48

Accepted Solution

by:
schwertner earned 200 total points
ID: 13733730
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to recover a database from a user managed backup
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question