Development

Planet Hard Drive? Muddled nonsense from Scientific American

FeuerThoughts - Thu, 2015-07-23 08:59
In the August 2015 issue of Scientific American, I came across an article titled "Planet Hard Drive", a "thought experiment" arguing that we can think of Earth as a kind of "hard drive" and "although Earth has an enormous capacity to store information, order is still rare....but the growth of order on Earth also stems from the production of cultural information."

The article is behind a paywall, so I cannot reproduce it here, but if you are a subscriber, here you go.

I find it generally hard to read SciAm these days, as well as many other scientific sources, because of the pervasive species-ism (humans unique, more important than all others) found sadly among scientists.

But this article was, I thought, a real disappointment, coming from SciAm. I sent this letter to the author:

Professor Hidalgo, 

I read your SciAm article with the above title, and I found it scientifically sloppy and offensively tone deaf, given the state of our planet today (specifically the threat of climate change and human-cause extinctions and species degradation). 

You might not read past that initial paragraph but if you do:

Scientifically Sloppy

I am all for interesting “thought experiments”, but it should have a reasonable amount of logical consistency. I think your experiment fails in this regard. 

Specifically, you talk about the growth of order on earth from production of cultural information.

This implies a clear net positive change in order due to our intensely “ordered” products. 

Yet previously, you recognized that there is order (lots of it) in living things. 

And that's where I see a very deep (specie-ist-driven) fallacy: to create our products humans destroy a vast amount of living things and therefore wipe out corresponding enormous amounts of order. 

Vast parts of the rainforest, extinction of entire species, degradation of the ocean, etc., etc., etc. - do you really think that if you even attempted to conceptualize the volume of order sacrificed to build iPhones, you could come out with a net positive growth in order?

I suppose it might be remotely possible - but you don’t even address this trade-off, making your argument incomplete and sloppy. I am very surprised that SciAm did not insist on a more rigorous treatment.

Sdaly, you seem to blithely accept that destruction of life on our planet in order to manifest our culture-as-thought as products. 

Which that brings me to…

Offensively Tone Deaf

Your insistence to see the entire world through a human filter and impose human paradigms onto the rest of the natural world is shocking, giving the growing awareness (especially among the most rational of us, like many scientists).
“A tree, for example, is a computer”
“Objects of this kind [manufactured products] are particularly special.”
“Biological cells are finite computers”
“People are also limited, and we transcend our finite compuational capacities by forming social and professional networks.”
“Special” “Transcend”

You use words that impute relentlessly positive values to human activity. 

Yet if you do not place humans “above” all others, you could at least say (my changes in bold):

“People are also limited, and we augment our finite compuational capacities by forming social and professional networks. A necessary consequence of this agumentation is the destruction of the computational capacities of billions of other living creatures.

At the very end of your muddled thought experiment, you finally hint at a bigger picture:
“The resulting hyperconnected society will present our species with some of the most challenging ethical problems in human history.”
Ah, ethics! Finally! Professor Hidalgo will now point out the grave price paid by our planet and co-inhabitants for human's desire for comfort and convenience, but....

No, no. For you, like way too many other humans, all that matters is the human species.
“We could lose aspects of our humanity that some of us consider essential: for example, we might cheat death.”
Now that would be a real ethical disaster (cheating death) - precisely because it mean accelerated devastation of our planet and non-humans.

But that doesn’t seem to even register in your thinking.
Categories: Development

New APEX Blog

Denes Kubicek - Wed, 2015-07-22 03:33
This is a new blog on APEX. I expect a lots of good stuff there in the future. The last example describes how to modify the Page Designer and move the panes, adjust the layout and simplify the development of an application since accessing element attributes is much easier that way. I think this is worth of looking at and maybe the APEX team considers this as an improvement for the future release.



Categories: Development

A Lot To Listen To

FeuerThoughts - Mon, 2015-07-20 07:40
A Lot To Listen To

Sometimes, if you're lucky,
there is nothing to hear
but the sound of the wind
blowing through trees.

Now you could say:
"That's not much to listen to."
Or you could listen...

Listen
to the rustling, hissing, whispering, sometimes angry sound
of thousands 
of almost silent brushings of leaf against leaf,
of feather-light taps of twig striking twig,
any single act nothing to hear at all
but when the tree is big enough
and the leaves are numerous enough
and the branches reach out 
thinner and thinner
poking out toward the sun
carrying leaves to their destiny,

then you might be able to hear
the sound of the wind
blowing through trees.

It's a lot to listen to,
if you can hear it.




Copyright 2015 Steven Feuerstein
Categories: Development

First patch set (5.0.1) released for APEX 5.0

Dimitri Gielis - Thu, 2015-07-16 16:22
I know some people waiting till the first patch set that comes available after a major Oracle APEX release... today you no longer have to wait, APEX 5.0.1 is now available.

In the patch set notes you can read what changed.

If you're still on APEX 4.x you can go immediately to APEX 5.0.1, you only need to download the latest version on OTN.

If you're already on APEX 5.0, you can download the patch set from support.oracle.com, search for patch number 21364820. Applying the patch took less than 5 minutes in my environment.



This patch set updates the Universal Theme too, so don't forget to update your images folder. When you login in APEX after the patch, it will check if you have the correct images folder, if not it will give you an alert. Although I updated the images directory I still got that alert due to browser caching. Refresh your page and it should be ok.

Note that it's important to be on APEX 5.0.1 when you use APEX Office Print - currently available in beta for a select audience, public release end of this month (July 2015). Behind the scenes we use the APEX_JSON and APEX_WEB_SERVICE packages which got an update in APEX 5.0.1.

And finally, there's a nice new D3 chart available in APEX 5.0.1 called "D3 Collapsible Treemap Chart"


Happy upgrading...

Update 18-JUL-2015: there're still a couple of known issues, please read those too and install some patchset exception when necessary.
Categories: Development

Oracle APEX 5.0.1 now available

Patrick Wolf - Thu, 2015-07-16 06:48
Oracle Application Express 5.0.1 is now released and available for download. If you wish to download the full release of Oracle Application Express 5.0.1, you can get it from the Downloads page on OTN. If you have Oracle APEX 5.0.0 … Continue reading
Categories: Development

Oracle LTRIM Function with Examples

Complete IT Professional - Thu, 2015-07-16 06:00

Oracle LTRIM FunctionThe LTRIM function removes characters from the left side of a string. Learn more about it and see some examples in this article.

Purpose of the Oracle LTRIM Function

The purpose of the LTRIM function is to remove a specified character from the left side of a string. The L in LTRIM stands for “Left”, and is the opposite of the RTRIM or “Right” Trim function.

 

Syntax

The syntax of the Oracle LTRIM functions is:

LTRIM( input_string, [trim_string] )

 

Parameters

The parameters of the LTRIM function are:

  • input_string (mandatory): This is the string to remove characters from the left-hand side of.
  • trim_string (optional): This is the string to be removed from the input_string. If it is not specified, a space is used, and all spaces are removed from the left of the input_string.

