• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 8657
  • Last Modified:

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
0
peter_chong
Asked:
peter_chong
  • 10
  • 7
  • 4
2 Solutions
 
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 LappinSenior 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
 
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
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

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

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

  • 10
  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now