Out put file as pipe format 1

[garnet59]

Can anyone give me sample scripts to generate output txt file in pipe format.

For example I have following sql script needed to generate output in pipe format automatically in unix environment

SELECT
c.CASES_CODE as Case_Cde,
'20090506' AS OR_Event_Data_Effctv_Dte,
pth.PHST_ACCOUNT AS Pt_Acct_Nbr,
pt.PAT_MEDREC AS MRN,
sn2.NODE_TYPEID AS T2_Event_Type_Id,
t1.TYPE_DESCRP AS T2_Event_Type_Dscr,
rtrim(sn2.NODE_MNEMONIC) AS T2_Event_Id,
sn2.NODE_DESCRP AS T2_Event_Id_Dscr



FROM CASES c,CASEMAS cm, PATHIST pth,PATMAS pt,SYS_TREE st1,SYS_TIME st1,SYS_TREE st2,SYS_NODE sn2,SYS_TYPE t1,SYS_TIME tm1


where
c.CASSES_PHISTID = pth.PHST_ID
and c.CASS_CODE = cm.SH_MGRLOG
and pth.phst_Patid = pt.PAT_ID
and c.CASS_ID = st1.TREE_HANDLE
AND st1.TREE_HANDLE = st1.TREE_NODEID
and st1.TREE_ID = tm1.TIME_TREEID

Thanks in advance

 

[SantaMufasa]

Garnet,

Please pardon my density, but what, specifically, do you want Oracle to produce to meet your specifications for "pipe format"? (Can you give an example of what you want for output?)

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[garnet59]

Mufasa,

When I ran my SQLselect statement as above I'm looking how to produce output in pipe format as below:
FACILITY|SURGICALCASE_NUM|ACCOUNT_NUM|MRN|BLKHOLDER|PRIM_SURG_ID|PRIM_SURG_NAME|CASE_DATE|SCHED_SVC|SCHED_TYPE|PATIENT_TYPE|ASA_RATING|SCHED_OR|ACT_UNIT|SCHED_DT|CASE_ADDED_DT|CANCEL_CD|CANCEL_DT|CANCEL_REASON|PATIENTINROOMACTUAL|PATIENTINROOMSCHEDULED|PATIENTOUTOFROOMACTUAL|CUT_TIME|CLOSE_TIME
ENDO|168611|5929533|868904|ENDO|183|PATEL,HARSHAD| 2009-01-0912:30|GENERAL SURGERY|N|DS|1 HEALTHY|F SUITE D|ENDO|2009-01-0900:00:00|2008-12-31||||2009-01-09 12:30|009-01-09 12:30|2009-01-09 13:00|

 

[SantaMufasa]

Garnet,

I presume that you are using SQL*Plus. To suppress the code from appearing in your output,
you should run the code from a script instead of a "copy-and-paste" methodology. (The
code suppression command, "set echo off" is in effect only when running the code from a script.)
The name of the script I invoke, below, is "tt_541.sql".

Here, then is the invocation of script "tt_541.sql", followed by the script's code that
simulates a solution for you. The pipe symbols result from concatenating each output
expression with the pipe symbols:

SQL> @tt_541
id|last_name|first_name|userid|start_date|comments|manager_id|title|dept_id|salary|commission_pct
1|Velasquez|Carmen|cvelasqu|03-MAR-90|||President|50|2500|
2|Ngao|LaDoris|lngao|08-MAR-90||1|VP, Operations|41|1450|
3|Nagayama|Midori|mnagayam|17-JUN-91||1|VP, Sales|31|1400|
4|Quick-To-See|Mark|mquickto|07-APR-90||1|VP, Finance|10|1450|
5|Ropeburn|Audry|aropebur|04-MAR-90||1|VP, Administration|50|1550|
6|Urguhart|Molly|murguhar|18-JAN-91||2|Warehouse Manager|41|1200|
7|Menchu|Roberta|rmenchu|14-MAY-90||2|Warehouse Manager|42|1250|

...and the script code:

set linesize 200
set pagesize 0
set trimspool on
set feedback off
set echo off
prompt id|last_name|first_name|userid|start_date|comments|manager_id|title|dept_id|salary|commission_pct
select id||'|'||
       last_name||'|'||
       first_name||'|'||
       userid||'|'||
       start_date||'|'||
       comments||'|'||
       manager_id||'|'||
       title||'|'||
       dept_id||'|'||
       salary||'|'||
       commission_pct
  from s_emp;

Let us know if you have followup questions.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[KenCunningham]

You can also produce pipe-delimited output with Oracle's own (free) SQL Developer:

http://www.oracle.com/technology/products/database/sql_developer/index.html

Some days are diamonds, some days are rocks - make sure most are the former.