create procedure

[fjralberto]

Guys, good morning.

Someone could help me create a procedure where this must receive as a table values ??parameter.

Ex:
And these values ??are char.

variables

Select all

PCodigo      NUMBER 
PEndereco  CHAR 
PNumero     CHAR

Only in particular, the variable PNumero although it accepts char I need to be required to be validated before Save to the table as you can not go to the Values ??table other than numbers.

Can anyone give me a hand?

Thanks.

[victorhugomuniz]

What specifically are you with difficulties?



Because you can not go to table values ??other than numbers. [/quote]

This part there I do not understand .. so that the type of it and char r not number?

[fjralberto]

The difficulty is in making the PNumero when receiving a char she before being recorded on the table has to be a number

[victorhugomuniz]

of a to_number within a Begin End block .. Dai and only capture the exception released

Select all

 
ORA-01722  - INVALID_NUMBER  	 
 
It isn't a number even though you are treating it like one to trying to turn it into one 

[fjralberto]

So ... there would be like this

Select all

 
CREATE OR REPLACE PROCEDURE GERAL.INSTI_GERATITULOS_TGPR 
( 
    PTGPR_SQPROCESSO                    IN OUT NUMBER, 
    PTGPR_NUREGRECUPERADOS     IN     NUMBER, 
    PTGPR_NUREGPROCESSADOS     IN     NUMBER, 
    PTGPR_NMARQUIVO                      IN     CHAR, 
    PTGPR_PATHARQUIVO                 IN     CHAR, 
    PTGPR_DSHEADERARQUIVO        IN     CHAR,      -> Eu preciso que essa variavel aqui quando trazer uma informação seja validado como número... 
    PTGPR_SISTEMA                             IN     CHAR 
) 
AS 
 
  vBuscaInt  NUMBER; -> pra i sso, eu criei essa variável que é do tipo numerico 
 
  BEGIN 
 
    vBuscaInt := NVL(vDSHEADERHARQUIVO,0);  --> caso ele receba um valor que não seja numerico... ele vai cair na excecao lá em baixo... ÍSSO QUE EU FIZ ESTÁ CERTO? 
     
    SELECT GERAL.INTEGRA_SQ_GERATITULO.NEXTVAL 
     INTO PTGPR_SQPROCESSO 
     FROM DUAL; 
 
    INSERT INTO GERAL.TI_GERATITULOS_TGPR( 
    TGPR_SQPROCESSO        , 
    TGPR_NUREGRECUPERADOS  , 
    TGPR_NUREGPROCESSADOS  , 
    TGPR_NMARQUIVO         , 
    TGPR_PATHARQUIVO       , 
    TGPR_DSHEADERARQUIVO   , 
    TGPR_DTGERACAO         , 
    TGPR_USUARIOGERACAO    , 
    TGPR_SISTEMA 
) 
      VALUES( 
        PTGPR_SQPROCESSO        , 
        PTGPR_NUREGRECUPERADOS  , 
        PTGPR_NUREGPROCESSADOS  , 
        PTGPR_NMARQUIVO         , 
        PTGPR_PATHARQUIVO       , 
        PTGPR_DSHEADERARQUIVO    , 
        SYSDATE, 
        USER, 
        PTGPR_SISTEMA); 
    
    EXCEPTION      
        WHEN OTHERS THEN    
   
  END; 
/ 

[victorhugomuniz]

