I don't know about your response - I thought Terry was asking how to generate a comma delimited file - not import. In SQL*PLUS the simplest way might be:-

set head off pages 0 feedback off
SELECT forename ||','||
surname ||','||
address1 ||','||
address2 ||','||
etc ..
FROM x
WHERE y;

and spool this to wherever. Other alternatives could involve PL-SQL (DBMS_OUTPUT/UTL_FILE) which is not what was asked for

As I'm a newbie to "Ask Tom", I've probably misread the question - if so then apologies

Sorry Tom - you're right (don't know why I doubted you) I went back and re-read your answer and the penny dropped. The way you built the ytmpy.sql script was very inventive and will be sure to help me out in the future.

However, what if you don't want every column in the table or want to pull in data from a couple (or more) tables ? I take it you would just have to write an ad-hoc query in this case. Or do you have some more tricks up your sleeve?

Thanks for all your excellent responses and sorry for being a twat ( I can't help it that I'm from the shallow end of the gene pool !)

Hi Tom and Stuart,

I tried the ways you guys mentioned. Both of them worked very well. They are really helpful for my work.

But another problem is that I want to extract over ten million records from a table. Do you know which method I should use to get the higher speed, spool or DBMS_OUTPUT or UTL_FILE.

Thanks a lot for your time and response again.

Harry

I'm not sure what method would be quickest - if you choose spool (I would in your situation) and it's the whole table you want outputted then use Tom's utility - however I would modify it so that the ytmpy.sql script spools a file to a location & name of your choosing - also I would 'set termout off' for when ytmpy.sql is running - it should run faster without having to display all 10 million rows back to you - Hope this helps though it might be worth waiting for Tom's answer

Cheers

tom,

</code> http://asktom.oracle.com/~tkyte/flat/index.html <code>

the link is pointing to sqlloader

can you give the new link to see the scripts that load data from tables to flat files

Tom:

I have a unix shell script that calls sqlplus and runs a sql*plus script. The script spools data to a file and has a statement like select 'update table .....' from dual;

when i run the shell script it shows all the statements that are being spooled.

is there a way to hide the output in sql*plus while it is running select statements in the background?

Tom:

why does it still show the output?

SQL> set termout off
SQL> spool t1.txt
SQL> select 'update table x where .....' from dual;

'UPDATETABLEXWHERE.....'
--------------------------
update table x where .... 

From the "SQL*Plus User's Guide and Reference":
</code> http://download-west.oracle.com/docs/cd/B10501_01/server.920/a90842/toc.htm <code>

SET TERM[OUT] {ON|OFF}
SET TERMOUT is not supported in iSQL*Plus

Controls the display of output generated by commands executed from a script. OFF suppresses the display so that you can spool output from a script without seeing the output on the screen. ON displays the output. TERMOUT OFF does not affect output from commands you enter interactively.


Huh. Right there it is. Who knew they documented this stuff?

- Mark

Tom,

The Question from Harry was like this..
He wants to spool the data from a table into a file but without spaces and if the column is NULL the the output should be just like ",,".

I don't know whether I did not undertand you reply.

Can you please explain again how to spool like this.

Harry,Jia,410 York Mills Road,,M3A1B9,ON,CA
Daniel,Xiang,250 Yonge Street,18 Floor,M3B1W9,ON,CA

Tom, Oracle is not set up properly on this QA box, is it?

First I get this:

asdprod1: /home/rchin > sqlplus
ksh: sqlplus: not found.

so I cd to the bin directory, now I get this:

asdprod1: /u01/app/oracle/product/8.1.7/bin > sqlplus
Message file sp1<lang>.msb not found
Error 6 initializing SQL*Plus

But the .msb files are there:

dns: /u01/app/oracle/product/8.1.7/sqlplus/mesg > ls -f
. cpyus.msb sp2us.msb sp1us.msg
.. sp1us.msb cpyus.msg sp2us.msg

What gives and what the heck are these files for anyway ?

Thanks

Robert,

I'm not Tom, but while you're waiting try:

echo $ORACLE_HOME and if it is not set, you should set it to the correct location. ie, export ORACLE_HOME=/u01/app/oracle/product/8.1.7

On a UNIX box, I would typically set:
ORACLE_BASE
ORACLE_HOME
ORAENV_ASK
ORACLE_SID
TNS_ADMIN

and call $ORACLE_HOME/bin/oraenv (for your ksh environment).

These are all documented here:
</code> http://download-west.oracle.com/docs/html/A97297_01/ch1_admin.htm#sthref27 <code>

