Another 5 neat 12c features for Oracle developers

In this post I will put 5 other new 12c features in the spotlight (in addition to the features of a previous post), that really makes the 12c an improvement against the previous versions of the Oracle database.

To get this result I listed up all the major new features and wanted to know my top 5 features that would make my life easier(as a developer), excluding the features from the previous  post (I certainly would have added the sequence modification(feature 1)), when doing development on an Oracle database.

  1. Top end query -> I really like this feature and I’m still wondering why it took Oracle so long before creating it. It is something I could have used a lot in the past, but instead I had to create far too complicated, not as nice readable queries to achieve this. How does it work? Well it’s very easy and it’s readable and can be used in a wide variety of cases. Some examples:
    Only get the first 3 rows:
    select * from X order by id
    fetch first 3 rows only;

    Skip the first 3 rows and get the next 3 rows:

    select * from X order by id
    offset 3 rows fetch next 3 rows only;

    Get the first 50% of records

    select * from X order by id
    fetch first 50 percent rows only;

    Get the first 3 rows together with the records equal to these department id’s

    select * from emp order by deptno
    fetch first 3 rows with ties;

    If you want the capture the last rows, you can obviously change ‘first’ with ‘last’…

  2. In the 12c database the use of 32767 characters for a VARCHAR2 in SQL is now available instead of the maximum of 4000(this is also the case for RAW and nvarchar2).
    We all have been waiting a long time for this one and before we had to use the clob datatype.
    But beware this is not an out of the box feature, you will have to execute the lines below before this is enabled :
    shutdown immediate
    startup upgrade
    alter system set max_string_size=EXTENDED scope=both;
    @<ORACLE_HOME>/rdbms/admin/utl32k.sql
    Shutdown immediate
    Startup

    More info can be found on: http://docs.oracle.com/cd/E16655_01/server.121/e17615/refrn10321.htm

  3. The invisible column is a feature of which I was wondering where I could use it for.
    Well it could be handy when you are adding a column to your table, but you don’t want any existing code to be impacted by it.
    Another case where it could be useful, is when using audit columns.  Columns as the creation_dt, update_dt, user_creation and user_update will only be of any added value when you would like to audit a certain column.
    Packages with inserts, updates, references to this table will not be impacted by the creation of this column.
    On the other hand there is also a risk that you forget that this column is in there, because you have to explicitly call for it (a describe or select * will not show this column). You can create invisible columns like this:

    ALTER TABLE
    ADD  INVISIBLE;

    If you want to make the column visible again, use this:

    ALTER TABLE
    MODIFY VISIBLE;

    In summary it could be handy, but don’t forget this column or it will pollute your table.

  4.  The with clause inline plsql feature is also something that I think is very welcome.
    It will make it possible to create a procedure or function inside your select statement instead of having to create this in a package or function. Oracle also says that this will optimize the performance against having to call a schema procedure/function(I still have to test this).
    A little example:

    WITH
    FUNCTION fnc$_add_one(p_num IN NUMBER) IS
    BEGIN
    RETURN p_num+1;
    END;
    SELECT fnc$_add_one(1)
    FROM DUAL;
  5.  Most of the time I use the ANSI way of writing for a left outer join, but the oracle way of writing left outer joins is still often used by many of the oracle developers.
    But there was one thing that you could do in ANSI, that you couldn’t in the oracle way.  You couldn’t write multiple tables on the left of an outer join, untill12c…
    In 11g and before when coding something like this:
    select *
    from a,b,c
    where a.id = b.id
    and a.id = c.id(+)
    and b.id = c.id2(+);

    This resulted in -> ORA-01417: a table may be outer joined to at most one other table

    In 12c this will work, also the ANSI solution obviously still works both on 12c and on 11g

    select *
    from a
    JOIN b ON (a.id = b.id)
    LEFT OUTER JOIN c ON (a.id = c.id AND b.id = c.id2);

Together with the previous post this makes 10 reasons why you should start to use the Oracle 12c database :-)

Read from anydata column

What should you do if you have an anydata column in a queue table and you don’t have any tool to read from it(sqldeveloper doesn’t support it natively)?

I have written some code to extract all the information from such a column and print it, whatever the content would be.

You can find out more Continue reading

Search database objects with a variable in oracle

Something I really often use is this select:

SELECT distinct type,line,name,text
FROM user_source
WHERE lower(Text) LIKE(‘%’||lower(:search_source)||’%’)
UNION
SELECT distinct ‘column’ type,null line,table_name||’.’|| COLUMN_NAME name,null
FROM user_tab_columns
WHERE lower(COLUMN_NAME) LIKE(‘%’||lower(:search_source)||’%’)
UNION
SELECT  DISTINCT ‘table’ type,null line,object_name name,null
from user_objects
WHERE object_type IN  (‘TABLE’,’VIEW’)
AND lower(object_name) LIKE(‘%’||lower(:search_source)||’%’)
ORDER BY type,name,line

