Re: How to query ...?

ganesh bora schrieb:
> hi if it helps ...??
>
> create or replace procedure numbers is
> i number :=1;
> j number :=1;
> flag number :=5;
> begin
> loop
> for j in 1..flag
> loop
> dbms_output.put(i);
> i:=i+1;
> end loop;
> if flag = 5 then
> flag :=3;
> else flag :=5;
> end if;
> dbms_output.put('*');
> dbms_output.new_line;
> exit when (i=1000);
> end loop;
> end ;
>
> On Jan 30, 2:40 pm, Jeremy <jeremy0..._at_gmail.com> wrote:

>> In article <1170138729.917043.271..._at_p10g2000cwp.googlegroups.com>,
>> Fhatoy says...
>>
>>> Dear all,
>>> How can I write the number from 1 to 1000 on the screen like the
>>> following in Oracle ?
>>> 12345*678*910111213*141516*1718192021*....
>>> Please help me...What are the * supposed to represent?
>> --
>> jeremy

Can't see a reason to resort to pl sql.

WITH t AS (
SELECT decode(mod(ROWNUM,8),0,ROWNUM||'*',5,ROWNUM||'*',ROWNUM) val ,ROWNUM rn, ROWNUM -1 prev
FROM dual
CONNECT BY LEVEL <= 1000),
t1 AS (
SELECT replace(sys_connect_by_path(val,' '),' ') part1 FROM (SELECT * FROM T
WHERE rn <= 500)
CONNECT BY PRIOR rn=prev
START WITH rn=1
),
t2 AS (
SELECT replace(sys_connect_by_path(val,' '),' ') part2 FROM (SELECT * FROM T
WHERE rn > 500)
CONNECT BY PRIOR rn=prev
START WITH rn=501
)
SELECT (MAX(part1))||MAX(part2) silly_string FROM t1,t2;

Best regards

Maxim Received on Tue Jan 30 2007 - 05:52:42 CST