Release 3.00
Oct 29, 2006
|
Sybernet / Supplied Procedures Reference
Release 3.00 Oct 29, 2006 |
|
This application provides a convenient mechanism for uploading files from a unix host or your PC into your Oracle database. These files can be column separated by a Comma, Tab or Tilde character, and each line 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, SP_HTML_UPLOAD_TABLE dynamically creates a stored procedure tailored for your Oracle table. You are then prompted for the file to upload and the options (such as the column separator) that are needed to process this file. To submit more than one file simply press the Back button on your browser and choose another filename on your PC.
When copying files from a unix host, your browser window remains busy while waiting for Sybercron to process your file. For large files you may prefer to run this offline using the stored procedure http.upload_table. The advantage here is you don't have to wait for Sybercron to finish. Instead, the results are e-mailed to you when it completes.
The first screen displayed is where you submit your request to dynamically build the stored procedure for uploading files to your table. You will specify the table owner and table name. You can also specfiy the name of the stored procedure that is created by this application. This procedure contains all the logic to upload flat files to your Oracle table. It is a real application that can be registered like any Sybernet application, should you desire.
By default SP_HTML_UPLOAD_TABLE will not truncate your table before submitting your file. If it is necessary to truncate your table, you can specify that the table is truncated once only (when you click the Continue button on this screen) because you want to upload several files, or you can specify that the table is truncated every time you Submit a file.
| Option | Description |
|---|---|
owner |
Table owner. |
table name |
Table name. |
procedure name |
The name of the procedure created by this application. |
truncate_table |
Determines whether or not you want to truncate your table. You can specify Never, After Continue, or After Submit. |
The second screen prompts you for the path and filename of the file to upload. To submit more than one file simply click the Back button on brower and submit each file separately. If you are submitting more than one file, make sure you don't do this with the truncate_table option set to true.
Your column separator (a tab or comma or tilde) 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.
| Option | Description |
|---|---|
delimiter |
Determines the column separator (Comma, Tab, or Tilde). |
trimming |
Determines if fields should be trimmed before evaluation. |
truncating |
Determines if fields should be truncated before evaluation. |
platform |
The unix hostname where this file resides or NULL to upload files from your PC. |
filename |
The path and filename of the file to upload. |
browse |
Use this button to search for input files on your PC. For unix files you can't use the browse button. Instead, you must enter its name in the filename field. |
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 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) Total rows: 490 (in table) Oops!
Because SP_HTML_UPLOAD_TABLE runs under the context of the current user, you can't grant execute to the casual user unless they have the permission to create any procedure and execute any procedure, something you probably don't allow. You can, however, create a wrapper around this procedure under the owner of this table and then grant execute to any user you wish to upload files to this table. The following example illustrates such a procedure:
create or replace procedure sp_html_upload_table_jacket(button in varchar2:=null)
is
begin
if (button is null) then
http.sp_html_frame
(
procname => http.myself.name
, title => http.myself.title
, version => http.myself.version
, center => '*'
, south => '60'
, border => '0'
) ;
return;
end if;
if (button = 'SOUTH') then
http.writeln('<html>');
http.sp_html_preferences;
http.sp_html_menubar(groupname=>http.myself.groupname);
http.writeln('</body>');
http.writeln('</html>');
return;
end if;
if (button = 'CENTER') then
http.sp_html_upload_table
(
button => 'REPORT' -- What to do (must be REPORT).
, owner => 'HTTP' -- Table Owner.
, table_name => 'BLAH' -- Table Name.
, procname => NULL -- Procedure Name.
, truncate_table => 1 -- 0=Never;1=After Continue;3=After Submit;
) ;
return;
end if;
return;
end sp_html_upload_table_jacket;
Typically you upload your flat files to staging tables so that the data can be manipulated by another process. To make this as seamless as possible you can pass an anonymous block to the parameter EPILOGUE. in the next example, we'll let the user decide if the uploaded data is ready for processing.
create or replace procedure sp_html_upload_table_jacket(button in varchar2:=null)
is
begin
if (button is null) then
http.sp_html_frame
(
procname => http.myself.name
, title => http.myself.title
, version => http.myself.version
, center => '*'
, south => '60'
, border => '0'
) ;
return;
end if;
if (button = 'SOUTH') then
http.writeln('<html>');
http.sp_html_preferences;
http.sp_html_menubar(groupname=>http.myself.groupname);
http.writeln('</body>');
http.writeln('</html>');
return;
end if;
if (button = 'CENTER') then
http.sp_html_upload_table
(
button => 'REPORT'
, owner => 'HTTP'
, table_name => 'BLAH'
, procname => NULL
, truncate_table => 1
, epilogue => 'begin http.sp_html_upload_table_jacket(button=>''EPILOGUE''); end;'
) ;
return;
end if;
if (button = 'EPILOGUE') then
http.sp_html_form(method=>'post');
http.sp_html_input(type=>'hidden',name=>'procedure',value=>http.myself.name);
http.sp_html_input(type=>'submit',name=>'button',value=>'PROCESS');
http.sp_html_form;
return;
end if;
if (button = 'PROCESS') then
/*
** Process the file here...
*/
return;
end if;
return;
end sp_html_upload_table_jacket;