Related articles:

• 5 Useful and Interesting Links for 2009-10-20
• Top 10 Ways to Know Your Software Project is Doomed
• SQL Server 2005 more complex

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

The Hello World collection includes 428 Hello World programs in many more-or-less well known programming languages, plus 63 human languages.

Can you name the programming language based on its Hello World syntax? Take this quiz and let us know. (via)

Related articles:

• What’s so special about the world cup?
• Do You Have These Symptoms?
• 10 Things About Computer Programming You May Not Agree With

Related articles:

• Oracle Database Architecture in Less than 10 Minutes (Video)
• Rewriting SQL Queries for Performance in 9 Minutes (Video)
• Oracle Performance Monitoring in Less than 12 Minutes (Video)

Posted via email from EddieAwad.com

Related articles:

• Oracle Opens Registration for OpenWorld San Francisco 2006
• Oracle Open World 2006 – Download Presentations Now
• Oracle OpenWorld 2006 – Registration and Oramag Dinner (Video + Photos)