Home » Infrastructure » Linux » Unable to get data into a CSV file (Database:11.2.0.1.0; O/S: Linux 3.0.101-0.47.71-default x86_64)
Unable to get data into a CSV file [message #648145] Wed, 17 February 2016 13:21 Go to next message
buggleboy007
Messages: 282
Registered: November 2010
Location: Canada
Senior Member
I have created a shell script file called:

a) ift225b.sh and have the following code:

FILENAME=MARK1MARK2 
LOGFILE1=$LOGDIR/$FILENAME.CSV
DB_User=$SQLUSER1/$SQLPWD1@$SQLCONNECTION

echo "Calling .sql file to generate the CSV report"

sqlplus -S $SQLUSER/$SQLPASS <<EOF>>${LOGFILE1}

whenever sqlerror exit failure
whenever os error exit failure
SET SERVEROUTPUT ON
SET LINESIZE 32767
SET PAGESIZE 0
SET FEEDBACK OFF
SET TRIMSPOOL ON
SET TERMOUT OFF
SPOOL ${LOGFILE1}
DBMS_OUTPUT.enable(buffer_size => NULL);         	
@${SCRIPTSDIR}/markprocedure.sql
SPOOL OFF
EXIT;
EOF
RSLT=$?
echo ${RSLT}


markprocedure.sql and have the following code:

     
--SET ECHO ON
--SET SERVEROUTPUT ON
--SET LINESIZE 32767
--SET WRAP OFF
--SET PAGESIZE 0
--SET FEEDBACK OFF
--SET TRIMSPOOL ON
--SET TERMOUT OFF
--SPOOL C:\Users\tan0078\Documents\01_myfile_MARKUAT.csv

   
         SELECT '"MESSAGE ID","TYPE CD","DESCRIPTION TEXT","IRF AREA CODE","TRANSACTION PROCESS ID","RECORD CREATE TMS",'||
                '"TRNCR_TRNSCN_PRCS_ID","RECORD_CREATE_TMS","RECORD_UPDATE_TMS","CREATE_USER_ID","UPDATE_USER_ID",'||
                '"MARK_SCAC_CD","MARK_SCAC_NM","AAR_UPDATE_DT","EFCTV_DT","DUNS_NBR","EXPIRY_DT","ICC_DOCKET_NBR",'||
                '"TRNSCN_NBR","TYPE_CD","OWNER_MARK_CD","OWNER_SCAC_CD","ROAD_NBR","REPORT_EQPMT_RLS_CD","REPORT_EQPMT_PLCMNT_CD",'||
                '"NMFTA_TRNSCN_PRCS_ID","RECORD_CREATE_TMS","RECORD_UPDATE_TMS","CREATE_USER_ID","UPDATE_USER_ID",'||  
                '"SCAC_CD","AAR_UPDATE_DT","TRNCR_NM","ADRS_TXT","CITY_NM","CNTRY_NM","POSTAL_ZIP_CD","TRNSCN_NBR","STPRV_CD",'
        FROM  dual; 
DECLARE

    lv_prev_irf_area_cd           IRF_MSG_TRNSCN_PRCS.irf_area_cd%TYPE;  
                     
    CURSOR cur_mark1details(v_trnscn_prcs_id   IN     TRNCR_TRNSCN_PRCS.trncr_trnscn_prcs_id%TYPE)
    IS     SELECT *
           FROM trncr_trnscn_prcs 
           WHERE trncr_trnscn_prcs_id = v_trnscn_prcs_id;
           
           
   CURSOR cur_mark2details(v_trnscn_prcs_id   IN     NMFTA_TRNSCN_PRCS.nmfta_trnscn_prcs_id%TYPE)
    IS     SELECT *
           FROM nmfta_trnscn_prcs
           WHERE nmfta_trnscn_prcs_id = v_trnscn_prcs_id;             
         
      lv_Row_Mark1Trans   TRNCR_TRNSCN_PRCS%ROWTYPE;        
      lv_NumFoundRows                  NUMBER:=0;
      
      lv_NumMark1Trans    NUMBER(10):=0;
      lv_NumMark2Trans    NUMBER(10):=0; 
      lv_NumMark1Trans1   NUMBER(10):=0; 
      lv_NumMark2Trans1   NUMBER(10):=0; 
      
      lv_NumMark1Recs    NUMBER(10):=0;
      lv_NumMark2Recs    NUMBER(10):=0;

