Kscope14

ultimate-seattle-wallpaper2

Photo by Nate Whitehill

It’s been a few weeks since I returned from another awesome Kscope conference and I just realized that I never wrote about it.

For me, it was the first time visiting Seattle and I really liked it even when I only managed to walk around the downtown area. I had some concerns about how the weather was going to be but everything worked out very well with clear skies, temperature in the mid 70’s and no rain!

The view from my hotel room.

The Sunday symposiums, the conference sessions and the hands-on labs provided really good content. I particularly enjoyed all the presentations delivered by Jonathan Lewis and Richard Foote.

My friend Amy Caldwell won the contest to have a dinner with ODTUG’s President Monty Latiolais and she was very kind to invite me as her guest. We had a good time talking about the past, present and future of ODTUG and it was enlightening and inspirational to say the least.

My presentation on row pattern matching went well but the attendance wasn’t the best mostly because I had to present on the last time slot when people were on party mode and ready to head to the EMP Museum for the big event. Nevertheless, I had attendees like Dominic Delmolino, Kim Berg Hansen, Alex Zaballa, Leighton Nelson, Joel Kallman and Patrick Wolf that had good questions about my topic.

Some comments on Social Media

As I said before, the big event took place at the EMP Museum and I believe everyone had a good time visiting the music and sci-fi exhibits and enjoying the food, drinks and music.

The EMP Museum

The EMP Museum

Next year, Kscope will take place on Hollywood, Florida. If you’re a Developer, DBA or an Architect working with Oracle products that’s where you want to be from June 21 – 25. I suggest you register and book your hotel room right away because it’s going to sell out really fast.

Hope to see you there!


SQL Developer’s Interface for GIT: Interacting with a GitHub Repository Part 2

In this post I’m going to show to synchronize the remote and local repositories after an existing file in local gets modified. What I’ll do is modify the sp_test_git.pls file in our local repository and then push those changes to the remote repository (GitHub).

First, I proceed to open the sp_test_git.pls file using SQL Developer, add another dbms_output line to it and save it. The moment I save the file, the Pending Changes (Git) window gets updated to reflect the change and the icons in the toolbar get enabled.

modify_file

Now I can include a comment and then add the file to the staging area by clicking on the Add button located on the Pending Changes (Git) window. Notice how the status changes from “Modified Not Staged” to “Modified Staged”.

staged_file

What if I want to compare versions before doing a commit to the local repository? I just have to click on the Compare with Previous Version icon located on the Pending Changes (Git) window.

compare2

The panel on the left displays the version stored in the local repository and the panel on the right displays the version in the Staging Area.

The next step is to commit the changes to the local repository. For that I click on the Commit button located on the Pending Changes (Git) window and then I click on the OK button in the Commit window.

commit

Now the Branch Compare window displays information telling that remote and local are out of sync.

branch_compare2

So the final step is to sync up remote and local by pushing the changes to GitHub. For that I go to the main menu and click on  Team -> Git -> Push to open the “Push to Git” wizard where I enter the URL for the remote repository, the user name and password to complete the operation. Now I go to GitHub to confirm the changes have been applied.

updated_github


SQL Developer’s Interface for GIT: Interacting with a GitHub Repository Part 1

In my previous post, I showed how to clone a GitHub repository using SQL Developer. In this post I’m going to show to synchronize the remote and local repositories after remote gets modified.

Here I use GitHub to commit a file called sp_test_git.pls.  You can create files by clicking on the icon the red arrow is pointing to.

new_file

The content of the file is a PL/SQL procedure that prints a message.

file_content

At this point, the remote repository and the local repository are out of sync. The first thing that you may want to do before modifying any repository, is to make sure that you have the most current version of it so that it includes the changes made by other developers. Let’s synchronize remote and local.

Make sure you open the Versions window. Go to the main menu click on Team -> Versions.

versions

Open the Local branch and click on master, then go to main menu click on Team -> Git -> Fetch to open the “Fetch from Git” wizard. Fetching a repository copies changes from the remote repository into your local system, without modifying any of your current branches. Once you have fetched the changes, you can merge them into your branches or simply view them. We can see the changes on the Branch Compare window by going to the main menu click on Team -> Git -> Branch Compare.

branch_compare

 Branch Compare is showing that sp_test_git.pls has been fetched from the remote master branch. We can right click on this entry and select compare to see the differences.

