1

I'm trying to achieve the below requirements in the filename being exported as csv
FILENAME: filename_yyyymmdd_nnnnnn_xxxxxx.csv
where
yyyymmdd = Datestamp on when the Account file was generated
nnnnnn = 6-byte random generated batch ID
xxxxxx = 6-byte zero-padded string that contains the number of records in the file

So far, I have the below query saved as a script which I run on sqlplus.

set head off;
set feedback off;
set term off;
set pagesize 0;
set linesize 3000;
set trimspool on;
set colsep ,;
set verify off;
set echo off;
ALTER SESSION SET NLS_DATE_FORMAT= 'MM-DD-YYYY';
whenever sqlerror exit sql.sqlcode;
whenever oserror exit failure;
column date_stamp new_value sys_date noprint
column rnd_num new_value random noprint
column row_count new_value rc noprint
select to_char(sysdate,'mmddYYYY') date_stamp
from dual;
select lpad(round(dbms_random.value(1,999999)),6,'0') rnd_num from dual;
/* select count(*) as row_count
from table a, table b join on a.id = b=id where condition; */
spool filepath.&sys_date..&random..&rc..&1..csv
select statement fetching the actual data;
spool off;
exit;

The problem is: I am able to generate the random no,but not able to add record count (xxxxxx part of the filename requirement). When the part commented is uncommented and run, the sql script runs but no file is generated. When it is run with the "select count(*)" part as commented, the file is generated as expected, has the random no bit(nnnnnn) but doesn't have the no of records(xxxxxx), obviously because it is commented out. How to include the no of records in the filename as well?

Aabhish
  • 21
  • 3

1 Answers1

1

So, with enough reading around the web, I found the solution. The row_count for some reason in my query was returning with a 5 empty spaces padded value then the actual row_count. Posting the answer below:

set head off;
set feedback off;
set term off;
set pagesize 0;
set linesize 3000;
set trimspool on;
set colsep ,;
set verify off;
set echo off;
ALTER SESSION SET NLS_DATE_FORMAT= 'MM-DD-YYYY';
whenever sqlerror exit sql.sqlcode;
whenever oserror exit failure;
column date_stamp new_value sys_date noprint
column rnd_num new_value random noprint
column row_count new_value rc noprint
select to_char(sysdate,'mmddYYYY') date_stamp
from dual;
select lpad(round(dbms_random.value(1,999999)),6,'0') rnd_num from dual;
select row_count from (select trim(count(*)) as row_count
from table a ...;
spool filepath.&sys_date..&random..&rc..&1..csv
select statement fetching the actual data;
spool off;
exit;
Aabhish
  • 21
  • 3