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!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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

Locked Post

New comments cannot be posted to this locked post.

Locked on Jun 26 2014

Added on May 29 2014

#and, #file, #from, #in, #script

0 comments

263 views