Some points to note about LTRIM:

  • If the trim_string is a literal value, you need to include it inside single quotes. For example, to remove an underscore, you need to specify it as ‘_’
  • Both input_string and trim_string can be of data type CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.
  • The returned value is a VARCHAR2 data type if the input types are CHAR, VARCHAR2, NCHAR or NVARCHAR2, and the returned value is a LOB data type if the input types are LOB or CLOB.
  • The trim_string can be more than one character
  • The function removes each individual value inside trim_string, not the string as a whole. See the Examples section below for more information.

 

Can You Use Oracle LTRIM To Remove Leading Zeroes?

Yes, you can. It’s one of the more common uses for the function that I’ve seen.

This can be done as:

LTRIM(value, ‘0’)

See the Examples section below for more information.

 

Can You Use Oracle LTRIM with RTRIM?

Yes, you can, and it works in the same way as just using the TRIM function.

You’ll need to use one inside the other, and it doesn’t really matter which one is used first.

So, you can use either LTRIM(RTRIM(value)) or RTRIM(LTRIM(value)).

See the Examples section below for more information.

 

Are There Other Ways for Oracle to Trim Strings?

Yes, there are a few ways you can trim strings in Oracle:

  • Use LTRIM or RTRIM
  • Use TRIM
  • Use REPLACE
  • Use SUBSTR if you need more advanced trimming features
  • Use regular expressions

 

Examples of the LTRIM Function

Here are some examples of the Oracle LTRIM function. I find that examples are the best way for me to learn about code, even with the explanation above.

Example 1

This example demonstrates a simple LTRIM with no trim value specified.

SELECT LTRIM('    Complete IT Professional')
AS LTRIM_EXAMPLE FROM DUAL;

Result:

LTRIM_EXAMPLEComplete IT Professional

The extra spaces are removed from the original value.

 

Example 2

This example uses a specific value to trim.

SELECT LTRIM('___Complete IT Professional', '_')
AS LTRIM_EXAMPLE FROM DUAL;

Result:

LTRIM_EXAMPLEComplete IT Professional

The underscores are removed from the original value.

Example 3

This example uses LTRIM with several characters as the string to trim.

SELECT LTRIM('; ; ; ; ; Complete IT Professional', ' ; ')
AS LTRIM_EXAMPLE FROM DUAL;

Result:

LTRIM_EXAMPLEComplete IT Professional

Both the spaces and semicolons are removed from the original value.

 

Example 4

This example uses LTRIM  on data in a table, instead of providing a value.

SELECT country, LTRIM(country, 'U')
AS LTRIM_EXAMPLE FROM customers;

Result:

COUNTRYLTRIM_EXAMPLEUSASAUSASACanadaCanadaUKKUSASA(null)(null)FranceFrance(null)(null)

The capital U is removed from several values.

 

Example 5

This example uses LTRIM on data in a table with several characters in the trim parameter.

SELECT full_address, LTRIM(full_address, '1')
AS LTRIM_EXAMPLE FROM customers;

Result:

FULL_ADDRESSLTRIM_EXAMPLE10 Long Road0 Long Road50 Market Street50 Market Street201 Flinders Lane201 Flinders Lane8 Smith Street8 Smith Street14 Wellington Road4 Wellington Road80 Victoria Street80 Victoria Street5 Johnson St5 Johnson St155 Long Road55 Long Road

The “1” characters are removed from several address values.

 

Example 6

This example uses LTRIM  with 0 as the parameter.

SELECT LTRIM('000Complete IT Professional', 0)
AS LTRIM_EXAMPLE FROM DUAL;

Result:

LTRIM_EXAMPLEComplete IT Professional

The zeroes are removed from the original value.

 

Example 7

This example uses both LTRIM and RTRIM in the one expression.

SELECT LTRIM(RTRIM('___Complete IT Professional__', '_'), '_')
AS RTRIM_EXAMPLE FROM DUAL;

Result:

LTRIM_EXAMPLEComplete IT Professional

The underscores are removed from both sides of the original value.

 

Example 8

This example uses Unicode characers as the trim parameter.

SELECT LTRIM('ééComplete IT Professional', 'é')
AS LTRIM_EXAMPLE FROM DUAL;

Result:

LTRIM_EXAMPLEComplete IT Professional

The accented “e” character is removed from the original value.

Similar Functions

Some functions which are similar to the LTRIM function are:

  • RTRIM – Trims characters from the right of the string. The opposite of the LTRIM function.
  • TRIM – Trims characters from both the left and right side of the string. A combination of LTRIM and RTRIM.
  • SUBSTR – Extracts one value from a larger value. Not really a TRIM function but does something similar.
  • REPLACE – Replaces occurrences of one text value with another.

You can find a full list of Oracle functions here.

Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!

Image courtesy of digitalart / FreeDigitalPhotos.net

Categories: Development

APEX 5 - Opening and Closing Modal Window

Denes Kubicek - Wed, 2015-07-15 05:56
This example is showing how to open a Modal Page from any element in your application. It is easy to get it working using some standards like a button or a link in a report. However, it is not 100% clear how to get it working with some other elements which don't have the redirect functionality built in (item, region title, custom links, etc.). This example is also showing how to get the success message displayed on the parent page after closing of the Modal Page.

Categories: Development

How To Keep Calm at Work

Complete IT Professional - Mon, 2015-07-13 06:00

How To Keep Calm at WorkAs part of your job in the IT industry, there will no doubt be times that you will get annoyed and stressed. Learn how to keep calm at work with some tips in this article.

Take a Deep Breath

When I’m not feeling calm at work, the first thing I do is stop and take a big, deep breath. It makes me stop what I’m doing for a few seconds and focus just on the breath that I’m taking.

It always makes me feel better, especially when I’m annoyed about something.

So, next time you’re feeling stressed, annoyed, or irritated about something at work, try this.

  • Stop what you’re dong.
  • Take one big deep breath in.
  • Breathe it all out.
  • Resume what you were doing.

That whole process only takes about ten seconds. But, it allows you to re-focus on what you’re doing and can have a calming effect on you.

 

Remember the Bigger Picture

Another tip that I can suggest to keep calm at work is to remember the bigger picture of why you’re there.

If you’re getting annoyed at someone else or having a disagreement with them over the way a certain feature should be developed for your project, for example, then one thing you can do is to try think of the bigger picture.

What’s the overall plan for what you’re doing? What are you both trying to achieve?

If you have different ideas on how something should be done, for example, you could be disagreeing over how it’s being done. Think about why you’re both there, on the project.

Are you trying to add a new feature to make the customers’ lives easier? Are you trying to improve an internal system? Make a new system from scratch?

If you can see what you’re both trying to do at a high level, and see things from the other person’s point of view, it can help you keep calm at work.

 

Get Away From Your Desk

Another tip I can suggest is to get away form your desk if you want to calm down.

You can try go for a walk outside. Get out of the office and get some fresh air. Walk around the block, down to th ecar park, to the coffee shop – anything to get outside and get some fresh air.

The exercise, the fresh air, and taking a break from your work all contribute to helping you keep calm at work. It can also give you more energy.

