Programming

I'm having a oracle server and i'm having a table in that.
I'm having a linux server which is in network with the oracle server. I need to write a c program in linux env when on execution loads the table with the text file given as input.

Please explain me the flow of process in that and also tell me the logic of that c pgm and what can be used in c under unix so that we can connect and load oracle

I dont think u need the pgm flow....coz, its quite simple....but I can throw some light on how to connect to oracle from c program...and other statements.


1.Connect to Oracle
EXEC SQL CONNECT :<OracleUID>;

2. Insert into table.
EXEL SQL INSERT INTO <Tablename> ( field1, field2 , ...)
VALUES ( :var_field1, :var_field2 , ...);

3. Commit the work.
EXEC SQL COMMIT WORK;

4. To roll back the work.
EXEC SQL ROLLBACK;

5. Release Oracle connection.
EXEC SQL ROLLBACK WORK RELEASE;


And.....all exception conditions should also be handled after each EXEC SQL statement. (SQLERROR, NOTFOUND, NOORACLE etc)


Will that do?

Deepa

Thanks deepa whats my question here is will it compile in a ordinary c compiler for unix. Else what has to be done

W

You got to use the Pro-C compiler...

Is there Oracle Client loaded in your Linux machine? Is yes, then you can use the sqlplus or the sqlldr utility provided with Oracle. You can create .sql files on the fly in your C code, which basically have commands created like:
INSERT INTO TABLE_NAME VALUES(va11, val2);
...
INSERT INTO TABLE_NAME VALUES(va11, val2);
where val1 and val2 are values read from your input text file.
These .sql files can then be run from sqlplus, which can again be called from your C code.

Depending what flavor of UNIX you are using you need to declare this:

/* Include the SQL Communications area */

typedef char asciz[20];
exec sql include sqlca;

exec sql begin declare section;
exec sql type asciz is string(20) reference;
asciz usr_name;
asciz pwd;
asciz dbname;
exec sql end declare section;

/* End SQL Communications area */

Also here is a really good link:

http://www-rohan.sdsu.edu/doc/oracle...661_01/toc.htm

The sql loader(sqlldr) loads your data from a file(.dat or .sql) into the oracle table.

The ctl file is the control file that helps in loading the data from the flat file into the oracle table.

The contents of sample demo.ctl file is

load data
infile 'path name of unix server' //Here is where the flat file
//would be located
insert
into table test
(
name position(01:10) char(10),
)


The execution of this ctl file goes like this:

sqlldr username/password control=demo.ctl


-Nisha