i have a data of 100000 rows ina text file.
i want that data to be ported to an Oracle Table.
I guess there is some thing called utl_file package,but can some one please send me a sample code to do this job,
This procedure demonstrates many DBMS_LOB routines, including the BFILE manipulation routines
CREATE OR REPLACE PROCEDURE FileExec(
-- Executes the SQL statements in the file identified by
-- p_Directory and p_FileName. Each statement should not contain
-- the trailing semicolon (unless it is a PL/SQL block) and should
-- be separated by p_SeparationChar.
p_Directory IN VARCHAR2,
p_FileName IN VARCHAR2,
p_SeparationChar IN CHAR) AS
v_StartPoint INTEGER := 1;
-- Initialize the character locator for writing. Note that we have
-- to select a CLOB from a table FOR UPDATE. This locks the row,
-- and is a requirement for LOADFROMFILE.
WHERE key = -1
-- Initialize the BFILE locator for reading.
v_FileLocator := BFILENAME(p_Directory, p_FileName);
-- Set up the cursor.
v_SQLCursor := DBMS_SQL.OPEN_CURSOR;
-- Load the entire file into the character LOB.
-- This is necessary so that we have the data in
-- character rather than RAW variables.
-- Loop over the LOB, searching for each occurrence of
-- the separation character.
v_EndPoint := DBMS_LOB.INSTR(v_CLOBLocator, p_SeparationChar,
EXIT WHEN v_EndPoint = 0;
-- Extract the contents between the starting and ending points.
-- This is the SQL statement to be executed.
v_StatementLength := v_EndPoint - v_StartPoint;
v_SQLStatement := DBMS_LOB.SUBSTR(v_CLOBLocator,
-- Echo the statement to the screen, and then execute it
-- using DBMS_SQL.
DBMS_SQL.PARSE(v_SQLCursor, v_SQLStatement, DBMS_SQL.V7);
v_RC := DBMS_SQL.EXECUTE(v_SQLCursor);
-- Increment the statement pointer for the next statement.
v_StartPoint := v_EndPoint + 1;
-- Clean up.
WHEN OTHERS THEN
-- Close the cursor and file, and reraise.
i am sorry ,i think we got carried away,the data is in a Single text file i don have to work with with multiple text files,data from some data base is posted intoa text file,i want that data to be moved to oracle table,
in th ebaopve solution u were talking abouit clobs and nclobs i was bit confused,
plese help me out,
thanks in advance,
Click Here to Expand Forum to Full Width