PL/SQL Help

[ebrian]

A slight mod to gandolf's solution should provide that to you:

SQL> DECLARE
  2     v_string  VARCHAR2(2000) :=  REPLACE('001 0072053 60 3716796', ' ', '');
  3     i         PLS_INTEGER;
  4     digitSum  PLS_INTEGER := 0;
  5    BEGIN
  6     i := 1;
  7     WHILE i <= length(v_string)
  8     LOOP
  9        digitSum := digitSum + substr(v_string, i, 1);
 10        i := i + 2;
 11     END LOOP;
 12
 13     digitSum := digitSum * 3;
 14
 15     i := 0;
 16     WHILE i <= length(v_string)
 17     LOOP
 18        digitSum := digitSum + substr(v_string, i, 1);
 19        i := i + 2;
 20     END LOOP;
 21
 22     DBMS_OUTPUT.PUT_LINE('The check digit is: ' || MOD(digitSum, 10));
 23    END;
 24    /
The check digit is: 5

PL/SQL procedure successfully completed.

[gandolf989]

It looks like Ebrian finished your homework.

Now you owe him a beer!

[rao]

I modified the pl/sql to catch the string in variables i am getting the following error. Can anyone please help

SQL> CREATE OR REPLACE FUNCTION GWMS_CHKDIGT_NBR_CALC
2 RETURN NUMBER IS
3 v_string VARCHAR2(2000); --:= REPLACE('00 0 0072053 60 3716796', ' ', '');
4 i PLS_INTEGER;
5 digitSum PLS_INTEGER := 0;
6 vPalletNum VARCHAR2(7) ;
7 vCenterNumber VARCHAR2(15);
8 BEGIN
9
10 --PalletNumber
11
12 SELECT 1000000
13 INTO vPalletNum
14 FROM dual;
15
16 --CenterNumber
17
18 SELECT Center_Number
19 INTO vCenterNumber
20 FROM UTLO_S s,UTLS u
21 WHERE s.Center_Letters = u.Site;
22 v_string := REPLACE('00 0 0072053||vCenterNumber||vPalletNum||', '||', '||');
23
24 i := 1;
25 WHILE i < length(v_string)
26 LOOP
27 digitSum := digitSum + substr(v_string, i, 1);
28 i := i + 2;
29 END LOOP;
30
31 digitSum := digitSum * 3;
32
33 i := 0;
34 WHILE i < length(v_string)
35 LOOP
36 digitSum := digitSum + substr(v_string, i, 1);
37 i := i + 2;
38 END LOOP;
39
40 RETURN MOD(digitSum, 10);
41 END GWMS_CHKDIGT_NBR_CALC;
42 /

Function created.

SQL> select GWMS_CHKDIGT_NBR_CALC from dual;
select GWMS_CHKDIGT_NBR_CALC from dual
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "GXU1.GWMS_CHKDIGT_NBR_CALC", line 27
ORA-06512: at line 1

[rao]

I modified the PL/SQL but i am getting the following error. Can anyone please help me.

SQL> CREATE OR REPLACE FUNCTION GWMS_CHKDIGT_NBR_CALC
2 RETURN NUMBER IS
3 v_string VARCHAR2(2000); --:= REPLACE('00 0 0072053 60 3716796', ' ', '');
4 i PLS_INTEGER;
5 digitSum PLS_INTEGER := 0;
6 vPalletNum VARCHAR2(7) ;
7 vCenterNumber VARCHAR2(15);
8 BEGIN
9
10 --PalletNumber
11
12 SELECT 1000000
13 INTO vPalletNum
14 FROM dual;
15
16 --CenterNumber
17
18 SELECT Center_Number
19 INTO vCenterNumber
20 FROM UTLO_S s,UTLS u
21 WHERE s.Center_Letters = u.Site;
22 v_string := REPLACE('00 0 0072053||vCenterNumber||vPalletNum||', '||', '||');
23
24 i := 1;
25 WHILE i < length(v_string)
26 LOOP
27 digitSum := digitSum + substr(v_string, i, 1);
28 i := i + 2;
29 END LOOP;
30
31 digitSum := digitSum * 3;
32
33 i := 0;
34 WHILE i < length(v_string)
35 LOOP
36 digitSum := digitSum + substr(v_string, i, 1);
37 i := i + 2;
38 END LOOP;
39
40 RETURN MOD(digitSum, 10);
41 END GWMS_CHKDIGT_NBR_CALC;
42 /

Function created.

SQL> select GWMS_CHKDIGT_NBR_CALC from dual;
select GWMS_CHKDIGT_NBR_CALC from dual
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "GXU1.GWMS_CHKDIGT_NBR_CALC", line 27
ORA-06512: at line 1

[gandolf989]

You aren;t really removing the spaces. Use dbms_output to show exactly what is in v_string.

[rao]

You aren;t really removing the spaces. Use dbms_output to show exactly what is in v_string.

Can you please help me...I am really struglling

[gandolf989]

Can you please help me...I am really struglling

Look at the what the function does:
http://download.oracle.com/docs/cd/B...134.htm#i78608

Make sure that the search string is a space, and the replace string is empty.
Also make sure that you concatenate the variables properly.
REPLACE('00 0 0072053'||vCenterNumber||vPalletNum, ' ', '')