Writing Fixed Length rows in SQL*Plus
640284Dec 24 2008 — edited Dec 26 2008Hi all,
I am trying to build an extract file for our external customer. Each row in the file must be fixed length of 82 characters. The last column in the table is a social security number.
Since the social security column in the source table contains some bad or missing numbers, I must make sure to fix or at least force blank into the bad or missing ssn column.
The social security column in the source file consists of the following problems:
1) No social security available
2) Social security number is not numeric
3) Social Security is less than 9 characters
Of course if the social security is present and is numeric and contains 9 characters, I have no problem because, that row would have 82 characters. I have written a function that I use in the select statement in the SQL script. But when I run the script,if the ssn is missing, that row will be 73 characters. And if the ssn is numeric but less than 9 characters say for instance 7 characters, then that row would be 8 characters. Any ideas what I could do to ensure that each row consists of 82 characters?
Here are the scripts:
======== script begin =========
SET ECHO OFF
SET LINESIZE 30000
set pagesize 0
SET ARRAYSIZE 1
SET TRIMSPOOL ON
SET COLSEP ""
SET FEEDBACK OFF
SET HEADING OFF
SET TERMOUT OFF
spool c:\temp\test_bad_ssn.dat
select distinct empno
, ename
, job
, mgr
, hiredate
, sal
, comm
, deptno
, VALIDATE_ATG_SSN(SSN)
from emp2;
SPOOL OFF
--quit
======== script end ==========
======== function begin =======
CREATE OR REPLACE FUNCTION SEYEDGOL.VALIDATE_ATG_SSN( str IN VARCHAR2 )
RETURN VARCHAR2
IS
NUM NUMBER;
V_SSN VARCHAR2(9);
V_NO_SSN VARCHAR2(9);
BEGIN
NUM := TO_NUMBER(str);
V_NO_SSN := chr(32);
V_NO_SSN := V_NO_SSN||chr(32)||chr(32)||chr(32)||chr(32)||
chr(32)||chr(32)||chr(32)||chr(32);
-- first see if the value is numeric
FOR i IN 1 .. 9 LOOP
if substr(str,i,1) NOT in ('0','1','2','3','4','5','6','7','8','9') THEN
RETURN V_NO_SSN;
END IF;
END LOOP;
IF LENGTH(NUM) = 9 THEN
V_SSN := STR;
ELSE
V_SSN := LPAD(RPAD(STR,9,' '),9,' ');
END IF;
IF NUM is not null then
RETURN V_SSN;
ELSE
RETURN V_NO_SSN;
END IF;
EXCEPTION
WHEN OTHERS THEN
RETURN V_NO_SSN;
END;
/
======== function end =======
Thank you for your input,
Seyed