Home » SQL & PL/SQL » SQL & PL/SQL » Urgent Help Required!! PL/SQL Table Doubt
icon5.gif  Urgent Help Required!! PL/SQL Table Doubt [message #306718] Sun, 16 March 2008 03:22 Go to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
Hi
All
I have modeled a simple case of a similar buisness logic that needs to be applied at my work.Need some urgent help in this,Unable to figure it out???? Sad

I need to extract the even places digit and odd places digit
from a column containg values such as this: and insert into two separate tables???????
(In real time it will contain millions of records)
SQL> select * from telephone;

TEL_NO
----------
1234567891
2134598
121146
3231114889
459876455


create or replace procedure pr_ver
AS
cursor tel_cur is
select tel_no from telephone;
counter number:=1;
--v_tel number(15):=1234567891;
TYPE tot_len IS TABLE OF telephone.tel_no%type
INDEX BY BINARY_INTEGER;
v_tot_len tot_len;
TYPE str_even IS TABLE OF telephone.tel_no%type
INDEX BY BINARY_INTEGER;
v_str_even str_even;
TYPE str_odd IS TABLE OF telephone.tel_no%type
INDEX BY BINARY_INTEGER;
v_str_odd str_odd;
begin
open tel_cur;
loop
fetch tel_cur BULK COLLECT
into
v_tot_len

limit 5;
--exit when tel_cur%notfound;
for i in 1..v_tot_len.count
loop
dbms_output.put_line(v_tot_len(i));
dbms_output.put_line(length(v_tot_len(i)));
for counter in 1..length(v_tot_len(i)) loop

dbms_output.put_line(v_tot_len.count);
if mod(counter,2)=0 then
v_str_even(i):=v_str_even(i)||substr(v_tot_len(i),counter,1);
else
v_str_odd(i):=v_str_odd(i)||substr(v_tot_len(i),counter,1);
end if;
dbms_output.put_line(v_str_even(i));


insert into temp_odd
values
(v_str_odd(i));
commit;
insert into temp_even
values
(v_str_even(i));
commit;
end loop;
end loop;
exit when tel_cur%notfound;
end loop;
close tel_cur;
exception
when no_data_found then
dbms_output.put_line(sqlerrm);
end;

I get this error:::::

SQL> exec pr_ver;
1234567891
10
5
ORA-01403: no data found

After the extraction from substr function it is unable to populate the table type variable.
(What i have found out)
May be some counter needs to be incremented but am unable to figure out.
Please help how can this be corrected.


Many Thanks
Re: Urgent Help Required!! PL/SQL Table Doubt [message #306723 is a reply to message #306718] Sun, 16 March 2008 04:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is not an expert question.
If you want an answer read and follow the guidelines, above all when they are printed in red, and also about formatting.

It is clearly stated in the forum description: "Newbies should not post to this forum!"
Expert notion is defined in the sticky: Not an EXPERT? Post in the NEWBIES forum, NOT here
Rules are described in: OraFAQ Forum Guide

In the end, there is nothing "urgent" in a forum.

Regards
Michel

[Updated on: Sun, 16 March 2008 04:19]

Report message to a moderator

Re: Urgent Help Required!! PL/SQL Table Doubt [message #306760 is a reply to message #306718] Sun, 16 March 2008 10:16 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
Apologies for not posting it in correct place,Needed help regarding this so proceeded immediately,From this forum will i get the replies?

Thanks and Regards
Re: Urgent Help Required!! PL/SQL Table Doubt [message #306762 is a reply to message #306760] Sun, 16 March 2008 10:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe if you repost in a formatted way, for myself I can't read unformatted post.
How to format is explained in the guidelines (see link in previous post).
Indent the code (See SQL Formatter).
You use the "Preview Message" button to verify.

Regards
Michel
Re: Urgent Help Required!! PL/SQL Table Doubt [message #306774 is a reply to message #306762] Sun, 16 March 2008 13:36 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Instead of using such a PL/SQL "monster" code, how about something simpler? It, though, requires PL/SQL "background" function called CONCAT_ALL (check Barbara Boehmer's post here and follow link she provided).

Here's an example (CONCAT_ALL has already been created):
SELECT  
       concat_all(concat_expr(DECODE(odd_digit , 0, NULL, odd_digit) , '')) odd,
       concat_all(concat_expr(DECODE(even_digit, 0, NULL, even_digit), '')) even
FROM
(SELECT SUBSTR('1234567891', LEVEL, 1) * SUBSTR('101010101010101', LEVEL, 1) odd_digit,
        SUBSTR('1234567891', LEVEL, 1) * SUBSTR('010101010101010', LEVEL, 1) even_digit
 FROM dual
 CONNECT BY LEVEL <= LENGTH('1234567891')
);

ODD        EVEN
---------- ----------
13579      24681

Shortly: multiply telephone number with a binary mask (1010... - I presumed that telephone numbers aren't longer than 15 digits) which starts with 1 or 0, depending on whether you want odd or even part of it. Subquery will return rows and we'll use CONCAT_ALL against this result in order to produce a string.
Re: Urgent Help Required!! PL/SQL Table Doubt [message #307360 is a reply to message #306718] Tue, 18 March 2008 10:37 Go to previous messageGo to next message
mnitu
Messages: 159
Registered: February 2008
Location: Reims
Senior Member
  1  Select translate('1234567890','02468',' ') odd,
  2         to_number(translate('1234567890','13579',' ')) even
  3*   From dual

ODD          EVEN
------ ----------
13579       24680
Re: Urgent Help Required!! PL/SQL Table Doubt [message #307363 is a reply to message #306718] Tue, 18 March 2008 10:43 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
mnitu wrote on Tue, 18 March 2008 16:37
  1  Select translate('1234567890','02468',' ') odd,
  2         to_number(translate('1234567890','13579',' ')) even
  3*   From dual

ODD          EVEN
------ ----------
13579       24680



That's not the requirement asked for:

swas_fly wrote on Sun, 16 March 2008 09:22

I need to extract the even places digit and odd places digit
from a column

Re: Urgent Help Required!! PL/SQL Table Doubt [message #307403 is a reply to message #307363] Tue, 18 March 2008 16:32 Go to previous messageGo to next message
mnitu
Messages: 159
Registered: February 2008
Location: Reims
Senior Member
Sorry, I've misted it.
Bellow is basically the same idea used by Litlefoot but it does not need CONCAT_ALL
Select replace(replace(odd,'/',''),'0','') odd,
       replace(replace(even,'/',''),'0','') even
from (
    Select level lv, 
           sys_connect_by_path(decode(mod(level,2), 1,SubStr('1234567891',level,1),0),'/') odd,
           sys_connect_by_path(decode(mod(level,2), 0,SubStr('1234567891',level,1),0),'/') even
      from dual
      connect by level <= length('1234567891')
)
Where lv = length('1234567891')

ODD        EVEN
---------- ----------
13579      24681
Re: Urgent Help Required!! PL/SQL Table Doubt [message #307579 is a reply to message #306718] Wed, 19 March 2008 03:38 Go to previous messageGo to next message
mnitu
Messages: 159
Registered: February 2008
Location: Reims
Senior Member
Supposing Oracle 10g and fixed length character strings there is a very nice solution based on regular expression
Select regexp_replace('1234567891','([0-9]).([0-9]).([0-9]).([0-9]).([0-9]).','\1\2\3\4\5') odd,
       regexp_replace('1234567891','.([0-9]).([0-9]).([0-9]).([0-9]).([0-9])','\1\2\3\4\5') even
  From dual 

Re: Urgent Help Required!! PL/SQL Table Doubt [message #307592 is a reply to message #306718] Wed, 19 March 2008 04:10 Go to previous message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
Thank for the solutions,These are really nice,precise piece of logic,I need to understand the working of it as well.

About my code in the procedure i can now do it from that as well,as i needed to initialise the variables,bcoz when it appends with the initial substr function it founds null,so i initialise it to 0.
and working.
Previous Topic: How to search text in CLOB column
Next Topic: How to compare the Varchar and Number in oracle10g
Goto Forum:
  


Current Time: Fri Dec 09 07:34:46 CST 2016

Total time taken to generate the page: 0.13862 seconds