BEGIN


    SELECT COUNT(*)
    INTO  lv_NumFoundRows
    FROM irf_msg a
    JOIN irf_msg_trnscn_prcs b ON b.msg_id = a.msg_id
    WHERE b.irf_area_cd IN('MARK1','MARK2')
    ORDER BY b.irf_area_cd;
    
    SELECT COUNT(*)
    INTO  lv_NumMark1Recs
    FROM irf_msg a
    JOIN irf_msg_trnscn_prcs b ON b.msg_id = a.msg_id
    WHERE b.irf_area_cd ='MARK1'
    ORDER BY b.irf_area_cd;
    
    SELECT COUNT(*)
    INTO  lv_NumMark2Recs
    FROM irf_msg a
    JOIN irf_msg_trnscn_prcs b ON b.msg_id = a.msg_id
    WHERE b.irf_area_cd ='MARK2'
    ORDER BY b.irf_area_cd;
    
       
    IF lv_NumFoundRows <> 0 THEN
    
        FOR rec IN (SELECT a.msg_id
                      ,a.type_cd
                      ,a.dscrpt_txt
                      ,b.irf_area_cd
                      ,b.trnscn_prcs_id
                      ,b.record_create_tms
                       FROM irf_msg a
                      JOIN irf_msg_trnscn_prcs b ON b.msg_id = a.msg_id
                      WHERE b.irf_area_cd IN('MARK1','MARK2')
                      ORDER BY b.irf_area_cd  
                     )
       LOOP
            DBMS_OUTPUT.enable(buffer_size => NULL);
            DBMS_OUTPUT.put_line(TO_CHAR(rec.msg_id)||','||rec.type_cd||','||'"'||rec.dscrpt_txt||'"'||','||rec.irf_area_cd||','||TO_CHAR(rec.trnscn_prcs_id)||','||
                                  TO_CHAR(rec.record_create_tms,'MM/DD/RRRR HH24:MI:SS'));
                                    
           IF rec.irf_area_cd = 'MARK1' THEN
            
                -- Passing each transaction obtained for an area code to retrieve MARK1's details
               FOR lv_Row_Mark1Trans IN cur_mark1details(rec.trnscn_prcs_id)
               LOOP
               DBMS_OUTPUT.put(','||TO_CHAR(lv_Row_Mark1Trans.trncr_trnscn_prcs_id)||','||
                                       TO_CHAR(lv_Row_Mark1Trans.record_create_tms, 'MM/DD/RRRR HH24:MI:SS')||','||
                                       TO_CHAR(lv_Row_Mark1Trans.record_update_tms, 'MM/DD/RRRR HH24:MI:SS')||','|| 
                                               lv_Row_Mark1Trans.create_user_id||','||
                                       TO_CHAR(lv_Row_Mark1Trans.mark_scac_cd)||','||
                                               lv_Row_Mark1Trans.mark_scac_nm||','||
                                               lv_Row_Mark1Trans.aar_update_dt||','||
                                               lv_Row_Mark1Trans.efctv_dt||','||
                                       TO_CHAR(lv_Row_Mark1Trans.duns_nbr)||','||
                                               lv_Row_Mark1Trans.expiry_dt||','||
                                       '="'||TO_CHAR(lv_Row_Mark1Trans.icc_docket_nbr)||'"'||','||
                                       '="'||TO_CHAR(lv_Row_Mark1Trans.trnscn_nbr)||'"'||','||
                                       TO_CHAR(lv_Row_Mark1Trans.type_cd)||','||
                                       TO_CHAR(lv_Row_Mark1Trans.owner_mark_cd)||','||
                                       TO_CHAR(lv_Row_Mark1Trans.owner_scac_cd)||','||
                                       '="'||lv_Row_Mark1Trans.road_nbr||'"'||','||
                                       TO_CHAR(lv_Row_Mark1Trans.report_eqpmt_rls_cd)||','||
                                       TO_CHAR(lv_Row_Mark1Trans.report_eqpmt_plcmnt_cd)
                                 );
               DBMS_OUTPUT.put_line('          '); 
               lv_NumMark1Trans:=cur_mark1details%ROWCOUNT;
               lv_NumMark1Trans1:=lv_NumMark1Trans1+lv_NumMark1Trans;
                
               END LOOP; 
               
           ELSIF rec.irf_area_cd = 'MARK2' THEN
           
              --DBMS_OUTPUT.put_line('"MESSAGE ID","TYPE CD","DESCRIPTION TEXT","IRF AREA CODE","TRANSACTION PROCESS ID","RECORD CREATE TMS",'||
                --                      '"NMFTA_TRNSCN_PRCS_ID","RECORD_CREATE_TMS","RECORD_UPDATE_TMS","CREATE_USER_ID","UPDATE_USER_ID",'||  
                  --                    '"SCAC_CD","AAR_UPDATE_DT","TRNCR_NM","ADRS_TXT","CITY_NM","CNTRY_NM","POSTAL_ZIP_CD","TRNSCN_NBR","STPRV_CD",');
               
               DBMS_OUTPUT.enable(buffer_size => NULL);
              --DBMS_OUTPUT.put_line(TO_CHAR(rec.msg_id) ||','||TO_CHAR(rec.type_cd)||','||TO_CHAR(rec.dscrpt_txt)||','||TO_CHAR(rec.irf_area_cd)||','||
                --                     TO_CHAR(rec.trnscn_prcs_id)||','||TO_CHAR(rec.record_create_tms,'MM/DD/RRRR HH24:MI:SS')||','
                  --                   );
              FOR lv_Row_Mark2Trans IN cur_mark2details(rec.trnscn_prcs_id)
              LOOP 
                DBMS_OUTPUT.put(','||TO_CHAR(lv_Row_Mark2Trans.nmfta_trnscn_prcs_id)||','||
                                        TO_CHAR(lv_Row_Mark2Trans.record_create_tms,'MM/DD/RRRR HH24:MI:SS')||','||
                                        TO_CHAR(lv_Row_Mark2Trans.record_update_tms,'MM/DD/RRRR HH24:MI:SS')||','||
                                                lv_Row_Mark2Trans.create_user_id||','||
                                                lv_Row_Mark2Trans.update_user_id||','||
                                        TO_CHAR(lv_Row_Mark2Trans.scac_cd)||','||
                                                lv_Row_Mark2Trans.aar_update_dt||','||
                                                lv_Row_Mark2Trans.trncr_nm||','||
                                        '"'||lv_Row_Mark2Trans.adrs_txt||'"'||','||
                                        '"'||lv_Row_Mark2Trans.city_nm||'"'||','||
                                        '"'||lv_Row_Mark2Trans.cntry_nm||'"'||','||
                                        '"'||lv_Row_Mark2Trans.postal_zip_cd||'"'||','||
                                        '="'||lv_Row_Mark2Trans.trnscn_nbr||'"'||','||
                                        '="'||lv_Row_Mark2Trans.stprv_cd||'"'
                                    );                                              
               DBMS_OUTPUT.put_line('          ');
               lv_NumMark2Trans:=cur_mark2details%ROWCOUNT;
               lv_NumMark2Trans1:=lv_NumMark2Trans1+lv_NumMark2Trans;
              END LOOP;
               
         END IF;       
       
       END LOOP;
    ELSE 
        RAISE NO_DATA_FOUND;
    END IF;
              
             DBMS_OUTPUT.put_line('          '); 
             DBMS_OUTPUT.put_line('          '); 
             DBMS_OUTPUT.put_line('          '); 
             DBMS_OUTPUT.put_line('SUMMARY DETAILS');
             DBMS_OUTPUT.put_line('======================================================================'); 
             
             DBMS_OUTPUT.put_line('Number of Mark1 master records: ' ||lv_NumMark1Recs);
                    
             IF lv_NumMark1Trans1=0 THEN   
               DBMS_OUTPUT.put_line('There are no transaction details for any of the transaction number against Mark1');
             ELSE
                DBMS_OUTPUT.put_line('Number of records with Mark1 transaction details: '||lv_NumMark1Trans1);
             END IF;  
                 
                 
             DBMS_OUTPUT.put_line ('Number of Mark2 master records: ' ||lv_NumMark2Recs);    
                           
              IF lv_NumMark2Trans1=0 THEN   
                DBMS_OUTPUT.put_line('There are no transaction details for any of the transaction number against Mark2');
              ELSE
                DBMS_OUTPUT.put_line('Number of records with Mark2 transaction details: '||lv_NumMark2Trans1);
              END IF;  
            
            
 EXCEPTION
    WHEN NO_DATA_FOUND THEN
         DBMS_OUTPUT.put_line('There is no data or area codes based on the join condition....');
        
     WHEN OTHERS THEN
        DBMS_OUTPUT.put_line(SQLCODE||':'||SQLERRM);
    
    
