spool the sql in csv.

Dear EE member,

I would like to spool the following in csv format:

set echo off;
set feedback off;
set heading off;
set verify off;
set linesize 80;
spool /home/oracle/testdata/test1.lst;

select part_no,description,
IFSAPP.Inventory_Part_In_Stock_API.Get_Inventory_Qty_Onhand('MIA', PART_NO,NULL),
IFSAPP.Inventory_Part_In_Stock_API.Get_Inventory_Qty2_Onhand('MIA', PART_NO,NULL),
IFSAPP.Inventory_Part_In_Stock_API.Get_Inventory_Qty3_Onhand('MIA', PART_NO,NULL)
FROM
IFSAPP.INVENTORY_PART  
WHERE CONTRACT = 'MIA'
AND
PART_NO like 'M%F%'
AND
IFSAPP.Inventory_Part_In_Stock_API.Get_Inventory_Qty_Onhand('MDF', PART_NO,NULL)>0;
spool off;

Regards,
Peter
peter_chongAsked:
Who is Participating?
 
Naveen KumarConnect With a Mentor Production Manager / Application Support ManagerCommented:
i might give you a solution for that as well but then it will not be a good solution because we are trying to use more than that what spool command can give us.

why you have not used utl_file package for file handling operations ?  This package has routines for opening , reaading, writing, closing , etc files.

I advice you to go for that even if it takes a bit more time to create a routine for doing that because in case if you want to add one more column output to the file and then again you will stuck up with this column command/spool command.

If you still want to do it with this...

Then try using the below sql instead of your sql :

select '"'||part_no             ||'",' ||
       '"'||description         ||'",' ||
       trim(to_char(IFSAPP.Inventory_Part_In_Stock_API.Get_Inventory_Qty_Onhand('MDF', PART_NO,NULL),'9999999.99999')) ||',' ||
trim(to_char(IFSAPP.Inventory_Part_In_Stock_API.Get_Inventory_Qty2_Onhand('MDF',PART_NO,NULL),'9999999.99999')) ||',' ||
trim(to_char(IFSAPP.Inventory_Part_In_Stock_API.Get_Inventory_Qty3_Onhand('MDF',PART_NO,NULL),'9999999.99999')) As wanted_output
from IFSAPP.INVENTORY_PART
where part_no like 'M%F%'and IFSAPP.Inventory_Part_In_Stock_API.Get_Inventory_Qty_Onhand('MDF', PART_NO,NULL)>0;

NOTE: i have changed only the sql and all other column commands/spool commands remain the same.

Thanks
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
Try with the below :

set echo off
set feedback off
set heading off
set verify off
set linesize 80
spool /home/oracle/testdata/test1.lst

select part_no||','||
description||','||
IFSAPP.Inventory_Part_In_Stock_API.Get_Inventory_Qty_Onhand('MIA', PART_NO,NULL) ||','||
IFSAPP.Inventory_Part_In_Stock_API.Get_Inventory_Qty2_Onhand('MIA', PART_NO,NULL) ||','||
IFSAPP.Inventory_Part_In_Stock_API.Get_Inventory_Qty3_Onhand('MIA', PART_NO,NULL)
FROM
IFSAPP.INVENTORY_PART  
WHERE CONTRACT = 'MIA'
AND PART_NO like 'M%F%'
AND IFSAPP.Inventory_Part_In_Stock_API.Get_Inventory_Qty_Onhand('MDF', PART_NO,NULL)>0;

spool off

Thanks,
0
 
Stephen LappinConnect With a Mentor Senior TechnologistCommented:
Add commas to the end of each column, and quotes round the description:

select part_no||',', '"'||description||'",',
IFSAPP.Inventory_Part_In_Stock_API.Get_Inventory_Qty_Onhand('MIA', PART_NO,NULL)||',',
IFSAPP.Inventory_Part_In_Stock_API.Get_Inventory_Qty2_Onhand('MIA', PART_NO,NULL)||',',
IFSAPP.Inventory_Part_In_Stock_API.Get_Inventory_Qty3_Onhand('MIA', PART_NO,NULL)
FROM
IFSAPP.INVENTORY_PART  
WHERE CONTRACT = 'MIA'
AND
PART_NO like 'M%F%'
AND
IFSAPP.Inventory_Part_In_Stock_API.Get_Inventory_Qty_Onhand('MDF', PART_NO,NULL)>0;
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
peter_chongAuthor Commented:
Dear lappins,
I know your statement works. But I am confuse
with the syntax of pipe with the delimiter:
,', '"'||description||'",',
',',
I can't capture the concept, so I hope that you
can enlightened me on this.

