Forum Stats

  • 3,782,459 Users
  • 2,254,650 Discussions
  • 7,880,082 Comments

Discussions

Please critque Function that produces the nextval

528873
528873 Member Posts: 25
edited Sep 19, 2008 8:42PM in 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!
Tagged:

Answers

  • DelfinoNunez
    DelfinoNunez Member Posts: 513
    edited Sep 19, 2008 2:35PM
    Your function specification doesn't have any parameter defined:

    CREATE OR REPLACE FUNCTION createSSN_fnc
    return VARCHAR

    and here you try to call it with a parameter:

    ssn = createSSN_fnc(*ssn_seq.nextval*)
  • Satyaki_De
    Satyaki_De Member Posts: 7,081
    In your
    ssn = createSSN_fnc(ssn_seq.nextval)
    In the original createSSN_fnc you didn't use any input parameter. But, here in this update statement you are using that. As a result of that your statement is ignored.

    I think you should check here and implement accordingly.

    Regards.

    Satyaki De.
  • 558383
    558383 Member Posts: 6,961 Silver Crown
    edited Sep 19, 2008 2:35PM
    CREATE OR REPLACE FUNCTION createSSN_fnc
    return VARCHAR2
    ssn = createSSN_fnc(ssn_seq.nextval)
    Your function is defined to have no argument and you pass 1 ....
  • Declarationwithout parameter...
    CREATE OR REPLACE FUNCTION createSSN_fnc
     return VARCHAR2
     Is 
    ...
    Call with parameter...
    ...
    createSSN_fnc(ssn_seq.nextval)
    ...
  • 528873
    528873 Member Posts: 25
    When I changed the line to ssn = createSSN_fnc instead of ssn = createSSN_fnc(ssn_seq.nextval) the procedure compiles. I don't know if this is the right way however. Can someone explain.

    Thanks
  • MichaelS
    MichaelS Member Posts: 8,424 Bronze Crown
    Your function createssn_fnc is probably supposed to be
    create or replace function createssn_fnc (ssn_seq integer)
       return varchar2
    is
       nssn   varchar2 (9);
    begin
       select '916' || lpad (ssn_seq, 6, '000') into nssn from dual;
    
       return (nssn);
    end;
    /
  • 528873
    528873 Member Posts: 25
    Thanks so much for your input; but, I want to know if there are any implications or forseen problems in doing it the way I indicated previously?

    ssn = createSSN versus ssn = createSSN(ssn_seq.nextval)

    Thanks
  • MichaelS
    MichaelS Member Posts: 8,424 Bronze Crown
    I want to know if there are any implications or forseen problems in doing it the way I indicated previously?
    No, both methods are more or less identical. If the input parameter never changes it just makes no sense to parameterize ;)
  • 528873
    528873 Member Posts: 25
    Thank you so much for all your help. Like I said before, the code compiled but I wanted to make sure that it would perform correctly and you just confirmed that it would. Again thanks.
  • William Robertson
    William Robertson Member Posts: 9,567 Bronze Crown
    WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE( 'ERROR:' ||sqlcode || sqlerrm);
    This is not a good idea.
    - DBMS_OUTPUT is a tool for optional debug messages. Depending on what calls the procedure and the settings in place it can often be silently ignored.
    - SQLERRM already contains SQLCODE. SQLCODE is just the number at the start of SQLERRM. Why would you duplicate it?
    - Without any exception handler, PL/SQL's default behaviour is to let an exception propagate up to the caller. Writing your own exception handler to catch all exceptions and do less with them is in effect an exception hider.
This discussion has been closed.