If you can’t walk outside for whatever reason, if it’s pouring with rain for example, then take a walk to the ktichen or somewhere else inside.

You can go get a coffee or a glass of water or something. This can help you feel better at work and also gets you away from the desk.

In any case, when you return to what you were doing, you should feel a little calmer and a little better.

 

Vent To A Friend

We can do all of the breathing and walking outside that we want, but sometimes, it just isn’t enough.

I mean, most of the time it works for me, but there’s been a few times over the years where I’ve felt really annoyed.

If you feel the same way, then speak to a friend of yours about it and tell them the story.

You can speak to your partner, a friend outside work, a family member, or someone in your team, depending on the issue.

Sometimes it can really help if you just get it off your shoulders and just express the way you’re feeling to someone.

It’s not something I suggest doing all of the time, but it can help sometimes if you really just can’t focus on your work and whatever the issue is has really thrown you off your game.

 

Readers: What methods do you use to keep calm at work after something goes wrong? Do you have any other tips to share about staying calm? Share them in the comments section below.

Career Action Tip: Next time you’re feeling annoyed about something at work, remember to take a deep breath and consider going for a short walk outside.

Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!

Image courtesy of stockimages / FreeDigitalPhotos.net

Categories: Development

Hot Deployment in JDeveloper 12c - Don't Stop/Start Your App

Shay Shmeltzer - Fri, 2015-07-10 16:45

Old habits are hard to get rid off, and I still see long time users of JDeveloper (and also many new users) who are stopping/starting their application on the embedded WebLogic each time that they make a change or addition to their code.

Well you should stop it! (I mean stop stopping the application).

For a while now, JDeveloper has support for hot deployment that means that when you do most of the changes to your code you just need to do save-all followed by a rebuild of your viewController project - and that's it.

You can then go to your browser and reload your page - and the changes will be reflected there.  This will not only save you the time it takes to undeploy and redeploy your app, it will also reduce the amount of memory you use since frequent redeployment of the app on the embedded WebLogic leads to bigger memory consumption.

In the demo below I use JDeveloper 12.1.3 to show you that I can just do the save->rebuild and pick up:

  • Changes in the JSF file
  • Changes to the JSF configuration file adfc-config.xml
  • New classes that are added to both the model and viewController projects
  • Changed to the ADF configuration files (pagedefs, data binding, data controls)

So for most cases, you should be covered with this hot-deployment capability.

There are some cases that will require a redeploy of the application (for example if you add a new skin css file, or if you change some runtime configuration of your app in web.xml) but for most cases you don't need to continue with the stop/start habit.

Categories: Development

How To Turn On or Off SQL Developer Autocommit Settings

Complete IT Professional - Fri, 2015-07-10 06:00

SQL Developer AutocommitIn this article, I’ll explain what the SQL Developer Autocommit feature is, where to find it, and how to turn it on or off.

What Is SQL Developer Autocommit?

When you write an SQL statement in SQL Developer, you perform an operation on that data. If you’re just reading the data, this is pretty straightforward.

Where it acts differently is when you change data in the database. This is usually done with the DELETE, INSERT, or UPDATE statements.

If you run an SQL statement that does any of these functions, then the data will be updated, but it won’t be committed.

If you’re not sure what committed means (when we talk about databases), it just means that the data is not permanently saved. It allows you to see the changes in your session, and then you can make a conscious decision to update the changes permanently (committing) or undoing the changes (rolling back).

Now, this is the default behaviour. There are two separate steps to this – running the statement to change the data, and then a second command to commit the changes to the database.

SQL Developer allows you to change this default behaviour, so that the statement is run and the changes are saved in a single step – hence the term “auto commit”.

 

How Do I Change The Autocommit Setting in SQL Developer?

SQL Developer is pretty flexible when it comes to what you can change, and autocommit is no exception.

To find the autocommit setting, go to the Tools > Preferences.

SQL Developer Tools and Preferences

The Preferences window will appear.

SQL Developer Preferences

Click on the + icon next to Database to expand it.

SQL Developer Preferences

Then, click on Advanced.

SQL Developer Preferences Advanced

Here, you’ll see the option for Autocommit.

SQL Developer Preferences Autocommit

Click the checkbox to turn it on.

Here’s what the setting does:

ValueImpactCheckedAutocommit is on. SQL statements that change data will have their changes automatically committed to the database.UncheckedDefault. Autocommit is off. SQL statements that change data will not have their changes automatically committed to the database. A separate COMMIT action is needed.
What Are The Disadvantages of Autocommit?

Sure, turning on autocommit may save you time and confusion if you expect changes to be saved but they aren’t.

But there are some disadvantages.

You won’t be able to undo any of your changes by issuing a ROLLBACK statement, as you’d normally be able to do. If you run a DELETE or UPDATE statement, your changes are made permanently, and it’s much harder to get them back

It’s not impossible, though. If you have set up save points or database backups then you can restore from a backup, but it is a hassle.

Generally, I would prefer to have this autocommit option turned off in SQL Developer. The only exception would be if you’re running Oracle Express on your own computer and want to save some time with your SQL.

Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!

Categories: Development

Oracle RTRIM Function with Examples

Complete IT Professional - Thu, 2015-07-09 06:00

Oracle RTRIM FunctionThe Oracle RTRIM function is one of the most commonly used string manipulation functions in SQL. Learn more about it and see some examples in this article.

Purpose of the Oracle RTRIM Function

The Oracle RTRIM function removes all characters that you specify from the right side of a string.

This is most often used when you want to remove spaces from the right side of the string. However, it can be used for other characters as well, depending on the data.

 

Syntax

The syntax for the Oracle RTRIM function is:

RTRIM ( input_string, [trim_character])

 

Parameters

The parameters of the RTRIM function are:

  • input_string (mandatory): This is the string that will have the characters trimmed from it.
  • trim_string (optional): This is the value to trim or remove from the input_string. If it is omitted, a space character is used for trimming.

The R in RTRIM stands for Right, because it removes the characters from the right of the string.

Some other points to remember:

  • If the trim_string is a literal value, you need to include it inside single quotes. For example, to remove an underscore, you need to specify it as ‘_’
  • Both parameters can be any of the types CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.
  • The string returned is a VARCHAR2 data type if the input_string was a character, and LOB if it was a LOB data type.
  • The trim_string can be more than one character
  • The function removes each individual value inside trim_string, not the string as a whole. See the Examples section below for more information.

 

Can You Use Oracle RTRIM with Special Characters?

Yes, you can. You can use exclamation points and many kinds of special characters as input.

You can also use unicode characters, such as å, as the function supports NVARCHAR2 types.

See the Examples section for more information.

 

Can You Use Oracle RTRIM with LTRIM?

Yes, you can. It works similar to the TRIM function.

You’ll need to enclose one within the other, and it doesn’t matter which one is outside and which is inside.

So, LTRIM(RTRIM(value)) works the same as RTRIM(LTRIM(value)).

See the Examples section for more information.

 

Can You Use Oracle RTRIM with CHAR or LOB Types?

