Oracle provides very limited support for loading data from a file into a table especially on LINUX. The Oracle Loader accepts only files on a server disk and therefore cannot be used by normal users.
The package UTL_FILE can read and write files on external disks, but the directories must be set in the initialization file of the Oracle server. The server does not check the access rights to these directories. The lack of flexibitilty and the security problem was the motivation to write a C++ program (using the Oracle C/C++ precompiler for embedded SQL) for the read/write of files on client directories. It uses the ROOT interpreter as user interface (but does not use any HYDRA classes).
The program reads data organized column-wise and fills them into a new (temporary) or already existing table. One line in the file corresponds to one row in the table.
Inside the program, tables can be created and dropped again when not needed anymore. Filling the data into a temporary table has some advantages: The file might contain errors and might spoil data in an already existing table. Before using other tools (WEBDB, TOAD, SQLPLUS) the inserts must be committed and it might be difficult to delete them later again. Once the data are in Oracle one can use SQL to check and maybe manipulate the data before inserting them into the permanent table(s).
The program supports the insert of single rows 'by hand' and also the update of rows (in temporary tables only).
Loading data from a file is the main aspect of the program. But it can also write the content of tables or views to a client-side file. It provides support for conditions and ordering of the results.
This documentation shows mainly how to use the program on the ROOT interpreter. See the class documentation for a complete list of classes and their functions.
To start the program load the library libOraUtil.so into ROOT.
Then instantiate an object of type HDbFileIo and connect to Oracle (asks for the password!).
HDbFileIo io;
io.connectDb("ikoenig","db-hades.gsi.de");
or
io.connectDb("hades");
The (default) database "db-hades.gsi.de" is the actual HADES production database.
User "hades" is only allowed to read data, not to create a table or to insert data.
Users can only create new tables in their own account. The insert of data is allowed into all tables owned by this user and also into all tables, the user has the insert privilege granted for.
The connection to Oracle is closed by
io.closeDbConn();
To load data from a file into an Oracle table first open a file in i/o mode "IN".
io.openFile("/u/ilse/analtest/macros/OraProg/test.txt","IN");
To separate a line in the file into different columns, the delimiter between columns must be eventually set (default: white space) or the text-delimiter (default: single quote), which encapsulates in the file a text column containing internally column delimiters (see Parameters for I/O how to change the default settings).
A default table name is generated by concatenating "TMP_" and the filename, skipping the directory and the file extension (in this example "TMP_TEST").
If the function
io.readFile();
is directly called after opening a file, the table with this name will be filled.
Normally this table does not exist and must be created. The first line to be inserted
defines the number of columns. The column names are COL1, COL2, ... and the default type of all columns is VARCHAR2(80).
One can also create a new table specifying the name of the table, the column names and the column types individually. This is especially necessary when the new table should have more or less columns than the first line to be inserted or if a column must be longer than 80 characters.
HDbTable* table=io.setTableName("tmp_test1");
returns a pointer to a form (object of class HDbTable), where columns can be inserted. It checks also if the table with this name exits already. Suppose the table does exist, then there are two possibilities to set the columns and eventually their individual types:
In one step by the function void HDbTable::setColumns(const char* columnNames, const char* columnTypes=0).
The list of columns and types must be comma separated.
Example:
table->setColumns("abc, def, ghi, jlm, nop","char(10), number(2),
number(4,2), date, varchar(50)" );
When the types are ommited, the columns are added with the default column type,
which can also be changed from VARCHAR2(80) to an other type with the function HDbTable::setColDefaultType(TString&).
By multiple calls of the function HDbColumn* HDbTable::addColumn(const char* columnName,const char* columnType=0);
A column can be removed from the list with the function void HDbTable::removeColumn(const char*);
and all columns by void HDbTable::clearColumns();
To see the actual table definition call
table->show();
The table is created automatically, when the function HDbFileIo::readFile() is called. It first shows the actual table definition and asks for confirmation to create the table.
Then the lines in the file are read and the data inserted into the table:
The function Bool_t HDbTable::readFile(Int_t nLineStart=0,Int_t nLineEnd=999999) provides the possibility to define the number of the first and last line to be inserted (counting starts with 1). All other lines are skipped.
Lines starting with "/" are ignored.
All white spaces are skipped before insert (but no tab unless it is the column delimiter)
In lines containing more values than columns in the table, the remaining values are ignored without any warning.
In lines containing less values than columns, the remaining columns are filled automatically with NULL unless the flag autoNull was set explicitly to kFALSE before (in this case the program would return an error).
A value which should be inserted as a NULL value in the table, must be provided in the file as a unique null-string (default: "null") unless no other values follow in the line.
An empty string between two column delimiters other than white spaces (e.g.",,") is recognized as a null value.
After all lines have been read, the program shows the number of rows inserted into the table. The data are not yet committed (unless the flag autoCommit was set explicitly to kTRUE before). One can print the content of the table on the screen (or a file) before committing or rolling back the transaction.
When an error occurs during insert, the transaction is rolled back (and also all former ones which where not yet committed). But the table still exists.
Remark: Because Oracle launches an automatic commit when creating or dropping a table, you are asked to confirm a commit when you open a new file or drop a table. When you leave the program without committing before, you are also asked to do so.
To see the data in the table call
io.printResult();
In case the table is very long you might want to set a condition before printing, e.g.
HDbTable* table=io.getCurTable();
table->setCondition("rownum < 50");
io.printResult();
table->clearCondition();
Single rows can be inserted into the table, e.g.
io.makeInsert("*","'XYZ',11,1.1,'Some text'");
io.makeInsert("col1,col4","'XX','Some text'");
The wildcard "*" instead of the list of columns can be used when values are provided for all columns in the table (in correct order!).
One row or several rows can be updated in a temporary table e.g.
io.makeUpdate("col2=11, col3=1.1","col1='XYZ'")
The first parameter is the assign string for the columns, the second is the condition for the rows to be updated.
WARNING: With an empty condition or a condition which is always true, all rows in the table will be updated!
Tables created inside the program can be dropped before closing the actual Oracle session.
Once the connection is closed and opened again, this is not allowed anymore within this program. To show all created tables call
io.showTemporaryTables();
and to drop them
io.dropTemporaryTables();
or to drop a single table
io.dropTemporaryTable("tableName")
(Asks for confirmation before dropping a table.)
The file is not closed in the function readFile() to allow subsequent partial reads of the file without re-open. The file is automatically closed when one opens a new file or leaves the program, or one calls explicitly the function closeFile()
Inserting data into an existing table is not much different from inserting data into a new table.
One sets only the name of the table. The funtion HDbTable* HDbFileIo::setTableName(const char*) returns a pointer to a table form containing already all columns and their types of the existing table. In case the file contains the columns in a different order than in the table, one removes all columns and sets them again in the order of the file (or maybe only part of the columns inserting NULL values in the other columns).
Before the first insert, the SQL statement is shown and must be confirmed.
Example:
io.openFile("test1.txt","IN");
HDbTable* table=io.setTableName("tmp_test");
table->show();
table->clearColumns();
table->setColumns("col4, col3, col2, col1");
io.readFile();
Single inserts 'by hand' are possible, but no updates.
Set the table name (or owner.tablename).
HDbTable* table=io.setTableName("mdc_ana.mdc_loc_history");
table->show();
Optional are the specification of
the list of columns (only when not selecting all or in a different order)
table->clearColumns();
table->setColumns("sector, plane, mdc_hwobj_id, valid_since");
a condition
table->setCondition("exp_location_id='HADES_CAVE'");
order of the result rows
table->setOrderBy("sector, plane");
To see the result on the screen call
io.printResult();
or open a file and write the result to the file.
io.openFile("test3.txt","out");
io.writeFile();
The file can be opened in "out" or "app" mode. The program will not automatically overwrite an existing file opend in "out" mode, but will ask for confirmation to do so.
The following functions support also the query from views which require a date or run set in advance: HDbFileIo::setSearchDate(const char* time="sysdate")
HDbFileIo::setRun(const char* daqFile)
Examples:
HDbTable* table=io.setTableName("mdc_ana.mdc_loc_at_run");
io.setRun("n99_089.hld");
io.printResult();
The following parameters are used in the I/O program (class
HDbFileIo):
Delimiter for columns:
Default: white space
Changed by: HDbFileIo::setColumnDelimiter(const char* s=" ")
Text enclosed by:
Default: single quote "'"
Changed by: HDbFileIo::setTextDelimiter(const char s)
For input from file, character data and dates must be enclosed in the specified
character when the text contains the same character (string) as the column delimiter.
Example:
Column delimiter: " "
line in the file: ... '23-AUG-2000 12:00:00' 'This is a text'...
In the output by default all char, varchar2 and date values are enclosed by this
character to allow a read back into a table without any changes. When this is not
needed this text delimiter can be replaced by white space(s) for a nicer look.
Null (no value) in the I/O:
Default: "null"
Changed by: HDbFileIo::setNullValue(const char* s"null")
Automatic adding of nulls:
Default: yes
Changed by: HDbFileIo::setAutoNull(Bool_t b=kTRUE)
Automatic commit for inserts:
Default: no
Changed by: HDbFileIo::setAutoCommit(Bool_t b)
Maximum output buffer size (maximum length of an output line):
4000 bytes (cannot be changed)
(The input buffer size is limited by the buffersize for an SQL statement: ~32k)
Maximum length of a single column:
Default: 80 bytes (varchar2(80))
Changed by: HDbTable::setColDefaultType(TString&)
Maximum: 4000 bytes (varchar2(4000))