END;



--SPOOL OFF





and when I execute it at the linux prompt ./ift225b.sh I see the following message:

Calling .sql file to generate the CSV report
0


and based on the exit code being "0" (which is displayed), there appears no error and when I open the .CSV file, I only see the
header information and nothing else. The header information obtained in the .csv file has the following:

MESSAGE ID TYPE CD DESCRIPTION TEXT IRF AREA CODE TRANSACTION PROCESS ID RECORD CREATE TMS TRNCR_TRNSCN_PRCS_ID RECORD_CREATE_TMS RECORD_UPDATE_TMS CREATE_USER_ID UPDATE_USER_ID MARK_SCAC_CD MARK_SCAC_NM AAR_UPDATE_DT EFCTV_DT DUNS_NBR EXPIRY_DT ICC_DOCKET_NBR TRNSCN_NBR TYPE_CD OWNER_MARK_CD OWNER_SCAC_CD ROAD_NBR REPORT_EQPMT_RLS_CD REPORT_EQPMT_PLCMNT_CD NMFTA_TRNSCN_PRCS_ID RECORD_CREATE_TMS RECORD_UPDATE_TMS CREATE_USER_ID UPDATE_USER_ID SCAC_CD AAR_UPDATE_DT TRNCR_NM ADRS_TXT CITY_NM CNTRY_NM POSTAL_ZIP_CD TRNSCN_NBR STPRV_CD


