Here is an example:

TIMING SHOW lists the current timer’s name and timing data:

SQL> timing show
SP2-0325: no timing elements to show

TIMING START sets up a timer and makes t1 (or whatever timer name you like) the name of the timer:

SQL> timing start t1
SQL> timing show
timing for: t1
Elapsed: 00:00:06.95

Another call to TIMING SHOW displays the elapsed time since the start of the timer:

SQL> timing show
timing for: t1
Elapsed: 00:00:16.01

You can have more than one active timer by STARTing additional timers before STOPping the first; SQL*Plus nests each new timer within the preceding one. The timer most recently STARTed becomes the current timer:

SQL> timing start t2

t2 is the current timer now:

SQL> timing show
timing for: t2
Elapsed: 00:00:02.25

TIMING STOP lists the current timer’s name and timing data, then deletes the timer. If any other timers are active, the next most recently STARTed timer becomes the current timer, in this example it’s t1:

SQL> timing stop
timing for: t2
Elapsed: 00:00:18.81
SQL> timing show
timing for: t1
Elapsed: 00:01:14.18
SQL> timing stop
timing for: t1
Elapsed: 00:01:22.09
SQL> timing show
SP2-0325: no timing elements to show

TIMING with no clauses lists the number of active timers:

SQL> timing
no timing elements in use    
SQL> timing start t1
SQL> timing
1 timing element in use
SQL> timing start t2
SQL> timing
2 timing elements in use

CLEAR TIMING deletes all timers:

SQL> clear timing
timing for: t2
Elapsed: 00:22:38.36
timing for: t1
Elapsed: 00:22:53.96

Sources and Resources:

• SQL*Plus Timing by David Clement
• SQL Timing utility by Dominic Giles

Related articles:

• How to Give Your System a Break from Oracle XE
• Underscores make a difference
• This is How You Should Ask for Help

Oracle Press is a marketing label used by McGraw Hill to market its line of Oracle books – the books are not formally written by Oracle Corporation nor are they formally tech edited by Oracle Corporation, although some employees of Oracle Corporation have used McGraw Hill as a publishing company. At least for me, for several years the “Officially Authorized Oracle Press” logo on books published by McGraw Hill seemed to be endorsement of the books by Oracle Corporation, but that is not the case.

via Charles Hooper’s Oracle Notes.

Related articles:

• Do You Understand This Oracle Press Release? WSJ Does Not
• New Oracle 10g Book Center
• Oracle Books on Ning.com

Related articles:

• 6 Interesting Things You May Have Missed on 2010-07-28
• links for 2006-07-28
• Boredom kills

Related articles:

• 5 Interesting Things You May Have Missed On This Aug 16, 2010
• Top 10 Ways to Know Your Software Project is Doomed
• 5 Useful and Interesting Links for 2009-10-20

Here is a summary of his seven “golden rules” that will help you write better code:

1. Don’t repeat anything.
2. Hide everything.
3. Don’t take shortcuts.
4. Embrace standards.
5. Build on a foundation.
6. Never lose information.
7. Don’t write code alone.

Agreed with all. I would also add an eighth one: Understand your data.

What would you add?

Related articles:

• 10 Programming Quotes and Lessons Learned
• Rules of Thumb Wiki
• PRAGMAtism in Oracle PL/SQL

Let’s execute this simple anonymous PL/SQL block:

eddie@db11gr2> DECLARE
  2    l_string VARCHAR2 (4000);
  3    l_rc sys_refcursor;
  4  BEGIN
  5    l_string := RPAD (' ', 4000);
  6    OPEN l_rc FOR 'select :1 from dual' USING l_string;
  7    EXECUTE immediate 'select :1 from user_objects where rownum = 1'
  8      INTO l_string USING l_string;
  9  END;
 10  /

PL/SQL procedure successfully completed.

The EXECUTE IMMEDIATE and OPEN FOR statements are used with the USING clause. USING supplies a bind argument for the SQL string. In this example the value of the bind argument is a 4,000 character string.