Yes, the Oracle RTRIM can handle CHAR and LOB types. It can handle CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB data types.

 

Examples of the RTRIM Function

Here are some examples of the RTRIM function. I find that examples are the best way for me to learn about code, even with the explanation above.

 

Example 1

This example demonstrates a simple RTRIM with no trim value specified.

SELECT RTRIM('Complete IT Professional   ')
AS RTRIM_EXAMPLE FROM DUAL;

Result:

RTRIM_EXAMPLEComplete IT Professional

The extra spaces are removed from the original value.

 

Example 2

This example uses a specific value to trim.

SELECT RTRIM('Complete IT Professional___', '_')
AS RTRIM_EXAMPLE FROM DUAL;

Result:

RTRIM_EXAMPLEComplete IT Professional

The underscores are removed from the original value.

 

Example 3

This example uses RTRIM with several characters as the string to trim.

SELECT RTRIM('Complete IT Professional; ; ; ; ; ', ' ; ')
AS RTRIM_EXAMPLE FROM DUAL;

Result:

RTRIM_EXAMPLEComplete IT Professional

Both the spaces and semicolons are removed from the original value.

 

Example 4

This example uses RTRIM on data in a table, instead of providing a value.

SELECT country, RTRIM(country, 'A')
AS RTRIM_EXAMPLE FROM customers;

Result:

COUNTRYRTRIM_EXAMPLEUSAUSUSAUSCanadaCanadaUKUKUSAUS(null)(null)FranceFrance(null)(null)

The capital A is removed from the “USA” values. It remains on the end of “Canada” because it is a lower-case “a”.

 

Example 5

This example uses RTRIM on data in a table with several characters in the trim parameter.

SELECT full_address, RTRIM(full_address, 'et')
AS RTRIM_EXAMPLE FROM customers;
Result:
FULL_ADDRESSRTRIM_EXAMPLE10 Long Road10 Long Road50 Market Street50 Market Str201 Flinders Lane201 Flinders Lan8 Smith Street8 Smith Str14 Wellington Road14 Wellington Road80 Victoria Street80 Victoria Str5 Johnson St5 Johnson S155 Long Road155 Long Road
The letters “e” and “t” are removed from the right of the street names. Some values have been changed to “Str” because all occurrences of these letters are removed, not just those that end in “et”.
Example 6

This example uses RTRIM with 0 as the parameter.

SELECT RTRIM('Complete IT Professional00', 0)
AS RTRIM_EXAMPLE FROM DUAL;

Result:

RTRIM_EXAMPLEComplete IT Professional

The zeroes are removed from the original value.

 

Example 7

This example uses both LTRIM and RTRIM in the one expression.

SELECT LTRIM(RTRIM('___Complete IT Professional__', '_'), '_')
AS RTRIM_EXAMPLE FROM DUAL;

Result:

RTRIM_EXAMPLEComplete IT Professional

The underscores are removed from both sides of the original value.

 

Example 8

This example uses Unicode characers as the trim parameter.

SELECT RTRIM('Complete IT Professionalé', 'é')
AS RTRIM_EXAMPLE FROM DUAL;

Result:

RTRIM_EXAMPLEComplete IT Professional

The accented “e” character is removed from the original value.

 

Similar Functions

Some functions which are similar to the RTRIM function are:

  • TRIM – This function trims characters from both the left and the right of the specified value.
  • LTRIM – This function trims characters from the left of the specified value only.
  • SUBSTR – Extracts one value from a larger value. Not really a TRIM function but does something similar.
  • REPLACE – Replaces occurrences of one text value with another.

You can find a full list of Oracle functions here.

Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!

Image courtesy of digitalart / FreeDigitalPhotos.net

Categories: Development

12c: New SQL PLAN OPERATIONS and HINTS

XTended Oracle SQL - Wed, 2015-07-08 08:27

This post is just a compilation of the links to other people’s articles and short descriptions about new SQL PLAN OPERATIONS and HINTS with a couple little additions from me.

th.c_operation_name { min-width:100px; max-width:100px; } th.c_description { min-width:200px } .c_links { min-width:150px; max-width:220px; } .c_links ul { margin: 0 0 5px 0 !important; -webkit-padding-start: 5px; } .c_links ul li { margin-left: 0px; -webkit-padding-start: 0px; } td.c_operation_name { font-size:12px;} td.c_description { font-size:12px;} td.c_links { font-size:10px;} .c_body td { vertical-align: text-top; } div.hints_wrapper { border-style: solid; border-width: 1px; padding: 2px; overflow: scroll !important; } div.hints_content { width: 1175px; min-width:1175px; padding: 2px; }


OPERATION_NAME Description Links JSONTABLE EVALUATION JSON_TABLE execution XMLTABLE EVALUATION This is new name for “COLLECTION ITERATOR PICKLER FETCH [XQSEQUENCEFROMXMLTYPE]”. XPATH EVALUATION still exists. MATCH RECOGNIZE New feature “PATTERN MATCHING” STATISTICS COLLECTOR Optimizer statistics collector OPTIMIZER STATISTICS GATHERING Automatic Optimizer statistics gathering during the following types of bulk loads:

  • CREATE TABLE … AS SELECT
  • INSERT INTO … SELECT into an empty table using a direct-path insert
CUBE JOIN Joining Cubes to Tables and Views EXPRESSION EVALUATION Each parallel slave executes scalar correllated subqueries from SELECT-list. parallel “FILTER” Each parallel slave executes own FILTER operation

Example
SQL> explain plan for
  2  select--+ parallel
  3      owner,object_name
  4  from xt_test l
  5  where exists(select/*+ no_unnest */ 0 from dual where dummy=object_name);

Explained.

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
Plan hash value: 2189761709

-------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name      | Rows  | Bytes | Cost (%CPU)|   TQ  |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |           |     2 |    62 |   177K  (1)|       |      |            |
|   1 |  PX COORDINATOR          |           |       |       |            |       |      |            |
|   2 |   PX SEND QC (RANDOM)    | :TQ10000  | 91060 |  2756K|   113   (0)| Q1,00 | P->S | QC (RAND)  |
|*  3 |    FILTER                |           |       |       |            | Q1,00 | PCWC |            |
|   4 |     PX BLOCK ITERATOR    |           | 91060 |  2756K|   113   (0)| Q1,00 | PCWC |            |
|   5 |      INDEX FAST FULL SCAN| IX_TEST_1 | 91060 |  2756K|   113   (0)| Q1,00 | PCWP |            |
|*  6 |     TABLE ACCESS FULL    | DUAL      |     1 |     2 |     2   (0)|       |      |            |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "SYS"."DUAL" "DUAL" WHERE "DUMMY"=:B1))
   6 - filter("DUMMY"=:B1)
                

