Thanks for the question, Safdar.

Asked: April 26, 2012 - 12:07 pm UTC

Last updated: February 22, 2019 - 6:47 am UTC

Version: 11

Viewed 50K+ times! This question is

You Asked

Hi I am new to oracle plsql and want advise on a Biz scenario:

Biz want to run a shipping label report and each shipping has one record.
They will choose particular shipping record and based on provided parameter they want to see number of labels to print:
i.e. if they supplied 5 in parameter then record should repeat 5 times…:

please advise

and Tom said...

ops$tkyte%ORA11GR2> variable n number
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> exec :n := 5;

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> with data as (select level l from dual connect by level <= 5)
  2  select *
  3    from scott.dept, data
  4   order by deptno, l
  5  /

    DEPTNO DNAME          LOC                    L
---------- -------------- ------------- ----------
        10 ACCOUNTING     NEW YORK               1
        10 ACCOUNTING     NEW YORK               2
        10 ACCOUNTING     NEW YORK               3
        10 ACCOUNTING     NEW YORK               4
        10 ACCOUNTING     NEW YORK               5
        20 RESEARCH       DALLAS                 1
        20 RESEARCH       DALLAS                 2
        20 RESEARCH       DALLAS                 3
        20 RESEARCH       DALLAS                 4
        20 RESEARCH       DALLAS                 5
        30 SALES          CHICAGO                1
        30 SALES          CHICAGO                2
        30 SALES          CHICAGO                3
        30 SALES          CHICAGO                4
        30 SALES          CHICAGO                5
        40 OPERATIONS     BOSTON                 1
        40 OPERATIONS     BOSTON                 2
        40 OPERATIONS     BOSTON                 3
        40 OPERATIONS     BOSTON                 4
        40 OPERATIONS     BOSTON                 5

20 rows selected.

just create a set of N records and cartesian join to it.

Rating

(13 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Also works nicely for sequences...

Dana, April 26, 2012 - 1:06 pm UTC

SQL> create sequence s;

Sequence created.

SQL> select s.nextval from dual connect by level <=10;

   NEXTVAL
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 rows selected.

Safdar Abbas, April 26, 2012 - 1:11 pm UTC

it works. Thanks for your quick reply.

Bind variable needle

John Gasch, April 26, 2012 - 2:33 pm UTC

Tom - you defined a bind variable :n, but then neglected to use it - hardcoding '5' instead. I'm sure that was an unintentional oversight, so we'll let it slide this time... ;-)

April 26, 2012 - 3:34 pm UTC

hah, you are correct ;)

Generating Rows

A reader, April 30, 2012 - 1:20 pm UTC

DROP TABLE SK_TEMP1;

CREATE TABLE SK_TEMP1
(ITEMS VARCHAR2(1000),
NAME VARCHAR2(1000));

INSERT INTO SK_TEMP1
VALUES
('12345',
'NAME1');

INSERT INTO SK_TEMP1
VALUES
('2212,34134,1342',
'NAME2');

INSERT INTO SK_TEMP1
VALUES
('234,6751',
'NAME3');

Is it possible to get the following output efficiently with SQL? Items can have n number of items.

Item Name
-----------------------------------------
12345 NAME1
2212 NAME2
34134 NAME2
1342 NAME2
234 NAME3
6751 NAME3

May 01, 2012 - 3:29 pm UTC

ops$tkyte%ORA11GR2> select *
  2    from (select ','||items||',' I, name from t) t,
  3         table(
  4               cast(
  5                     multiset( select substr( i, instr( i, ',', 1, level )+1, instr(i,',',1,level+1)-instr(i,',',1,level)-1 )
  6                         from dual
  7                      connect by level < length(i)-length(replace(i,',','')) )
  8               as sys.odciVarchar2List )
  9                   )
 10  /

I                    NAME                           COLUMN_VALUE
-------------------- ------------------------------ --------------------
,12345,              NAME1                          12345
,2212,34134,1342,    NAME2                          2212
,2212,34134,1342,    NAME2                          34134
,2212,34134,1342,    NAME2                          1342
,234,6751,           NAME3                          234
,234,6751,           NAME3                          6751