The .msb files are the "message binary" files and contain the message resources used by Oracle.

Hope that helps, and pardon the interruption...

- Mark

Stuart, you dont need all your ||','|| in your SQPLUS script. Just use the handy set colsep ',' command

Tom,

When I spool my query results to a file, it appends spaces at the end of the line based on the linesize. Is there a way to truncate those trailing spaces ?

Thanks much.

damn .. how couldn't I find the command :(

Now 1 more question :)

I am generating a comma separated file with around 45 columns using SQL*Plus and ||','||. Sorry, since I am on windoze 2000 & dont have access to the unix box, cant use your famous (and my favourite) dump_csv function. So to get the proper output, I have set heading off and pagesize 0. But to get the heading on 1st line, I have done something like this :

select 'empno,ename,deptno,sal,hiredate'
from dual
union
select empno||','||ename||','||deptno||','||sal||','||hiredate
from emp
order by 1 desc
/

I was forced to use "order by 1 desc" because the first column being numeric, the heading is displayed at the bottom. And there are almost 127,000 rows in the output and I want to avoid using "order by" -- for performance and the empno are generated in reverse order.

So is it possible to get the heading and empno in ascending order ?

Thanks again.

Can I give you more than 5 stars ??? :-) So simple but works like a charm !!

Thanks a lot Tom !!!

Hi Tom,

It's possible to get such a string using a for-loop. ie

for rec in (select col from a_table) loop
v_str := v_str || ',' || rec.col;
end loop;

But is it possible to make that using a single query ?

Tom, please dont give me the method now, if it is possible. Let me try to find it by myself. But I would like to know if it is possible indeed (using a single query) in Oracle.

Thanks for your answer

Praveen

Hi Tom,

1)I was unable to find the answer by myself, hence reffered to your answer (to a similar question) at

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:8682544392674

and made a query as below. It gives me unexpected result when EMPLOYEE table contains less than 5 records.

SELECT E1 || ',' || E2 || ',' || E3 || ',' || E4 || ',' || E5 ECODE_SUM 
FROM (
    SELECT MAX(DECODE(R, 1, ECODE)) E1,
           MAX(DECODE(R, 2, ECODE)) E2,
            MAX(DECODE(R, 3, ECODE)) E3,
            MAX(DECODE(R, 1, ECODE)) E4,
            MAX(DECODE(R, 1, ECODE)) E5
    FROM (SELECT ROWNUM R, ECODE FROM EMPLOYEE ORDER BY ROWNUM)
    WHERE R < 6
     )

2) As per your suggesion about user-defined aggregate I found the following link, but the method given there is 
infact using procedural statements, rather than single SQL queries. (but that was an excellent feature of oracle and a brilliant explanation by you, indeed).

http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:2196162600402 <code>

Does this mean that using a single SQL query, we cannot combine the rows into a single row, unless we know the maximum number of rows in that table and the table should always contain that many number of records ? (I accept the constraint of VARCHAR2(4000) bytes.)

Thanks for your answer

warm regards

Praveen

Hi Tom,

I need create a flat file on parent-child tables with the following format

parent record 1
child record1_1
child record1_2
...
child record1_n
parent record 2
child record2_1
child record2_2
...
child record2_n
...
parent record m
child recordm_1
child recordm_2
...
child recordm_n


can I do it by a sql instead of using nest loop in PL/SQL?

Thanks a lot !

Steve

Hi Tom,

Sorry I did not express my question clearly.

I have 2 table

orders (order_id, order_name, total_order_items, ...)

and order_items (item_id, order_id, item_name, item_price,...)

and create a report like
order record 1
item record1_1
item record1_2
...
item record1_n
order record 2
item record2_1
item record2_2
...
item record2_n
...
order record m
item recordm_1
item recordm_2
...
item recordm_n


Thanks!


Steve

Dear tom;
the above link is not found, can you send onther one?

All the methods detailed here are great but how do I modify Tom's tab delimited format to CSV?

I've got a quick dirty hack piping it into tr. <Tom's script> | tr '\t' ','

And I've also written:

set feedback off
set linesize 32767
set null '<NULL>'
set recsep off
set tab off
set termout off
set trimout off
set trimspool on
set verify off
set wrap off
set colsep ','
set heading off
set pagesize 0
spool S_CONTACT.dump
select *
from S_CONTACT;
spool off
exit

This nearly works except I've got trailing white spaces after each field like:

xxx ,xxx , ...

How do I cull thsi white space?

Hi Tom,

I have a shell script as below in which I wish to suppress the SQL output data. I have set the TERMOUT OFF but it still displays the data. Please can you tell me why?