Thank you.

Regards,
Peter
0
 
peter_chongAuthor Commented:
Dear EE members,
Another thing is:
A:
how to fix the width of each field including the one
called from:
IFSAPP.Inventory_Part_In_Stock_API.Get_Inventory_Qty_Onhand('MIA', PART_NO,NULL)

So that it can output to a file in a proper format without space and wrap around.

B:
How to avoid the output skip one row in the top and bottom of the record rows.

Thanks.

Regards,
Peter
0
 
peter_chongAuthor Commented:
I have answered question B.
answer: set pagesize to avoid skip line.

I have also fix the width format by:
e.g:
column part_no format A8 truncate

BUT FAIL to apply column function in delimited format.
Any idea? (Which is question A)
0
 
Stephen LappinSenior TechnologistCommented:
Two pipes || is the concatenation token in SQL*Plus, Hence
'"'||description||'",'
would output the following string
"description"

You can set the linesize in SQL*Plus to a longer line in order to prevent lione wrap. Use the following command to allow 300 characters in the line:

SET LINESIZE 300

Having space padding in fieds in your CSV file should not cause any problems unless it is a string field. In this case, wrap the output in quotes as shown above.
0
 
peter_chongAuthor Commented:
Dear EE member,
When I apply the following to normal sql without
concatenation, it works well. But when I apply
|| to establish the delimited file effect. The column
setting not work.

E.g:
column part_no format A8 truncate
column description format A30 truncate
column Q1 format  9,99 truncate
column Q2 format  9,99 truncate
column Q3 format  9,99 truncate

select part_no,description,
IFSAPP.Inventory_Part_In_Stock_API.Get_Inventory_Qty_Onhand('MDF', PART_NO,NULL) As Q1
from IFSAPP.INVENTORY_PART where part_no like 'M%F%'and  IFSAPP.Inventory_Part_In_Stock_API.Get_
Inventory_Qty_Onhand('MDF', PART_NO,NULL)>0

How to realise my column purpose in csv format?

Thanks.

Regards,
Peter
0
 
Stephen LappinSenior TechnologistCommented:
Why do you want to form numbers in a CSV file. Whatever application the CSV file is loaded into should handle the display format. Also, having commas in your formatted string is a bad idea as a comma is the field delimeter.

The reason that the SQL*Plus formatting does not work is because by concatenanting a character, the field becomes a string field instead of a number. If your really want to fomat yout numbers, then to the TO_CHAR function like so:

TO_CHAR(col,'990.00')||','
0
 
peter_chongAuthor Commented:
Dear EE member,
I want to form number in CSV is because I want to
perform calculation on it in MS Access. I need to make
sure the number data type is match with the number
data type of ms access link table.
0
 
peter_chongAuthor Commented:
The following code shown better result.
But the leading space and first zero is empty occurred between each number field.

For example,
"ABC","DEF",   1.96456,     .71423,     55.00012

My Expected Result:
"ABC","DEF",1.96456,0.71423,55.00012

How to achieve this?

While, the end of the output show "Input truncated
to 9 characters." How to eliminate this message?

set echo off
set feedback off
set heading off
set verify off
set linesize 132
set pagesize 200

column part_no format A12 truncate
column description format A40 truncate