[collapse]
PX SELECTOR Execution of the serial plan parts in the one of the parallel slaves PX SEND 1 SLAVE Execution of the serial plan parts in the one of the parallel slaves(single DFO tree) PX TASK Parallel access to fixed tables(x$) by each node in RAC HYBRID HASH DISTRIBUTION Adaptive parallel data distribution that does not decide the final data distribution(HASH, BROADCAST or SKEW) method until execution time. PQ_DISTRIBUTE_WINDOW In addition to “PX SEND” HASH-distribution for WINDOW functions, “PX SEND RANGE” was added
Example
-- TESTPART - list-partitiioned table:
-------------------------------------------------------------------------------------------------
| Operation               | Name     | Rows  | Cost | Pstart| Pstop |   TQ  |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------
| SELECT STATEMENT        |          | 74384 |   102|       |       |       |      |            |
|  PX COORDINATOR         |          |       |      |       |       |       |      |            |
|   PX SEND QC (RANDOM)   | :TQ10001 | 74384 |   102|       |       | Q1,01 | P->S | QC (RAND)  |
|    WINDOW SORT          |          | 74384 |   102|       |       | Q1,01 | PCWP |            |
|     PX RECEIVE          |          | 74384 |   100|       |       | Q1,01 | PCWP |            |
|      PX SEND RANGE      | :TQ10000 | 74384 |   100|       |       | Q1,00 | P->P | RANGE      |
|       PX BLOCK ITERATOR |          | 74384 |   100|     1 |     3 | Q1,00 | PCWC |            |
|        TABLE ACCESS FULL| TESTPART | 74384 |   100|     1 |     3 | Q1,00 | PCWP |            |
-------------------------------------------------------------------------------------------------
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      PQ_DISTRIBUTE_WINDOW(@"SEL$1" 3)
      FULL(@"SEL$1" “TESTPART"@"SEL$1")
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      DB_VERSION('12.1.0.2')
      OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

[collapse]

Hint PQ_DISTRIBUTE_WINDOW(@Query_block N), where N=1 for hash, N=2 for range, N=3 for list VECTOR
KEY VECTOR Inmemory aggregation RECURSIVE ITERATION Unknown WINDOW CONSOLIDATOR WINDOW CONSOLIDATOR BUFFER for parallel execution of analyrical WINDOW aggregation functions

Example
SQL> explain plan for select/*+ parallel(t 4) PQ_DISTRIBUTE_WINDOW(2) */ count(*) over(partition by owner) cnt,owner from xt_test t;
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
Plan hash value: 3410952625
---------------------------------------------------------------------------------------------------
| Id | Operation                    |Name    |Rows |Cost |Pstart|Pstop|   TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT             |        |91060|  124|      |     |       |      |            |
|  1 |  PX COORDINATOR              |        |     |     |      |     |       |      |            |
|  2 |   PX SEND QC (RANDOM)        |:TQ10001|91060|  124|      |     | Q1,01 | P->S | QC (RAND)  |
|  3 |    WINDOW CONSOLIDATOR BUFFER|        |91060|  124|      |     | Q1,01 | PCWP |            |
|  4 |     PX RECEIVE               |        |91060|  124|      |     | Q1,01 | PCWP |            |
|  5 |      PX SEND HASH            |:TQ10000|91060|  124|      |     | Q1,00 | P->P | HASH       |
|  6 |       WINDOW SORT            |        |91060|  124|      |     | Q1,00 | PCWP |            |
|  7 |        PX BLOCK ITERATOR     |        |91060|  122|    1 |    4| Q1,00 | PCWC |            |
|  8 |         TABLE ACCESS FULL    |XT_TEST |91060|  122|    1 |    4| Q1,00 | PCWP |            |
---------------------------------------------------------------------------------------------------

Note
-----
   - Degree of Parallelism is 4 because of table property
                

[collapse]
DETECT END Unknown DM EXP MAX AGGR Unknown DM EXP MAX PAR Unknown FAULT-TOLERANCE BUFFER The fault-tolerance for parallel statement.
Patent #US8572051: Making parallel execution of structured query language statements fault-tolerant
  • PX_FAULT_TOLERANCE / NO_PX_FAULT_TOLERANCE hints


See also:

  1. Randolf Geist “12c New Optimizer Features”
  2. Randolf Geist “Parallel Execution 12c New Features Overview”


HINTS:

sup {color: red} table.HINTS{ font-size:12px; } .HINTS td {vertical-align: text-top;}

PATH HINT_CLASS HINT_NAME VERSION VERSION_OUTLINE ALL WITH_PLSQL WITH_PLSQL 12.1.0.1 ALL -> ANSI_REARCH ANSI_REARCH 1 ANSI_REARCH
NO_ANSI_REARCH 12.1.0.2 12.1.0.2 ALL -> EXECUTION BATCH_TABLE_ACCESS_BY_ROWID 2 BATCH_TABLE_ACCESS_BY_ROWID
NO_BATCH_TABLE_ACCESS_BY_ROWID 12.1.0.1 12.1.0.1 INMEMORY INMEMORY
NO_INMEMORY 12.1.0.2 12.1.0.2 INMEMORY_PRUNING INMEMORY_PRUNING
NO_INMEMORY_PRUNING 12.1.0.2 12.1.0.2 ALL -> COMPILATION -> ZONEMAP ZONEMAP ZONEMAP
NO_ZONEMAP 12.1.0.1 12.1.0.1 ALL -> COMPILATION -> DATA_SECURITY_REWRITE DATA_SECURITY_REWRITE_LIMIT DATA_SECURITY_REWRITE_LIMIT

NO_DATA_SECURITY_REWRITE 12.1.0.1 12.1.0.1 ALL -> COMPILATION -> CBO CLUSTER_BY_ROWID CLUSTER_BY_ROWID
CLUSTER_BY_ROWID 12.1.0.1(11.2.0.4) 12.1.0.1 ALL -> COMPILATION -> CBO -> ACCESS_PATH -> BITMAP_TREE BITMAP_AND BITMAP_AND 12.1.0.1 12.1.0.1 ALL -> COMPILATION -> CBO -> ADAPTIVE_PLAN ADAPTIVE_PLAN ADAPTIVE_PLAN
NO_ADAPTIVE_PLAN 12.1.0.2 12.1.0.2 ALL -> COMPILATION -> CBO -> AUTO_REOPT AUTO_REOPTIMIZE 2 AUTO_REOPTIMIZE
NO_AUTO_REOPTIMIZE 12.1.0.1 ALL -> COMPILATION -> CBO -> JOIN_METHOD ANTIJOIN CUBE_AJ 12.1.0.1 12.1.0.1 SEMIJOIN CUBE_SJ 12.1.0.1 12.1.0.1 ALL -> COMPILATION -> CBO -> JOIN_METHOD -> USE_CUBE JOIN USE_CUBE

NO_USE_CUBE 12.1.0.1 12.1.0.1 ALL -> COMPILATION -> CBO -> PARTIAL_JOIN PARTIAL_JOIN PARTIAL_JOIN
NO_PARTIAL_JOIN 12.1.0.1 12.1.0.1 ALL -> COMPILATION -> CBO -> PARTITION USE_HIDDEN_PARTITIONS USE_HIDDEN_PARTITIONS 12.1.0.1 ALL -> COMPILATION -> CBO -> PQ PARTIAL_ROLLUP_PUSHDOWN PARTIAL_ROLLUP_PUSHDOWN

NO_PARTIAL_ROLLUP_PUSHDOWN 12.1.0.1 12.1.0.1 PQ_CONCURRENT_UNION PQ_CONCURRENT_UNION

NO_PQ_CONCURRENT_UNION 12.1.0.1 12.1.0.1 PQ_DISTRIBUTE_WINDOW PQ_DISTRIBUTE_WINDOW 12.1.0.1 12.1.0.1 PQ_FILTER PQ_FILTER 12.1.0.1 12.1.0.1 PQ_SKEW PQ_SKEW

NO_PQ_SKEW 12.1.0.1 12.1.0.1 PX_FAULT_TOLERANCE PX_FAULT_TOLERANCE
NO_PX_FAULT_TOLERANCE 12.1.0.1 12.1.0.1 ALL -> COMPILATION -> CBO -> PQ -> PQ_REPLICATE PQ_REPLICATE PQ_REPLICATE
NO_PQ_REPLICATE 12.1.0.1 12.1.0.1 ALL -> COMPILATION -> CBO -> STATS -> DBMS_STATS GATHER_OPTIMIZER_STATISTICS GATHER_OPTIMIZER_STATISTICS

NO_GATHER_OPTIMIZER_STATISTICS 12.1.0.1 ALL -> COMPILATION -> TRANSFORMATION ELIM_GROUPBY ? ELIM_GROUPBY

NO_ELIM_GROUPBY ALL -> COMPILATION -> CBO -> CBQT -> VECTOR_AGG
and

ALL -> COMPILATION -> TRANSFORMATION -> CBQT -> VECTOR_AGG USE_VECTOR_AGGREGATION USE_VECTOR_AGGREGATION
NO_USE_VECTOR_AGGREGATION 12.1.0.2 12.1.0.2 VECTOR_TRANSFORM VECTOR_TRANSFORM

NO_VECTOR_TRANSFORM 12.1.0.2 12.1.0.2 VECTOR_TRANSFORM_DIMS VECTOR_TRANSFORM_DIMS

NO_VECTOR_TRANSFORM_DIMS 12.1.0.2 12.1.0.2 VECTOR_TRANSFORM_FACT VECTOR_TRANSFORM_FACT

NO_VECTOR_TRANSFORM_FACT 12.1.0.2 12.1.0.2

ALL -> COMPILATION -> TRANSFORMATION -> HEURISTIC -> DECORRELATE DECORRELATE DECORRELATE

NO_DECORRELATE 12.1.0.1 12.1.0.1

See also:
Fuyuncat(Wei Huang) – “Oracle 12c new SQL Hints”

Categories: Development

APEX 5.0 New Features Training September 2015

Denes Kubicek - Wed, 2015-07-08 07:12




Oracle Application Express 5.0 wurde am 15.04.2015 freigegeben und ist als Download verfügbar.

Wir haben jetzt alle über zwei Jahre auf das neue Release gewartet ... und das Warten hat sich mehr als gelohnt ... die neuen Möglichkeiten werden Sie umhauen!

Wir haben die neuen Möglichkeiten genau unter die Lupe genommen und sind selbst total begeistert, weil sie das tägliche Arbeiten extrem vereinfachen. Wir haben schon einige Anwendungen in APEX 5.0 neu programmiert und einige ältere Versionan auf 5.0 umgestellt.

Mit dem neuen Pagedesigner sind wir so produktiv wie noch nie, das Universal Theme erlaubt auf einfache Weise, richtig elegante Applikationen zu entwickeln. Damit ist wirklich ein Durchbruch gelungen, die Konfiguration (über Template Optionen) und selbst farbliche Anpassungen sind ein Kinderspiel, der integrierte Theme Roller ist einfach genial!

Modale Dialoge, multiple Interaktive Berichte pro Seite, Erweiterungen für die mobilen Endgeräte, ein komplett neues File-Handling und viele neue Security - Features ... dieses Release ist wirklich umfangreich!

Neben diesen großen Features wurden auch wieder über 100 kleinere oder auch größere Verbesserungen implementiert.

In unseren Kursen haben wir schon über 200 APEX-Fans die besten Herangehensweisen, Tipps und Tricks beigebracht. Durch die Hands-On Übungen vertiefen wir diese und Sie können diese sofort einsetzen ... oder Sie schlagen sie nach ... wenn Sie sie später brauchen ;). Aber auf jeden Fall wissen Sie nach dem Kurs, was möglich ist!

