view counter

PL/SQL Feed

Oracle PL/SQL (Procedural Language/Structured Query Language) resources, news, and support articles.

Winner Selected for Hierarchical Queries Challenge (8471)

In October 2011, we posted a competition regarding hierarchical queries, with this introduction:

Some words on result cache dependencies tracking in 11.2.

First, every result of a function or query for which result cache is enabled has its own dependencies.
Second, all dependencies are tracked while the function/query is being executed.
Third,  the PL/SQL function result cache and the SQL result cache internals are the same.
Fourth, if you have a function within a query, for which result cache hint is used, the dependencies for the query result will include the dependencies on the objects accessed by the function.
This has several consequences.

APEX: Show Actions menu with Authorization

With an Interactive Report you can choose whether you want the Actions Menu to be shown or not. This has to be done by the developer. Of course the customer wanted to have this based upon the existing authorization scheme. How can you solve this?

Discovering the Author within me: My upcoming book

Happily I would announce the listing of my upcoming book at PacktPub publishers website. The book is expected to be released by May, 2012. The book is for the Oracle Associate professionals who are aspiring for Professional level exam (1Z0-146 certification). “The Oracle Advanced PL/SQL Developer Professional Guide helps you master advanced PL/SQL concepts.

Function or Procedure?

Somebody asked for a simple comparison between a PL/SQL pass-by-value function and pass-by-reference procedure, where the procedure uses only an OUT mode parameter to return the result. This provides examples of both, but please note that a pass-by-value function can be used in SQL or PL/SQL context while a pass-by-reference procedure can only be used in another anonymous of named block PL/SQL program.

Comments in hint

 
It seems that CBO can work with the line comment inside sql hints.
Let’s see. I create a table with 3 indexes:
--drop table ttt purge;
create table ttt
(
a number not null,
b number not null,
c number not null
);

insert into ttt
select 1,1,1
from dual connect by level <= 10000;
commit;

create index iiia on ttt(a);
create index iiib on ttt(b);
create index iiic on ttt(c);
And then I’m able to switch access paths by commenting:

Different error handling behavior between EXECUTE IMMEDIATE and DBMS_SQL (11296)

One of the Q4 2011 playoff quizzes examined the way that user-defined exceptions are handled. If you didn't participate in the playoff, you may want to view this quiz - even try to answer it for yourself - before reading this post.

Q4 2011 Championship Playoff Results

You will find below the rankings for the Q4 2011 playoff; the number next to the player's name is the number of times that player has participated in a playoff. Congratulations first and foremost to our top-ranked players:

1st Place: Frank Schrader, Germany, wins an Amazon.com US$250 Gift Card.
2nd Place: Janis Baiza, Latvia, wins an Amazon.com US$175 Gift Card.
3rd Place: Valentin Nikotin, Russia, wins an Amazon.com US$100 Gift Card.

A Conversation with Hans Forbrich

Hans Forbrich, an Oracle ACE Director, talks about his path to becoming a prized speaker at Oracle User Group meetings around the world. http://medianetwork.oracle.com/video/player/961894745001 Enjoy all Saurabh Filed under: Oracle Tagged: Oracle, oracle user group, Saurabh, video player

Compiling Incomplete Program Units (9633)

The 25 January quiz tested your knowledge of the minimal amount of code needed to "create" a PL/SQL procedure in the data dictionary, even if the resulting program unit is invalid.

The choices offered were:

CREATE PROCEDURE plch_test;
CREATE PROCEDURE plch_test AS;
CREATE PROCEDURE plch_test NULL;
CREATE PROCEDURE plch_test IS BEGIN NULL; END;

The first was scored as incorrect and the last three as correct.

view counter