how to create control files

**amitpatel66** · Feb 6 '08, 12:20 PM

Originally posted by san1014

Hi
I am using oracle 9i
I want to import the excel sheet data into the database.

i have an excel sheet to import the data into the database in node table.
SQL> desc node
Name Type Nullable Default Comments
----------- ------------ -------- ------- --------
USER_ID NUMBER(6)
SCENARIO_ID NUMBER(9)
STATE_ID VARCHAR2(20)
DIST_ID VARCHAR2(20)
NODE_ID VARCHAR2(20)
NODE_NAME VARCHAR2(40)
NODE_TYPE VARCHAR2(25)

Please let me know how to create the control file?
and how to import that control file into DB using sql loader?

Any Help?
Thank you

Check here and here for detail

**subashsavji** · Feb 7 '08, 07:06 AM

Originally posted by san1014

Hi
I am using oracle 9i
I want to import the excel sheet data into the database.

i have an excel sheet to import the data into the database in node table.
SQL> desc node
Name Type Nullable Default Comments
----------- ------------ -------- ------- --------
USER_ID NUMBER(6)
SCENARIO_ID NUMBER(9)
STATE_ID VARCHAR2(20)
DIST_ID VARCHAR2(20)
NODE_ID VARCHAR2(20)
NODE_NAME VARCHAR2(40)
NODE_TYPE VARCHAR2(25)

Please let me know how to create the control file?
and how to import that control file into DB using sql loader?

Any Help?
Thank you

may be this will be helpful to you.

create table clobdemo
( id int primary key,
theclob clob
);
/

create or replace directory my_files as 'C:\';
/

create or replace procedure load_file
as
l_clob clob;
l_bfile bfile;
begin
insert into clobdemo values ( 1, empty_clob() )
returning theclob into l_clob;
l_bfile := bfilename( 'MY_FILES', 'yy.txt' );
dbms_lob.fileop en( l_bfile );
dbms_lob.loadfr omfile( l_clob, l_bfile,
dbms_lob.getlen gth( l_bfile ) );
dbms_lob.filecl ose( l_bfile );
end;
/

EXECUTE load_file
/
SELECT DBMS_LOB.SUBSTR (THECLOB,4000,1 ) FROM CLOBDEMO;
/

or use sql loader