6 rows selected.

Generating Rows....

A reader, April 30, 2012 - 1:50 pm UTC

Is the following SQL an efficient way to get the above result or is there a better solution?

WITH ITM AS
(SELECT /*+ materialize */ ITEMS,
        NAME
 FROM SK_TEMP1),
ITM_LST AS
(SELECT ITEMS, 
       SUBSTR(','||ITM.ITEMS||',', 
               INSTR(','||ITM.ITEMS||',', ',', 1, lvl.cnt)+1,
               INSTR(','||ITM.ITEMS||',', ',', 1, lvl.cnt+1) - INSTR(','||ITM.ITEMS||',', ',', 1, lvl.cnt) - 1) item,
       NAME
 FROM ITM,
      (SELECT level cnt
       FROM DUAL
       CONNECT BY LEVEL <= (SELECT MAX(LENGTH(r.ITEMS) - LENGTH(REPLACE(r.ITEMS,','))) + 1 lv
                            FROM ITM r)) lvl)
SELECT ITEMS, ITEM, NAME
FROM ITM_LST
WHERE ITEM IS NOT NULL
ORDER BY 1,2;

A reader, January 09, 2013 - 7:17 am UTC

generate duplicate for selected records

Pubudu, April 28, 2016 - 4:10 am UTC

I see in this example given by TOM all the records are duplicated. Can this be done only for selected records in a one data set?

April 28, 2016 - 10:56 am UTC

What do you mean duplicated ? I see one row per item


SQL> DROP TABLE t purge;

Table dropped.

SQL>
SQL> CREATE TABLE t
  2  (ITEMS   VARCHAR2(1000),
  3   NAME    VARCHAR2(1000));

Table created.

SQL>
SQL>  INSERT INTO t
  2   VALUES
  3   ('12345',
  4    'NAME1');

1 row created.

SQL>
SQL>  INSERT INTO t
  2   VALUES
  3   ('2212,34134,1342',
  4    'NAME2');

1 row created.

SQL>
SQL>  INSERT INTO t
  2   VALUES
  3   ('234,6751',
  4    'NAME3');

1 row created.

SQL>
SQL>
SQL> select *
  2        from (select ','||items||',' I, name from t) t,
  3             table(
  4                   cast(
  5                         multiset( select substr( i, instr( i, ',', 1, level )+1, instr(i,',',1,level+1)-instr(i,',',1,level)-1 )
  6                             from dual
  7                          connect by level < length(i)-length(replace(i,',','')) )
  8                   as sys.odciVarchar2List )
  9                       )
 10     /


I                    NAME                           COLUMN_VALUE
-------------------- ------------------------------ ------------------------------
,12345,              NAME1                          12345
,2212,34134,1342,    NAME2                          2212
,2212,34134,1342,    NAME2                          34134
,2212,34134,1342,    NAME2                          1342
,234,6751,           NAME3                          234
,234,6751,           NAME3                          6751

6 rows selected.

SQL>

Can you elaborate on what you mean ?

Lateral in 12c

Rajeshwaran Jeyabal, April 28, 2016 - 12:03 pm UTC

Connor - I hope you will be on 12c, then you can leverage cast/multiset/sys.odcinumberlist/TABLE all these jargons with LATERAL sub-queries.

demo@ORA12C> select t.* ,
  2    substr( ','||items||',' ,
  3        instr( ','||items||',' ,',',1,r)+1 ,
  4        instr( ','||items||',' ,',',1,r+1) -
  5        instr( ','||items||',' ,',',1,r) - 1) x
  6  from t , lateral ( select level r
  7              from dual
  8              connect by level <= length(items) -
  9              length(replace(items,','))+1 )
 10  /

ITEMS             NAME       X
----------------- ---------- ----------
12345             NAME1      12345
2212,34134,1342   NAME2      2212
2212,34134,1342   NAME2      34134
2212,34134,1342   NAME2      1342
234,6751          NAME3      234
234,6751          NAME3      6751