compare

The window on the left displays the content of the fetched file and the window on right displays the content of the same file in the local repository. In this case the right windows is empty because this is a brand new file that doesn’t exist locally. Let’s accept the changes and merge them into the local repository. We go to the Branch Compare window, right click on the entry, select merge and click on the “Ok” button.

merge

Now the changes should have been applied to the local repository.

local_update

We can go to the path where the local repository is located and confirm that sp_test_git.pls is there.

 

 


SQL Developer’s Interface for GIT: Cloning a GitHub Repository

SQL Developer 4 provides an interface that allows us to interact with Git repositories. In this post, I’m going to show how to clone a GitHub (A web based hosting service for software development projects that uses the Git revision control system) repository.

First you need to sign up for a GitHub account. You can skip this step if you already have one.

Your account will give you access to public repositories that could be cloned but I suggest you create your own repository so that you can play with SQL Developer and see what the different options can do.

Once you have an account, click on the green button that says “New Repository”. It will take you to a screen like this:

github_create_repo

Give your repository a name, decide if you want it to be public or private (you have to pay), click on the check box and then click on the green button. Now you should be taken to the main repository page.

github_repo

Pay attention to the red arrow on the previous image. It points to a text box that contains the HTTPS clone URL that we’re going to use in SQL Developer to connect to GitHub.

Let’s go to SQL Developer and click on Team –> Git –> Clone… to open the “Clone from Git Wizard”. Click on the next button and you should see the screen that lets you enter the repository details:

remote_repo

Enter the repository name, the HTTPS clone URL, your GitHub user name and your password. Click on next to connect to the repository and see the remote branches that are available.

remote_branch

The master branch gets created by default for every new repository. Take the defaults on this screen and click on next to get to the screen where you specify the destination for your local Git repository.

destination

Enter the path for the local repository and click on next. A summary screen is displayed and showing the options you chose. Click on finish to complete the setup.

How do we know if it worked? Go to the path of your local repository and it should contain the same structure as in the online repository.

local_repo

On a next post I’ll show how to commit changes to the local repository and how to push them to GitHub.


SQL Developer’s PL/SQL Debugger and Oracle 12c

The PL/SQL Debugger works pretty much out of the box when used with a previous Oracle version. These are the things we needed in place before we could start debugging PL/SQL:

  1. A grant of the DEBUG CONNECT SESSION privilege.
  2. EXECUTE privilege on DBMS_DEBUG_JDWP.
  3. EXECUTE privilege on the stored procedure you want to debug.
  4. Make sure the stored procedure is “Compiled for Debug”.

Jeff Smith talks about it in this post.

But what happens when you use Oracle 12c? You still need all the stuff that I mentioned but that’s not enough. See the error I got when I was trying to debug a procedure.

Executing PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP( ’192.168.0.10′, ’49428′ )
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at “SYS.DBMS_DEBUG_JDWP”, line 68
ORA-06512: at line 1
Process exited.
Disconnecting from the database SCOTT – ORA12CPDB1

Starting with Oracle 12c, if you want to debug PL/SQL stored procedures in the database through a Java Debug Wire Protocol (JDWP)-based debugger, such as SQL Developer or JDeveloper, then you must be granted the jdwp ACL privilege to connect your database session to the debugger at a particular host.

This is one way you can configure network access for JDWP operations:

BEGIN
 DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE
 (
 host => '192.168.0.10',
 lower_port => null,
 upper_port => null,
 ace => xs$ace_type(privilege_list => xs$name_list('jdwp'),
 principal_name => 'scott',
 principal_type => xs_acl.ptype_db)
 );
END;
Host can can be a host name, domain name, IP address, or subnet.

Principal name in the access control entry (ACE) section is the schema that holds the stored procedures you want to debug.

Now the error goes away and you can start your debugging task.

Connecting to the database SCOTT – ORA12CPDB1.
Executing PL/SQL: ALTER SESSION SET PLSQL_DEBUG=TRUE
Executing PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP( ’192.168.0.10′, ’49428′ )
Debugger accepted connection from database on port 49428.
Executing PL/SQL: CALL DBMS_DEBUG_JDWP.DISCONNECT()
Inside the Procedure
Process exited.
Disconnecting from the database SCOTT – ORA12CPDB1.
Debugger disconnected from database.

