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?