6 rows selected.

demo@ORA12C>

Inserting the results

Omer Hedvat, May 14, 2018 - 8:47 am UTC

Hi Tom,

I used your code and it all worked great but when I tried to insert the data it inserted only the 5 original rows.

what can I do?

the code I used is:

INSERT INTO table_name
with data as (select level l from dual connect by level <= 5)
select *
from scott.dept, data
order by deptno, l

even when I tried to create a table w/ this data or to select the selected query it went wrong...

CREATE TABLE table_name AS
(with data as (select level l from dual connect by level <= 5)
select *
from scott.dept, data
order by deptno, l)

OR

select * from (
with data as (select level l from dual connect by level <= 5)
select *
from scott.dept, data
order by deptno, l
)

May 14, 2018 - 3:23 pm UTC

What exactly is the output of your query? Which rows do you have in scott.dept?

Jess, February 21, 2019 - 5:53 pm UTC

Hi Colin,

I think the follow-up from 2016 "all the records are duplicated. Can this be done only for selected records in a one data set?" refers to only duplicating a subset (which is a problem I'm having).

I have a query that returns data like so (simplified):

NAME  TYPE
BOB   EMP
SAM   TMP
JOE   TMP
ROB   TMP
EVE   EMP

Because TEMPs are also EMPloyees, we want to "duplicate" only the TEMP rows (subset), so the output looks like so:

NAME  TYPE
BOB   EMP
SAM   TMP
JOE   TMP
ROB   TMP
EVE   EMP
SAM   EMP
JOE   EMP
ROB   EMP

Can't quit work out how to do it conditionally for a subset based on existing value and push a different value into the duplicate. Any ideas?

Jess, February 21, 2019 - 7:35 pm UTC

Worked it out in the end, but don't know if there is a better/more efficient way of doing it...
(But thought I'd post in case it's useful to someone else)


select  username, blah 
        case when column_value = 1 then type 
             when column_value = 2 then 'EMP' end as type 
from
(
    select name, type, odci.column_value
    from my_table,
        TABLE(
            cast(
                multiset(select level from dual connect by level < 3) 
            as sys.odcinumberlist)
        ) odci
)
where column_value = 1 or (column_value = 2 and play_type = 'TMP')

February 22, 2019 - 6:42 am UTC

Nice stuff

Jess, February 21, 2019 - 7:42 pm UTC

It would be great to make it more efficient such that only the "TMP" rows are duplicated. At the moment they're all duplicated and abandoned, which will add up once it's 500K+ base records returned :(

February 22, 2019 - 6:47 am UTC

SQL> with emp as
  2  (
  3  select 'BOB' e,'EMP' t from dual union all
  4  select 'SAM','TMP' from dual union all
  5  select 'JOE','TMP' from dual union all
  6  select 'ROB','TMP' from dual union all
  7  select 'EVE','EMP' from dual
  8  )
  9  select *
 10  from emp,
 11       lateral (select decode(level,1,'EMP',2,'TMP') revised_t
 12                from   dual
 13                connect by level <= decode(emp.t,'EMP',1,'TMP',2)
 14                );

E   T   REV
--- --- ---
BOB EMP EMP
SAM TMP EMP
SAM TMP TMP
JOE TMP EMP
JOE TMP TMP
ROB TMP EMP
ROB TMP TMP
EVE EMP EMP

8 rows selected.

Jess, February 25, 2019 - 9:10 am UTC

That's really clever! Thanks Connor :)

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Also works nicely for sequences...

Bind variable needle

Generating Rows

Generating Rows....

generate duplicate for selected records

Lateral in 12c

Inserting the results

More to Explore

PL/SQL demos

PL/SQL docs

Generating Duplicate Rows

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Also works nicely for sequences...

Bind variable needle

Generating Rows

Generating Rows....

generate duplicate for selected records

Lateral in 12c

Inserting the results

More to Explore

PL/SQL demos

PL/SQL docs