-- **************************************
#!/bin/ksh
function print_data
{
sqlplus -s <uid>/<pwd> << ENDSQL
set termout off
set echo off
set feed off
set head off
spool 1.lst
select 1 from dual;
spool off
ENDSQL
}
echo "Starting .."
print_data
echo "Ending .."
-- ************************************

All I need is the statements "Starting" and "Ending" with the data in the 1.lst file.

Thanks

Sorry Tom, I don't think I did ask clearly what I wanted ..
All I want on the terminal is:

Starting...
Ending...

The data should go to the file 1.lst i,e the file will contain just "1" in it and nothing else.

I want to do this because I just wanted the debug messages to be echoed and the SQL output if any should go in a file. The SQL output should not be displayed on the terminal. I hope I am clear now.

Thanks Tom.. That does the job!

Hi
I need to use the environment variable in sql*PLUS.
Please not that I do not want to pass variables to sql*plus but use the environment variables from within sql*plus.

This I have achieved in UNIX environment.

UNIX
-----
USER_1  is the UNIX environment variable.

export USER_1="'ANONYMOUS','AUDIT','SYS','SCOTT'"

Inside sql*plus
SQL>SELECT  *  FROM dba_users WHERE (username NOT IN (${USER_1})
The above statement works fine in UNIX environment.

DOS
----
USER_1  is the DOS environment variable.

set  USER_1="'ANONYMOUS','AUDIT','SYS','SCOTT'"

how do I use USER_1 (DOS Environment variable) in SQL*PLUS  as I have done in the UNIX Box. 

In unix you were passing them in.

You did not interactively go into sqlplus and type:

 
ops$tkyte@ORA10G> !echo $UID
500
 
ops$tkyte@ORA10G> select ${UID} from dual;
select ${UID} from dual
       *
ERROR at line 1:
ORA-00911: invalid character


You have a shell script like this:

[tkyte@desktop tkyte]$ ./test.sh
 
SQL*Plus: Release 10.1.0.4.0 - Production on Fri Jun 3 07:29:03 2005
 
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - Production
With the Partitioning, OLAP and Data Mining options
 
ops$tkyte@ORA10G>
       500
----------
       500
 
ops$tkyte@ORA10G> Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - Production
With the Partitioning, OLAP and Data Mining options
[tkyte@desktop tkyte]$


<b>and that shell script did all of the "work"</b>

You'll find scripting in windows to be "less than easy" without a unix like shell to script with.

I always 'echoed' the script to run line by line out to a temp file in windows, then run that.


 

I'm completely new to this datawarehouse programming. will you please explain me in bit more elaborated manner.

Question:

Can we dump the oracle data along with the path and file name into temp table along with total no of records, displayed in seperate column, it should work as tool who ever call this procedure in their program.
Thanks

Hi :

I am trying to run a job from OEM for a 10g database to just "select * from dual". The job fails with the error

Error 6 initializing SQL*Plus
Message file sp1<lang>.msb not found
SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory

But if I just copy and paste the command to the command-line in Unix it executes successfully. Please advise on what the problem cud be.

I also checked the environment settings and everything appears fine.

ORACLE_HOME=/orahome/OraHome92
PATH=/FACET/prod/java/bin:/ora.dump/backup/local/bin:/bin:/usr/bin:/usr/ucb:/etc:/usr/ccs/bin:/usr/sbin:/usr/openwin/bin:/orahome/oms10g/dcm/bin:/usr/dt/bin:/usr/lpp/X11/bin:/orahome/OCS-12_5/bin:/orahome/ASE-12_5/bin:/orahome/REP-12_5/bin:/orahome/CFG-1_0:/orahome/dba_admin:/orahome/ASE-12_5/install:/orahome/OraHome92/bin:.
TNS_ADMIN=/orahome/OraHomeDefault/network/admin

Thanks.

Tom,
Need your expert advice on this situation...I have to create a flat file extract..I have the sql in the script and i am spooling it....The whole thing runs from a unix script which gets reported back to a Scheduling tool we have..The problem i am having is when a error gets raised in the sql file..its not raising it back from sql plus...

I have attached a portion of the script below
...The process checks the spoolfile for errors and raises an error based on the job_status..
say for instance
TEST.SQL has the following sql
select value from dual;

the above script when run raises invalid identifier..but thats not getting raised...
I tried set termout off...but then it dumps the sql o/p in the flat file which i dont want..any ideas??

------------------
spoolfile=$LOG_DIR/tmp_spool$$.lst

$SQLPLUS -s /NOLOG <<END_SQL 2>&1 | $LOG
# GETS THE CONNECTION STRING
CONNECT $CONNECTSTR
SPOOL $spoolfile
SET FEEDBACK OFF
SET SERVEROUTPUT ON
SET TRIMSPOOL ON
SET TERMOUT OFF
SET HEADING OFF
SET PAGESIZE 0
SET LINESIZE 700
WHENEVER OSERROR EXIT FAILURE ROLLBACK
WHENEVER SQLERROR EXIT FAILURE ROLLBACK

COLUMN fw new_val fw1 noprint

SELECT
'/ads/odm/file'||fyweek||'.txt' fw
FROM
ods.calendar_dim
WHERE calendat_date = TO_CHAR(SYSDATE,'YYYYMMDD') ;

SET feedback OFF
SET echo OFF
SET heading OFF
SET verify OFF
SET TERMOUT OFF


spool &fw1

@/ads/odm/TEST.SQL

spool OFF

END_SQL
job_status=$?

ora_error=$( egrep -i "ERROR|ORA-|SP2" $spoolfile 2>/dev/NULL | SORT -u )

# CHECK EXIT status OF sqlplus, IF nonzero ora_error string
IF [[ $job_status -ne 0 || -n $ora_error ]]; THEN
echo " Job Status = [$job_status]" | $LOG
echo " Spool file errors" | $LOG
echo " ORACLE Errors" | $LOG
exit_error "Encountered during SQL*Plus"
ELSE
echo "SQL*Plus Completed" | $LOG
fi

tom..we are using Korn Shell...

#!/bin/ksh
# parameters
typeset -x ORA_USER=hnad
typeset -x ORACLE_APP=ods
typeset -x BASE_DIR=${0%%/bin*}
typeset -x LOG_DIR=$BASE_DIR/log
typeset -x ARC_DIR=$BASE_DIR/arc
typeset -x BIN_DIR=$BASE_DIR/bin
typeset -x TMP_DIR=$BASE_DIR/tmp
typeset -x DAT_DIR=$BASE_DIR/dat
typeset -x CTL_DIR=$BASE_DIR/ctl
typeset -x BAD_DIR=$BASE_DIR/bad
typeset -x script_name=${0##*/}
typeset LOGFILE=$LOG_DIR/${script_name%%\.*}.log.$(date +'%y%m%d%H%M')
typeset log="tee -a $LOGFILE"
typeset -ix TRUE=0
typeset -ix FALSE=1
umask 022
function exit_error {
echo "ERROR $1" | $log
echo "[${script_name}] FATAL ERROR $(date)" | $log
echo "See $LOGFILE for details" | $log
exit 1
}

function oracle_is_ok {
if print "connect $connectstr\n select dummy||'OK' from dual;" | \
$SQLPLUS -s /NOLOG | egrep -q XOK
then return ${TRUE}
fi
return ${FALSE}
}

function check_base {
if [[ ! -d $BASE_DIR ]]; then
echo "ERROR: Cannot determine base directory for [${1}]"
echo "You must invoke the script using the full path."
exit 99
fi
}

function check_log {
if [[ ! -d $LOG_DIR ]]; then
echo "[$script_name] Creating log directory in $BASE_DIR"
mkdir -m 777 $LOG_DIR
if [[ $? -ne 0 ]]; then
echo "[$script_name] **PERMISSIONS ERROR** can't create \$LOG_DIR"
echo $(ls -ld $BASE_DIR)
exit 99
fi
fi

# log dir exists, now see if we can create a log file
touch $LOGFILE
if [[ $? -ne 0 ]]; then
echo "[$script_name] **ERROR** Can't create \$LOGFILE"
exit 99
fi
}

function initialize_oracle {
# get dbconnect string
export connect_str=$( /dsg/bin/dbconnect.ksh $ORACLE_APP $ORA_USER )

testDB=$(echo $connect_str | \
egrep '^[a-zA-Z]+[a-zA-Z0-9]*\/[a-zA-Z0-9]+\@[a-zA-Z0-9]+$')
if [[ -z $testDB ]]; then
echo $connect_str # this will be an error string
exit_error "Can't set Oracle \$connect_str - check c-code directory."
fi


export ORACLE_SID= hump
export ORAENV_ASK=NO
echo "Defining \$ORACLE_HOME with .oraenv" | $log
. oraenv
if [[ $? -ne 0 ]]; then
exit_error "Line: $LINENO, Can't set Oracle Environment with oraenv"
fi

export SQLPLUS=$ORACLE_HOME/bin/sqlplus
export SQLLDR=$ORACLE_HOME/bin/sqlldr
export TNSPING=$ORACLE_HOME/bin/tnsping
export ORACLE_SID=${connect_str#*/*@}

echo "Testing Oracle connection using \$connect_str" | $log
if oracle_is_ok ; then
echo "Connection to Oracle OK, SID=$ORACLE_SID" | $log
else
echo "ORACLE CONNECTION ERROR" | $log
echo "Check SID status: $TNSPING $ORACLE_SIDg" | $log
echo
$TNSPING $ORACLE_SID | egrep -i "TNS-" | $log # will show TNS error
exit_error "connecting to Oracle, user@sid=${ORA_USER}@${ORACLE_SID}"
fi

}

check_base ${0}
check_log


echo "Begin SQL*Plus Processing" | $log

spoolfile=$LOG_DIR/tmp_spool$$.lst

spoolfile=$LOG_DIR/tmp_spool$$.lst

$SQLPLUS -s /NOLOG <<END_SQL 2>&1 | $LOG
# GETS THE CONNECTION STRING
CONNECT $CONNECTSTR
SPOOL $spoolfile
SET FEEDBACK OFF
SET SERVEROUTPUT ON
SET TRIMSPOOL ON
SET TERMOUT OFF
SET HEADING OFF
SET PAGESIZE 0
SET LINESIZE 700
WHENEVER OSERROR EXIT FAILURE ROLLBACK
WHENEVER SQLERROR EXIT FAILURE ROLLBACK

COLUMN fw new_val fw1 noprint

SELECT
'/ads/odm/file'||fyweek||'.txt' fw
FROM
ods.calendar_dim
WHERE calendat_date = TO_CHAR(SYSDATE,'YYYYMMDD') ;

SET feedback OFF
SET echo OFF
SET heading OFF
SET verify OFF
SET TERMOUT OFF


spool &fw1

@/ads/odm/TEST.SQL

spool OFF

END_SQL
job_status=$?

ora_error=$( egrep -i "ERROR|ORA-|SP2" $spoolfile 2>/dev/NULL | SORT -u )

# CHECK EXIT status OF sqlplus, IF nonzero ora_error string
IF [[ $job_status -ne 0 || -n $ora_error ]]; THEN
echo " Job Status = [$job_status]" | $LOG
echo " Spool file errors" | $LOG
echo " ORACLE Errors" | $LOG
exit_error "Encountered during SQL*Plus"
ELSE
echo "SQL*Plus Completed" | $LOG
fi

Using SQL Plus it is possible to see the totals (sum) at the end of the report.

If the report goes for more than one page, is it possible to display the sum on each page (for those rows that are displayed in that particular page) and grand total at the end of the report?

Also is it possible to display the sum of the previous page in the first line of the current page?

Thanks,

I appreciate if you have an answer to the above question.
This helps in generating reports management requires that are not part of standard reports.

Hi Tom,
I am creating an SQL script with the following command in SQL plus.

SQL>select 'truncate table '||owner||'.'||table_name ||';' from dba_tables where owner = 'USERA'

I would like to replace the semicolon in the above command with '/' so that '/' acts as statement separator.

I appeded chr(10) and chr(13) but it's not working.

How to do this?

Tom, I come here as a last resort..After many unsuccessful tries..

I have recently installed PERL DBD::Oracle module. And after that I started getting this error anytime I try to open sql plus.
I tried to uninstall and re-install Oracle 10g client , set up Envirnonment path [I'm using Windows 2000 as client] and still no use. I see TNSNames.ora file is there, sqlplus has all MSB files and path has oracle in it. Regedit has Oracle Home created with proper path.

Still I keep getting this..Any thing I'm missing?

Thanks for any help you can provide ..

Tom, I do not have a cut paste currently, as I'm in the process of re-formatting my hard drive [thought something got really messed up]..But here are the sequence of errors I get one after the other:

=========Errors ==============

Error 6 initializing SQL*Plus

Message file SP1<lang>.msb not found

SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory

==========End Errors=============

But I see Oracle Home is set and all seem to be as they were set before..Hopefully reformattign my hard drive might help..but stil want to know what caused this problem..

Thanks,

I agree with you, as I'm reformatting, nothing can be said or done till I recreate the same scenario. But Is there any known issues [that you have come across sofar] , with installing PERL DBD::Oracle module that causes this problem?

Thanks.

Hi Tom,

The article makes a reference to this link:
http://asktom.oracle.com/~tkyte/flat/index.html but when I try to open the link it is not working...it would be very much useful to me , can someone check if the link has changed?

thanks,
Nirav