This select will show you most of the database objects in a schema(columns, table,view,code), where this ‘search_source’ string is used.

You can add this into you sqldeveloper as well, as a user defined report.
To accomplish this you will have to do the following steps:
1) Open sqldeveloper
2) go to Reports tab(If you don’t see this tab go to the Menu choose view->Reports
3) right-click ‘User Defined Reports’ -> add Report -> give name and put the sql code as above into SQL input screen(you can leave the rest as default)
You can use this Search in sqldeveloper now.

Force page submits when paginating via selectlist

This is an extension for my previous post, how to not only submit via the next and previous links, but also via select.

This might not be the best solutions, and improvements are possible, but it’s a good startpoint. We will replace the #TEXT#-variable of Apex with our own generated html-code.

You need to add 3 extra lines in your sql-query to get the information you need, these columns don’t need to be displayed:
,ROWNUM API_ROWNUM
,COUNT(1) OVER() API_TOTALROWS
,NVL(:P1_ROWS,15) API_RPP

You will need to create a function that create our html-code, and an application-item to store the result in. My app-item will be API_SELECT and my function will be f_get_report_vars. The code will be shown below.

Next we have 2 modification in the report template.

We need to retrieve our total number of rows, our current displayed rows and our rows per page. I changed the column template 1 condition to PLSQL and added f_set_reportvars(‘#API_ROWNUM#’,’#API_TOTALROWS#’,’#API_RPP#’) = 0.

0 is a dummy value so my evalution is always TRUE. It might not be the correct place to do this, because it wasn’t designed to be used like this and the function is execute for every displayed row, instead of just once. (Suggestions are welcome)

Next step is changing the pagination template, which is default empty or
&ltspan class=”instructiontext”>#TEXT#&lt/span>
and use our app-item
&ltspan class=”instructiontext”>&API_SELECT.&lt/span>

And the function making this magic happen would be:

create or replace
FUNCTION f_set_reportvars(pin_rownum IN NUMBER
,pin_total_rows IN NUMBER
,pin_rpp IN NUMBER
) RETURN NUMBER
IS
lv_select VARCHAR2(1000);
ln_from NUMBER;
ln_to NUMBER;
ln_sets NUMBER;
ln_page_id NUMBER := apex_util.get_session_state(‘APP_PAGE_ID’);
ln_app_id NUMBER := apex_util.get_session_state(‘APP_ID’);
ln_session NUMBER := apex_util.get_session_state(‘SESSION’);
ln_region_id NUMBER;
BEGIN
–optional, set variables for other usage
–apex_util.set_session_state(‘API_ROWNUM’,piv_rownum);
–apex_util.set_session_state(‘API_TOTALROWS’,piv_totalrows);
–apex_util.set_session_state(‘API_PPR’,piv_ppr);

–get region id, when more then one report per page you need a tag in your static_id
SELECT region_id
INTO ln_region_id
FROM apex_application_page_regions
WHERE application_id = ln_app_id
AND page_id = ln_page_id
AND source_type = ‘Report’
/*AND static_id LIKE ‘MYTAG%’*/;

–define number of row ranges
ln_sets := CEIL(pin_total_rows/pin_rpp);
–when more then 1 row range, create select list
IF ln_sets > 1 THEN
lv_select := ‘&ltselect id=”X01_’||ln_region_id
||'” onchange=”doSubmit(”f?p=’||ln_app_id||':’||ln_page_id||':’||ln_session
||':pg_R_’||ln_region_id||':NO&pg_min_row=”+this.options[selectedIndex].value+”&pg_rows_fetched=_’||pin_rpp||”’);” size=”1″ name=”X01″>';
–make an option in the select list for every row range
FOR i IN 1..ln_sets LOOP
ln_from := ((i-1)*pin_rpp)+1;
ln_to := LEAST((i*pin_rpp),pin_total_rows);
–make difference between current row range and others
IF pin_rownum BETWEEN ln_from AND ln_to THEN
lv_select :=lv_select||’&ltoption selected=”selected” value=”current”>Row(s) ‘||ln_from||’-‘||ln_to||’ of ‘||pin_total_rows||’&lt/option>';
ELSE
lv_select :=lv_select||’&ltoption value=”‘||ln_from||’&pg_max_rows=’||pin_rpp||'”>’||ln_from||’-‘||ln_to||’ of ‘||pin_total_rows||’&lt/option>';
END IF;
END LOOP;
–close select-tag
lv_select := lv_select||’&lt/select>';
END IF;
–set application item with select list
apex_util.set_session_state(p_name => ‘API_SELECT’
,p_value => lv_select);
–return dummy
RETURN 0;
END;