0
votes

Hi i am trying to run a file named query.sql using sqlplus on cmd but getting 'No Rows Selected' inside csv while the same query gives results when Run on Oracle SQL Developer

I have run the following command on cmd

sqlplus <username>/<password>@sid @query.sql > output.csv

The query inside query.sql is

SELECT
   SR.SOID,
   EL.SOID,
   EO.EPTNUMBER,
   EO.SLELABEL,
   EL.LOGTEXT,
   utl_raw.cast_to_varchar2(dbms_lob.substr(SR.SODATA, 3000, 1)) AS NAC_DATA 
FROM
   SORECORD SR,
   SOAPPKEY SK,
   EPTORDER EO,
   EPTLOG EL 
WHERE
   SK.APPKEYNAME = 'MSISDN' 
   AND SK.appkeyvalue = '<ctn here>' 
   AND SK.SOID = SR.SOID 
   AND SR.SOTYPE = 'NAC' 
   AND SR.Receipttimestamp LIKE '07-JAN-20' 
   AND SR.SOID = EO.SOID 
   AND EO.EPTNUMBER = EL.EPTNUMBER 
   AND EL.SOID LIKE TO_CHAR((
   Select
      SUBSTR(TO_CHAR(SR.SOID), 1, LENGTH(SR.SOID) - 1) 
   FROM
      SORECORD SR, SOAPPKEY SK 
   WHERE
      SK.APPKEYNAME = 'MSISDN' 
      AND SK.appkeyvalue = '<ctn here>' 
      AND SK.SOID = SR.SOID 
      AND SR.SOTYPE = 'NAC' 
      AND SR.Receipttimestamp LIKE '07-JAN-20')) || '%' 
      AND EL.SOID > TO_NUMBER((
      Select
         SUBSTR(TO_CHAR(SR.SOID), 1, LENGTH(SR.SOID) - 1) 
      FROM
         SORECORD SR, SOAPPKEY SK 
      WHERE
         SK.APPKEYNAME = 'MSISDN' 
         AND SK.appkeyvalue = '<ctn here>' 
         AND SK.SOID = SR.SOID 
         AND SR.SOTYPE = 'NAC' 
         AND SR.Receipttimestamp LIKE '07-JAN-20') || '0');

I tried other queries to generate csv and they were working fine. I have no clue why this one is giving 'No Rows Selected' for sqlplus cmd when this same query fetches results in Oracle SQL Developer.

Can anyone help me out in pointing out the issue?

2

2 Answers

0
votes

If SR.Receipttimestamp column's datatype is DATE, why are you comparing it to a string? '07-JAN-20' is a string, not a date. You're relying on Oracle's capabilities to implicitly convert that string into a valid date value, but - that doesn't work always. I presume that's what bothers your query.

I'd suggest you to rewrite it; a simple option - just to see whether it helps - is

where trunc(SR.Receipttimestamp) = date '2020-01-07'

i.e.

  • with trunc, "remove" time component (it'll set that column's value to 00:00 hours)
  • compare it to a date literal which is always in date 'yyyy-mm-dd' format
0
votes

@Littlefoot, I made the mentioned change and it worked but facing a few issues in the csv file now

I have used the below Spool Code:

set pagesize 0
set feed off
set term off
spool '<my path here>'

SELECT
   SR.SOID,
   EL.SOID,
   EO.EPTNUMBER,
   EO.SLELABEL,
   EL.LOGTEXT,
   utl_raw.cast_to_varchar2(dbms_lob.substr(SR.SODATA, 3000, 1)) AS NAC_DATA 
FROM
   SORECORD SR,
   SOAPPKEY SK,
   EPTORDER EO,
   EPTLOG EL 
WHERE
   SK.APPKEYNAME = 'MSISDN' 
   AND SK.appkeyvalue = '07996703863' 
   AND SK.SOID = SR.SOID 
   AND SR.SOTYPE = 'NAC' 
   AND trunc(SR.Receipttimestamp) = date '2020-01-07' 
   AND SR.SOID = EO.SOID 
   AND EO.EPTNUMBER = EL.EPTNUMBER 
   AND EL.SOID LIKE TO_CHAR((
   Select
      SUBSTR(TO_CHAR(SR.SOID), 1, LENGTH(SR.SOID) - 1) 
   FROM
      SORECORD SR, SOAPPKEY SK 
   WHERE
      SK.APPKEYNAME = 'MSISDN' 
      AND SK.appkeyvalue = '07996703863' 
      AND SK.SOID = SR.SOID 
      AND SR.SOTYPE = 'NAC' 
      AND trunc(SR.Receipttimestamp) = date '2020-01-07')) || '%' 
      AND EL.SOID > TO_NUMBER((
      Select
         SUBSTR(TO_CHAR(SR.SOID), 1, LENGTH(SR.SOID) - 1) 
      FROM
         SORECORD SR, SOAPPKEY SK 
      WHERE
         SK.APPKEYNAME = 'MSISDN' 
         AND SK.appkeyvalue = '07996703863' 
         AND SK.SOID = SR.SOID 
         AND SR.SOTYPE = 'NAC' 
         AND trunc(SR.Receipttimestamp) = date '2020-01-07') || '0');

spool off

and CMD used

sqlplus <username>/<password>@sid @<path of spool txt>

Now I am getting csv data as below

5764377,5764371,1,EEVMS,                                                        
Tue Jan 07 08:29:49:887 2020SOAP MESSAGE SENT:                                  
<S:Envel                                                                        
                                                                                
5764377,5764375,1,EEVMS,                                                        
Tue Jan 07 08:30:49:900 2020SOAP MESSAGE SENT:                                  
<S:Envel                                                                        
                                                                                
5764377,5764376,1,EEVMS,                                                        
Tue Jan 07 08:31:50:003 2020SOAP MESSAGE SENT:                                  
<S:Envel            

Now i am facing 2 issues in this:

  1. I am getting partial data from EL.LOGTEXT whose data type is CLOB
  2. Data column of utl_raw.cast_to_varchar2(dbms_lob.substr(SR.SODATA, 3000, 1)) AS NAC_DATA is all together missing from the csv

Here is the snapshot of query data on SQL Developer. I actually want the data in this tabular format in my CSV:

enter image description here