I hope this post saves you some time when you migrate to 12c.


RMOUG Training Days 2014

training_days

I’ll be presenting on February 6th. Here are the details of my session:

SQL Pattern Matching in Oracle 12c

Room 501, 11:15 – 12:15

Last year I had a great time, so I can’t wait to be back in Denver.

I hope to see you there!

Updated on 02/17/2014: The presentation is available on Slideshare


Recursive Subquery Factoring to the Rescue

Recursive subquery factoring (AKA Recursive Common Table Expressions) has been available in Oracle since version 11g R2 was launched sometime around 2009. I always thought of it as a replacement for the connect by clause and just another way to write hierarchical queries, so I never really took the time to play with it.

During Kscope13, I attended Dominic Delmolino’s session on this SQL feature and he mentioned and showed that recursive subquery factoring can be used for more than hierarchical queries. One use case for recursive subquery factoring is when you have to “generate” a value and then “carry over” that value to the next row.

Not too long ago I had to work on a query with a similar requirement. The basic idea is to have a variable number of records per client with some columns that are evaluated to establish a new column (let’s call it VAL). The caveat is that for some records VAL has to be determined using VAL from the previous record.

First I’m going to create a table and insert some data to play with.


SQL> CREATE TABLE RSF_TEST
 2 (
 3 ID_CLIENT NUMBER NOT NULL,
 4 DT_ELIG DATE NOT NULL,
 5 TP NUMBER NOT NULL,
 6 SRV_GRP NUMBER NOT NULL,
 7 SRV_CODE NUMBER NOT NULL,
 8 DT_ADMIT DATE NOT NULL,
 9 DT_DISCHARGE DATE NOT NULL
 10 );

Table created

SQL> insert into RSF_TEST (ID_CLIENT, DT_ELIG, TP, SRV_GRP, SRV_CODE, DT_ADMIT, DT_DISCHARGE)
 2 values (525459313, to_date('01-11-2013', 'dd-mm-yyyy'), 13, 19, 12, to_date('01-05-2013', 'dd-mm-yyyy'), to_date('31-12-2013', 'dd-mm-yyyy'));
1 row inserted

SQL> insert into RSF_TEST (ID_CLIENT, DT_ELIG, TP, SRV_GRP, SRV_CODE, DT_ADMIT, DT_DISCHARGE)
 2 values (615086545, to_date('01-11-2013', 'dd-mm-yyyy'), 13, 19, 12, to_date('07-03-2013', 'dd-mm-yyyy'), to_date('31-03-2014', 'dd-mm-yyyy'));
1 row inserted

SQL> insert into RSF_TEST (ID_CLIENT, DT_ELIG, TP, SRV_GRP, SRV_CODE, DT_ADMIT, DT_DISCHARGE)
 2 values (525459313, to_date('01-10-2013', 'dd-mm-yyyy'), 13, 19, 12, to_date('01-05-2013', 'dd-mm-yyyy'), to_date('31-12-2013', 'dd-mm-yyyy'));
1 row inserted

SQL> insert into RSF_TEST (ID_CLIENT, DT_ELIG, TP, SRV_GRP, SRV_CODE, DT_ADMIT, DT_DISCHARGE)
 2 values (615086545, to_date('01-10-2013', 'dd-mm-yyyy'), 13, 19, 12, to_date('07-03-2013', 'dd-mm-yyyy'), to_date('31-03-2014', 'dd-mm-yyyy'));
1 row inserted

SQL> insert into RSF_TEST (ID_CLIENT, DT_ELIG, TP, SRV_GRP, SRV_CODE, DT_ADMIT, DT_DISCHARGE)
 2 values (615086545, to_date('01-09-2013', 'dd-mm-yyyy'), 13, 19, 12, to_date('07-03-2013', 'dd-mm-yyyy'), to_date('31-03-2014', 'dd-mm-yyyy'));
1 row inserted

SQL> insert into RSF_TEST (ID_CLIENT, DT_ELIG, TP, SRV_GRP, SRV_CODE, DT_ADMIT, DT_DISCHARGE)
 2 values (525459313, to_date('01-09-2013', 'dd-mm-yyyy'), 13, 19, 12, to_date('01-05-2013', 'dd-mm-yyyy'), to_date('31-12-2013', 'dd-mm-yyyy'));
1 row inserted

