|
Collected Notes and Tips on our
FoxPro-Oracle Conversions:
Tip #1 (Yvonne McCool)
check your SQL statements in your apps for the following problem:
select * from tablename(where condition) order by fields
that works fine when querying foxPro table but crashes when querying an
Oracle table
it should read
select * from tablename where condition order by fields
You can use parentheses since they make the query easier to read, but
it can't touch any word
select * from tablename where ( condition ) order by fields
Tip #2 (Yvonne McCool)
Look at your fields before you convert them from FoxPro to Oracle. If
your app allows them to be blank, you must mark the field as ALLOWS
NULL in the db because otherwise Oracle will mark it as NOT NULL.
Conversely, if you have a field that must be filled in your app, either
have a default value or mark the entry field so the user knows they
have to fill it in.
If you've already converted your file, all is not lost.
Go into SQLPlus and use the following format to update your existing
table:
alter table <table name> modify <column name> NULL;
example
alter table client modify zip4 NULL;
Tip #4 (Yvonne McCool)
My programs kept bombing out when trying to compare dates and
after many permutations, I got this to work:
mysql = "select * from filename where date_field >= to_date('" &
date_string1 & "','MM-DD-YY') and date_field <= to_date('" &
date_string2 & "','MM-DD-YY') order by field1,field2,field3"
Notice the single quotes before and after the date string, do not leave
them off.
Tip #5 (Steve Hall)
I am able to make an ASP page call an Oracle procedure
Here's the snippet of thr web page
==================================================
myDSN = "orcl_publications"
set cn = Server.CreateObject( "ADODB.Connection" )
cn.Open myDSN
set io_cursor = server.CreateObject ( "ADODB.Recordset" )
SQL="add2it"
set cmd = server.CreateObject ("ADODB.Command")
with cmd
set .ActiveConnection = cn
.CommandText = SQL
.CommandType = 4 'adCmdStoredProc
set param = .CreateParameter(,3,1) '<<adVarChar, 2=adParamInput
.Parameters.Append param
set param = .CreateParameter(,3,1) '<<adVarChar, 2=adParamInput
.Parameters.Append param
set param = .CreateParameter(lcOut,3,2) '<<adVarChar, 2=adParamOutput
.Parameters.Append param
end with
IF Err.number>0 Then
Response.Write "...Err.des "&Err.Description&"<br>"
Response.Write "...Err.number "&Err.number&"<br>"
END IF
cmd(0)=2
cmd(1)=8
Response.Write "<br>...(Parm0) "&cmd.Parameters(0)&" *"
Response.Write " (Parm1) "&cmd.Parameters(1)&" ="
cmd.execute
Response.Write " (Parm2) "&cmd.Parameters(2)&"<br>"
Set cmd = Nothing
Set param = Nothing
cn.close
Set cn = Nothing
================================================================
Below is the Oracle code
================================================================
create or replace PROCEDURE add2it(p1 in number,p2 in number,iResult
Out number)
IS
BEGIN
iResult := p1 * p2;
END add2it;
Tip #6 (Steve Hall)
Here's a snippet of code where a Foxpro pgm calls an oracle procedure
Foxpro pgm
====================================================
lnAA=2
lnBB=8
lnoSum=0
lcSql="BEGIN strongly_typed.add3it("+transform(lnAA)
lcSql=lcSql+","+transform(lnBB)+",iResult=>?@lnoSum);END;"
lnValue=SqlExec(lnSqlConnect,lcSql)
? "The Sum of "+str(lnAA)+" * "+str(lnBB)+" = "+str(lnoSum)
lnValue=SQLDISCONNECT(lnSqlConnect)
====================================================
Below is the Oracle which is inside a package called strongly_typed
====================================================
PROCEDURE add3it(p1 in number,p2 in number,iResult Out number)
IS
BEGIN
iResult := p1 * p2;
END add3it;
=====================================================
Tip #7 Order By on a character field
numerically (Becky)
When using the "ORDER BY" on a table, if the column happens to be a
number saved in a character field, use the to_number() function to
order by numerically instead of by alphabetically by character.
In this table, TOTSITES is a character field, but the value is always a
number, and I want the records sorted descending by number of totsites.
Select TYEAR, VARCODE, COMMONNAME, TOTSITES from cotvar.STEP3 order by
to_number(totsites) desc
Tip #8 Using a column alias (Becky)
A column alias needs to be enclosed in quotes, and cannot be used as a
GROUP BY parameter. To "GROUP BY" a column alias, the expression used
in creating the alias must be given:
MYSQL="SELECT (SUBSTR(scode,1,3)||experiment) as "&chr(34)&"newcol"&chr(34)&"
FROM cotvardata WHERE (tyear='2005') GROUP BY
(SUBSTR(scode,1,3)||experiment)"
Tip #9 GLOBAL TEMPORARY TABLE - Shared
Structure, but Private Records (Bruce and Becky)
These are notes collected for solving a problem in converting a
FoxPro program to use an Oracle backend database. For this
use, we needed a temporary table. One that would be empty when
we start, empty when we finish, and never used to share records with
other programs.
"Yvonne was kind enough to create a GLOBAL TEMPORARY TABLE for me.
I populated it through my ASP page using the INSERT command.
I changed he references in the code to the new table and/or
columns." Becky
There are TABLEs and GLOBAL TEMPORARY TABLEs in Oracle.
TABLEs are pretty much the same as a FoxPro table. You can
share data with other users or applications. Records can be DELETEd
or TRUNCATEd (only if you own the table).
DELETE FROM sometable;
TRUNCATE TABLE yourtable;
TRUNCATE is much more efficient but it is only available to the
Table Owner. With a TABLE, records remain (if COMMITed) until you
remove them.
GLOBAL TEMPORARY TABLEs are very special. These TABLEs will empty
when you exit. They will have zero records for any other user until
that user inserts records. Seems to work very well for creating
selection subsets. I am using this to limit the set of records to a
pre-selected set of keys, PIDMs in my case. The idea is to use it in
many scripts and I think it makes the reporting code much simpler. I
know it speeds up the process in tiaacref where I needed to check
the same View for the selected PIDMs for each of several output
records. The code to create the selected keys can be very complex or
very slow, but once I am SELECTing the keys, that code is very fast.
Code for using the TABLE and GLOBAL TEMPORARY TABLE is the same
except for the temporary and non-shared nature of the Records in the
GLOBAL TEMPORARY TABLE.
These are easier to use, in my opinion, than CURSORs, but note that
CURSORs are very commonly used in Oracle programming. The real plus
for CURSORs is that you create them on the fly in a very contained
(encapsulated) fashion. When your code goes away, so does the
CURSOR. With a Table, if you stop using it, it stays until you DROP
it. BTW, you do not want to DROP a TABLE to make it Temporary. You
just delete or truncate the records leaving the structure.
An Example:
Here is an example using the syntax for creating the GLOBAL
TEMPORARY TABLE. There is nothing to prevent having more Columns in
your Table, this single Column Table example is just a special
purpose Table.
BTW, the "ON COMMIT PRESERVE ROWS" sounds illogical to me, but this
does the trick. (The rows are definitely gone when you exit,
regardless of using a COMMIT.)
We create such objects in SQL*Plus as USER common and then provide
grants to another users that may need the object. (If you do not
have a common USERID, then bbridges will do fine for now.
Maintenance gets complex with many users creating objects.)
I created a GLOBAL TEMPORARY TABLE pidms.
DESC PIDMS
Name Null? Type
----------------------------------------- -------- ----------
PIDM NOT NULL NUMBER(8)
I am using this in tiaacref.sql and for other uses to limit the set
of records to a pre-selected set of PIDMs. Some programmers refer to
this concept as using a Driving Table, but the idea is to isolate
some of the more complex code, simplify other code, and to improve
Query performance.
The idea is to use it in many scripts and I think it makes the
reporting code much simpler. I know it speeds up the process in
tiaacref where I needed to check the same View repeatedly for the
set of PIDMs to get each of several output records.
The table will empty when I exit. It will have zero records for any
other user until they insert records. Seems to work very well. I
think I will be using this frequently.
Details:
Created from common.
CREATE GLOBAL TEMPORARY TABLE pidms
ON COMMIT PRESERVE ROWS
AS
SELECT SPRIDEN_PIDM PIDM FROM SPRIDEN WHERE ROWNUM < 2;
--{This just uses the structure from our ID Table to create this
nearly universal Banner key field. PIDMs are in many of the Banner
Tables.}
COMMIT;
DELETE FROM PIDMS
/
--{I had created one record in the Table, so I just removed it. It
would go away when I exited SQL*Plus without me deleting the record,
but I was experimenting.)
09:03:14 COMMON: ACES> desc pidms
Name Null? Type
----------------------------------------- -------- ----------
PIDM NOT NULL NUMBER(8)
grant SELECT,insert,update,delete on PIDMS to
bknox,srea,sbaker,nkhaidakova,mhubbell,kgrigsby,sfunderburg,lmeeks,dholzhauser;
create public synonym PIDMS for common.PIDMS;
--{If you do not grant privileges to other users, they cannot use
your Table.}
Tip #10 (Steve Hall) Mass
Converting Fields to NULLs.
My problem was I imported several
files to ORACLE. After doing that, I needed to change ALL of those
fields in EACH file to NULL.
The routine below reads ALL FILES in the database and converts the
fields to NULL. I left a segment in IF you only want to change
ONE FILE in the database to NULL.
DECLARE
v_tablename varchar2(50);
v_columnname varchar2(50);
v_msg varchar2(2000);
v_msg2 varchar2(2000);
v_msg3 varchar2(2000);
CURSOR x_cur IS
SELECT TABLE_NAME FROM USER_TABLES;
r_cur x_cur%ROWTYPE;
CURSOR t_cur (v_name VARCHAR2) IS
SELECT COLUMN_NAME,Nullable
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME=v_name;
r_fld t_cur%ROWTYPE;
BEGIN
OPEN x_cur;
LOOP
fetch x_cur into r_cur;
exit when x_cur%NOTFOUND;
v_tablename:=r_cur.Table_Name;
DBMS_OUTPUT.put_line(' v_t:'||v_tablename);
IF v_tablename='CADDY' THEN
v_msg:='abcd';
ELSE
open t_cur(v_tablename);
LOOP
fetch t_cur into r_fld;
exit when t_cur%NOTFOUND;
v_columnname:=r_fld.Column_Name;
v_msg2:=r_fld.Nullable;
IF v_msg2='N' then
v_msg:=CONCAT('ALTER TABLE ',v_tablename);
v_msg2:=CONCAT(' modify (',v_columnname);
v_msg3:=CONCAT(v_msg,v_msg2);
v_msg:=CONCAT(v_msg3,' NULL)');
DBMS_OUTPUT.put_line('_'||v_columnname);
execute immediate v_msg;
END IF;
END LOOP;
close t_cur;
END IF;
END LOOP;
close x_cur;
END;
Tip #11 (Steve Hall) List the
fields in a table alphabetically
-- Here's a snippet that list the fields in a table
alphabetically.
column type format a28 truncate;
select
column_name "Name",
decode(nullable, 'Y', '', 'N', 'NOT NULL', nullable) "Null?",
decode( data_type,
'CHAR', data_type || '(' || data_length || ')',
'NCHAR', data_type || '(' || data_length/2 || ')',
'VARCHAR2', data_type || '(' || data_length || ')',
'NVARCHAR2', data_type || '(' || data_length/2 || ')',
'NUMBER', data_type ||
decode( data_precision,
'', decode(data_scale, 0, '(38)', '', ''),
'(' || data_precision || decode(data_scale, 0, '', '', '', ',' ||
data_scale) || ')'
),
'FLOAT', data_type || decode(data_precision, '', '', '(' ||
data_precision
|| ')'),
'RAW', data_type || '(' || data_length || ')', data_type) "Type"
from
all_tab_columns
where
table_name = upper('&MYTABLE') order by column_name;
Tip #12
How do I get the DATE of MyTable
when it was updated?
Oracle does not track the time or date of record changes
automatically.
Add a Date COLUMN, such as Activity_Date to your Table.
INSERT SYSDATE INTO Activity_Date when the record is created.
Then,
UPDATE Activity_Date with SYSDATE as you change a record.
Note that an Oracle DATE is ALWAYS really Date and Time, although
one rarely displays the Time.
Tip#14-1 (Steve Hall)
Oracle Dates
Oracle Dates and Date Math are very powerful. The DATE Data
Type stores date and time in a very compact and complete form. You
can display them any way you need them to appear.
Getting a Julian Date/Number is pretty easy.
select to_char(sysdate,'J') from dual;
As is converting the Julian number to an Oracle Date:
select to_Date(trunc(2454228),'J') "Date for No" from dual;
We sometimes speak of the day of the year as being the Julian Date,
well not really, but there is also a Format for the Day of the Year,
e.g.: TO_CHAR(SYSDATE,'DDD')
There are some other handy DATE handling routines at:
http://www.uaex.edu/bknox/oracle_dates.htm
Tip#14 (Steve Rea and Steve Hall)
CLOB - an errant routine can lock up Oracle.
Using CLOB in a string concatenation (||) implicitly converts
the CLOB to VARCHAR2 on the right side of the assignment, which
eventually exceeds the 32K limit on strings in PL/SQL, and causes a
crash. It looks like you will need to use the DBMS_LOB package to
work with CLOB's, such as shown below - creating your strings to
append and using the writeappend function to append them to the CLOB.
declare
l_clob clob := empty_clob;
l_str varchar2 (32000);
begin
dbms_lob.createTemporary( l_clob, true );
dbms_lob.open ( l_clob, dbms_lob.lob_readwrite );
...
l_str := 'something1' || 'something2' || 'whatever else';
dbms_lob.writeappend ( l_clob, length (l_str), l_str );
...
dbms_lob.close (l_clob);
dbms_lob.freeTemporary ( l_clob );
...
end;
see also:
http://www.oracletips.guru/Huge_Strings_Using_LOBs.htm
should work!
Tip#15 (Steve Hall) Limit the
size of an input field
While you can left(trim(title),40)
so only the leftmost 40 characters, e.g., are stored in the Table,
that will not prevent the user from keying in more information (that
is then lost). Use:
INPUT type="text" maxlength="40"
Will limit the data entered on the web page to only 40 characters.
Tip#16 (Steve Hall and Bruce Knox)
Improve Oracle functionality using the Oracle ODBC Driver instead of
the Microsoft ODBC for Oracle Driver
When you add a DSN be sure you select the correct Oracle Driver.
The Oracle Driver can eliminate some errors you see when using the
MS Drivers. For example, use:
Our experience has been that the most current Oracle ODBC Drivers
are the best ones to use. They have maintained very good
backward compatibility with older versions of the Oracle Database.
We have seen no need to match an older ODBC version to the current
Database version.
A good source of the latest appropriate Oracle OLE DB Client version
can be found at
http://www.oracle.com/technology/software/tech/windows/ole_db/index.html
Tip#17 (Judi Shipps) Unique
"Key" Fields and Duplicate Records I
This code is useful when determining how to index or if to create a
primary key on tables. This SQL statement retrieves the field value
and a count of duplicate records for that field value:
select field, count(*) from table
group by field HAVING count(*) > 1;
Tip#18 (Steve Rea) e-Mailing
from Oracle
Steve has created CES.email_files, an Oracle Package, to make it
easier to use Oracle’s UTL_SMTP Package (which
can be used to create and send e-mails from PL/SQL routines).
see: E-Mailing
From Oracle which includes examples. This is the
easy way to use plain text messages, html messages, and attachments
of many types. It works well for simple text messages or
complex ones with various business documents or images as
attachments.
(And the non-DBA version of
Using Steve Rea's
CES.email_files Package.)
Tip#18-2 (Becky Bridges and Steve Hall)
using CES_email_files from .ASP
1. example:
SET DataConn7 = Server.CreateObject("ADODB.Connection")
DataConn7.Open "orcl_HayProducers"
mysql="BEGIN"
mysql=mysql&"CES.email_files(from_name=>'Becky Bridges <bbridges @t
uaex.edu>',"
mysql=mysql&"to_names=>'Bruce Knox <bknox @t uaex.edu>',
mysql=mysql&"subject =>'BeckyNewSimple Text with No Attachments',"
mysql=mysql&"message =>'No files attached.');"
mysql=mysql&"END;"
2. To avoid getting <> around the from_name or use an descriptive
name (i.e. "Arkansas Hay Producer's database") and to provide a
"reply to" email address:
mysql=mysql&" CES.email_files(from_name => 'AR Hay Producers <bbridges
@t uaex.edu>',"
3. Multiple "to" names can be included. Entire string must be
included in single quotes:
'mysql=mysql&" to_names => 'bbridges @t uaex.edu,bsims @t
uaex.edu',"
(If you want the recipiant to see their name instead of their email
address, use
'mysql=mysql&" to_names => 'Becky Bridges <bbridges @t uaex.edu>,BeVerly
Sims <bsims @t uaex.edu>',"
4. Multiple message lines can be included by appending the CR/LF at
the end:
mysql=mysql&" message => 'Animal Science AOS, "&chr(13)&chr(10)
mysql=mysql&"A new Hay Producer for Farm " & strFarmName & " is
awaiting your review."&chr(13)&chr(10)
Tip#19 (Bruce Knox) Slowing a
Flood of Generated email Messages
When we generate several hundred mail messages from a batch job,
it can severely impact GroupWise performance. One way to stem
the tide is to use Oracle's DBMS_LOCK.SLEEP( Number_of_seconds );
Here is a very simple example using Steve Rea's CES.email_files:
SET SERVEROUTPUT ON
EXECUTE DBMS_OUTPUT.ENABLE(1000000);
BEGIN
DBMS_LOCK.SLEEP( 1 );
CES.email_files(
from_name => 'Bruce Knox',
to_names => 'bknox @t uaex.edu',
subject => 'test time delay',
message => 'Test Oracle DBMS_LOCK.SLEEP with email_files.');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE ('Unknown error, details below');
DBMS_OUTPUT.PUT_LINE ('Error code: '||sqlcode);
DBMS_OUTPUT.PUT_LINE ('Error message: '||sqlerrm);
END;
/
Be warned that DBMS_LOCK.SLEEP apparently has rather
unpredictable results if used for longer than 10 minutes delays.
Also, the exact delay may be a bit more than specified.
Using DBMS_LOCK.SLEEP( 1 ) in a mass mailing to all employees
should spread the flood of messages enough to avoid impacting
GroupWise for simple messages. Perhaps longer would be
required if an attachment is large?
Tip#20 (Steve Hall, Steve Rea, Bruce
Knox) Using Oracle External Tables on a Windows platform: Excel CSV
to Oracle Table
External Tables use the ORACLE_LOADER to move between the OS file
and the Oracle Table. The ORACLE_LOADER is a fairly complete subset
of SQL*Loader.
There are lots of options and we are going to only cover the simple
case of moving from an Excel .csv file to Oracle. It is very easy
for an Excel user to save their worksheet as a “CSV (Comma
delimited) (*.csv)” file type.
These files are plain text files and can be viewed using NotePad as
well as opened directly by Excel. The CSV, Comma Separated Value,
file is one of the simplest to import into Oracle as well as being
the preferred way to export Oracle to Windows readable flat files.
Why Excel? It is an application that accountants and managers both
use. The most difficult part of getting data from an Excel user
might be getting them to use a Date format that you are expecting to
load. (You might need to remove commas and parentheses from numbers
too. Load it as VARCHAR2 and use REPLACE.)
You must find the real directory (Folder) that is allowed for Oracle
Directories.
You will likely need to ask you DBA for this info.
DBA Note:
To see which directories can be referenced in an Oracle database:
select value from v$parameter where name = 'utl_file_dir';
VALUE
------------------
c:\temp, d:\temp
Probably should leave c:\temp for Windows OS use.
Now, you could add directories by specifying them in an Oracle
parameter file referred to as the "init.ora" file.
To add other directories, the directory name would need to be added
to that init.ora file,
and the database
rebooted for it to take effect (since the init.ora
file is only
read during database startup).
(Read this as forget adding new
directories; use d:\temp.)
Whatever user ID you are trying to create directories in must have
that create privilege, which most do not.
When you run into an
insufficient privileges message,
just e-mail your DBA what you are trying to do and
what user ID you
are trying to do it in,
and to get it set that up for you. Then, the DBA can CREATE an
Oracle Directory for you to use. This is a logical
directory, not a real one in Windows.
We are going to use Oracle Directory EXT2_TABLES and Table EMP_EXT
for our example:
First time only, you want to do a 'Create Directory', then do a
'grant read, write'
(Since it is shared, you should never need to re-Create and grant
for this Directory.)
CREATE DIRECTORY ext2_tables AS 'd:\temp';
GRANT READ, WRITE ON DIRECTORY ext2_tables TO PUBLIC;
CREATE TABLE emp2_ext
(
empcode NUMBER(4),
empname VARCHAR2(25),
deptname VARCHAR2(25),
hiredate date
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY EXT2_TABLES
-- see note below on case of the Directory Name
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
BADFILE 'emp_ext.bad'
LOGFILE 'emp_ext.log'
DISCARDFILE 'emp_ext.dsc'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
MISSING FIELD VALUES ARE NULL
)
LOCATION ('emp_ext.csv')
)
REJECT LIMIT UNLIMITED;
note: for the
DEFAULT DIRECTORY we recently saw errors when using lowercase for a
Directory Name.
DEFAULT DIRECTORY "ext_soyva"
*
ERROR at line 23:
ORA-06564: object ext_soyva does not exist
which was resolved by using uppercase. Changing the statement
to:
DEFAULT DIRECTORY "EXT_SOYVA"
resolved the problem.
Move your emp_ext.csv file to d:\temp. That is right.
There is no Windows Folder for ext2_tables. It is only in
Oracle.
SELECT * FROM emp_ext; -- the file will be loaded to emp_ext by this
or another select.
BADFILE contains any records rejected by ORACLE_LOADER
DISCARDFILE contains any records ignored by the rules we specify
(you won't be seeing this file with our example). It is, I think,
easier to just load all the data into the External Table and select
out what you really need in the next step.
LOGFILE contains the Log of the load process and will include any
error messages.
These loader files should allow specifying the directory name;
however, this failed for us. Note also that with the file
names specified, the .log file will append each new runs messages to
the existing file. If you do not specify the .log file name,
then Oracle will create a .log file with the name of the Table (not
the LOCATION ( ) file name) with a sequence number such as:
EMP2_EXT_2632_708.log
If you can delete the log files, it will be easier to locate the
current log file using the named file option avoiding the
sequencing.
This load will work as long as the data is OK. The most frequent
problem is likely to be the Date format not being the system
default. A way around this is to specify the Date using something
like:
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
BADFILE 'emp_ext.bad'
LOGFILE 'emp_ext.log'
DISCARDFILE 'emp_ext.dsc'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
(
empcode NUMBER(4),
empname VARCHAR(25),
deptname VARCHAR(25),
hiredate CHAR DATE_FORMAT DATE MASK "mm/dd/yyyy",
)
)
LOCATION ('emp_ext.csv')
While this looks like we are specifying the Table definition twice,
actually this second spec is for the csv file rather than the Oracle
Table. The available data types in the csv file are not the
same as for Oracle Tables. These are available: char, date,
defaultif, decimal, double, float, integer, (, nullif, oracle_date,
oracle_number, position, raw, recnum, ), unsigned, varrawc, varchar,
varraw, varcharc, and zoned
REJECT ROWS WITH ALL NULL FIELDS will keep you from getting 65,000
"empty" rows loaded when a user sends you an entire worksheet
including the used rows.
Instead of SELECT *, you will normally want to move the loaded
External Table into a Internal Table, e.g.:
INSERT INTO emp_int (empcode,empname,deptname,hiredate) SELECT *
FROM emp_ext;
There is a lot more about External Tables at:
http://download.oracle.com/docs/cd/B10501_01/server.920/a96652/ch11.htm
External Tables Concepts
http://download.oracle.com/docs/cd/B10501_01/server.920/a96652/ch12.htm#SUTIL012
External Tables Access Parameters
The example above is based upon one (stuff added) found in
http://www.dbasupport.com/oracle/ora9i/External_Tables9i.shtml
External Tables in Oracle 9i by Ajay Gursahani, which was clear but
a bit too simple to actually run for us.
More notes:
You can, e.g., dummy the BADFILE and DISCARDFILE. I find these,
especially the BADFILE, useful, but if you never intend to use them
...
I use the existence of the BADFILE to generate email to myself so
that I know the process has failed. That way I get a head start on
my end users. I know the error and the fix usually before they
notice something has gone wrong.
You will want to remove the LOGFILE before running the next Table
Load.
Tip#20-1
(Becky Bridges) Using Oracle External Tables on a Windows
platform: An Example
I've been able to get the upload procedure to work for
Oracle. I told you I'd update you on what I did, so here it is as of
right now:
1. I asked Steve R to grant rights and give me a link to a
directory name on the server (d:\devapps\dcotvarSEC\websubmit) and
the link is CESDIR_DCOTVARSEC.
2. Using Tip #20 as a guide, I created an external table that
"links" to the uploaded file (this only needs to be done once). I
did this in SQLPLUS. For this to work, the uploaded file must be
uploaded as the same filename each time. I used the code for the
WEBSUBMIT program to actually upload the .csv file to a cotvar\WEBSUBMIT
folder on Jerry.
3. The file must then be inserted into an Oracle table. CREATE
TABLE COTVARUPLOAD1 ........... in sql plus. Insert records from .csv
file INSERT INTO COTVAR.COTVARDATA07 (TYEAR,SCODE,....) SELECT *
FROM ext_cotvardata
4. I then needed to convert a Foxpro program to oracle to
manipulate the new files into one file. I ended up doing this
through an .ASP page, using a series of .EXECUTE statements such as
myTempSQL = "Insert into varxtsitenew07 .............."
dbobj.Open
dbObj.Execute myTempSQL
dbObj.close
The file is created by selecting the button to call the .asp
page. I know it could have been done differently, but that's coming
down the road. Right now it works for me!
Tip#20-2 Oracle External Tables and Bad
Data.
Here is the case for loading data into VARCHAR2 rather than NUMBER
Columns:
External Table loads will not complete for bad data, things like
nonnumeric data for a NUMBER column. e.g., Oracle considers a
decimal to be nonnumeric if it has no numbers in the string.
Good data is great, but sometimes what is input is beyond our control.
For when you must clean up the data after it is loaded, you can use
the External Table to load the data into a column defined as
VARCHAR2 and then convert the data when it is inserted into the
Standard Oracle Table. Or, use SQL*Loader which allows the use of
Functions before the data is inserted into the Oracle Table.
You cannot use Functions until you have the data in Oracle with the
External Table (maybe with 10g or 11g, but not for 9i).
Here is an example where the External Table had HT defined as a
NUMBER.
HT CHAR (255)
Terminated by ","
Enclosed by """ and """
Trim whitespace same as SQL Loader
…
error processing column HT in row 227 for datafile data.csv
ORA-01722: invalid number
While it is telling you that you have CHAR (255) [the default since
it was not specified in the ACCESS PARAMETERS] coming in, it is
trying to stuff it immediately into a NUMBER and cannot do the
conversion. SQL*Loader will let you run functions on the incoming
data before loading it. So, that is an alternative.
If you want to keep the External Table for the load, then you would
need to change the definition of the columns to VARCHAR2(n) and use
a CASE or a DECODE to “edit” the data plus a TO_NUMBER to convert it
upon the INSERT into your standard Table. BTW, expect it to reject
any fields in the input that are longer than the destination column
(you could catch that in SQL*Loader too).
Why isn’t a decimal a number? Don’t know, but Oracle is consistent
about it:
Trying to do a TO_NUMBER(.), which will not work:
SELECT TO_NUMBER('1.0') FROM DUAL
/
TO_NUMBER('1.0')
----------------
1
SELECT TO_NUMBER('.0') FROM DUAL
/
TO_NUMBER('.0')
---------------
0
SELECT TO_NUMBER('.') FROM DUAL
*
ERROR at line 1:
ORA-01722: invalid number
Here is a work-a-round, but it only works if you have the data in
Oracle:
SELECT TO_NUMBER(DECODE('.',NULL,NULL)) FROM DUAL
/
TO_NUMBER(DECODE('.',NULL,NULL))
--------------------------------
Tip#21 (Bruce Knox) - Extract to CSV or
Comma Separated Values file for Excel
CSV or Comma Separated Values
files are frequently used to move between applications or platforms.
This variation of the Delimited Text File is very portable.
This flat file is often very busy, but readable with a simple editor.
The usual way is to always "optionally" enclose the values in double
quotes.
This prevents errors when embedded commas or newlines are unexpectedly
found in the data.
With these files, embedded double quotes must be quoted, i.e., doubled.
I suggest using REPLACE(value,'"','""') only when double quotes prove to
be a problem.
Unexpected Results in Excel?
By default Excel will display values with leading zeros stripped,
leading and trailing spaces stripped, and large numbers converted to
scientific notation.
These display format changes can be made to display properly by your Excel
users
and if the file is to be used with Excel only,
you can prevent them from appearing by
importing the values as an Excel Formula for text.
What could possibly be that large and still need to be read? A code or an
account number.
Excel ignores that you have defined a number as text by
enclosing the text string "number" with double quotes. A number is a
number to Excel.
If you do not want these Excel display conversions to appear,
then use a formula to convert the numbers to text:
use ="value" instead of "value"
Since Excel by default does not display the Formula, only the results,
then your text "numbers" will appear as intended.
This trick will also work for leading and trailing spaces (or unprintable
characters).
Retaining Formats when a .csv file is opened by Excel using an Excel
Formula probably will
cause other spreadsheet apps to fail.
By the way, when the spreadsheet generated is saved from Excel as .csv,
the optional double quotes and the Excel Formulas are dropped.
It will provide double quotes where needed.
You can concatenate the values exported with commas using
',' or CHR(44) and
'"' or CHR(34)
'"'|| is good for the start
||'","'|| is good for all but the first and last
||'"' is good for the last
||'",="'|| will turn the next value into a formula ="value"
for an example script see:
http://www.uaex.edu/bknox/extract_to_csv.sql
Tip#22 (Judi Shipps) - Foreign Key
Declarations
Foreign key declarations - here's how you tell Oracle that a
field is a foreign key.
Alter table tablename modify fieldname references
tablename2(fieldname2);
I created a new table APAC_CODES with CODE_KEY as the primary
key. This corresponded to the CODE field in the APAC table, but I
needed to declare it as a foreign key. The syntax was:
alter table apac modify code references apac_codes(code_key);
Tip#23 (Bruce Knox) - CASE and DECODE
DECODE and CASE are among the most useful SQL Commands.
DECODE is clear and concise for simple replacements. For
complex conditions and anytime you need a BETWEEN condition,
stick with the CASE statement.
CASE WHEN <conditions1> THEN <result1>
WHEN <conditions2> THEN
<result2>
WHEN <conditions3> THEN
<result3>
...
ELSE <result>
END "ColumnName"
DECODE(evaluating,
'if evaluating matches this', 'then use this value',
'if evaluating is this', 'then use this',
...
'none of the above then use this value') "ColumnName"
Tip#24 (Bruce Knox) - PL/SQL Update Logic Example
This example code clearly shows how to selectively
UPDATE one Table from another Table
or View.
Tip#25 (Steve Hall) - PL/SQL conversions for Microsoft
"smart" character set
Microsoft character set imported or cut and pasted into Oracle
generated mail, html, or xml is frequently a cause of errors.
Here are some useful translations in pl/sql:
v_txtmsg_cl:=replace(v_txtmsg_cl,CHR(147),'"');-- begin double quote
v_txtmsg_cl:=replace(v_txtmsg_cl,CHR(146),'''');-- single quote
v_txtmsg_cl:=replace(v_txtmsg_cl,CHR(148),'"');-- ending double
quote
v_txtmsg_cl:=replace(v_txtmsg_cl,CHR(150),'-'); -- Hyphen
v_txtmsg_cl:=replace(v_txtmsg_cl,CHR(60),'<');-- Less than
v_txtmsg_cl:=replace(v_txtmsg_cl,CHR(62),'>');-- Greater than
v_txtmsg_cl:=replace(v_txtmsg_cl,CHR(39),''''); -- Single quote
v_txtmsg_cl:=replace(v_txtmsg_cl,CHR(10),'<br>');-- Line return
here are some others that are frequently seen (or not seen, as the
case may be):
CHR(145) IS Smart Open Single Qoute
Replace(v_txtmsg_cl,Chr(145),Chr(39))
CHR(151) is Smart Long Hyphen Replace(v_txtmsg_cl,Chr(151),'--')
There are many others that may cause problems. The Oracle ASCII
function can be very useful in determining which unprintable
characters are causing the problem. A printable example:
> SELECT ASCII('"') FROM DUAL;
34
> SELECT CHR(34) FROM DUAL;
"
This is
a special topic page maintained by Bruce Knox ( bknox @t uaex.edu )
documenting University of Arkansas, Division of Agriculture,
Cooperative Extension Service IT projects.
Disclaimer
Use this information and these scripts at your own risk.
As a condition of using these scripts and information from this
site, you agree to hold harmless both the University of Arkansas
Cooperative Extension Service and Bruce Knox for any problems
that they may cause or other situations that may arise from
their use, and that neither the Extension Service nor I will be
held liable for those consequences. The scripts and information
are provided "as is" without warranty, implied or otherwise.
Limitation of liability will be the amount paid to the
University of Arkansas specifically for this information. (It
was free:)
Any University of Arkansas Division of Agriculture website
Disclaimer terms found in conflict with terms of this disclaimer
shall over ride and replace the conflicting terms found herein.
http://betwinx.com
is my personal site. betwinx?
This site is largely oriented to Oracle Reporting
Solutions in the context of generating bespoken
reports from Ellucian's Banner
product.
(I maintain a number of special topic pages with
links from this primary
site.)
|
|
|