Select all

 
CREATE OR REPLACE PROCEDURE GERAL.INSTI_GERATITULOS_TGPR 
( 
    PTGPR_SQPROCESSO           IN OUT NUMBER, 
    PTGPR_NUREGRECUPERADOS     IN     NUMBER, 
    PTGPR_NUREGPROCESSADOS     IN     NUMBER, 
    PTGPR_NMARQUIVO            IN     CHAR, 
    PTGPR_PATHARQUIVO          IN     CHAR, 
    PTGPR_DSHEADERARQUIVO      IN     CHAR, 
    PTGPR_SISTEMA              IN     CHAR 
) 
AS 
 
  vBuscaInt  NUMBER; 
 
  BEGIN 
    BEGIN           
        select to_number(PTGPR_DSHEADERARQUIVO) into vBuscaInt from dual;    
   EXCEPTION      
        WHEN INVALID_NUMBER THEN  
        PTGPR_DSHEADERARQUIVO := 0; 
    END; 
    
    SELECT GERAL.INTEGRA_SQ_GERATITULO.NEXTVAL 
     INTO PTGPR_SQPROCESSO 
     FROM DUAL; 
 
    INSERT INTO GERAL.TI_GERATITULOS_TGPR( 
    TGPR_SQPROCESSO        , 
    TGPR_NUREGRECUPERADOS  , 
    TGPR_NUREGPROCESSADOS  , 
    TGPR_NMARQUIVO         , 
    TGPR_PATHARQUIVO       , 
    TGPR_DSHEADERARQUIVO   , 
    TGPR_DTGERACAO         , 
    TGPR_USUARIOGERACAO    , 
    TGPR_SISTEMA 
) 
      VALUES( 
        PTGPR_SQPROCESSO        , 
        PTGPR_NUREGRECUPERADOS  , 
        PTGPR_NUREGPROCESSADOS  , 
        PTGPR_NMARQUIVO         , 
        PTGPR_PATHARQUIVO       , 
        PTGPR_DSHEADERARQUIVO    , 
        SYSDATE, 
        USER, 
        PTGPR_SISTEMA); 
    
    EXCEPTION      
        WHEN OTHERS THEN    
  
  END; 
/  

[fjralberto]

Right ... but in this case you are sending the field as zero ... it was show ... but in made the value go as zero I want the insert not to be done ... bringing some error.

Is it possible?

[victorhugomuniz]

You do not want to do the insert if something different from numbers .. I thought you wanted to treat this as you had said it on top ..

and just take this begin exception end there .. the select into q dai goes straight to your exception of low ..


a tip ... others and half fuck because you will never know what is happening .. or So the Error Code there .. do something on your others because it has the use "deconsavible"

[fjralberto]

Ok ...

Then it would be like this !!! I understand that it will only record if it is number if it is not it will not record.

Select all

 
CREATE OR REPLACE PROCEDURE GERAL.INSTI_GERATITULOS_TGPR  
(  
    PTGPR_SQPROCESSO           IN OUT NUMBER,  
    PTGPR_NUREGRECUPERADOS     IN     NUMBER,  
    PTGPR_NUREGPROCESSADOS     IN     NUMBER,  
    PTGPR_NMARQUIVO            IN     CHAR,  
    PTGPR_PATHARQUIVO          IN     CHAR,  
    PTGPR_DSHEADERARQUIVO      IN     CHAR,  
    PTGPR_SISTEMA              IN     CHAR  
)  
AS  
 
  vBuscaInt  NUMBER;  
 
  BEGIN  
       
    SELECT TO_NUMBER(PTGPR_DSHEADERARQUIVO) 
     INTO vBuscaInt 
     FROM DUAL;     
     
    SELECT GERAL.INTEGRA_SQ_GERATITULO.NEXTVAL  
     INTO PTGPR_SQPROCESSO  
     FROM DUAL;  
 
    INSERT INTO GERAL.TI_GERATITULOS_TGPR(  
    TGPR_SQPROCESSO        ,  
    TGPR_NUREGRECUPERADOS  ,  
    TGPR_NUREGPROCESSADOS  ,  
    TGPR_NMARQUIVO         ,  
    TGPR_PATHARQUIVO       ,  
    TGPR_DSHEADERARQUIVO   ,  
    TGPR_DTGERACAO         ,  
    TGPR_USUARIOGERACAO    ,  
    TGPR_SISTEMA  
)  
      VALUES(  
        PTGPR_SQPROCESSO        ,  
        PTGPR_NUREGRECUPERADOS  ,  
        PTGPR_NUREGPROCESSADOS  ,  
        PTGPR_NMARQUIVO         ,  
        PTGPR_PATHARQUIVO       ,  
        PTGPR_DSHEADERARQUIVO    ,  
        SYSDATE,  
        USER,  
        PTGPR_SISTEMA);  
     
    EXCEPTION       
        WHEN OTHERS THEN     
   
  END;  
