Oracle Notes

  by Bruce Knox  bknox @t     Oracle Database Programmer/Analyst                                                                   date last updated 02/13/2014

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;


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" )
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>"
Response.Write "<br>...(Parm0) "&cmd.Parameters(0)&"&nbsp;&nbsp;*"
Response.Write "&nbsp;&nbsp;(Parm1) "&cmd.Parameters(1)&"&nbsp;="
Response.Write "&nbsp;(Parm2) "&cmd.Parameters(2)&"<br>"
Set cmd = Nothing
Set param = Nothing
Set cn = Nothing
Below is the Oracle code
create or replace PROCEDURE add2it(p1 in number,p2 in number,iResult Out number)
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
lcSql="BEGIN strongly_typed.add3it("+transform(lnAA)

? "The Sum of "+str(lnAA)+" * "+str(lnBB)+" = "+str(lnoSum)
Below is the Oracle which is inside a package called strongly_typed
PROCEDURE add3it(p1 in number,p2 in number,iResult Out number)
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 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.)


Name Null? Type
----------------------------------------- -------- ----------

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.


Created from common.


--{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.}



--{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
----------------------------------------- -------- ----------

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.

v_tablename varchar2(50);
v_columnname varchar2(50);
v_msg varchar2(2000);
v_msg2 varchar2(2000);
v_msg3 varchar2(2000);
r_cur x_cur%ROWTYPE;
CURSOR t_cur (v_name VARCHAR2) IS
r_fld t_cur%ROWTYPE;
OPEN x_cur;
fetch x_cur into r_cur;
exit when x_cur%NOTFOUND;
DBMS_OUTPUT.put_line(' v_t:'||v_tablename);
IF v_tablename='CADDY' THEN
open t_cur(v_tablename);
fetch t_cur into r_fld;
exit when t_cur%NOTFOUND;
IF v_msg2='N' then
v_msg:=CONCAT('ALTER TABLE ',v_tablename);
v_msg2:=CONCAT(' modify (',v_columnname);
v_msg:=CONCAT(v_msg3,' NULL)');
execute immediate v_msg;
close t_cur;

close x_cur;

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;
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"
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:

(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.

l_clob clob := empty_clob;
l_str varchar2 (32000);
dbms_lob.createTemporary( l_clob, true ); ( 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 );

see also:

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 

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=mysql&"CES.email_files(from_name=>'Becky Bridges <bbridges @t>',"
mysql=mysql&"to_names=>'Bruce Knox <bknox @t>',
mysql=mysql&"subject =>'BeckyNewSimple Text with No Attachments',"
mysql=mysql&"message =>'No files attached.');"

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>',"

3. Multiple "to" names can be included. Entire string must be included in single quotes:
'mysql=mysql&" to_names => 'bbridges @t,bsims @t',"

(If you want the recipiant to see their name instead of their email address, use
'mysql=mysql&" to_names => 'Becky Bridges <bbridges @t>,BeVerly Sims <bsims @t>',"

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)

(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:


from_name => 'Bruce Knox',
to_names => 'bknox @t',
subject => 'test time delay',
message => 'Test Oracle DBMS_LOCK.SLEEP with email_files.');
DBMS_OUTPUT.PUT_LINE ('Unknown error, details below');
DBMS_OUTPUT.PUT_LINE ('Error code: '||sqlcode);
DBMS_OUTPUT.PUT_LINE ('Error message: '||sqlerrm);

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';

                       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';

empcode NUMBER(4),
empname VARCHAR2(25),
deptname VARCHAR2(25),
hiredate date
DEFAULT DIRECTORY EXT2_TABLES                  -- see note below on case of the Directory Name
BADFILE 'emp_ext.bad'
LOGFILE 'emp_ext.log'
DISCARDFILE 'emp_ext.dsc'
LOCATION ('emp_ext.csv')

  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:

  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:

BADFILE 'emp_ext.bad'
LOGFILE 'emp_ext.log'
DISCARDFILE 'emp_ext.dsc'
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:
External Tables Concepts
External Tables Access Parameters

The example above is based upon one (stuff added) found in
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.Execute myTempSQL


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:



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:


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:

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"

'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),'&lt;');-- Less than
v_txtmsg_cl:=replace(v_txtmsg_cl,CHR(62),'&gt;');-- 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:




This is a special topic page maintained by Bruce Knox ( bknox @t ) documenting University of Arkansas, Division of Agriculture, Cooperative Extension Service IT projects. 

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. 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.)                                
View Bruce Knox's profile on LinkedIn