Now, let’s pass a string greater than 4,000 characters to the OPEN FOR statement:

eddie@db11gr2> DECLARE
  2    l_string VARCHAR2 (4001);
  3    l_rc sys_refcursor;
  4  BEGIN
  5    l_string := RPAD (' ', 4001);
  6    OPEN l_rc FOR 'select :1 from dual' USING l_string;
  7  END;
  8  /
DECLARE
*
ERROR at line 1:
ORA-01460: unimplemented or unreasonable conversion requested
ORA-06512: at line 6

Oops, got an error. Let’s also try the EXECUTE IMMEDIATE statement with a string greater than 4,000 characters:

eddie@db11gr2> DECLARE
  2    l_string VARCHAR2 (4001);
  3  BEGIN
  4    l_string := RPAD (' ', 4001);
  5    EXECUTE immediate 'select :1 from user_objects where rownum = 1'
  6      INTO l_string USING l_string;
  7  END;
  8  /
DECLARE
*
ERROR at line 1:
ORA-01460: unimplemented or unreasonable conversion requested
ORA-06512: at line 5

Got the same error: “ORA-01460: unimplemented or unreasonable conversion requested”.

How about if we pass a NULL:

eddie@db11gr2> DECLARE
  2    l_rc sys_refcursor;
  3  BEGIN
  4    OPEN l_rc FOR 'select :1 from dual' USING NULL;
  5  END;
  6  /
  OPEN l_rc FOR 'select :1 from dual' USING NULL;
                                            *
ERROR at line 4:
ORA-06550: line 4, column 45:
PLS-00457: expressions have to be of SQL types
ORA-06550: line 4, column 3:

In this case we get “PLS-00457: expressions have to be of SQL types”.

Well, there is a restriction on what values you can bind: When binding values to dynamic SQL, only SQL datatypes are supported. You can bind strings, numbers, dates, collections, LOBs, XML documents… However, you cannot bind values having a PL/SQL specific datatype such as Booleans, associative arrays and varchar2 values greater than 4000.

Make sure you keep the above restriction in mind when you use the EXECUTE IMMEDIATE … USING and OPEN FOR … USING statements.

Source and resources:

• EXECUTE IMMEDIATE Statement
• Unsupported Data Type in Native Dynamic SQL
• OPEN FOR Statement
• Discussions about the ORA-01460 error

Related articles:

• More on Generating Strings
• CASE gotcha in Oracle 8i
• Here’s a Quick Way to Get the Line Number in PL/SQL

Try the following in your SQL*Plus. It will ask you for your Twitter username and password and the status message that you want to post:

