Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

.sql file from a Unix shell script and saving the output in another file

2683187May 29 2014

SQL Query:

SELECT
cc2.cost_center "ORG_UNIT_ID",
cc2.COST_CENTER_SHORT_NAME "ALPHA_CODE",
cc2.cost_center_name "SHORT_NAME",
cc2.cost_center_name "LONG_Name",
substr(CC.COST_CENTER_SHORT_NAME,1,3) "PMU_ALPHA",
CC.cost_center_name "PMU_NAME",
cc.company_code "Org_Acronym"
FROM
FUND_CENTER_HIERARCHY@CMD FC, COST_CENTER@CMD CC, COST_CENTER@CMD CC2 WHERE FC.FC_LEVEL3 = CC.COST_CENTER AND FC.FUND_CENTER = CC2.COST_CENTER
ORDER BY 1,2

----------------------.sql file in UNIX ------------------------------
SET echo off
SET verify off
SET colsep ,
SET pagesize 0
SET trimspool on
SET feedback off
SPOOL ON
SPOOL /data/CostCenterData.csv
SELECT
'"'||trim (cc2.cost_center "ORG_UNIT_ID)"||'","'
||trim (cc2.COST_CENTER_SHORT_NAME "ALPHA_CODE")||'","'
||trim (cc2.cost_center_name "SHORT_NAME")||'","'
||trim (cc2.cost_center_name "LONG_Name")||'","'
||trim (substr(CC.COST_CENTER_SHORT_NAME,1,3) "PMU_ALPHA")||'","'
||trim (CC.cost_center_name "PMU_NAME")||'","'
||trim (cc.company_code "Org_Acronym")||'",'
FROM

FUND_CENTER_HIERARCHY@CMD FC, COST_CENTER@CMD CC, COST_CENTER@CMD CC2 WHERE FC.FC_LEVEL3 = CC.COST_CENTER AND FC.FUND_CENTER = CC2.COST_CENTER

ORDER BY 1,2
SPOOL OFF
EXIT

This query is working in sql developer, but not working in unix server...can you please anyone help me to conver into .sql file correctly....

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jun 26 2014
Added on May 29 2014
#and, #file, #from, #in, #script
0 comments
267 views