Home » SQL & PL/SQL » SQL & PL/SQL » PLSQL Tables!!! A little Help Reqd
PLSQL Tables!!! A little Help Reqd [message #251078] Thu, 12 July 2007 06:32 Go to next message
punithavel
Messages: 21
Registered: May 2007
Location: Chennai
Junior Member

While I am doing customer conversion i came through validating Telephone number..

I need to remove the junk character and replace with 'X' and will update 'X' as 'x'

I got the answer.... But i need to do an anonymous block with fetching the replaced junk with 'X' and display the record using DBMS_OUTPUT

You will understand with the following block:-

declare
type vc is table of varchar2(3000) index by Binary_integer;
v_count_tel number;
v_tele vc;
cursor cc is select telephone from xx_pun_cc_stg;
num_company_rows BINARY_INTEGER := 0;
begin
--for j in cc loop
for i in cc loop
num_company_rows := num_company_rows+1;

FOR v_count_tel IN 1..25 LOOP
select REPLACE(i.TELEPHONE,SUBSTR(i.TELEPHONE,v_count_tel,1),
DECODE(SUBSTR(i.TELEPHONE,v_count_tel,1),
'0','0','1','1','2','2','3','3','4','4','5','5','6','6','7','7','8','8','9','9','X'))
into v_tele(num_company_rows) from xx_pun_cc_stg;
--dbms_output.put_line(v_tele(i));
End LOOP;
--dbms_output.put_line(v_tele(x));
end loop;
--end loop;
end;
declare
type vc is table of varchar2(3000) index by Binary_integer;
v_count_tel number;
v_tele vc;
cursor cc is select telephone from xx_pun_cc_stg;
num_company_rows BINARY_INTEGER := 0;
begin
--for j in cc loop
for i in cc loop
num_company_rows := num_company_rows+1;

FOR v_count_tel IN 1..25 LOOP
select REPLACE(i.TELEPHONE,SUBSTR(i.TELEPHONE,v_count_tel,1),
DECODE(SUBSTR(i.TELEPHONE,v_count_tel,1),
'0','0','1','1','2','2','3','3','4','4','5','5','6','6','7','7','8','8','9','9','X'))
into v_tele(num_company_rows) from xx_pun_cc_stg;
--dbms_output.put_line(v_tele(i));
End LOOP;
--dbms_output.put_line(v_tele(x));
end loop;
--end loop;
end;

Please try to solve it....

Thanks
Re: PLSQL Tables!!! A little Help Reqd [message #251084 is a reply to message #251078] Thu, 12 July 2007 06:40 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
punithavel wrote on Thu, 12 July 2007 13:32

You will understand with the following block:-

declare
...rubbish...
end;
I can't make anything of it. Try to format your code and use the correct tags.

MHE
Re: PLSQL Tables!!! A little Help Reqd [message #251090 is a reply to message #251084] Thu, 12 July 2007 06:43 Go to previous messageGo to next message
punithavel
Messages: 21
Registered: May 2007
Location: Chennai
Junior Member

Maaher wrote on Thu, 12 July 2007 17:10
punithavel wrote on Thu, 12 July 2007 13:32

You will understand with the following block:-

declare
...rubbish...
end;
I can't make anything of it. Try to format your code and use the correct tags.

MHE


declare
type vc is table of varchar2(3000) index by Binary_integer;
v_count_tel number;
v_tele vc;
cursor cc is select telephone from xx_pun_cc_stg;
num_company_rows BINARY_INTEGER := 0;
begin

num_company_rows := num_company_rows + 1

FOR v_count_tel IN 1..25 LOOP
select REPLACE(i.TELEPHONE,SUBSTR(i.TELEPHONE,v_count_tel,1),
DECODE(SUBSTR(i.TELEPHONE,v_count_tel,1),
'0','0','1','1','2','2','3','3','4','4','5','5','6','6','7','7','8','8','9','9','X'))
into v_tele(num_company_rows) from xx_pun_cc_stg;
dbms_output.put_line(v_tele(num_company_rows));
End LOOP;
end loop;
end;
  • Attachment: query.txt
    (Size: 0.61KB, Downloaded 154 times)

[Updated on: Thu, 12 July 2007 06:44]

Report message to a moderator

Re: PLSQL Tables!!! A little Help Reqd [message #251100 is a reply to message #251090] Thu, 12 July 2007 07:05 Go to previous messageGo to next message
vinoth_mohan
Messages: 3
Registered: May 2007
Junior Member
declare
type vc is table of varchar2(3000) index by Binary_integer;
v_count_tel number;
v_tele vc;
cursor cc is select telephone from xx_pun_cc_stg;

begin
--for j in cc loop
for i in cc loop


FOR v_count_tel IN 1..25 LOOP
select REPLACE(i.TELEPHONE,SUBSTR(i.TELEPHONE,v_count_tel,1),
DECODE(SUBSTR(i.TELEPHONE,v_count_tel,1),
'0','0','1','1','2','2','3','3','4','4','5','5','6','6','7','7','8','8','9','9','X'))
bulk collect into
v_tele
from xx_pun_cc_stg;

End LOOP;


end loop;

end;
Re: PLSQL Tables!!! A little Help Reqd [message #251109 is a reply to message #251090] Thu, 12 July 2007 07:15 Go to previous message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Make sure that lines of code do not exceed 80 or 100 characters when you format.
Please always post your Oracle version (4 decimals).

Regards
Michel
Previous Topic: a simple query
Next Topic: package passing values
Goto Forum:
  


Current Time: Wed Dec 07 14:24:02 CST 2016

Total time taken to generate the page: 0.10807 seconds