Newsgroup: it.comp.software.database
Titolo: Stored procedure + PL/SQL

Ciao a tutti,
    ho appena creato da Oracle SQL Plus 8 la seguente stored procedure:
CREATE OR REPLACE PROCEDURE AGAC.PartitaIvaOK (strPartitaIva IN VARCHAR2) AS
    P_IVA BOOLEAN;
    i INTEGER;
    iSum INTEGER;
    iWork INTEGER;
    strTmp VARCHAR2;
    strCDC VARCHAR2(1);
BEGIN
    P_IVA := FALSE;
    iSum := 0;
    strTmp := TRANSLATE(strPartitaIva,'$1234567890','$');
    IF lenght(strPartitaIva) = 11 AND strTmp = NULL THEN
          i := 1;
          WHILE i < 10 LOOP
               iWork := TO_NUMBER(SUBSTR(strPartitaIva, i+1, 1)) * 2;
               IF iWork >= 10 THEN
                  iWork := (iWork MOD 10) + 1;
               END IF;
               i := i + 1;
               iSum := iSum + iWork + TO_NUMBER(SUBSTR(strPartitaIva, i,
1));
         END LOOP;
        strCDC := (10 - (iSum MOD 10)) MOD 10;
        IF strCDC = SUBSTR(strPartitaIva, 11, 1) THEN
           P_IVA := TRUE;
        END IF;
    END IF;
END PartitaIvaOK;
La procedure è stata creata correttemente. Ma eseguendola con l'istruzione:
SQL> Exec PartitaIvaOk('01234567890');
mi restituisce l'errore:
ERRORE alla riga 1:
ORA-06550: line 1, column 7:
PLS-00905: object AGAC.PARTITAIVAOK is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
In cosa ho sbagliato?!?
Infiniti grazie a chi saprà e vorrà rispondermi all'e-mail: 
al...@freemail.it
Alberto





Ciao !


 


I think I have a solution for you.
Your Stored 
Procedure code was:
SQL> CREATE OR REPLACE PROCEDURE PartitaIvaOK 
(strPartitaIva IN VARCHAR2) AS
  2      P_IVA 
BOOLEAN;
  3      i INTEGER;
  
4      iSum INTEGER;
  
5      iWork INTEGER;
  
6      strTmp VARCHAR2;
  
7      strCDC VARCHAR2(1);
  8  
BEGIN
  9      P_IVA := 
FALSE;
 10      iSum := 
0;
 11      strTmp := 
TRANSLATE(strPartitaIva,'$1234567890','$');
 12      
IF lenght(strPartitaIva) = 11 AND strTmp = NULL 
THEN
 13            
i := 
1;
 14            
WHILE i < 10 
LOOP
 15                 
iWork := TO_NUMBER(SUBSTR(strPartitaIva, i+1, 1)) * 
2;
 16                 
IF iWork >= 10 
THEN
 17                    
iWork := (iWork MOD 10) + 
1;
 18                 
END 
IF;
 19                 
i := i + 
1;
 20                 
iSum := iSum + iWork + TO_NUMBER(SUBSTR(strPartitaIva, i,
 21  
1));
 22           END 
LOOP;
 23          strCDC 
:= (10 - (iSum MOD 10)) MOD 
10;
 24          IF strCDC 
= SUBSTR(strPartitaIva, 11, 1) 
THEN
 25             
P_IVA := TRUE;
 26          
END IF;
 27      END IF;
 28  END 
PartitaIvaOK;
 29  /


 


The problem is that your Procedure was created with 
complilation errors.
You have to change your code as follows:


 


    1. In line 6 you declared strTmp 
VARCHAR2 variable but you didn`t specified a length. 
You have to put some 
value, eg. 
        strTmp VARCHAR2 
(250);


 


    2. In line 12 you have 
written    
        IF 
lenght(strPartitaIva) = 11 AND strTmp = NULL THEN


 


this is not correct because the built-in function 
is LENGTH, not LENGHT as you`ve written
(replace the letters order 
ht->th).
This is just a "lapsus", I know, but it can make your code 
incorrect :-).
    
    3. Finally if you 
create an object in someone`s schema like AGAC, 
eg. 
  CREATE 
OR REPLACE PROCEDURE AGAC.PartitaIvaOK (strPartitaIva IN VARCHAR2) 
AS
  ...
  Begin
     
...
  End;


 


you will have to create a PUBLIC SYNONYM if you 
want to let another user (eg. ALBERTO) to execute 
procedure 
as
    EXEC PartitaIvaOK ('01234567890');


 


else if you are connected ALBERTO and want to 
execute AGAC`s procedure
you will have to use dot notation:


 


 EXEC AGAC.PartitaIvaOK 
('01234567890');


 


    4. Also I have a suggestion for 
you
Don`t use 
 IF strTmp = NULL, 
try to use 
 IF strTmp 
IS NULL


 


At the end your code should look like 
this:


 


SQL>  CREATE OR REPLACE PROCEDURE 
PartitaIvaOK (strPartitaIva IN VARCHAR2) AS
  
2       P_IVA BOOLEAN;
  
3       i INTEGER;
  
4       iSum INTEGER;
  
5       iWork INTEGER;
  
6        strTmp VARCHAR2(250);
  
7        strCDC VARCHAR2(1);
  
8    BEGIN
  9        
P_IVA := FALSE;
 10        iSum := 
0;
 11        strTmp := 
TRANSLATE(strPartitaIva,'$1234567890','$');
 12    IF 
length(strPartitaIva) = 11 AND strTmp IS NULL 
THEN
 13               
i := 
1;
 14               
WHILE i < 10 
LOOP
 15                    
iWork := TO_NUMBER(SUBSTR(strPartitaIva, i+1, 1)) * 
2;
 16                    
IF iWork >= 10 
THEN
 17                       
iWork := (iWork MOD 10) + 
1;
 18                    
END 
IF;
 19                    
i := i + 
1;
 20                    
iSum := iSum + iWork + TO_NUMBER(SUBSTR(strPartitaIva, 
i,
 21     
1));
 22              
END 
LOOP;
 23             
strCDC := (10 - (iSum MOD 10)) MOD 
10;
 24             
IF strCDC = SUBSTR(strPartitaIva, 11, 1) 
THEN
 25                
P_IVA := 
TRUE;
 26             
END IF;
 27         END 
IF;
 28     END PartitaIvaOK;


 


That`s all... After this corrections you should be 
able to run your procedure.
I hope this suggestions will help you.
You can 
contact me anytime, if you have problems...


    


        E-Mail : 
igo...@ptt.yu


 


BYE