Wir beide (Denes und Dietmar) entwickeln seit 2006 fast jeden Tag mit APEX Applikationen für unsere Kunden, wir haben mit APEX schon alles ausprobiert.

Nehmen Sie die Abkürzung und lernen Sie von den Besten, was für die Praxis am wichtigsten sein wird.

Um die Vorteile des neuen Releases möglichst schnell nutzen zu können, melden Sie sich am besten sofort an und sichern Sie sich Ihren Platz!
  • Klicken Sie auf den Link "Anmeldung zum Kurs".
  • Tragen Sie Ihre Anmeldedaten ein und klicken Sie auf den Button "Anmelden".
  • Sie bekommen sofort eine Bestätigungs-Email zugeschickt.
  • Sobald Sie in der Email auf den Link zur Bestätigung klicken, haben Sie Ihren Platz gesichert und sind auf jeden Fall dabei!


Anmeldung zum Kurs

P.S.: Die vollständige Agenda und weitere Infos zum Kurs gibt es online in der Kursbeschreibung.
Categories: Development

How To Turn On Line Numbers in SQL Developer

Complete IT Professional - Wed, 2015-07-08 06:00

SQL Developer Line NumbersIn this article, I’ll show you what line numbers are in SQL Developer and how to turn this setting on.

What Are Line Numbers in SQL Developer?

Line numbers are numbers that are next to each line in the Code Editor. They indicate which line of code you’re working on.

They are helpful for developers for many reasons:

  • They let you see how many lines of code you’ve written
  • They help with debugging errors, as error messages usually refer to a line number (related: 7 Debugging Tips To Rapidly Improve Your Programming)
  • They let you refer to a specific line from another place (e.g. when talking to another developer)

SQL Developer - no line numbersBy default, the line numbers are turned off in SQL Developer. Many IDEs have them on by default. I much prefer to have them on, and I’m sure you do too.

Let’s take a look at how to toggle the SQL Developer line numbers setting.

 

 

 

How To Turn Line Numbers On in SQL Developer

So, the first step is to go to Tools > Preferences.

SQL Developer - Tools and Preferences

The Preferences window is now shown.

SQL Developer - Preferences

Then, click on the + next to Code Editor to expand that section.

SQL Developer - Code Editor Preferences

 

Then, click on Line Gutter.

SQL Developer - Line Gutter Preferences

The Show Line Numbers option is then shown.

SQL Developer - Show Line Numbers option

If you check this, the line numbers will appear in your code.

SQL Developer - Show Line Numbers

 

 

SQL Developer Line Numbers Possible Values

So, the Line Numbers setting can be changed to:

