Release 3.00
Oct 16, 2006
|
Sybernet / Supplied Procedures Reference
Release 3.00 Oct 16, 2006 |
|
This procedure provides a convenient mechanism for uploading unix flat files into your Oracle database. These files can be column separated by any character (a tab or comma, for example), but must be terminated by a line-feed. You can request the data be trimmed or truncated before inserting it into your table. Any errors in your file (maybe because you are trying to insert a string field into a numeric field) are flagged with a full description of the problem. This includes its line number, column number, column name, data type, and the Oracle error that was produced. The upload routine also complains if your input file contained too few or too many fields.
When you submit your request, UPLOAD_TABLE dynamically creates a stored procedure tailored for your Oracle table. It then calls Sybercron to upload your unix flat file using that procedure. You can upload any unix flat file where Sybercron is running by passing the appropriate value for PLATFORM.
Procedure UPLOAD_TABLE
(
OWNER VARCHAR2 (IN)
, TABLE_NAME VARCHAR2 (IN)
, PATHNAME VARCHAR2 (IN)
, DELIMETER NUMBER (IN)
, TRIMMING PL/SQL BOOLEAN (IN)
, TRUNCATING PL/SQL BOOLEAN (IN)
, TRUNCATE_TABLE PL/SQL BOOLEAN (IN)
, VERBOSE PL/SQL BOOLEAN (IN)
, MAILFROM VARCHAR2 (IN)
, MAILTO VARCHAR2 (IN)
, MAILCC VARCHAR2 (IN)
, MAILBCC VARCHAR2 (IN)
, PLATFORM VARCHAR2 (IN)
, EPILOGUE VARCHAR2 (IN)
) ;
| Parameter | Description |
|---|---|
owner |
Table owner. |
table_name |
Table name. |
pathname |
Path and filename to import file. |
delimeter |
Ascii value that separates each column. A tab is the number 9. |
trimming |
Determines if fields should be trimmed before evaluation. |
truncating |
Determines if fields should be truncated before evaluation. |
truncate_table |
Determines if the table should be truncated before copying. |
verbose |
Displays each input line as it is being processed. |
mailfrom |
Your e-mail address. |
mailto |
The e-mail address where the results are sent. |
mailcc |
The e-mail address for any carbon-copies. |
mailcc |
The e-mail address for any blind carbon-copies. |
platform |
Platform where Sybercron is running from. Specify NULL to run from the default platform. |
epilogue |
An anonymous PL/SQL block you want to execute when the upload is completed.
If you just want to wait for this process to finish before you begin processing this data, your anonymous PL/SQL block could be "BEGIN NULL; END;" Without an anonymous PL/SQL block your process will continue without waiting for Sybercron to complete. |
Your column separator separates each column. To specify a NULL value in a comma separated file (for example) simply omit that value from your workfile. If you need to include the column separator as part of your text, the entire field should be quoted with single or double quotes. To specify Hunter,Ian (for example) in a comma separated field, you would write this as
"Hunter,Ian"
Microsoft Excel conforms to this convention when you save your Excel spread sheet as comma separated values (CSV). You may also need to set trimming to true because quoted strings that contain leading or trailing spaces are preserved. In other words, the quotes are not stripped if the field contains spaces before or after this quoted string.
Numeric values should look like numbers. Gratuitous commas are removed, but if you are inserting a numeric field into a numeric column, the number should look like a number.
Date fields should look like dates. Although the interpretation here is quite flexible, you may need to refer to the DATETIME function if you are having trouble loading dates and times.
The following example illustrates how to call upload_table:
begin
http.upload_table
(
owner => 'SARNOFF'
, table_name => 'SARNOFF_PHONE_BOOK'
, pathname => '/export/home/sarnoff/sarnoffpb.xls'
, delimeter => 9
, trimming => false
, truncating => false
, truncate_table => true
, verbose => false
, mailfrom => 'nospam@nospam.com'
, mailto => 'nospam@nospam.com'
, mailcc => null
, mailbcc => null
, platform => null
, epilogue => null
) ;
end;
The output resembles something like this. In this case, our input file contained column headings (which really aren't needed). These errors were expected so the upload was successful.
SARNOFF.SARNOFF_PHONE_BOOK (/EXPORT/HOME/SARNOFF/SARNOFFPB.XLS) LINE | COL | COLUMN_NAME | DATA_TYPE | VALUE | SQLERRM ------+-----+----------------------+------------------+----------------------+----------------------------------------------------------------------------- 00000 | 002 | FIRST_NAME | VARCHAR2(30) | NULL | ORA-01403: no data found 00000 | 003 | MIDDLE_NAME | VARCHAR2(30) | NULL | ORA-01403: no data found 00000 | 004 | EMPLID | NUMBER(5) | NULL | ORA-01403: no data found 00000 | 005 | WORK_PHONE | VARCHAR2(24) | NULL | ORA-01403: no data found 00000 | 006 | MAIL_DROP | VARCHAR2(50) | NULL | ORA-01403: no data found 00000 | 007 | DEPTID | VARCHAR2(10) | NULL | ORA-01403: no data found 00000 | 008 | EMAILID | VARCHAR2(70) | NULL | ORA-01403: no data found 00000 | 009 | BUSINESS_TITLE | VARCHAR2(30) | NULL | ORA-01403: no data found 00001 | 004 | EMPLID | NUMBER(5) | Employee# | ORA-06502: PL/SQL: numeric or value error: character to number conversion error Total rows: 492 (exported) Total rows: 490 (imported) Oops!