SQL> insert into RSF_TEST (ID_CLIENT, DT_ELIG, TP, SRV_GRP, SRV_CODE, DT_ADMIT, DT_DISCHARGE)
 2 values (525459313, to_date('01-08-2013', 'dd-mm-yyyy'), 13, 19, 12, to_date('01-05-2013', 'dd-mm-yyyy'), to_date('31-12-2013', 'dd-mm-yyyy'));
1 row inserted

SQL> insert into RSF_TEST (ID_CLIENT, DT_ELIG, TP, SRV_GRP, SRV_CODE, DT_ADMIT, DT_DISCHARGE)
 2 values (615086545, to_date('01-08-2013', 'dd-mm-yyyy'), 13, 19, 12, to_date('07-03-2013', 'dd-mm-yyyy'), to_date('31-03-2014', 'dd-mm-yyyy'));
1 row inserted

SQL> insert into RSF_TEST (ID_CLIENT, DT_ELIG, TP, SRV_GRP, SRV_CODE, DT_ADMIT, DT_DISCHARGE)
 2 values (615086545, to_date('01-07-2013', 'dd-mm-yyyy'), 13, 19, 12, to_date('07-03-2013', 'dd-mm-yyyy'), to_date('31-03-2014', 'dd-mm-yyyy'));
1 row inserted

SQL> insert into RSF_TEST (ID_CLIENT, DT_ELIG, TP, SRV_GRP, SRV_CODE, DT_ADMIT, DT_DISCHARGE)
 2 values (525459313, to_date('01-07-2013', 'dd-mm-yyyy'), 13, 19, 12, to_date('01-05-2013', 'dd-mm-yyyy'), to_date('31-12-2013', 'dd-mm-yyyy'));
1 row inserted

SQL> insert into RSF_TEST (ID_CLIENT, DT_ELIG, TP, SRV_GRP, SRV_CODE, DT_ADMIT, DT_DISCHARGE)
 2 values (525459313, to_date('01-06-2013', 'dd-mm-yyyy'), 13, 19, 12, to_date('01-05-2013', 'dd-mm-yyyy'), to_date('31-12-2013', 'dd-mm-yyyy'));
1 row inserted

SQL> insert into RSF_TEST (ID_CLIENT, DT_ELIG, TP, SRV_GRP, SRV_CODE, DT_ADMIT, DT_DISCHARGE)
 2 values (615086545, to_date('01-06-2013', 'dd-mm-yyyy'), 13, 19, 12, to_date('07-03-2013', 'dd-mm-yyyy'), to_date('31-03-2014', 'dd-mm-yyyy'));
1 row inserted

SQL> insert into RSF_TEST (ID_CLIENT, DT_ELIG, TP, SRV_GRP, SRV_CODE, DT_ADMIT, DT_DISCHARGE)
 2 values (525459313, to_date('01-05-2013', 'dd-mm-yyyy'), 13, 19, 12, to_date('01-05-2013', 'dd-mm-yyyy'), to_date('31-12-2013', 'dd-mm-yyyy'));
1 row inserted

SQL> insert into RSF_TEST (ID_CLIENT, DT_ELIG, TP, SRV_GRP, SRV_CODE, DT_ADMIT, DT_DISCHARGE)
 2 values (615086545, to_date('01-05-2013', 'dd-mm-yyyy'), 13, 19, 12, to_date('07-03-2013', 'dd-mm-yyyy'), to_date('31-03-2014', 'dd-mm-yyyy'));
1 row inserted

SQL> insert into RSF_TEST (ID_CLIENT, DT_ELIG, TP, SRV_GRP, SRV_CODE, DT_ADMIT, DT_DISCHARGE)
 2 values (525459313, to_date('01-04-2013', 'dd-mm-yyyy'), 13, 19, 12, to_date('12-12-2012', 'dd-mm-yyyy'), to_date('30-04-2013', 'dd-mm-yyyy'));
1 row inserted

SQL> insert into RSF_TEST (ID_CLIENT, DT_ELIG, TP, SRV_GRP, SRV_CODE, DT_ADMIT, DT_DISCHARGE)
 2 values (615086545, to_date('01-04-2013', 'dd-mm-yyyy'), 13, 19, 12, to_date('07-03-2013', 'dd-mm-yyyy'), to_date('31-03-2014', 'dd-mm-yyyy'));
