Format Mask para To_char

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
Avatar do usuário
alef
Rank: Analista Pleno
Rank: Analista Pleno
Mensagens: 119
Registrado em: Ter, 06 Nov 2007 2:45 pm
Localização: Patos de Minas - MG
Alexandre Matos
Patos de Minas - MG
Do interior de Minas para o resto do Mundo

Galera, da uma ajudinha ai, preciso fazer uma formatação que não esta nos padroes do to_char, seria por exemplo criar uma mascara que varia de cpf com os pontos de traços e ou na mesma coluna aparecer o cnjp com seus devidos pontos, traços e barras. eu tentei fazer esse exemplo aqui.

Selecionar tudo

select 
    a.empresa, 
    decode(nvl(a.cpf, a.cgc), a.cpf, to_char(a.cpf,00"."000"."000"-"00), to_char(a.cgc, 00"."000"."000"/"0000"-"00))  doc  
 from 
   tb_empresa a
 where
   a.empresa is not null;
a estrutura é a seguinte
tb_pessoa
empresa varchar2(60),
cpf number(11),
cgc number(14)

mas a duvida acontece no to_char, se essa forma que eu usei esta correta.
Trevisolli
Moderador
Moderador
Mensagens: 2016
Registrado em: Qua, 12 Jan 2005 3:25 pm
Localização: Araraquara - SP
Abraço,

Trevisolli
OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional
Araraquara-SP

Brother, beleza velho?

Cara, tem que ser resolvido via SQL mesmo?
Não tem como jogar num LOOP e tratar via PL/SQL?
Avatar do usuário
alef
Rank: Analista Pleno
Rank: Analista Pleno
Mensagens: 119
Registrado em: Ter, 06 Nov 2007 2:45 pm
Localização: Patos de Minas - MG
Alexandre Matos
Patos de Minas - MG
Do interior de Minas para o resto do Mundo

na verdade eu precisei disso no forms, ai consegui fazer, mas seria interessante fazer isso via sql, não acha?

no forms eu fiz o select empresa, cpf, cgc into a,b,c from tb_empresa where rownum = 1

depois verifiquei qual dos duas variaveis tinha sido setada,

Selecionar tudo

if cpf is null then
  set_item_property( 'bloco.doc', FORMAT_MASK, '00"."000"."000"/"0000"-"00');
  :bloco.doc := cgc;
else
  set_item_property( 'bloco.doc', FORMAT_MASK, '00"."000"."000"-"00');
  :bloco.doc := cpf;
end if; 
e pronto, ai agora dependendo do valor ele coloca a mascara no campo doc.

mas no sql que faz ficar interessante.
desafios.
Trevisolli
Moderador
Moderador
Mensagens: 2016
Registrado em: Qua, 12 Jan 2005 3:25 pm
Localização: Araraquara - SP
Abraço,

Trevisolli
OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional
Araraquara-SP

Brother, beleza meu velho?
Segue abaixo aí.. acho q te serve.
Só seguir a mesma linha de raciocício do CPF pro teu CNPJ.

Selecionar tudo

-- 99977766655 = Teu campo da base 
--               Se for de tamanho 11, é CPF, senão, CNPJ
SELECT  DECODE(LENGTH(99977766655),11, SUBSTR(99977766655,1,3)||'.'||
                                       SUBSTR(99977766655,4,3)||'.'||
                                       SUBSTR(99977766655,7,3)||'-'||
                                       SUBSTR(99977766655,10,2)   -- CPF 
                                     ,0) docto -- Formatar CNPJ aqui ... 
FROM dual                                      ;
Qualquer coisa, manda pra gente.
Avatar do usuário
alef
Rank: Analista Pleno
Rank: Analista Pleno
Mensagens: 119
Registrado em: Ter, 06 Nov 2007 2:45 pm
Localização: Patos de Minas - MG
Alexandre Matos
Patos de Minas - MG
Do interior de Minas para o resto do Mundo

Fiz assim

Selecionar tudo