But when I run the markprocedure.sql (by removing the comments in the .sql file) in TOAD, I am able to generate a .CSV file with all
the data as expected.

Anything that I am missing that is preventing the data from being displayed? Can anyone help?
Re: Unable to get data into a CSV file [message #648146 is a reply to message #648145] Wed, 17 February 2016 13:24 Go to previous messageGo to next message
buggleboy007
Messages: 282
Registered: November 2010
Location: Canada
Senior Member
My apologies if I have posted the question in a wrong area as I see another area called "Linux" and I will post it there. I did not see that "Linux" area until now.
Re: Unable to get data into a CSV file [message #648147 is a reply to message #648145] Wed, 17 February 2016 13:27 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Eliminate, remove the whole EXCEPTION handler

DBMS_OUTPUT is wrong way to use as data presentation utility.

sqlplus scott/tiger
set colsep ,
spool emp.csv
select * from emp;
spool off
exit
Re: Unable to get data into a CSV file [message #648149 is a reply to message #648147] Wed, 17 February 2016 13:34 Go to previous messageGo to next message
buggleboy007
Messages: 282
Registered: November 2010
Location: Canada
Senior Member
@ BS: I don't want to embed the "select" statements and other processing logic in the shell script but want to call the .sql file. Will your above idea given work BS?
Re: Unable to get data into a CSV file [message #648154 is a reply to message #648149] Wed, 17 February 2016 14:03 Go to previous messageGo to next message
buggleboy007
Messages: 282
Registered: November 2010
Location: Canada
Senior Member
I was missing the terminator symbol '/' in my .sql script. After inserting it and running the shell script I was able to generate the data as desired. That fixes the issue.
Re: Unable to get data into a CSV file [message #648192 is a reply to message #648149] Thu, 18 February 2016 06:07 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
buggleboy007 wrote on Wed, 17 February 2016 13:34
@ BS: I don't want to embed the "select" statements and other processing logic in the shell script but want to call the .sql file. Will your above idea given work BS?


It's not a matter of embedding it in the shell script with input redrection vs. calling a sql script.
It's simply a matter of using the sqlplus colsep directive instead of all that unnecessary pl/sql use of cursors and dbms_output and concatenating data with literals to insert your commas.
Re: Unable to get data into a CSV file [message #648202 is a reply to message #648154] Thu, 18 February 2016 08:28 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
buggleboy007 wrote on Wed, 17 February 2016 12:03
I was missing the terminator symbol '/' in my .sql script. After inserting it and running the shell script I was able to generate the data as desired. That fixes the issue.


NEVER do in PL/SQL that which can be done in plain SQL to avoid costly & unnecessary context switch between SQL & PL/SQL
Re: Unable to get data into a CSV file [message #648222 is a reply to message #648192] Thu, 18 February 2016 20:14 Go to previous messageGo to next message
buggleboy007
Messages: 282
Registered: November 2010
Location: Canada
Senior Member
EdStevens wrote on Thu, 18 February 2016 06:07
buggleboy007 wrote on Wed, 17 February 2016 13:34
@ BS: I don't want to embed the "select" statements and other processing logic in the shell script but want to call the .sql file. Will your above idea given work BS?


It's not a matter of embedding it in the shell script with input redrection vs. calling a sql script.
It's simply a matter of using the sqlplus colsep directive instead of all that unnecessary pl/sql use of cursors and dbms_output and concatenating data with literals to insert your commas.


Then I don't know how else I could have achieved the result that I wanted i.e. retrieving master records and then passing each transaction number of master record into detail table to retrieve the final details.

The only thing I can think of is, creating a procedure and then placing all my sql,pl/sql code in that; compiling it successfully and then calling it in shell script. Yet I would still have use cursors and dbms_output and concatenating data with literals to insert my commas (by the way this was referenced/applied after reading Jonathan Gennick's Oracle SQL * Plus - The definitive guide; page 281-283)
Re: Unable to get data into a CSV file [message #648223 is a reply to message #648202] Thu, 18 February 2016 20:18 Go to previous messageGo to next message
buggleboy007
Messages: 282
Registered: November 2010
Location: Canada
Senior Member
BlackSwan wrote on Thu, 18 February 2016 08:28
buggleboy007 wrote on Wed, 17 February 2016 12:03
I was missing the terminator symbol '/' in my .sql script. After inserting it and running the shell script I was able to generate the data as desired. That fixes the issue.


NEVER do in PL/SQL that which can be done in plain SQL to avoid costly & unnecessary context switch between SQL & PL/SQL


Yeah - I concur with you and also the above quote is from Steven Feuerstein. However considering the task given and no way I could use UTL_FILE utility (because of security issues), I had to export the data in .CSV format. The only way that I could think and come up was based on what I read and applied from Jonathan Gennick's Oracle SQL * Plus - The definitive guide; page 281-283.

How else would you have accomplished it BS? BTW- I also tried your approach given by placing set colsep statement and also EXIT at the bottom. None worked. Only after placing the terminator(/) it started working.

The other thing that comes to my mind is placing the "exit" statement in the .sql file itself instead of terminator symbol and trying it out.

[Updated on: Thu, 18 February 2016 20:21]

Report message to a moderator

Re: Unable to get data into a CSV file [message #648226 is a reply to message #648223] Fri, 19 February 2016 00:20 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If you post a test case with a clear specification we may show you how to do it without PL/SQL.

Re: Unable to get data into a CSV file [message #648252 is a reply to message #648223] Fri, 19 February 2016 06:25 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
buggleboy007 wrote on Thu, 18 February 2016 20:18
BTW- I also tried your approach given by placing set colsep statement and also EXIT at the bottom. None worked. Only after placing the terminator(/) it started working.



You are confusing sql, pl/sql, and sqlplus commands.
colsep is a command to sqlplus, telling it how to format output from SELECT statements that it (sqlplus) asks the database to execute and return a result. SQL that exists inside a pl/sql block does not fit that scenario. In that case, sqlplus is asking the database to execute a pl/sql block. And within that block, results of SELECT are returned to a variable within the block, not to sqlplus.

The '/' at the end is what tells sqlplus to quit reading lines of code and submit what it has for execution.
Re: Unable to get data into a CSV file [message #648268 is a reply to message #648252] Fri, 19 February 2016 08:59 Go to previous message
buggleboy007
Messages: 282
Registered: November 2010
Location: Canada
Senior Member
EdStevens wrote on Fri, 19 February 2016 06:25
buggleboy007 wrote on Thu, 18 February 2016 20:18
BTW- I also tried your approach given by placing set colsep statement and also EXIT at the bottom. None worked. Only after placing the terminator(/) it started working.



You are confusing sql, pl/sql, and sqlplus commands.
colsep is a command to sqlplus, telling it how to format output from SELECT statements that it (sqlplus) asks the database to execute and return a result. SQL that exists inside a pl/sql block does not fit that scenario. In that case, sqlplus is asking the database to execute a pl/sql block. And within that block, results of SELECT are returned to a variable within the block, not to sqlplus.

The '/' at the end is what tells sqlplus to quit reading lines of code and submit what it has for execution.


Since SQL that exists inside a pl/sql block does not fit that scenario of colsep,I used the method shown by Jonathan Gennick via his book which solved my purpose, but what I missed was the terminator symbol (/) in my .sql script because after executing the .sql script at the SQL * PLUS, it kept hanging because of not letting the buffer know that I am done with my script. I had executed in TOAD and did not have issues but after executing in SQL * PLUS had issues; after a few minutes of posting my question here, realised the oversight.

Anyways thanks for your assistance Ed.
Previous Topic: need your help to search oracle app server 10g(9.0.4)
Next Topic: Unable to send email from Java in Linux
Goto Forum:
  


Current Time: Fri Mar 29 10:43:43 CDT 2024