1 row inserted

SQL> insert into RSF_TEST (ID_CLIENT, DT_ELIG, TP, SRV_GRP, SRV_CODE, DT_ADMIT, DT_DISCHARGE)
 2 values (615086545, to_date('01-03-2013', 'dd-mm-yyyy'), 13, 19, 12, to_date('07-03-2013', 'dd-mm-yyyy'), to_date('31-03-2014', 'dd-mm-yyyy'));
1 row inserted

SQL> insert into RSF_TEST (ID_CLIENT, DT_ELIG, TP, SRV_GRP, SRV_CODE, DT_ADMIT, DT_DISCHARGE)
 2 values (525459313, to_date('01-03-2013', 'dd-mm-yyyy'), 13, 19, 12, to_date('12-12-2012', 'dd-mm-yyyy'), to_date('30-04-2013', 'dd-mm-yyyy'));
1 row inserted

SQL> insert into RSF_TEST (ID_CLIENT, DT_ELIG, TP, SRV_GRP, SRV_CODE, DT_ADMIT, DT_DISCHARGE)
 2 values (525459313, to_date('01-02-2013', 'dd-mm-yyyy'), 13, 19, 12, to_date('12-12-2012', 'dd-mm-yyyy'), to_date('30-04-2013', 'dd-mm-yyyy'));
1 row inserted

SQL> insert into RSF_TEST (ID_CLIENT, DT_ELIG, TP, SRV_GRP, SRV_CODE, DT_ADMIT, DT_DISCHARGE)
 2 values (525459313, to_date('01-01-2013', 'dd-mm-yyyy'), 13, 19, 12, to_date('12-12-2012', 'dd-mm-yyyy'), to_date('30-04-2013', 'dd-mm-yyyy'));
1 row inserted

SQL> insert into RSF_TEST (ID_CLIENT, DT_ELIG, TP, SRV_GRP, SRV_CODE, DT_ADMIT, DT_DISCHARGE)
 2 values (525459313, to_date('01-12-2012', 'dd-mm-yyyy'), 13, 19, 12, to_date('12-12-2012', 'dd-mm-yyyy'), to_date('30-04-2013', 'dd-mm-yyyy'));
1 row inserted

SQL> insert into RSF_TEST (ID_CLIENT, DT_ELIG, TP, SRV_GRP, SRV_CODE, DT_ADMIT, DT_DISCHARGE)
 2 values (525459313, to_date('01-03-2012', 'dd-mm-yyyy'), 13, 1, 1, to_date('18-01-2012', 'dd-mm-yyyy'), to_date('29-03-2012', 'dd-mm-yyyy'));
1 row inserted

SQL> commit;

Commit complete

SQL>

And now a simplified version of the query. The real query has more predicates and the CASE statements are a little bit more complex to cover different srv_grp, srv_code combinations. I removed as much as I could just to show how VAL is determined for the particular clients that I have in my table.


