Home » SQL & PL/SQL » SQL & PL/SQL » Ceating a series of records with a pattern
Ceating a series of records with a pattern [message #351545] Wed, 01 October 2008 08:43 Go to next message
soham.desai@gmail.com
Messages: 21
Registered: August 2008
Junior Member
Let me simplify my problem.

I have a table tab with only one field fld (varchar2(10)). I want to insert records with values A01B01 to A01b02, A02B01 to A02B02 (i.e. A01B01, A01B02, A02B01 and A02B02) through a PL/SQL script.

Any ideas?

Thanks in advance.
Re: Ceating a series of records with a pattern [message #351550 is a reply to message #351545] Wed, 01 October 2008 08:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Insert into mytable values ('A01B01');
Insert into mytable values ('A01B02');
Insert into mytable values ('A02B01');
Insert into mytable values ('A02B02');

Regards
Michel

Re: Ceating a series of records with a pattern [message #351552 is a reply to message #351550] Wed, 01 October 2008 08:55 Go to previous messageGo to next message
soham.desai@gmail.com
Messages: 21
Registered: August 2008
Junior Member
In fact I have simplified the problem. I want to add values from A01B01 to A45B65 (i.e. A01B01 to A01B65, A02B01 to A02B65 etc). This way I will have to write 45 * 65 statements. I want to use loops to accomplish this.

Thanks
Re: Ceating a series of records with a pattern [message #351554 is a reply to message #351552] Wed, 01 October 2008 08:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> with
  2    a as (select level a from dual connect by level <= 4),
  3    b as (select level b from dual connect by level <= 6)
  4  select 'A'||to_char(a,'fm00')||'B'||to_char(b,'fm00')
  5  from a,b
  6  /
'A'||TO_
--------
A01B01
A01B02
A01B03
A01B04
A01B05
A01B06
A02B01
A02B02
A02B03
A02B04
A02B05
A02B06
A03B01
A03B02
A03B03
A03B04
A03B05
A03B06
A04B01
A04B02
A04B03
A04B04
A04B05
A04B06

24 rows selected.

Regards
Michel
Re: Ceating a series of records with a pattern [message #351558 is a reply to message #351554] Wed, 01 October 2008 09:12 Go to previous messageGo to next message
soham.desai@gmail.com
Messages: 21
Registered: August 2008
Junior Member
Thanks it works. But to insert the values, if I use the following code, it gives error 'Missing expression)

Insert into tab3 (id)
values(
With
a as (select level a from dual connect by level <= 4),
b as (select level b from dual connect by level <= 6)
select 'A'||to_char(a,'fm00')||'B'||to_char(b,'fm00')
from a,b);
Re: Ceating a series of records with a pattern [message #351560 is a reply to message #351558] Wed, 01 October 2008 09:25 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
1) Read the Documentation
2) Remove the VALUES clause.
Re: Ceating a series of records with a pattern [message #351565 is a reply to message #351560] Wed, 01 October 2008 09:51 Go to previous messageGo to next message
soham.desai@gmail.com
Messages: 21
Registered: August 2008
Junior Member
If I use the program as attached, I get ORA-32034: unsupported use of WITH clause error.
  • Attachment: sql.txt.txt
    (Size: 0.52KB, Downloaded 124 times)
Re: Ceating a series of records with a pattern [message #351566 is a reply to message #351565] Wed, 01 October 2008 10:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The first thing you have to post is your ORACLE VERSION WITH 4 DECIMALS.

Then please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.

Use SQL*Plus and copy and paste your session.

SQL> Insert into tab (id)
  2          (
  3              With
  4              a as (select level a from dual connect by level <= 4),
  5              b as (select level b from dual connect by level <= 6)
  6              select 'A'||to_char(a,'fm00')||'B'||to_char(b,'fm00')
  7              from a,b);
            from a,b)
                    *
ERROR at line 7:
ORA-32034: unsupported use of WITH clause


SQL> Insert into tab (id)
  2              With
  3              a as (select level a from dual connect by level <= 4),
  4              b as (select level b from dual connect by level <= 6)
  5              select 'A'||to_char(a,'fm00')||'B'||to_char(b,'fm00')
  6              from a,b
  7  /

24 rows created.


Finally, your WHEN OTHERS clause is a BUG.
And "WHENEVER SQLERROR EXIT 1" as it is useless.

Regards
Michel

[Updated on: Wed, 01 October 2008 10:07]

Report message to a moderator

Re: Ceating a series of records with a pattern [message #351590 is a reply to message #351566] Wed, 01 October 2008 11:54 Go to previous messageGo to next message
soham.desai@gmail.com
Messages: 21
Registered: August 2008
Junior Member
Thanks. It works nicely.
Re: Ceating a series of records with a pattern [message #351604 is a reply to message #351566] Wed, 01 October 2008 12:36 Go to previous messageGo to next message
soham.desai@gmail.com
Messages: 21
Registered: August 2008
Junior Member
One more twist in the problem!

If I want to create records from A09B01 to A16B05 (i.e. A09B01, A09B02, .... A16B05), how to modify the statement? I have tried select level a from dual connect by level in (9,16), but it does not work.
Re: Ceating a series of records with a pattern [message #351611 is a reply to message #351604] Wed, 01 October 2008 13:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
if level=1 then
level+1=2
level+2=3
level+3=4
level+...
level+8=9
Yes, I found the starting point.
I let you find what should be in "<=" to get the end point.

Regards
Michel
Re: Ceating a series of records with a pattern [message #351613 is a reply to message #351545] Wed, 01 October 2008 13:07 Go to previous message
soham.desai@gmail.com
Messages: 21
Registered: August 2008
Junior Member
Thanks Michel,

A statement which has

a as (select level+8 a from dual connect by level <= Cool,

works nicely.

Again thanks

Previous Topic: timestamp
Next Topic: Help in explanation of this
Goto Forum:
  


Current Time: Sat Dec 03 04:20:09 CST 2016

Total time taken to generate the page: 0.11068 seconds