SQL & PL/SQL

Hello,

I realize that this is not a SQL/PLSQL workshop, but I am in need of some assistance with the following code. I am sure it is something that I overlooked or 'just don't understand.'


These 2 functions compiles just fine (scroll down).

CREATE OR REPLACE FUNCTION createSSN_fnc
return VARCHAR2
Is
nSSN VARCHAR2(9);
Begin
Select
'916' || lpad(ssn_seq.nextval, 6, '000')
INTO
nSSN
FROM
dual;
return(nSSN);
end;
/

CREATE OR REPLACE FUNCTION pg_valid_ssn_fnc
(p_ssn IN VARCHAR2)

return VARCHAR2
IS
lv_valid VARCHAR2(1);
BEGIN
IF
substr(p_ssn,1,3) = '000'
OR
substr(p_ssn,1,3) between '650' and '699'
OR
p_ssn = ' '
OR
p_ssn is NULL
OR
substr(p_ssn,1,3) between '729' and '999'
OR
translate(p_ssn,'012345678', '999999999') != '999999999'
THEN
lv_valid := 'N';
ELSIF
substr(p_ssn,1,3) = '916'
THEN
lv_valid := 'Y';
END IF;

return(lv_valid);

END pg_valid_ssn_fnc;
/

When I call this function in the following procedure I get these errors. What am I missing here?

CREATE or REPLACE PROCEDURE pgUpdateSSN_prc
AS
cursor student_cur IS
SELECT distinct
ce.student_id,
ce.ssn
FROM
dev_sy_district_defaults sd,
dev_st_enrollment st,
dev_ce_family_member ce
WHERE
st.student_id = ce.student_id
AND
st.SCHOOL_YEAR = sd.CURRENT_SCHOOL_YEAR
AND
st.is_active_ind = 1
AND
pg_valid_ssn_fnc(ce.ssn) = 'N';

student_rec student_cur%ROWTYPE;

process_counter NUMBER := 0;

BEGIN
FOR student_rec in student_cur
LOOP

DBMS_OUTPUT.PUT_LINE ('start Updating Students');

UPDATE
dev_ce_family_member
SET
ssn = createSSN_fnc(ssn_seq.nextval)
WHERE
pg_valid_ssn_fnc(student_rec.ssn) = 'N';

process_counter := process_counter + 1;

END loop;

COMMIT;

DBMS_OUTPUT.PUT_LINE ('Total records Processed: ' || process_counter);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE( 'ERROR:' ||sqlcode || sqlerrm);

END;
/


LINE/COL ERROR
-------- -----------------------------------------------------------------
30/12 PL/SQL: SQL Statement ignored
33/14 PL/SQL: ORA-00904: "CREATESSN_FNC": invalid identifier
33/14 PLS-00306: wrong number or types of arguments in call to
'CREATESSN_FNC'

Thanks in advance!