Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Writing Fixed Length rows in SQL*Plus

640284Dec 24 2008 — edited Dec 26 2008
Hi 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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jan 23 2009
Added on Dec 24 2008
12 comments
1,010 views