ODI debugging

With RMS 13.2 Oracle introduced new Retail Analytics (RA) application, which depreciated old Oracle Retail Data Warehouse. RA is using Oracle Data Integrator (ODI) application to extract/transform/load data from different sources instead of flat files and RETL processing.

ODI has a user friendly GUI, however when it comes to debugging and checking what is really going with yours Scenario it means a lot of clicking and switching of the windows.   As a simple guy I like to use old school SQL queries to parse debug information straight from ODI database.

This query will return all information about your session and tasks executed with additional executable and error information provided. SQL MODEL was used to flatten the information so it can be readable. Please bare in mind that it is still work in progress (a little). The parameter is yours ODI session number.

Columns EXECUTABLE will show what was executed and ERROR will show the error.  I cant put everything into one column because in PL/SQL it is impossible to cast CLOBS on a fly. So just copy all content from EXECUTABLE for particular task in another window and enjoy debugging!


SELECT executable_sql.sess_name,
executable_sql.sess_no,
executable_sql.nno,
executable_sql.nb_run,
executable_sql.scen_task_no,
executable_sql.task_status,
executable_sql.task_end,
executable_sql.task_name1,
executable_sql.task_name2,
executable_sql.task_name3,
executable_sql.executable0,
executable_sql.executable1,
executable_sql.executable2,
executable_sql.executable3,
executable_sql.executable4,
executable_sql.executable5,
executable_sql.executable6,
error_sql.error1,
error_sql.error2,
error_sql.error3,
error_sql.error4,
error_sql.error5
FROM
(SELECT sess_name,
sess_no,
nno,
nb_run,
scen_task_no,
task_status,
task_end,
task_name1,
task_name2,
task_name3,
max(output0)executable0,
max(output1)executable1,
max(output2)executable2,
max(output3)executable3,
max(output4)executable4,
max(output5)executable5,
max(output6)executable6
FROM
(
SELECT sess_name,
sess_no,
nno,
nb_run,
scen_task_no,
task_beg,
task_end,
task_status,
task_name1,
task_name2,
task_name3,
output0,
output1,
output2,
output3,
output4,
output5,
output6,
txt_count,
position
FROM (SELECT sess.sess_name,
task_log.sess_no,
task_log.nno,
task_log.nb_run,
task_log.scen_task_no,
task_log.task_beg,
task_log.task_end,
task_log.task_status,
task.task_name1,
task.task_name2,
task.task_name3,
row_number() over (PARTITION BY task_log.sess_no, task_log.nno, task_log.nb_run, task_log.scen_task_no order by txt_log.txt_type desc, txt_log.txt_ord asc) position,
txt_log.txt val,
COUNT(1) over(PARTITION BY task_log.sess_no, task_log.nno, task_log.nb_run, task_log.scen_task_no) txt_count
FROM snp_session sess,
SNP_SESS_TASK task,
SNP_SESS_TASK_LOG task_log,
SNP_SESS_TXT_LOG txt_log
WHERE task_log.sess_no = txt_log.sess_no
AND sess.sess_no = task.sess_no
AND task.nno = task_log.nno
AND task.scen_task_no = task_log.scen_task_no
AND task_log.nno = txt_log.nno
AND task_log.nb_run = txt_log.nb_run
AND task_log.scen_task_no = txt_log.scen_task_no
AND sess.sess_no = task_log.sess_no
and task_log.sess_no = &session
ORDER BY sess.sess_end DESC NULLS LAST,
task_log.sess_no,
task_log.nno,
task_log.nb_run,
task_log.scen_task_no,
position)
MODEL UNIQUE SINGLE REFERENCE
RETURN UPDATED ROWS
DIMENSION BY
(position,
sess_no,
nno,
nb_run,
scen_task_no)
MEASURES
(val AS tmp,
cast('' AS varchar2(4000)) as output0,
cast('' AS varchar2(4000)) as output1,
cast('' AS varchar2(4000)) as output2,
cast('' AS varchar2(4000)) as output3,
cast('' AS varchar2(4000)) as output4,
cast('' AS varchar2(4000)) as output5,
cast('' AS varchar2(4000)) as output6,
sess_name,
task_beg,
task_end,
task_status,
task_name1,
task_name2,
task_name3,
txt_count)
RULES(
output0[position between 0 and 10,any,any,any,any] = output0[cv()-1,cv(),cv(),cv(),cv()] || tmp[cv(),cv(),cv(),cv(),cv()],
output1[position between 11 and 20,any,any,any,any] = output1[cv()-1,cv(),cv(),cv(),cv()] || tmp[cv(),cv(),cv(),cv(),cv()],
output2[position between 21 and 30,any,any,any,any] = output2[cv()-1,cv(),cv(),cv(),cv()] || tmp[cv(),cv(),cv(),cv(),cv()],
output3[position between 31 and 40,any,any,any,any] = output3[cv()-1,cv(),cv(),cv(),cv()] || tmp[cv(),cv(),cv(),cv(),cv()],
output4[position between 41 and 50,any,any,any,any] = output4[cv()-1,cv(),cv(),cv(),cv()] || tmp[cv(),cv(),cv(),cv(),cv()],
output5[position between 51 and 60,any,any,any,any] = output5[cv()-1,cv(),cv(),cv(),cv()] || tmp[cv(),cv(),cv(),cv(),cv()],
output6[position between 61 and 70,any,any,any,any] = output6[cv()-1,cv(),cv(),cv(),cv()] || tmp[cv(),cv(),cv(),cv(),cv()]))
GROUP BY sess_name,
sess_no,
nno,
nb_run,
scen_task_no,
task_beg,
task_end,
task_status,
task_name1,
task_name2,
task_name3) executable_sql,
(
SELECT sess_name,
sess_no,
nno,
nb_run,
scen_task_no,
max(output1)error1,
max(output2)error2,
max(output3)error3,
max(output4)error4,
max(output5)error5
FROM
(
SELECT sess_name,
sess_no,
nno,
nb_run,
scen_task_no,
output1,
output2,
output3,
output4,
output5,
txt_count,
position
FROM (SELECT sess.sess_name,
task_log.sess_no,
task_log.nno,
task_log.nb_run,
task_log.scen_task_no,
row_number() over (PARTITION BY task_log.sess_no, task_log.nno, task_log.nb_run, task_log.scen_task_no order by sess_mess.txt_ord) position,
sess_mess.txt val,
COUNT(1) over(PARTITION BY task_log.sess_no, task_log.nno, task_log.nb_run, task_log.scen_task_no) txt_count
FROM snp_session sess,
SNP_SESS_TASK task,
SNP_SESS_TASK_LOG task_log,
SNP_EXP_TXT sess_mess
WHERE sess.sess_no = task_log.sess_no
AND sess.sess_no = task.sess_no
AND task.nno = task_log.nno
AND task.scen_task_no = task_log.scen_task_no
AND task_log.i_txt_task_mess = sess_mess.i_txt
and task_log.sess_no = &session
ORDER BY sess.sess_end DESC NULLS LAST,
task_log.sess_no,
task_log.nno,
task_log.nb_run,
task_log.scen_task_no,
sess_mess.txt_ord)
MODEL UNIQUE SINGLE REFERENCE
RETURN UPDATED ROWS
DIMENSION BY
(position,
sess_no,
nno,
nb_run,
scen_task_no)
MEASURES
(val AS tmp,
cast('' AS varchar2(4000)) as output1,
cast('' AS varchar2(4000)) as output2,
cast('' AS varchar2(4000)) as output3,
cast('' AS varchar2(4000)) as output4,
cast('' AS varchar2(4000)) as output5,
sess_name,
txt_count)
RULES(
output1[position<=10,any,any,any,any] = output1[cv()-1,cv(),cv(),cv(),cv()] || tmp[cv(),cv(),cv(),cv(),cv()],
output2[position between 11 and 20,any,any,any,any] = output2[cv()-1,cv(),cv(),cv(),cv()] || tmp[cv(),cv(),cv(),cv(),cv()],
output3[position between 21 and 30,any,any,any,any] = output3[cv()-1,cv(),cv(),cv(),cv()] || tmp[cv(),cv(),cv(),cv(),cv()],
output4[position between 31 and 40,any,any,any,any] = output4[cv()-1,cv(),cv(),cv(),cv()] || tmp[cv(),cv(),cv(),cv(),cv()],
output5[position between 41 and 50,any,any,any,any] = output5[cv()-1,cv(),cv(),cv(),cv()] || tmp[cv(),cv(),cv(),cv(),cv()]))
GROUP BY sess_name,
sess_no,
nno,
nb_run,
scen_task_no
) error_sql
WHERE executable_sql.sess_no=error_sql.sess_no (+)
AND executable_sql.nno=error_sql.nno(+)
AND executable_sql.nb_run=error_sql.nb_run(+)
AND executable_sql.scen_task_no=error_sql.scen_task_no(+)
ORDER BY executable_sql.scen_task_no DESC NULLS LAST,
executable_sql.sess_no,
executable_sql.nno,
executable_sql.nb_run,
executable_sql.scen_task_no;

