Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2013
    Posts
    6

    Unanswered: Spool method - no Data



    Hello Everyone,

    I'm trying to pull the data from my ORACLE 11g Database using the below syntax , certainly it create a fail in the directory but no data in it .


    set echo off;
    Set define Off;
    Set feedback Off;
    Set serveroutput On;
    SET PAGESIZE 0
    SET LINESIZE 1000
    spool on
    Spool 'J:\TQA\FXProgram\Thinksoft\2012 ITGs\17 - ITG # 99948 ATOM Revitalization QA\Shared\test';

    SELECT select * from ASSETEDW.T_FACT_ALM_BASE_OPUT_PROP where ACCT_KEY =1374 and MM_OFST_NBR = 24;

    Spool Off;
    SET DEFINE ON;
    SET FEEDBACK ON;


    somebody could help me to fix this .. [ Note Im using in the QA Region]



    Thanks in Adavnce,
    Srini.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    it works OK for me
    Code:
    [oracle@localhost ~]$ cat spoolit.sql
    set echo off
    Set define Off
    Set feedback Off
    Set serveroutput On
    SET PAGESIZE 0
    SET LINESIZE 1000
    spool on 
    Spool  spoolit.lis
    
    select count(*) from user_objects;
    
    Spool Off
    SET DEFINE ON
    SET FEEDBACK ON
    exit
    [oracle@localhost ~]$ sqlplus user1/user1 @spoolit.sql
    
    SQL*Plus: Release 11.2.0.2.0 Production on Thu Jul 25 06:42:57 2013
    
    Copyright (c) 1982, 2010, Oracle.  All rights reserved.
    
    
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
            17
    Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    [oracle@localhost ~]$ cat spoolit.lis
            17                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
    [oracle@localhost ~]$
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    SELECT select * from...
    Why do you have 2 SELECTs?
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  4. #4
    Join Date
    Jul 2013
    Posts
    6
    HI cis_groupie ,

    you're correct .. I have taken the one select away and it works

  5. #5
    Join Date
    Jul 2013
    Posts
    6


    Hello ,

    If i execute [f5 script runner] i'm not getting results



    set echo off;
    Set define Off;
    Set feedback Off;
    Set serveroutput On;
    SET PAGESIZE 0
    SET LINESIZE 1000
    SPOOL ON
    Spool 'C:\Documents and Settings\502180792\Desktop\New Folder\PRESTG_Noncost Liability.txt';


    SELECT (IQ.POSN_DATE||'_'||IQ.RPTG_DATE||'_'||IQ.SCNRIO_N M||'_'||IQ.ACCT_NM||'_'||IQ.BSLA_NM||'_'||IQ.LGL_E NT_NM||'_'||IQ.CURR_CODE||'_'||IQ.PROD_NM||'_'||IQ .RATE_DRV_NM||'_'||IQ.MM_OFST_NBR) AS SUG_KEY,
    iq.* FROM (SELECT DISTINCT TRUNC(b.SCHEDULEDATE) AS posn_date,
    TRUNC(B.SCHEDULEDATE) AS rptg_date,
    TRUNC(C.SCENARIODATE) AS MM_OFST_DATE,
    --A.TIMESTAMP,
    A.SCENARIODATEINDEX AS MM_OFST_NBR,
    E.SCENARIONAME AS SCNRIO_NM,
    A.site_nm,
    DECODE(D.BSLA,'BSLA','NAV','UnDefined','NAV',D.BSL A) AS BSLA_NM,
    DECODE(D.LEGAL_ENTITY,'UnDefined','NAV','Legal_Ent ity','NAV',D.LEGAL_ENTITY) AS LGL_ENT_NM,
    DECODE(D.CRNCY,'Crncy','NAV','UnDefined','NAV',D.C RNCY) AS CURR_CODE,
    D.ACCOUNTCLASS AS ACCT_CLASS_NM,
    TRIM(TRAILING ':'
    FROM D.ACCOUNTNAME ) AS ACCT_NM,
    DECODE(D.PRODUCT,'Product','NAV','UnDefined','NAV' ,D.PRODUCT) AS PROD_NM,
    DECODE(D.RATE_DRIVER,'Rate_Driver','NAV','UnDefine d','NAV',D.RATE_DRIVER) AS RATE_DRV_NM,
    --D.CSTM_1_NM AS CSTM_NM,
    A.EOPBAL AS EOP_BAL_AMT,
    A.INCEXP AS INCM_EXPNS_AMT,
    A.COUPON AS CPN_RATE,
    A.NETINCOME AS NET_INCM_AMT,
    A.NOTIONAL AS NOTL_AMT,
    A.EOPYLD AS EOP_YLD_RATE,
    A.NETPRESENTVALUE AS NPV_AMT,
    '' MACLY_DUR,
    '' MOD_DUR,
    A.MARKETVALUE AS MKT_VAL_AMT,
    '0' AS CNVXTY_RATE,
    A.MARKETPRICE AS MKT_PRICE_AMT,
    A.PREMAMORT AS PREM_AMORT_AMT,
    A.EFFDURATION AS EFF_DUR,
    DECODE(A.EFFCONVEXITY,'',0,A.EFFCONVEXITY) AS EFF_CNVXTY ,
    A.DOLLARDURATION AS DOL_DUR_AMT,
    A.DOLLARCONVEXITY AS DOL_CNVXTY_AMT,
    --DECODE(D.CONTRA,'No','N','Yes','Y') AS ACCT_CNTRA_FLAG,
    --'N' ACCT_EXCL_FLAG,
    A.AVGBAL AS AVG_BAL_AMT,
    A.AMORTCF AS AMORT_CFLW_AMT,
    A.PREPAYCF AS PRE_PYMT_CFLW_AMT,
    A.ADDAMT AS ADDL_AMT,
    A.PRINCF AS PRIN_CFLW_AMT,
    A.AVGYLD AS AVG_YLD_RATE,
    A.REPRAMT AS RPRICG_AMT,
    A.OAS AS OPT_ADJ_SPRD,
    A.XFEREOPYLD_1 AS XFER_EOP_YLD_RATE,
    A.WAL AS WGT_AVG_LIFE,
    'ALCO' AS RUN_TYPE_CODE,
    A.PREMDISC AS PREM_DISC_AMT,
    A.AVGNOTIONAL AS AVG_NOTIONAL_AMT
    --DECODE(A.SITE_NM,'SWISS','Consolidation','Alco') AS RUN_TYPE_CODE
    FROM ASSETSTG.ALM_R_OUTPUT partition(P_ALM_R_OUTPUT_SWISS) A,
    ASSETSTG.alm_SCHEDULEDATES partition(P_ALM_SCHEDULEDATES_SWISS) B,
    ASSETSTG.ALM_SCENARIODATES PARTITION(P_ALM_SCENARIODATES_SWISS) C,
    ASSETSTG.alm_ACCOUNTS partition(P_ALM_ACCOUNTS_SWISS) D,
    ASSETSTG.alm_SCENARIOS PARTITION (P_ALM_SCENARIOS_SWISS) E
    WHERE A.Scenarioid = B.Scenarioid
    AND A.Scenarioid = C.Scenarioid
    AND A.Scenariodateindex = C.Scenariodateindex
    AND A.Accountid = D.id
    AND A.SCENARIOID = E.ID
    AND D.ACCOUNTCLASS IN ('Excluded')) IQ ;


    Spool Off;
    SET DEFINE ON;
    SET FEEDBACK ON;



    [COLOR="rgb(255, 0, 255)"]were i execute the below i do get the results . couldn't figure it out why ! somebody could help me out to fix this [/COLOR]


    set echo off;
    Set define Off;
    Set feedback Off;
    Set serveroutput On;
    SET PAGESIZE 0
    SET LINESIZE 1000
    SPOOL ON
    Spool 'C:\Documents and Settings\502180792\Desktop\New Folder\PRESTG_Noncost Liability.txt';

    (SELECT * from (select
    (DT.CAL_DATE||'_'||DT1.CAL_DATE||'_'||SCNR.SCNRIO_ NM||'_'||ACCT.ACCT_NM||'_'||BSLA.BSLA_NM||'_'||LGL .LGL_ENT_NM||'_'||CUR.CURR_CODE||'_'||PRD.PROD_NM| |'_'||RATE.RATE_DRV_NM||'_'||BS.MM_OFST_NBR) AS COLUMN_1,
    DT.CAL_DATE AS COLUMN_2 ,
    DT1.CAL_DATE AS COLUMN_3,
    DT2.CAL_DATE AS COLUMN_4,
    BS.MM_OFST_NBR AS COLUMN_6,
    SCNR.SCNRIO_NM AS COLUMN_7,
    BS.SITE_NM AS COLUMN_5,
    BSLA.BSLA_NM AS COLUMN_8,
    LGL.LGL_ENT_NM AS COLUMN_9,
    CUR.CURR_CODE AS COLUMN_10,
    CLS.ACCT_CLASS_NM AS COLUMN_11,
    ACCT.ACCT_NM AS COLUMN_12,
    PRD.PROD_NM AS COLUMN_13,
    RATE.RATE_DRV_NM AS COLUMN_14,
    BS.EOP_BAL_AMT AS COLUMN_15,
    BS.INCM_EXPNS_AMT AS COLUMN_16,
    BS.CPN_RATE AS COLUMN_17,
    BS.NET_INCM_AMT AS COLUMN_18,
    BS.NOTL_AMT AS COLUMN_19,
    BS.EOP_YLD_RATE AS COLUMN_20,
    BS.NPV_AMT AS COLUMN_21,
    BS.MACLY_DUR AS COLUMN_22,
    BS.MOD_DUR AS COLUMN_23,
    BS.MKT_VAL_AMT AS COLUMN_24,
    DECODE(BS.CNVXTY_RATE,'',0,BS.CNVXTY_RATE) AS COLUMN_25,
    BS.MKT_PRICE_AMT AS COLUMN_26,
    BS.PREM_AMORT_AMT AS COLUMN_27,
    BS.EFF_DUR AS COLUMN_28,
    DECODE(BS.EFF_CNVXTY,'',0,BS.EFF_CNVXTY) AS COLUMN_29,
    BS.DOL_DUR_AMT AS COLUMN_30,
    BS.DOL_CNVXTY_AMT AS COLUMN_31,
    --BS.ACCT_CNTRA_FLAG AS COLUMN_32,
    --BS.ACCT_EXCL_FLAG AS COLUMN_33,
    BS.AVG_BAL_AMT AS COLUMN_34,
    BS.AMORT_CFLW_AMT AS COLUMN_35,
    BS.PRE_PYMT_CFLW_AMT AS COLUMN_36,
    BS.ADDL_AMT AS COLUMN_37,
    BS.PRIN_CFLW_AMT AS COLUMN_38,
    BS.AVG_YLD_RATE AS COLUMN_39,
    BS.RPRICG_AMT AS COLUMN_40,
    BS.OPT_ADJ_SPRD AS COLUMN_41,
    BS.XFER_EOP_YLD_RATE AS COLUMN_42,
    BS.WGT_AVG_LIFE AS COLUMN_43,
    BS****N_TYPE_CODE AS COLUMN_44,
    BS.PREM_DISC_AMT AS COLUMN_45,
    BS.AVG_NOTL_AMT AS COLUMN_46


    FROM ASSETSTG.T_STG_FACT_ALM_BASE_OPUT_PROP BS,
    ASSETEDW.T_DIM_ALM_DATE DT,ASSETEDW.T_DIM_ALM_DATE DT1,
    ASSETEDW.T_DIM_ALM_DATE DT2,ASSETEDW.T_DIM_ALM_SCNRIO SCNR,
    ASSETEDW.T_DIM_ALM_BSLA BSLA,ASSETEDW.T_DIM_ALM_LGL_ENT LGL,
    ASSETEDW.T_DIM_ALM_CURR CUR,ASSETEDW.T_DIM_ALM_ACCT_CLASS CLS,
    ASSETEDW.T_DIM_ALM_CHRT_OF_ACCT ACCT,ASSETEDW.T_DIM_ALM_PROD PRD,
    ASSETEDW.T_DIM_ALM_RATE_DRV RATE

    WHERE
    DT.DATE_KEY=BS.POSN_DATE_KEY
    AND DT1.DATE_KEY=BS.RPTG_DATE_KEY
    AND DT2.DATE_KEY=BS.MM_OFST_DATE_KEY
    AND SCNR.SCNRIO_KEY=BS.SCNRIO_KEY
    AND BSLA.BSLA_KEY=BS.BSLA_KEY
    AND LGL.LGL_ENT_KEY=BS.LGL_ENT_KEY
    AND CUR.CURR_KEY=BS.CURR_KEY
    AND CLS.ACCT_CLASS_KEY=BS.ACCT_CLASS_KEY
    AND ACCT.ACCT_KEY=BS.ACCT_KEY
    AND PRD.PROD_KEY=BS.PROD_KEY
    AND RATE.RATE_DRV_KEY=BS.RATE_DRV_KEY
    --AND BS.EOP_BAL_AMT='5986.75'
    and CLS.ACCT_CLASS_NM in ( 'Excluded')
    --,'Capital')
    AND BS.SITE_NM = 'SWISS'
    GROUP BY DT.CAL_DATE, DT1.CAL_DATE, DT2.CAL_DATE, BS.SITE_NM, BS.MM_OFST_NBR, SCNR.SCNRIO_NM, BSLA.BSLA_NM, LGL.LGL_ENT_NM, CUR.CURR_CODE, CLS.ACCT_CLASS_NM, ACCT.ACCT_NM, PRD.PROD_NM, RATE.RATE_DRV_NM, BS.EOP_BAL_AMT, BS.INCM_EXPNS_AMT, BS.CPN_RATE, BS.NET_INCM_AMT, BS.NOTL_AMT, BS.EOP_YLD_RATE, BS.NPV_AMT, BS.MACLY_DUR, BS.MOD_DUR, BS.MKT_VAL_AMT, DECODE(BS.CNVXTY_RATE,'',0,BS.CNVXTY_RATE), BS.MKT_PRICE_AMT, BS.PREM_AMORT_AMT, BS.EFF_DUR, DECODE(BS.EFF_CNVXTY,'',0,BS.EFF_CNVXTY), BS.DOL_DUR_AMT, BS.DOL_CNVXTY_AMT, BS.AVG_BAL_AMT, BS.AMORT_CFLW_AMT, BS.PRE_PYMT_CFLW_AMT, BS.ADDL_AMT, BS.PRIN_CFLW_AMT, BS.AVG_YLD_RATE, BS.RPRICG_AMT, BS.OPT_ADJ_SPRD, BS.XFER_EOP_YLD_RATE, BS.WGT_AVG_LIFE, BS****N_TYPE_CODE, BS.PREM_DISC_AMT, BS.AVG_NOTL_AMT )) ;



    Spool Off;
    SET DEFINE ON;
    SET FEEDBACK ON;




  6. #6
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    i'm not getting results
    What do you mean? That you're getting a blank screen? Or that you're getting an error message? Or that you're not getting the data that you expect to get?
    Are there any clues to what's happening in the spool file?
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  7. #7
    Join Date
    Jul 2013
    Posts
    6
    Sure !

    the output seems to be empty

    here is the log

    line 5: SQLPLUS Command Skipped: SET PAGESIZE 0
    line 6: SQLPLUS Command Skipped: SET LINESIZE 1000

  8. #8
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    "SPOOL ON"
    You don't need this line.
    (and the correct sytax, if you did need it, is: "spool on to <filename>")
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  9. #9
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Its

    spool filename

    Do not have blank lines in your select command!!
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •