create procedure

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
Post Reply
fjralberto
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 6
Joined: Thu, 16 Sep 2010 10:38 am
Location: Santo André - SP

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
Moderador
Moderador
Posts: 1396
Joined: Fri, 01 Feb 2008 2:06 pm
Location: Rio de Janeiro - RJ
Contact:
:D

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
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 6
Joined: Thu, 16 Sep 2010 10:38 am
Location: Santo André - SP

The difficulty is in making the PNumero when receiving a char she before being recorded on the table has to be a number
victorhugomuniz
Moderador
Moderador
Posts: 1396
Joined: Fri, 01 Feb 2008 2:06 pm
Location: Rio de Janeiro - RJ
Contact:
:D

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
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 6
Joined: Thu, 16 Sep 2010 10:38 am
Location: Santo André - SP

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
Moderador
Moderador
Posts: 1396
Joined: Fri, 01 Feb 2008 2:06 pm
Location: Rio de Janeiro - RJ
Contact:
:D

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
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 6
Joined: Thu, 16 Sep 2010 10:38 am
Location: Santo André - SP

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
Moderador
Moderador
Posts: 1396
Joined: Fri, 01 Feb 2008 2:06 pm
Location: Rio de Janeiro - RJ
Contact:
:D

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
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 6
Joined: Thu, 16 Sep 2010 10:38 am
Location: Santo André - SP

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
Moderador
Moderador
Posts: 1396
Joined: Fri, 01 Feb 2008 2:06 pm
Location: Rio de Janeiro - RJ
Contact:
:D

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; 
 
User avatar
fsitja
Rank: OraSauro
Rank: OraSauro
Posts: 611
Joined: Mon, 19 Jan 2009 4:29 pm
Location: Gaúcho no Rio de Janeiro - RJ
"The scars exist to remind us that the past was real"
Campanha: Como fazer uma pergunta e obter uma resposta.
http://tkyte.blogspot.com/2005/06/how-t ... tions.html

OCA & OCP Developer — OCE SQL Expert — OCS Data Warehousing Specialist

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
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 6
Joined: Thu, 16 Sep 2010 10:38 am
Location: Santo André - SP

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
Post Reply
  • Information
  • Who is online

    Users browsing this forum: No registered users and 5 guests