/  

Related to others, what do you recommend to bring a type error to the user? or log?

[victorhugomuniz]

I understand the same way that you ...

The insert will not be executed if you generate some exception before ..

Select all

 
 
    EXCEPTION 
       WHEN OTHERS THEN 
          err_code := SQLCODE; 
          err_msg := substr(SQLERRM, 1, 200); 
 
           DBMS_OUTPUT.PUT_LINE ('Exception, status=' || err_code || ' - mensagem: '|| err_msg); 
 
    END; 
 

[fsitja]

I think it's easier for you to simply declare the parameter as a number ... if the program that calls the procedure send a character other than a number, it will give an error in implicit conversion.

Select all

 
CREATE OR REPLACE PROCEDURE GERAL.INSTI_GERATITULOS_TGPR 
( 
    PTGPR_SQPROCESSO                    IN OUT NUMBER, 
    PTGPR_NUREGRECUPERADOS     IN     NUMBER, 
    PTGPR_NUREGPROCESSADOS     IN     NUMBER, 
    PTGPR_NMARQUIVO                      IN     CHAR, 
    PTGPR_PATHARQUIVO                 IN     CHAR, 
    PTGPR_DSHEADERARQUIVO        IN     NUMBER,      -> Eu preciso que essa variavel aqui quando trazer uma informação seja validado como número... 
    PTGPR_SISTEMA                             IN     CHAR 
) 
AS  

But even so ... the best is change the column type of the table to number .

If you do not have any way ... Put a check construct in the column as below:

Select all

 
SQL> -- cria tabela de exemplo 
SQL> create table ti_geratitulos_tgpr (tgpr_dsheaderarquivo varchar2(30)); 
  
Table created 
SQL> -- criação da função de verificação 
SQL> CREATE OR REPLACE FUNCTION testa_numero(p_char IN VARCHAR2) RETURN NUMBER IS 
  2    v_temp NUMBER; 
  3  BEGIN 
  4    v_temp := to_number(p_char); 
  5    RETURN 1; 
  6  EXCEPTION 
  7    WHEN invalid_number THEN 
  8      RETURN 0; 
  9  END; 
 10  / 
  
Function created 
SQL> -- criação da check constraint 
SQL> alter table ti_geratitulos_tgpr add constraint ck_header_is_number check (to_number(tgpr_dsheaderarquivo) = to_number(tgpr_dsheaderarquivo)); 
  
Table altered 
SQL> -- testa inserção na tabela 
SQL> insert into ti_geratitulos_tgpr (tgpr_dsheaderarquivo) values ('123'); 
  
1 row inserted 
SQL> insert into ti_geratitulos_tgpr (tgpr_dsheaderarquivo) values ('123.45'); 
  
1 row inserted 
SQL> insert into ti_geratitulos_tgpr (tgpr_dsheaderarquivo) values ('1,5'); 
  
insert into ti_geratitulos_tgpr (tgpr_dsheaderarquivo) values ('1,5') 
  
ORA-01722: invalid number 
SQL> insert into ti_geratitulos_tgpr (tgpr_dsheaderarquivo) values ('.2'); 
  
1 row inserted 
SQL> insert into ti_geratitulos_tgpr (tgpr_dsheaderarquivo) values ('0.4'); 
  
1 row inserted 
SQL> select tgpr_dsheaderarquivo from ti_geratitulos_tgpr; 
  
TGPR_DSHEADERARQUIVO 
------------------------------ 
123 
123.45 
.2 
0.4 

again: The correct is to change the column to number. It's simple and it's the right thing to do.

[fjralberto]

Hmmm ... I understand ... I'll test and I'll tell you ...

for the change in the column type ... It was the first thing I wanted to do ... But I do not I can because the table is part of a pattern!

Return.

ABS