PL/SQL Help

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

Code:

---

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.

---

It looks like Ebrian finished your homework.

Now you owe him a beer! ;)

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

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

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

Quote:

---

Originally Posted by gandolf989

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

Quote:

---

Originally Posted by rao

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, ' ', '')