Posted in database, Not so simple query, oracle, pl/sql, toolset | Tagged , , , , , , , , | Leave a comment

Retreiving user queries with V$SQL_BIND_CAPTURE view

Most of Oracle developers uses V$SQL view to understand what SQL are killing DB perfomance ;), but if your looking for some long running cursor, you cannot see binded values in V$SQL.SQL_FULLTEXT view. For this purposes V$SQL_BIND_VIEW was introduced in Oracle 10g.

V$SQL_BIND_CAPTURE displays information on bind variables used by SQL cursors. Each row in the view contains information for one bind variable defined in a cursor.

Using this information we can simply build anonymous block that will retrieve SQL’s as it was entered by user.

To retrieve data from V$SQL_BIND_CAPTURE we need SQL_ID,  HASH_VALUE and CHILD_NUMBER of executed statement.
V$SQL_BIND_CAPTURE hold all binded data as VARCHAR2(4000) and we will choose proper datatype using DATATYPE column of view. I tried to match all of types that I can found on my DB, so if you will see message ‘PLEASE CHECK OUT DATATYPE’ please do not be scared :) you should only check datatype and add proper case statement. Column WAS_CAPTURED will be used to distinguish when bind value was captured.

Bind values are not always captured for this view. Bind values are displayed by this view only when the type of the bind variable is simple (this excludes LONG, LOB, and ADT datatypes) and when the bind variable is used in the WHERE or HAVING clauses of the SQL statement.