SELECT  DECODE(LENGTH(nvl(cpf,cgc)),11, SUBSTR(nvl(cpf,cgc),0,3)||'.'||
                                       SUBSTR(nvl(cpf,cgc),3,3)||'.'||
                                       SUBSTR(nvl(cpf,cgc),6,3)||'-'||
                                       SUBSTR(nvl(cpf,cgc),9,2)   -- CPF
                                     , SUBSTR(nvl(cpf,cgc),0,3)||'.'|| -- CNPJ
                                       SUBSTR(nvl(cpf,cgc),3,3)||'.'||
                                       SUBSTR(nvl(cpf,cgc),6,3)||'/'||
                                       SUBSTR(nvl(cpf,cgc),9,4)||'-'||
                                       SUBSTR(nvl(cpf,cgc),13)) docto 
FROM tb_empresa
ai deu certo!!!
^^

vlw Trevis
Avatar do usuário
alef
Rank: Analista Pleno
Rank: Analista Pleno
Mensagens: 119
Registrado em: Ter, 06 Nov 2007 2:45 pm
Localização: Patos de Minas - MG
Alexandre Matos
Patos de Minas - MG
Do interior de Minas para o resto do Mundo

tava bom d+ pra ser verdade....

enquanto não botei o sql a prova, que não econtrei as falhas, hehehehehe


o oracle não armazena 0 a esquerda. então se um cpf por exemplo 012345678 90 e dar um length na coluna, esse valor vai ter apenas 10 bytes, ou seja? nesse padrão de mascara ele não será nem um cpf, nem um cnpj, e outro detalhe, se um cnpj tambem começar por 0 e tiver 0 nos tres primeiros digitos, o cnpj vira um cpf.