WITH
clients AS
(
 SELECT
   id_client,
   dt_elig,
   tp,
   srv_grp,
   srv_code,
   dt_admit,
   dt_discharge,
   row_number() OVER (PARTITION BY id_client ORDER BY id_client, dt_elig) rn
 FROM rsf_test
),
results (id_client, dt_elig, tp, srv_grp, srv_code, dt_admit, dt_discharge, rn, val) AS
(
 SELECT
   id_client,
   dt_elig,
   tp,
   srv_grp,
   srv_code,
   dt_admit,
   dt_discharge,
   rn,
   CASE
     WHEN tp <> 14 AND srv_grp = 19 AND srv_code = 12 AND dt_elig BETWEEN dt_admit AND dt_discharge THEN 3
     WHEN tp <> 14 AND srv_grp = 19 AND srv_code = 12 AND dt_elig NOT BETWEEN dt_admit AND dt_discharge THEN 0
     WHEN tp <> 14 AND srv_grp IN (1,10) AND srv_code IN (1,3) THEN 0
   END val
 FROM clients
 WHERE
   rn = 1
 UNION ALL
 SELECT
   c.id_client,
   c.dt_elig,
   c.tp,
   c.srv_grp,
   c.srv_code,
   c.dt_admit,
   c.dt_discharge,
   c.rn,
   CASE
     WHEN c.tp <> 14 AND c.srv_grp = 19 AND c.srv_code = 12 AND p.val = 3 AND c.dt_elig BETWEEN c.dt_admit AND c.dt_discharge THEN 3
     WHEN c.tp <> 14 AND c.srv_grp = 19 AND c.srv_code = 12 AND p.val < 3 AND c.dt_elig BETWEEN c.dt_admit AND c.dt_discharge THEN p.val + 1
     WHEN c.tp <> 14 AND c.srv_grp IN (1,10) AND c.srv_code IN (1,3) AND p.val = 3 THEN 3
     WHEN c.tp <> 14 AND c.srv_grp IN (1,10) AND c.srv_code IN (1,3) AND p.val < 3 THEN p.val
     WHEN c.tp <> 14 AND c.srv_grp = 19 AND c.srv_code = 12 AND p.val = 3 AND c.dt_elig NOT BETWEEN c.dt_admit AND c.dt_discharge THEN 3
     WHEN c.tp <> 14 AND c.srv_grp = 19 AND c.srv_code = 12 AND p.val < 3 AND c.dt_elig NOT BETWEEN c.dt_admit AND c.dt_discharge THEN p.val
   END val
 FROM
   clients c,
   results p
 WHERE
   c.id_client = p.id_client
   AND c.rn = p.rn + 1
)
SELECT
  r.id_client,
  r.dt_elig,
  r.tp,
  r.srv_grp,
  r.srv_code,
  r.dt_admit,
  r.dt_discharge,
  --r.rn,
  r.val
FROM results r
ORDER BY
  r.id_client,
  r.dt_elig;

As you can see, the recursive subquery factoring clause contains two query blocks: The anchor query that generates the first row which is then processed by the recursive query. The anchor query and the recursive query are combined using the UNION ALL operator.

Here is the query’s output:


 ID_CLIENT DT_ELIG           TP    SRV_GRP   SRV_CODE DT_ADMIT  DT_DISCHA        VAL
---------- --------- ---------- ---------- ---------- --------- --------- ----------
 525459313 01-MAR-12         13          1          1 18-JAN-12 29-MAR-12          0
 525459313 01-DEC-12         13         19         12 12-DEC-12 30-APR-13          0
 525459313 01-JAN-13         13         19         12 12-DEC-12 30-APR-13          1
 525459313 01-FEB-13         13         19         12 12-DEC-12 30-APR-13          2
 525459313 01-MAR-13         13         19         12 12-DEC-12 30-APR-13          3
 525459313 01-APR-13         13         19         12 12-DEC-12 30-APR-13          3
 525459313 01-MAY-13         13         19         12 01-MAY-13 31-DEC-13          3
 525459313 01-JUN-13         13         19         12 01-MAY-13 31-DEC-13          3
 525459313 01-JUL-13         13         19         12 01-MAY-13 31-DEC-13          3
 525459313 01-AUG-13         13         19         12 01-MAY-13 31-DEC-13          3
 525459313 01-SEP-13         13         19         12 01-MAY-13 31-DEC-13          3
 525459313 01-OCT-13         13         19         12 01-MAY-13 31-DEC-13          3
 525459313 01-NOV-13         13         19         12 01-MAY-13 31-DEC-13          3
 615086545 01-MAR-13         13         19         12 07-MAR-13 31-MAR-14          0
 615086545 01-APR-13         13         19         12 07-MAR-13 31-MAR-14          1
 615086545 01-MAY-13         13         19         12 07-MAR-13 31-MAR-14          2
 615086545 01-JUN-13         13         19         12 07-MAR-13 31-MAR-14          3
 615086545 01-JUL-13         13         19         12 07-MAR-13 31-MAR-14          3
 615086545 01-AUG-13         13         19         12 07-MAR-13 31-MAR-14          3
 615086545 01-SEP-13         13         19         12 07-MAR-13 31-MAR-14          3
 615086545 01-OCT-13         13         19         12 07-MAR-13 31-MAR-14          3
 615086545 01-NOV-13         13         19         12 07-MAR-13 31-MAR-14          3

22 rows selected.

In this particular example, the anchor query assigns either a 0 or a 3 to VAL for the first row and after that the recursive query evaluates if VAL should increase or stay the same for every other row. So basically the point is to have access to VAL from a previous execution of the recursive query to determine the new value in the current execution.


Follow

Get every new post delivered to your Inbox.