spool /home/oracle/testdata/dummyTrace
select '"'||part_no||'",' As part_no , '"'||description||'",' As description,      
trim(to_char(IFSAPP.Inventory_Part_In_Stock_API.Get_Inventory_Qty_Onhand('MDF', PART_NO,NULL),'9999999.99999')||',') As Q1,
trim(to_char(IFSAPP.Inventory_Part_In_Stock_API.Get_Inventory_Qty2_Onhand('MDF',PART_NO,NULL),'9999999.99999')||',') As Q2,
trim(to_char(IFSAPP.Inventory_Part_In_Stock_API.Get_Inventory_Qty3_Onhand('MDF',PART_NO,NULL),'9999999.99999')) As Q3
from IFSAPP.INVENTORY_PART
where part_no like 'M%F%'and IFSAPP.Inventory_Part_In_Stock_API.Get_Inventory_Qty_Onhand('MDF', PART_NO,NULL)>0;
spool off

Another thing: The following skip one line everytime
I start executing. How to get rid of it?
#!/bin/sh
#spool hand data to a csv file.
#date: 01-08-2007
#
ORACLE_HOME=/uo1/oracle/product/8.1.6
ORACLE_SID=test; export ORACLE_SID;

sqlplus myuser/mypass@test <<EOF

@spoolIt.sql


exit

Regards,
Peter
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
"Input truncated
to 9 characters." How to eliminate this message?
---> add an empy blank line in your .sql file so that it will not show this message
0
 
peter_chongAuthor Commented:
Dear nav_kum_v,
Your solution solved the input truncated problem.

How about other problems listed in
ID:19615762 Date:08.02.2007 at 07:02PM CST at the above comment list.

More points will be rewarded if you can solve them.

Thank you.

Regards,
Peter


0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
select '"' || 'abc'|| '",' ||
       trim(to_char(1.96456,'9999999.99999')) || ',' ||
       'naveen' output
from dual ;

OUTPUT
--------------------
"abc",1.96456,naveen

I just tried this and it works for me. If it does not work, then paste the output you are getting here..so that i can look into your sql
0
 
peter_chongAuthor Commented:
Great! nav_kum_v.
I almost on the way.

But how to show 0.93 instead of .93 in the record
list, while eliminating the first blank row.

Refer: Comment in ID:19615762 for the shell script:
My Shell script start sqlplus by the following:
Connected to:
Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
With the Partitioning option
JServer Release 8.1.6.0.0 - Production

SQL> SQL>

Result Example:
      <---One line skip while .12 instead of  0.12
"M123","Sample1",1.96469,.71429,.12
"M456","Sample2",2.36567,1.06660,63.99573                                    
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
change the below :
set pagesize 200   -->   set pagesize 0

and then run your code without any modifications.


0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
SQL> select trim(to_char(.21,'99.99')) val from dual;    
                                                         
VAL                                                      
------                                                  
.21                                                      

SQL> select trim(to_char(.21,'90.99')) val from dual; --> 0 in the format mask says 0 is mandatory in the output
 -- if there is no digit for that position
                                                       
VAL                                                    
------                                                
0.21                      

The above is for your understanding only.

So the last column

trim(to_char(IFSAPP.Inventory_Part_In_Stock_API.Get_Inventory_Qty3_Onhand('MDF',PART_NO,NULL),'9999999.99999')) As wanted_output

can be changed as below and test it :
                             
trim(to_char(IFSAPP.Inventory_Part_In_Stock_API.Get_Inventory_Qty3_Onhand('MDF',PART_NO,NULL),'9999990.99999')) As wanted_output

All other things remain the same and only the above line is the change for .12 to 0.12

Thanks
0
 
peter_chongAuthor Commented:
nav_kum_v,
Thank you very much for your help.
I will test the script on Monday in the office.
Anyway, I will assure you that you gained the
most point for this question.

BTW, Is the set page size 0 will caused all rows break by a line after certain rows display? I put page size 200, since the records are less than 200, so it won't skip a line after certain rows display. May be I misunderstand the function, please clarify it to me.

Thank you.

Regards,
Peter
0
 
Stephen LappinSenior TechnologistCommented:
Page size zero means that there will never be a page break, no matter how many rows are returned.

Page size 200 will instert a page break after each 200 rows.

If you are returning less than 200 rows, the effect is the same.
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
since you are spooling content to a file, i thought a page break is not required for the comma delimited file and hence set pagesize to 0 which stops the blank line coming as the first line.

Thanks
0
 
peter_chongAuthor Commented:
Dear EE members,
Thank you for your helping hand.
Regards,
Peter
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.