eddie@db11gr2> set define !
eddie@db11gr2> set verify off
eddie@db11gr2> set sqlterminator off
eddie@db11gr2> SELECT HTTPURITYPE(
  2    UriFactory.escapeUri(
  3      'http://query.yahooapis.com/v1/public/yql?q=
  4        USE "http://awads.net/yql/twitter.xml" as tw_t;
  5          INSERT INTO tw_t (status, username,password)
  6            VALUES("!status","!!username","!!password")
  7              &format=json
  8              &env=store://datatables.org/alltableswithkeys&format=xml&callback=cbfunc')).getclob()
  9    json_response
 10  FROM dual
 11  /
Enter value for status: Tweeting from SQL*Plus.
Enter value for username: sqlyql
Enter value for password: sqlyqlpw

JSON_RESPONSE
--------------------------------------------------------------------------------
cbfunc({"query":{"count":"1","created":"2010-05-02T02:02:14Z","lang":"en-US","re
sults":{"status":{"created_at":"Sun May 02 02:02:14 +0000 2010","id":"1322057475
5","text":"Tweeting from SQL*Plus.","source":"<a href=\"http://apiwiki.twitter.c
om/\" rel=\"nofollow\">API</a>","truncated":"false","in_reply_to_status_id":null
,"in_reply_to_user_id":null,"favorited":"false","in_reply_to_screen_name":null,"
user":{"id":"139255376","name":"sqlyql","screen_name":"sqlyql","location":null,"
description":null,"profile_image_url":"http://s.twimg.com/a/1272578449/images/de
fault_profile_1_normal.png","url":null,"protected":"false","followers_count":"1"
,"profile_background_color":"9ae4e8","profile_text_color":"000000","profile_link
_color":"0000ff","profile_sidebar_fill_color":"e0ff92","profile_sidebar_border_c
olor":"87bc44","friends_count":"0","created_at":"Sun May 02 01:59:28 +0000 2010"
,"favourites_count":"0","utc_offset":null,"time_zone":null,"profile_background_i
mage_url":"http://s.twimg.com/a/1272578449/images/themes/theme1/bg.png","profile
_background_tile":"false","notifications":"false","geo_enabled":"false","verifie
d":"false","following":"false","statuses_count":"1","lang":"en","contributors_en
abled":"false"},"geo":null,"coordinates":null,"place":null,"contributors":null}}
}});


Elapsed: 00:00:01.92

The above is made possible using HTTPURITYPE and YQL, the Yahoo! Query Language. Awesome combination!

Note that on June 30, 2010 Twitter will be shutting off basic authentication on the Twitter API. After that date, the above will stop working unless the YQL query is modified to use OAuth.

Are you on Twitter? Follow me at eddieawad and the rest of the Oracle Tweeple at eddieawad/oracle.

Related articles:

• HTTP POST from inside Oracle
• The Oracle Community on Twitter
• Cool Undocumented SQL Function SYS_OP_MAP_NONNULL and Some Alternatives

What is the output of this PL/SQL block?

eddie@db11gr2> DECLARE
  2     aname varchar2 (5);
  3  BEGIN
  4     DECLARE
  5        aname varchar2 (5) := 'Big String';
  6     BEGIN
  7        DBMS_OUTPUT.put_line (aname);
  8     EXCEPTION
  9        WHEN VALUE_ERROR
 10        THEN
 11           DBMS_OUTPUT.put_line ('Inner block');
 12     END;
 13     DBMS_OUTPUT.put_line ('What error');
 14  EXCEPTION
 15     WHEN VALUE_ERROR
 16     THEN
 17        DBMS_OUTPUT.put_line ('Outer block');
 18  END;
 19  /

Instead of telling you what the answer is, I will point you to the documentation:

In declarations, faulty initialization expressions can raise exceptions, but exception handlers cannot handle them… Handlers in the current block cannot catch the raised exception because an exception raised in a declaration propagates immediately to the enclosing block.

Even though I selected the wrong choice when I took the quiz, adding the above fact to my PL/SQL knowledge and sharing it with you is a big win for me.

By the way, should we consider initializing variables in the declaration section a bad practice?

Related articles:

• How to find where an error was raised in PL/SQL
• Little known way to get the error message in PL/SQL
• Here’s a Quick Way to Get the Line Number in PL/SQL

via

Related articles:

• 15 Ways Oracle Can Make Java Better (and Improve Its Stance with Developers)
• Securing Java In Oracle [PDF]
• PLSQL Ranks 12th

In the past, I’ve added a deleted_at (datetime) column and appended “WHERE deleted_at IS NULL” to every query involving that table. But it’s a total pain in the ass, and it’s complicated as shit: there’s always a few queries that are missed, a few developers that forgot or didn’t know they had to check that the record wasn’t deleted, denormalized counts are hard to keep in sync, etc. etc. It always turns into an embarrassing fiasco rather than something you add once and then don’t have to worry about.

Good discussion followed which included a couple of interesting links: What is the best way to implement soft deletion? and The trouble with soft delete.

So, if there is a business need to retain deleted records, do you go soft or hard?

Related articles:

• Insert into DUAL
• Beware of Comments in SQL
• SQL PLUS Gotcha