:(


agora ta esquentando o desafio.
jucruzjc
Rank: Programador Sênior
Rank: Programador Sênior
Mensagens: 57
Registrado em: Qui, 11 Jan 2007 10:26 am
Localização: Santa Bárbaba d'Oeste

Oi!

Não sei se entendi o que você disse... mas e se você usar o LPAD/ RPAD nos campos determinando o tamanho e o "0".... não funciona? :?:




Até mais :wink:

--
Juliana Cr. Cruz
jucruzjc
Rank: Programador Sênior
Rank: Programador Sênior
Mensagens: 57
Registrado em: Qui, 11 Jan 2007 10:26 am
Localização: Santa Bárbaba d'Oeste

oi dinovu!

Achei algo interessante aqui....

Selecionar tudo

SELECT 
Decode(cpf, NULL,NULL, Translate(To_Char(cpf/100 
                     ,'000,000,000.00') 
                     ,',.' 
                     ,'.-')) cpf_com_mascara, 
                     
Decode(cnpj,NULL,NULL, 
       REPLACE(REPLACE(REPLACE(To_Char(LPad(REPLACE(cnpj,' ') ,14 ,'0') ,'00,000,000,0000,00') 
                               ,',','.') ,' ') 
              ,'.'||Trim(To_Char(Trunc(Mod(LPad(cnpj,14,'0') 
                                      ,1000000)/100) 
                                ,'0000'))||'.' 
              ,'/'||Trim(To_Char(Trunc(Mod(LPad(cnpj,14,'0') 
                                       ,1000000)/100) 
                                ,'0000'))||'-')) cnpj_com_mascara 
FROM ( 
  (SELECT '12345678912' cpf, '12345678000189' cnpj FROM dual));

-- Retirar a máscara 
SELECT REPLACE(Translate('012.365.478-56','.-','  '),' ') FROM dual; 
SELECT REPLACE(Translate('12.365.478/0001-56','./-','   '),' ') FROM dual; 

Fica de dica galera!!! :wink:


bjão

Juliana Cr. Cruz
Avatar do usuário
alef
Rank: Analista Pleno
Rank: Analista Pleno
Mensagens: 119
Registrado em: Ter, 06 Nov 2007 2:45 pm
Localização: Patos de Minas - MG
Alexandre Matos
Patos de Minas - MG
Do interior de Minas para o resto do Mundo

grande Juliana!!!!

Parabens!!! otima solução para esse desafio,

Depois vou colocar outro aqui.

na verdade esses desafios, são mais duvidas de uma mente ociosa. heheheheh


muito obrigado e PARABENS mais uma vez!!!

:-o
Avatar do usuário
alef
Rank: Analista Pleno
Rank: Analista Pleno
Mensagens: 119
Registrado em: Ter, 06 Nov 2007 2:45 pm
Localização: Patos de Minas - MG
Alexandre Matos
Patos de Minas - MG
Do interior de Minas para o resto do Mundo

agora para trazer esse select em um LOV, onde traz a o nome e o documento(cpf/cnpj).

Selecionar tudo

 SELECT
nvl(Decode(cpf, NULL,NULL, Translate(To_Char(cpf/100
                     ,'000,000,000.00')
                     ,',.'
                     ,'.-')) ,
                     
Decode(cnpj,NULL,NULL,
       REPLACE(REPLACE(REPLACE(To_Char(LPad(REPLACE(cnpj,' ') ,14 ,'0') ,'00,000,000,0000,00')
                               ,',','.') ,' ')
              ,'.'||Trim(To_Char(Trunc(Mod(LPad(cnpj,14,'0')
                                      ,1000000)/100)
                                ,'0000'))||'.'
              ,'/'||Trim(To_Char(Trunc(Mod(LPad(cnpj,14,'0')
                                       ,1000000)/100)
                                ,'0000'))||'-')) ) doc, empresa
FROM tbempresa 
Jisaias
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Mensagens: 3
Registrado em: Qui, 20 Set 2018 11:05 am

Olá Pessoal,

Estou tentando fazer isso no Oracle Forms e estou tendo alguns erros. Estou gravando o campo limpo no BD, sem máscaras, só números, no entanto, além desse campo ser uma PK, ele foi criado como VARCHAR2. Tentei várias maneiras de apresentar o campo com máscara para o usuário e gravá-lo sem a máscara, porém sem sucesso. Vale lembrar que uso o mesmo campo tanto para CPF quanto para CNPJ, me valendo de uma check box, que se checada diz que é pessoa física, se não checada, diz que é pessoa jurídica. Será que estou usando as triggers certas? Segue o problema:

1-Para apresentar o campo formatado quando trago ele na tela, estou codificando na trigger "When-New-Record-Instance" a nível de bloco.

Selecionar tudo

IF :VIEW_CLI_ISAIAS.CLI_TIPO_ID = 'F' THEN
	
   l_prc_alerta('Entrou como CPF');		  
   /*SELECT Decode(:VIEW_CLI_ISAIAS.CLI_ID, NULL, NULL, 
	  			 Translate(To_Char(:VIEW_CLI_ISAIAS.CLI_ID/100, 
	  			           '000,000,000.00'), ',.', '.-')) 
	  			 into :VIEW_CLI_ISAIAS.CLI_ID 
	  			 from dual;*/
	
		 /* SELECT  DECODE(LENGTH(:VIEW_CLI_ISAIAS.cli_id),14, SUBSTR(:VIEW_CLI_ISAIAS.cli_id,1,2)||'.'||
                                       								 SUBSTR(:VIEW_CLI_ISAIAS.cli_id,3,3)||'.'||
                                       								 SUBSTR(:VIEW_CLI_ISAIAS.cli_id,5,3)||'/'||
                                       								 SUBSTR(:VIEW_CLI_ISAIAS.cli_id,8,4)||'-'||                                       
                                       								 SUBSTR(:VIEW_CLI_ISAIAS.cli_id,12,2)
                                     									 ,0)
                                     									 into :VIEW_CLI_ISAIAS.CLI_ID 
	  			 																						from dual;*/ 														  			 																					
	  			 																						
	SET_ITEM_PROPERTY('CLI_ID', FORMAT_MASK, '000"."000"."000"-"00');
	  			 
ELSIF :VIEW_CLI_ISAIAS.CLI_TIPO_ID = 'J' THEN 
	
		l_prc_alerta('Entrou como CNPJ');
		 /* SELECT  DECODE(LENGTH(:VIEW_CLI_ISAIAS.cli_id),14, SUBSTR(:VIEW_CLI_ISAIAS.cli_id,1,2)||'.'||
                                       								 SUBSTR(:VIEW_CLI_ISAIAS.cli_id,3,3)||'.'||
                                       								 SUBSTR(:VIEW_CLI_ISAIAS.cli_id,5,3)||'/'||
                                       								 SUBSTR(:VIEW_CLI_ISAIAS.cli_id,8,4)||'-'||                                       
                                       								 SUBSTR(:VIEW_CLI_ISAIAS.cli_id,12,2)
                                     								 ,0)
                                     								 into :VIEW_CLI_ISAIAS.CLI_ID 
 	  			 								 from dual;*/ 														 
 
	set_item_property('CLI_ID', FORMAT_MASK, '00"."000"."000"/"0000"-"00');
	
	
END IF;
ERRO APRESENTADO:
FRM-40105: Unable to resolve reference to item 45704205836 <------(isso é um CPF)

2-Tiro a máscara para gravação na trigger "Pre-Insert" e "Pre-Update" também a nível de bloco (isso está dando certo!).

Selecionar tudo

	IF :VIEW_CLI_ISAIAS.CLI_TIPO_ID = 'F' THEN
		
		SELECT REPLACE(Translate(:VIEW_CLI_ISAIAS.CLI_ID,'.-','  '),' ')
		INTO :VIEW_CLI_ISAIAS.CLI_ID
		FROM dual;
	
	ELSIF	:VIEW_CLI_ISAIAS.CLI_TIPO_ID = 'J' THEN
	
		SELECT REPLACE(Translate(:VIEW_CLI_ISAIAS.CLI_ID,'./-','   '),' ')
		INTO :VIEW_CLI_ISAIAS.CLI_ID
		FROM dual;
	
	END IF;	
3-Para apresentar um campo que está sendo inserido ou atualizado para o usuário, codifico a máscara na trigger "When-Validate_Item" do Item em questão.

Selecionar tudo

  IF :VIEW_CLI_ISAIAS.CLI_TIPO_ID = 'F' THEN
  	
  	SET_ITEM_PROPERTY(:VIEW_CLI_ISAIAS.CLI_ID, FORMAT_MASK, '000"."000"."000"-"00');
	  
		/*SELECT  DECODE(LENGTH(:VIEW_CLI_ISAIAS.cli_id),11, SUBSTR(:VIEW_CLI_ISAIAS.cli_id,1,3)||'.'||
                                      								 SUBSTR(:VIEW_CLI_ISAIAS.cli_id,4,3)||'.'||
                                      								 SUBSTR(:VIEW_CLI_ISAIAS.cli_id,7,3)||'-'||
                                      								 SUBSTR(:VIEW_CLI_ISAIAS.cli_id,10,2)  
                                     									 ,0)
		 																									 into :VIEW_CLI_ISAIAS.CLI_ID 
	  			 																						from dual;*/  																			  			 																						                                     
	 
	ELSIF :VIEW_CLI_ISAIAS.CLI_TIPO_ID = 'J' THEN	
		
		SET_ITEM_PROPERTY(:VIEW_CLI_ISAIAS.CLI_ID, FORMAT_MASK, '00"."000"."000"/"0000"-"00');	  
	  
	  /*SELECT  DECODE(LENGTH(:VIEW_CLI_ISAIAS.cli_id),14, SUBSTR(:VIEW_CLI_ISAIAS.cli_id,1,2)||'.'||
                                       								 SUBSTR(:VIEW_CLI_ISAIAS.cli_id,3,3)||'.'||
                                       								 SUBSTR(:VIEW_CLI_ISAIAS.cli_id,5,3)||'/'||
                                       								 SUBSTR(:VIEW_CLI_ISAIAS.cli_id,8,4)||'-'||                                       
                                       								 SUBSTR(:VIEW_CLI_ISAIAS.cli_id,12,2)
                                     									 ,0)
                                     									 into :VIEW_CLI_ISAIAS.CLI_ID 
	  			 																						from dual;*/ 		 																						
	  
  END IF;
ERRO APRESENTADO:
FRM-40735: WHEN-VALIDATE-ITEM trigger raised unhandled exception ORA-01722

Percebam que tentei de várias maneiras em nenhuma delas consigo ter sucesso conforme os erros reportados!
Me ajudem aí por favor!
DanielNN
Moderador
Moderador
Mensagens: 641
Registrado em: Seg, 03 Set 2007 3:26 pm
Localização: Fortaleza - CE
att,

Daniel N.N.

Olá, por que você não cria um outro campo apenas para ficar tratando exibição da mascara e deixa o outro(a PK) invisível gravando apenas os números. Assim você não precisa ficar aplicando função/trigger direto, diminuindo problemas.
Jisaias
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Mensagens: 3
Registrado em: Qui, 20 Set 2018 11:05 am

Consegui aqui.

A empresa usa muitas libs internas, mas eu consegui entender os processos e consegui achar uma solulção.
Obrigado pela ajuda pessoal.
Responder
  • Informação
  • Quem está online

    Usuários navegando neste fórum: Nenhum usuário registrado e 7 visitantes