ValueImpactCheckedLine numbers are shown in the Code Editor windowUncheckedDefault. Line numbers are not shown in the Code Editor window.
If you’re wondering how to turn on SQL Developer line numbers, then this is how it’s done, and this is the impact.

Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!

Categories: Development

RESULT_CACHE: run-time dependency tracking

XTended Oracle SQL - Sun, 2015-07-05 14:20

As you know, since 11.2 “relies_on” clause was deprecated and oracle tracks dependencies at runtime now.

Test function and tables
create or replace function f_without_deps(p_tab varchar2) return varchar2
as
   res varchar2(30);
begin
   execute immediate 'select '''||p_tab||''' from '||p_tab||' where rownum=1' into res;
   return res;
end;
/
create table a as select 'a' a from dual;
create table b as select 'b' b from dual;
create view v_ab as select a,b from a,b;

[collapse]

And it works fine with normal tables:
v_ab
SQL> exec :p_tab:='v_ab';

PL/SQL procedure successfully completed.

SQL> call DBMS_RESULT_CACHE.flush();

Call completed.

SQL> select/*+ result_cache */ f_without_deps(:p_tab) result from dual;

RESULT
----------
v_ab

1 row selected.

SQL> select name,bucket_no, id,type,status,pin_count,scan_count,invalidations from v$result_cache_objects o order by type,id;

NAME                                                               BUCKET_NO  ID TYPE       STATUS     PIN_COUNT SCAN_COUNT INVALID
----------------------------------------------------------------- ---------- --- ---------- --------- ---------- ---------- -------
XTENDER.F_WITHOUT_DEPS                                                  1579   0 Dependency Published          0          0       0
XTENDER.V_AB                                                            3127   2 Dependency Published          0          0       0
XTENDER.B                                                                778   3 Dependency Published          0          0       0
XTENDER.A                                                                464   4 Dependency Published          0          0       0
select/*+ result_cache */ f_without_deps(:p_tab) result from dual       1749   1 Result     Published          0          0       0

[collapse]

But don’t forget that the result_cache also caches such functions with the objects, that usually should not be cached, and such objects will not be listed in the result_cache dependencies list:
v$database
SQL> exec :p_tab:='v$database';

PL/SQL procedure successfully completed.

SQL> call DBMS_RESULT_CACHE.flush();

Call completed.

SQL> select/*+ result_cache */ f_without_deps(:p_tab) result from dual;

RESULT
----------
v$database

1 row selected.

SQL> select name,bucket_no, id,type,status,pin_count,scan_count,invalidations from v$result_cache_objects o order by type,id;

NAME                                                               BUCKET_NO  ID TYPE       STATUS     PIN_COUNT SCAN_COUNT INVALID
----------------------------------------------------------------- ---------- --- ---------- --------- ---------- ---------- -------
XTENDER.F_WITHOUT_DEPS                                                   772   0 Dependency Published          0          0       0
PUBLIC.V$DATABASE                                                       1363   2 Dependency Published          0          0       0
select/*+ result_cache */ f_without_deps(:p_tab) result from dual       2283   1 Result     Published          0          0       0

3 rows selected.

[collapse]
As you can see, there is only dependency on public synonym V$DATABASE, but not real base fixed X$-tables.
SYS.OBJ$
SQL> exec :p_tab:='sys.obj$';

PL/SQL procedure successfully completed.

SQL> call DBMS_RESULT_CACHE.flush();

Call completed.

SQL> select/*+ result_cache */ f_without_deps(:p_tab) result from dual;

RESULT
----------
sys.obj$

1 row selected.

SQL> select name,bucket_no, id,type,status,pin_count,scan_count,invalidations from v$result_cache_objects o order by type,id;

NAME                                                               BUCKET_NO ID TYPE       STATUS     PIN_COUNT SCAN_COUNT INVALID
----------------------------------------------------------------- ---------- -- ---------- --------- ---------- ---------- -------
XTENDER.F_WITHOUT_DEPS                                                  3922  0 Dependency Published          0          0       0
select/*+ result_cache */ f_without_deps(:p_tab) result from dual       3753  1 Result     Published          0          0       0

2 rows selected.

[collapse]
The results were cached and the dependencies do not include system objects.
We easily check that the queries with any table in SYS schema or with sysdate,systimestamp,current_date,current_timestamp,dbms_random will not be cached:
SYS tables
SQL> select/*+ result_cache */ current_scn result from v$database;

    RESULT
----------
##########

1 row selected.

SQL> select name,bucket_no, id,type,status,pin_count,scan_count,invalidations from v$result_cache_objects o order by type,id;

no rows selected

SQL> explain plan for select/*+ result_cache */ * from sys.obj$;

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
Plan hash value: 2311451600

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 87256 |  7328K|   296   (1)| 00:00:04 |
|   1 |  TABLE ACCESS FULL| OBJ$ | 87256 |  7328K|   296   (1)| 00:00:04 |
--------------------------------------------------------------------------

[collapse]
Note that there is no “RESULT CACHE” line.
And even if create own tables in SYS schema(don’t do it :)), they will not be cached :
SYS.V_AB
SYS> create table a as select 'a' a from dual;
SYS> create table b as select 'b' b from dual;
SYS> create view v_ab as select a,b from a,b;
SYS> grant select on v_ab to xtender;

XTENDER> explain plan for select/*+ result_cache */ * from sys.v_ab;
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
Plan hash value: 215283502

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     1 |     6 |     4   (0)| 00:00:01 |
|   1 |  MERGE JOIN CARTESIAN|      |     1 |     6 |     4   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL  | A    |     1 |     3 |     2   (0)| 00:00:01 |
|   3 |   BUFFER SORT        |      |     1 |     3 |     2   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL | B    |     1 |     3 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)

[collapse]

But sys_context and userenv will be cached successbully:
sys_context
SQL> explain plan for select/*+ result_cache */ sys_context('userenv','os_user')  from dual;

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
Plan hash value: 1388734953

---------------------------------------------------------------------------------------
| Id  | Operation        | Name                       | Rows  | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                            |     1 |     2   (0)| 00:00:01 |
|   1 |  RESULT CACHE    | 267m2hcwj08nq5kwxcb0nb2ka8 |       |            |          |
|   2 |   FAST DUAL      |                            |     1 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Result Cache Information (identified by operation id):
------------------------------------------------------

   1 - column-count=1; attributes=(single-row); parameters=(sys_context);
        name="select/*+ result_cache */ sys_context('userenv','os_user')  from dual"

14 rows selected.

[collapse]
userenv
SQL> explain plan for select/*+ result_cache */ userenv('instance')  from dual;

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
Plan hash value: 1388734953

---------------------------------------------------------------------------------------
| Id  | Operation        | Name                       | Rows  | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                            |     1 |     2   (0)| 00:00:01 |
|   1 |  RESULT CACHE    | dxzj3fks1sqfy35shbbst4332h |       |            |          |
|   2 |   FAST DUAL      |                            |     1 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Result Cache Information (identified by operation id):
------------------------------------------------------

   1 - column-count=1; attributes=(single-row); parameters=(sys_context); 
       name="select/*+ result_cache */ userenv('instance')  from dual"

[collapse]

Categories: Development

A function gets called twice if the result_cache is used

XTended Oracle SQL - Sun, 2015-07-05 08:37

Recently I showed simple example how result_cache works with non-deterministic functions and observed strange behaviour: a function gets fired once in the normal query, but twice with the result_cache hint.
Moreover, only third and subsequent query executions return same cached results as second query execution.
I didn’t want to investigate such behavior, just because 1) we should not cache the results of non-deterministic functions and 2) it doesn’t matter if we use deterministic functions.
But later I was asked to explain this, so this post is just a short description with test case.

Look at the simple function that just returns random values:

create or replace function f_nondeterministic(p int:=100) return int
as
  res number;
begin
   res:=round(dbms_random.value(0,p));
   return res;
end;
SQL> exec dbms_result_cache.flush;
PL/SQL procedure successfully completed.

SQL> select/*+ result_cache */ f_nondeterministic(1000) nondeter from dual;

  NONDETER
----------
       481

SQL> select/*+ result_cache */ f_nondeterministic(1000) nondeter from dual;

  NONDETER
----------
       689

SQL> select/*+ result_cache */ f_nondeterministic(1000) nondeter from dual;

  NONDETER
----------
       689

with result_cache_statistics
SQL> exec dbms_result_cache.flush;

PL/SQL procedure successfully completed.

SQL> select/*+ result_cache */ f_nondeterministic(1000) nondeter from dual;

  NONDETER
----------
       481

SQL> select name,value from v$result_cache_statistics where name in ( 'Create Count Success', 'Find Count');

NAME                                               VALUE
-------------------------------------------------- ----------
Create Count Success                               1
Find Count                                         0

SQL> select/*+ result_cache */ f_nondeterministic(1000) nondeter from dual;

  NONDETER
----------
       689

SQL> select name,value from v$result_cache_statistics where name in ( 'Create Count Success', 'Find Count');

NAME                                               VALUE
-------------------------------------------------- ----------
Create Count Success                               1
Find Count                                         1

SQL> select/*+ result_cache */ f_nondeterministic(1000) nondeter from dual;

  NONDETER
----------
       689

SQL> select name,value from v$result_cache_statistics where name in ( 'Create Count Success', 'Find Count');

NAME                                               VALUE
-------------------------------------------------- ----------
Create Count Success                               1
Find Count                                         2

SQL> select name,bucket_no, id,type,status,pin_count,scan_count,invalidations from v$result_cache_objects o;

NAME                                                BUCKET_NO         ID TYPE       STATUS     PIN_COUNT SCAN_COUNT INVALIDATIONS
-------------------------------------------------- ---------- ---------- ---------- --------- ---------- ---------- -------------
XTENDER.F_NONDETERMINISTIC                                552          0 Dependency Published          0          0             0
select/*+ result_cache */ f_nondeterministic(1000)       2102          1 Result     Published          0          2             0
 nondeter from dual

[collapse]

As you can see, second execution returns different result than first one.
If we change this function:

create or replace function f_nondeterministic(p int:=100) return int
as
  res number;
begin
   res:=round(dbms_random.value(0,p));
   dbms_output.put_line('fired! ('||res||')');
   return res;
end;

and repeat this test-case:

SQL> select/*+ result_cache */ f_nondeterministic(1000) nondeter from dual;

  NONDETER
----------
       943    -- << (2)

1 row selected.

fired! (607)    -- << (1)
fired! (943)    -- << (2)
SQL> /

  NONDETER
----------
       607    -- << (1)

1 row selected.

SQL> /

  NONDETER
----------
       607    -- << (1)

1 row selected.

SQL> /

  NONDETER
----------
       607    -- << (1)

1 row selected.

we will see that there were 2 function executions: first result was cached, and the second was fetched!

Categories: Development

Make even more of UKOUG Tech15: APEX 5.0 UI Training - Dec 10th in Birmingham

Dimitri Gielis - Tue, 2015-06-30 01:48

APEX 5.0 has been released this spring. People who have already spent some time on this new version know this version is packed with new features aimed to make APEX developers even more productive, like the Page Designer.
Another striking new subset of features is aimed at creating better looking user interfaces for your APEX applications in an easy and maintainable way. 
The definition of user interface components in APEX 5.0 is very different to what we're used to. For example there is a new Universal Theme with Template Options and a Theme Roller. To get you up and running with this new toolset as quickly as possible, Dimitri Gielis of APEX R&D and Roel Hartman of APEX Consulting have joined forces and set up a one day course fully aimed at APEX 5.0 UI. So if you want to know not only how to use the new Theme, but also how to modify it to fit your needs, this is the event you should attend!

The training will be at the Jury’s Inn in Birmingham (UK) on Thursday Dec 10 - so conveniently immediately after the UKOUG Tech15 conference.
More information and registration see www.apextraining.eu

If you are from another country and think this training should be available in your country as well, please contact us - then we'll see what we can do!
Categories: Development

SQL*Plus tips #7: How to find the current script directory

XTended Oracle SQL - Fri, 2015-06-26 14:06

You know that if we want to execute another script from the current script directory, we can call it through @@, but sometimes we want to know the current path exactly, for example if we want to spool something into the file in the same directory.
Unfortunately we cannot use “spool @spoolfile”, but it is easy to find this path, because we know that SQL*Plus shows this path in the error when it can’t to find @@filename.

So we can simply get this path from the error text:

rem Simple example how to get path (@@) of the current script.
rem This script will set "cur_path" variable, so we can use &cur_path later.
 
set termout off
spool _cur_path.remove
@@notfound
spool off;
 
var cur_path varchar2(100);
declare 
  v varchar2(100);
  m varchar2(100):='SP2-0310: unable to open file "';
begin v :=rtrim(ltrim( 
                        q'[
                            @_cur_path.remove
                        ]',' '||chr(10)),' '||chr(10));
  v:=substr(v,instr(v,m)+length(m));
  v:=substr(v,1,instr(v,'notfound.')-1);
  :cur_path:=v;
end;
/
set scan off;
ho (rm _cur_path.remove 2>&1  | echo .)
ho (del _cur_path.remove 2>&1 | echo .)
col cur_path new_val cur_path noprint;
select :cur_path cur_path from dual;
set scan on;
set termout on;
 
prompt Current path: &cur_path

I used here the reading file content into variable, that I already showed in the “SQL*Plus tips. #1″.
UPDATE: I’ve replaced this script with a cross platform version.

Also I did it with SED and rtrim+ltrim, because 1) I have sed even on windows; and 2) I’m too lazy to write big PL/SQL script that will support 9i-12c, i.e. without regexp_substr/regexp_replace, etc.
But of course you can rewrite it without depending on sed, if you use windows without cygwin.

PS. Note that “host pwd” returns only directory where SQL*Plus was started, but not executed script directory.

Download latest version

Categories: Development

Pages

Subscribe to Oracle FAQ aggregator - Development