Knowing all prerequisites we can build our anonymous block.

DECLARE
  l_output           CLOB;
  l_sql_id           VARCHAR2(13) := '&sql_id';
  l_sql_hash_value   NUMBER := &sql_hash_value;
  l_sql_child_number NUMBER := &sql_child_number;
BEGIN

  BEGIN
    l_output := NULL;
    SELECT vsql.sql_fulltext
      INTO l_output
      FROM v$sql vsql
     WHERE vsql.sql_id = l_sql_id
       AND vsql.hash_value = l_sql_hash_value
       AND vsql.child_number = l_sql_child_number;

    dbms_output.put_line('--SQL_ID=' || l_sql_id || ' SQL_HASH_VALUE=' || l_sql_hash_value ||
                         ' SQL_CHILD_NUMBER=' || l_sql_child_number);
    dbms_output.put_line('--OLD');
    dbms_output.put_line(l_output || ';');
    dbms_output.new_line;

    FOR inner_loop IN (SELECT vsqlb.name,
                              CASE
                                WHEN was_captured = 'NO' THEN
                                 '''' || 'VALUE NOT CAPTURED!!!' || ''''
                                WHEN value_string = 'NULL' THEN
                                 'NULL'
                                WHEN datatype IN (1, 11, 23, 96) THEN
                                 '''' || value_string || ''''
                                WHEN datatype = 2 THEN
                                 value_string
                                WHEN datatype IN (12, 180) THEN
                                 'to_date(''' || value_string ||
                                 ''',''RR/MM/DD HH24:MI:SS'')'
                                ELSE
                                 'PLEASE CHECK OUT DATATYPE'
                              END value_string
                         FROM v$sql              vsql,
                              v$sql_bind_capture vsqlb
                        WHERE vsql.sql_id = l_sql_id
                          AND vsql.hash_value = l_sql_hash_value
                          AND vsql.child_number = l_sql_child_number
                          AND vsql.sql_id = vsqlb.sql_id
                          AND vsql.hash_value = vsqlb.hash_value
                          AND vsql.child_number = vsqlb.child_number
                        ORDER BY position) LOOP

      l_output := regexp_replace(l_output,
                                 inner_loop.name,
                                 nvl(inner_loop.value_string, 'NULL'),
                                 1,
                                 1,
                                 'i');

    END LOOP;
    dbms_output.put_line('--NEW');
    dbms_output.new_line;
    dbms_output.put_line(l_output || ';');
    dbms_output.new_line;

  EXCEPTION
    WHEN no_data_found THEN
      NULL;
    WHEN OTHERS THEN
      RAISE;
  END;

END;

Output of such block in my test case retrieve perfect query to run.

--SQL_ID=4v4va1xphs000 SQL_HASH_VALUE=1795948544 SQL_CHILD_NUMBER=0
--OLD
SELECT t.comp_id,
       t.comp_currency
  FROM tsfdetail_chrg t,
       elc_comp       e
 WHERE t.tsf_no = :b8
   AND t.tsf_seq_no = :b7
   AND ((t.shipment = :b5 AND :b5 IS NOT NULL AND t.ship_seq_no = :b6 AND :b6 IS NOT NULL) OR
       (t.shipment IS NULL AND :b5 IS NULL))
   AND t.from_loc = :b4
   AND t.to_loc = :b3
   AND t.item = :b2
   AND ((t.pack_item = :b1 AND :b1 IS NOT NULL AND t.pack_item IS NOT NULL) OR
       (t.pack_item IS NULL AND :b1 IS NULL))
   AND t.comp_id = e.comp_id
   AND e.up_chrg_type = 'P';

--NEW

SELECT t.comp_id,
       t.comp_currency
  FROM tsfdetail_chrg t,
       elc_comp       e
 WHERE t.tsf_no = 100293334
   AND t.tsf_seq_no = 9
   AND ((t.shipment = 147666 AND 147666 IS NOT NULL AND t.ship_seq_no = 1 AND 1 IS NOT NULL) OR
       (t.shipment IS NULL AND 147666 IS NULL))
   AND t.from_loc = 21042
   AND t.to_loc = 1021025
   AND t.item = '803650474'
   AND ((t.pack_item = NULL AND NULL IS NOT NULL AND t.pack_item IS NOT NULL) OR
       (t.pack_item IS NULL AND NULL IS NULL))
   AND t.comp_id = e.comp_id
   AND e.up_chrg_type = 'P';
Posted in oracle, pl/sql, Simple query | Tagged , , , , , , , , , | Leave a comment

Oracle Timeline VS COLUMN to ROWS

Looking on Oracle release timeline we can see how much of  improvements for efficient and clear coding they give to developers from release to release. Many of developers think that between Oracle 9i, 10g and 11g no difference from coding view exists (that they only improve some inside processes, that are visible only for DBA), but they are wrong.

I will show to you this improvements on example of simple query in Oracle 11g/10g and not so simple in Oracle 9i.

Problem definition:

Divide COLUMN of data into ROWS using predefined character value.

Speaking simpler we should divide COLUMN=’abc,def,gh’ into ROW using comma as a divisor(you can use character whatever you want).

Oracle 11g, release date 2007

SQL> SELECT REGEXP_SUBSTR('abc,def,ge', '[^,]+', 1, LEVEL) "ROW"
2      FROM dual
3    CONNECT BY LEVEL <= REGEXP_COUNT('abc,def,ge', ',')+1;

ROW
------------------------------
abc
def
ge

Look on this query! I cannot imagine simpler solution in SQL for this problem. We use 2 REGEXP functions CONNECT’ed BY LEVEL to retrieve data and that’s all.

In Oracle 10g this query also was simple, but not so much.

Oracle 10g, release date 2004

SQL> SELECT regexp_substr('abc,def,ge', '[^,]+', 1, LEVEL) "ROW"
  2    FROM dual
  3  CONNECT BY LEVEL <= length('abc,def,ge')-
  4  length(REPLACE('abc,def,ge', ',', '')) + 1;

ROW
----------------------------------------
abc
def
ge

Regular expressions was introduced in Oracle 10g, but REGEXP_COUNT function was not implemented. In that case we should calculate occurences of comma delimiter manually, using LENGTH and REPLACE functions.
But it was also simple query, we only use 4 functions.

And here comes Oracle 9i (this is the part for hardcore ambitious developers)

Oracle 9i, release date 2001

SQL> SELECT substr('abc,def,ge',
  2        x + 1,
  3        coalesce(lead(x) over(ORDER BY x) - x - 1, length('abc,def,ge') - x)) "ROW"
  4    FROM (SELECT (instr('abc,def,ge', ',', 1, LEVEL)) x
  5            FROM dual
  6          CONNECT BY LEVEL <=
  7          length('abc,def,ge') - length(REPLACE('abc,def,ge', ',', '')) + 1);

ROW
----------------------------------------
abc
def
ge

Look on this query… it is hard to read, and I’m not speaking about understanding this query.

I should admit that I doesn’t know author of this query, but I think that it took much time from him to write this, as it took time from me to understand how it works. Author used COALESCE combined with LEAD functions and multiplied by SUBSTR,LENGTH,REPLACE to get the same result as we get using only 2 REGEXP functions.

It took from Oracle only 6 years to evolve from hyper complex query style to smooth and easy understanding SQL. I think now you understand how much Oracle versions are different from each other,and how is important to use and knew about new functionalities.

Posted in contemplations, Not so simple query, oracle, pl/sql, Simple query | Tagged , , , , , , , , , , , , , , , | Leave a comment

SQL MODEL clause magic

Today we will take a look on creation of random strings using mask. It is very helpful when you output string based on some input value should change on-line. This functionality can be assumed by using SQL MODEL clause magic, that allows to generate from many rows one row with output(flat) .
Lets look on an example and you will understand what I mean

Users want to create 2 different package labels from one package number.
Input: Package number = 57896854742
Output:First label = 5/789-685-4742
Second label =[578|968|547|42]

For those who wants to see immediate final query click here

Whole process of creation output can be divided into 2 parts:

  1. Find what mask format you should use
  2. Make a  query. No procedures or functions, just not so simple query

To create mask I will use next notation: ‘x’ will mean number, and other chars “/[|"  - mask characters.
First mask     = x/xxx-xxx-xxxx
Second mask=[xxx|xxx|xxx|xx]

Query logic will be next:

  • Split mask string into rows. One character in a row
  • Replace ‘x’ in mask with subsequent input number
  • Use MODEL clause to create output string

Lets take a closer look on query:

Split mask string into rows

SELECT LEVEL position,
       substr('x/xxx-xxx-xxxx', LEVEL, 1) val
  FROM dual
CONNECT BY LEVEL <= length('x/xxx-xxx-xxxx');

  POSITION VAL
---------- ---
         1 x
         2 /
         3 x
         4 x
         5 x
         6 -
         7 x
         8 x
         9 x
        10 -
        11 x
        12 x
        13 x
        14 x

14 rows selected

I use SUBSTR function with LEVEL clause to get one character in row. LEVEL is used like index of actual split character.

Replace ‘x’ in mask with subsequent input number

SELECT t.position,
       CASE val
         WHEN 'x' THEN
          substr('57896854742',
          rank() over(PARTITION BY val ORDER BY position), 1)
         ELSE
          val
       END val,
       val AS temp,
       rank() over(PARTITION BY val ORDER BY position) rank
     FROM (SELECT LEVEL position,
                  substr('x/xxx-xxx-xxxx', LEVEL, 1) val
             FROM dual
           CONNECT BY LEVEL <= length('x/xxx-xxx-xxxx')) t
    ORDER BY position;

  POSITION VAL TEMP       RANK
---------- --- ---- ----------
         1 5   x             1
         2 /   /             1
         3 7   x             2
         4 8   x             3
         5 9   x             4
         6 -   -             1
         7 6   x             5
         8 8   x             6
         9 5   x             7
        10 -   -             2
        11 4   x             8
        12 7   x             9
        13 4   x            10
        14 2   x            11

14 rows selected

First of all look on a RANK() column. Function RANK() returns for each occurence of ‘x’ in VAL column its rank number and it is equal to a position number of character in Input string. After using RANK() function I know exactly what ‘x’ should be replaced with wich character from Input string.
Next use CASE statement to check if value in VAL column is equal to ‘x’. If yes – replace ‘x’ with  SUBSTRinged character from Input, if no – put character from mask.

Use MODEL clause to create output string

SELECT position,
           output
      FROM (SELECT t.position,
                   CASE val
                     WHEN 'x' THEN
                      substr('57896854742',
                      rank() over(PARTITION BY val ORDER BY position ASC), 1)
                     ELSE
                      val
                   END val
             FROM (SELECT LEVEL position,
                          substr('x/xxx-xxx-xxxx', LEVEL, 1) val
                     FROM dual
                   CONNECT BY LEVEL <= length('x/xxx-xxx-xxxx')) t
            ORDER BY position)
      MODEL
      RETURN UPDATED ROWS
            DIMENSION BY(position)
            MEASURES    (to_char(val) AS tmp,'x/xxx-xxx-xxxx' AS output)
      RULES
      (output[ANY] = output[ CV() - 1] || tmp[cv()]);

  POSITION OUTPUT
---------- --------------
         1 5
         2 5/
         3 5/7
         4 5/78
         5 5/789
         6 5/789-
         7 5/789-6
         8 5/789-68
         9 5/789-685
        10 5/789-685-
        11 5/789-685-4
        12 5/789-685-47
        13 5/789-685-474
        14 5/789-685-4742

14 rows selected

And here we can see a Magic of SQL MODEL clause.
MODEL clause was added to Oracle 10g and described as:

The Model clause defines a multidimensional array by mapping the columns of a query into three groups: partitioning, dimension, and measure columns

Speaking simpler – MODEL clause add spreadsheet functionality to PL/SQL based on DIMENSIONS, MEASURES and RULES. Rules show start of the  formulas, and DIMENSION and MEASURES define dataset.
To see difference between Dimension and Measures I use 2 simple rules:

  1. You cannot change Dimension value in RULES formulas
  2. You use Dimension value to select proper cell from Measures

RULES part in our example works exactly as Excel fill handle: For each Output cell take value from previous Output cell and concatenate it with actual split character. [ANY] clause works as wild card and CV() returns actual value of cell. Look on OUTPUT row and you will see how string growing for each row, just like in Excel. And here comes Final query…

Final query

SELECT output FROM
(SELECT position,
           output
      FROM (SELECT t.position,
                   CASE val
                     WHEN 'x' THEN
                      substr('&input',
                      rank() over(PARTITION BY val ORDER BY position ASC), 1)
                     ELSE
                      val
                   END val
             FROM (SELECT LEVEL position,
                          substr('&mask', LEVEL, 1) val
                     FROM dual
                   CONNECT BY LEVEL <= length('&mask')) t
            ORDER BY position)
      MODEL
      RETURN UPDATED ROWS
            DIMENSION BY(position)
            MEASURES    (to_char(val) AS tmp,'&mask' AS output)
      RULES
      (output[ANY] = output[ CV() - 1] || tmp[cv()])
ORDER BY position DESC)
WHERE ROWNUM=1;

OUTPUT
--------------
5/789-685-4742
----------------
[578|968|547|42]

Try this query entering your own Input and Mask values, or use from example.
ORDER BY clause was added because I found that if I want to get Output value using directly POSITION=14 Oracle query optimizer will try to get value of Output cell earlier that MODEL clause run. To avoid it, use next: first select from subquery all MODEL’led values, next ORDER’ed descending and next simply select first value from subquery.
And that’s all… just not so simple query

To fully understand MODEL clause you can read next articles:
Oracle MODEL clause white paper
SQL Snippets
PSOUG

Posted in Not so simple query, oracle, pl/sql | Tagged , , , , , | Leave a comment

Technorati

Technorati: MMD5FHYZZ44A

Posted in Uncategorized | Tagged | Leave a comment

Hello world or maybe FizzBuzz!

Hi!

First thing that good developer should do is to write “Hello World” in his programming language.

I will try to make it more complex. We will use some “standard”  programming question:Using fizzbuzz to find developers who grok coding

And change it to “Hello World” problem:

Write a program that prints the numbers from 1 to 100. But for multiples of three print “Hello” instead of the number and for the multiples of five print “World”. For numbers which are multiples of both three and five print “HelloWorld”.

In PL/SQL we can solve this problem in two ways:

  • Crazy way, we will not do it: wrote some procedure that will use DBMS_OUTPUT package to show to lines
  • Silky smooth simple way: make a query
SELECT CASE
         WHEN MOD(LEVEL, 15) = 0 THEN
          'HelloWorld'
         WHEN MOD(LEVEL, 3) = 0 THEN
          'Hello'
         WHEN MOD(LEVEL, 5) = 0 THEN
          'World'
         ELSE 
          to_char(LEVEL)
       END
  FROM dual
CONNECT BY LEVEL <= 100;

As you can see in this query we use only one PL/SQL function

CONNECT BY LEVEL<=100

that generate series of records and that’s all… no magic, no unnecessary code, no headache.

Posted in Simple query | Tagged , , | 1 Comment