Steven Feuerstein published a new presentation titled: Golden Rules for Developers [PDF].
Here is a summary of his seven “golden rules” that will help you write better code:
Agreed with all. I would also add an eighth one: Understand your data.
What would you add?
Filed in Oracle, Tips with 11 Comments | Tags: pl/sqlHere is something you need to be aware of when you use the EXECUTE IMMEDIATE … USING and OPEN FOR … USING statements in 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:
I have been playing the PL/SQL Challenge quiz every day. It’s a great way to brush up on my PL/SQL skills. After many years of using PL/SQL, I am still learning about features that I have never known before or totally forgotten about. Take for example one of last week’s quiz questions:
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?
Filed in Oracle, Tips with 3 Comments | Tags: pl/sqlOn the weekend of May 22, 2010, the Portland Development community is coming together in a way never experienced before.
Using the University of Portland campus, SQLSaturday, Portland Code Camp, and Portland Bar Camp are combining and coordinating efforts to bring 800-1000 regional technology professionals together for the opportunity to immerse themselves in seminars, presentations, group exploration, and networking. Participants will be able to engage in their preferred technology, as well as to 'sample' other options.
Admittance to this event is free, all costs are covered by donations and sponsorships. Please register soon as seating is limited, and let friends and colleagues know about the event.
>> Click here to register.
Most sessions will be one hour and fifteen minutes (1:15), and a few 'deep dive' sessions will be longer (2.5 hours). The 'deep dive' sessions will be scheduled for 7:00 PM (after the snacks and refreshments.) Also, throughout the day, there may be opportunities for 30 minute 'SQL snack' sessions.
>> Click here to submit your presentation abstract.
I encourage all Oracle SQL and PL/SQL enthusiasts living in the Pacific Northwest to present in and/or attend this not-to-be-missed event.
The following is a list of database limits which are divided into four categories in addition to the PL/SQL compiler limits. It is an aggregation of 5 separate web pages from the Oracle Database 11g Release 2 documentation library. I put them all here on one page for convenience.
| Datatypes | Limit | Comments |
|---|---|---|
BFILE |
Maximum size: 4 GB Maximum size of a file name: 255 characters Maximum size of a directory name: 30 characters Maximum number of open BFILEs: see Comments | The maximum number of BFILEs is limited by the value of the SESSION_MAX_OPEN_FILES initialization parameter, which is itself limited by the maximum number of open files the operating system will allow. |
BLOB |
Maximum size: (4 GB – 1) * DB_BLOCK_SIZE initialization parameter (8 TB to 128 TB) |
The number of LOB columns per table is limited only by the maximum number of columns per table (that is, 1000). |
CHAR |
Maximum size: 2000 bytes | None |
CHAR VARYING |
Maximum size: 4000 bytes | None |
CLOB |
Maximum size: (4 GB – 1) * DB_BLOCK_SIZE initialization parameter (8 TB to 128 TB) |
The number of LOB columns per table is limited only by the maximum number of columns per table (that is, 1000). |
| Literals (characters or numbers in SQL or PL/SQL – more) | Maximum size: 4000 characters | None |
LONG |
Maximum size: 2 GB – 1 | Only one LONG column is allowed per table. |
NCHAR |
Maximum size: 2000 bytes | None |
NCHAR VARYING |
Maximum size: 4000 bytes | None |
NCLOB |
Maximum size: (4 GB – 1) * DB_BLOCK_SIZE initialization parameter (8 TB to 128 TB) |
The number of LOB columns per table is limited only by the maximum number of columns per table (that is, 1000). |
NUMBER |
999…(38 9′s) x10125 maximum value -999…(38 9′s) x10125 minimum value | Can be represented to full 38-digit precision (the mantissa) Can be represented to full 38-digit precision (the mantissa) |
| Precision | 38 significant digits | None |
RAW |
Maximum size: 2000 bytes | None |
VARCHAR |
Maximum size: 4000 bytes | None |
VARCHAR2 |
Maximum size: 4000 bytes | None |
| Item | Type of Limit | Limit Value |
|---|---|---|
GROUP BY clause |
Maximum length | The GROUP BY expression and all of the nondistinct aggregate functions (for example, SUM, AVG) must fit within a single database block. |
| Indexes | Maximum per table | Unlimited |
| Indexes | Total size of indexed column | 75% of the database block size minus some overhead |
| Columns | Per table | 1000 columns maximum |
| Columns | Per index (or clustered index) | 32 columns maximum |
| Columns | Per bitmapped index | 30 columns maximum |
| Constraints | Maximum per column | Unlimited |
| Subqueries | Maximum levels of subqueries in a SQL statement | Unlimited in the FROM clause of the top-level query
255 subqueries in the WHERE clause |
| Partitions | Maximum length of linear partitioning key | 4 KB – overhead |
| Partitions | Maximum number of columns in partition key | 16 columns |
| Partitions | Maximum number of partitions allowed per table or index | 1024K – 1 |
| Rows | Maximum number per table | Unlimited |
| Stored Packages | Maximum size | PL/SQL and Developer/2000 may have limits on the size of stored procedures they can call. The limits typically range from 2000 to 3000 lines of code. |
| Trigger Cascade Limit | Maximum value | Operating system-dependent, typically 32 |
| Users and Roles | Maximum | 2,147,483,638 |
| Tables | Maximum per clustered table | 32 tables |
| Tables | Maximum per database | Unlimited |
| Item | Type of Limit | Limit Value |
|---|---|---|
| Database Block Size | Minimum | 2048 bytes; must be a multiple of operating system physical block size |
| Database Block Size | Maximum | Operating system dependent; never more than 32 KB |
| Database Blocks | Minimum in initial extent of a segment | 2 blocks |
| Database Blocks | Maximum per datafile | Platform dependent; typically 222 – 1 blocks |
| Controlfiles | Number of control files | 1 minimum; 2 or more (on separate devices) strongly recommended |
| Controlfiles | Size of a control file | Dependent on operating system and database creation options; maximum of 20,000 x (database block size) |
| Database files | Maximum per tablespace | Operating system dependent; usually 1022 |
| Database files | Maximum per database | 65533
May be less on some operating systems
Limited also by size of database blocks and by the DB_FILES initialization parameter for a particular instance |
| Database extents (more) | Maximum per dictionary managed tablespace | 4 GB * physical block size (with K/M modifier); 4 GB (without K/M modifier) |
| Database extents | Maximum per locally managed (uniform) tablespace | 2 GB * physical block size (with K/M modifier); 2 GB (without K/M modifier) |
| Database file size | Maximum | Operating system dependent. Limited by maximum operating system file size; typically 222 or 4 MB blocks |
MAXEXTENTS |
Default value | Derived from tablespace default storage or DB_BLOCK_SIZE initialization parameter |
MAXEXTENTS |
Maximum | Unlimited |
| Redo Log Files | Maximum number of logfiles | Limited by value of MAXLOGFILES parameter in the CREATE DATABASE statement
Control file can be resized to allow more entries; ultimately an operating system limit |
| Redo Log Files | Maximum number of logfiles per group | Unlimited |
| Redo Log File Size | Minimum size | 4 MB |
| Redo Log File Size | Maximum Size | Operating system limit; typically 2 GB |
| Tablespaces | Maximum number per database | 64 K
Number of tablespaces cannot exceed the number of database files because each tablespace must include at least one file |
| Bigfile Tablespaces | Number of blocks | A bigfile tablespace contains only one datafile or tempfile, which can contain up to approximately 4 billion ( 232 ) blocks. The maximum size of the single datafile or tempfile is 128 terabytes (TB) for a tablespace with 32 K blocks and 32 TB for a tablespace with 8 K blocks. |
| Smallfile (traditional) Tablespaces | Number of blocks | A smallfile tablespace is a traditional Oracle tablespace, which can contain 1022 datafiles or tempfiles, each of which can contain up to approximately 4 million (222) blocks. |
| External Tables file | Maximum size | Dependent on the operating system.An external table can be composed of multiple files. |
| Item | Type of Limit | Limit Value |
|---|---|---|
| Instances per database | Maximum number of cluster database instances per database | Operating system-dependent |
| Locks | Row-level | Unlimited |
| Locks | Distributed Lock Manager | Operating system dependent |
| SGA size | Maximum value | Operating system-dependent; typically 2 to 4 GB for 32-bit operating systems, and > 4 GB for 64-bit operating systems |
| Advanced Queuing Processes | Maximum per instance | 10 |
| Job Queue Processes | Maximum per instance | 1000 |
| I/O Slave Processes | Maximum per background process (DBWR, LGWR, etc.) | 15 |
| I/O Slave Processes | Maximum per Backup session | 15 |
| Sessions | Maximum per instance | 32 KB; limited by the PROCESSES and SESSIONS initialization parameters |
| Global Cache Service Processes | Maximum per instance | 10 |
| Shared Servers | Maximum per instance | Unlimited within constraints set by the PROCESSES and SESSIONS initialization parameters, for instance |
| Dispatchers | Maximum per instance | Unlimited within constraints set by PROCESSES and SESSIONS initialization parameters, for instance |
| Parallel Execution Slaves | Maximum per instance | Unlimited within constraints set by PROCESSES and SESSIONS initialization parameters, for instance |
| Backup Sessions | Maximum per instance | Unlimited within constraints set by PROCESSES and SESSIONS initialization parameters, for instance |
| Item | Limit |
|---|---|
| bind variables passed to a program unit | 32768 |
| exception handlers in a program unit | 65536 |
| fields in a record | 65536 |
| levels of block nesting | 255 |
| levels of record nesting | 32 |
| levels of subquery nesting | 254 |
| levels of label nesting | 98 |
| levels of nested collections | no predefined limit |
magnitude of a PLS_INTEGER or BINARY_INTEGERvalue |
-2147483648..2147483647 |
| number of formal parameters in an explicit cursor, function, or procedure | 65536 |
| objects referenced by a program unit | 65536 |
precision of a FLOAT value (binary digits) |
126 |
precision of a NUMBER value (decimal digits) |
38 |
precision of a REAL value (binary digits) |
63 |
| size of an identifier (characters) | 30 |
| size of a string literal (bytes) | 32767 |
size of a CHAR value (bytes) |
32767 |
size of a LONG value (bytes) |
32760 |
size of a LONG RAW value (bytes) |
32760 |
size of a RAW value (bytes) |
32767 |
size of a VARCHAR2 value (bytes) |
32767 |
size of an NCHAR value (bytes) |
32767 |
size of an NVARCHAR2 value (bytes) |
32767 |
size of a BFILE value (bytes) |
4G * value of DB_BLOCK_SIZE parameter |
size of a BLOB value (bytes) |
4G * value of DB_BLOCK_SIZE parameter |
size of a CLOB value (bytes) |
4G * value of DB_BLOCK_SIZE parameter |
size of an NCLOB value (bytes) |
4G * value of DB_BLOCK_SIZE parameter |
In an email he sent to me last week, Jason William wanted to get my thoughts on the future of Oracle PL/SQL. He wrote:
Will PL/SQL still be a big skillset in demand? Or will it convert to Java (Oracle seems to pushing a lot of middleware with Java?) I was just curious because talking with some friends, alot seem to think Java is being pushed pretty heavily by Oracle… I am always curious as to what the future holds and what [Oracle] skillsets I should learn.
Jason also forwarded this LinkedIn thread in the Oracle SQL and PL/SQL group, in which Bob writes:
What keeps PL/SQL developers up at night? What technologies are you most worried about? Excited about? Is enhancing and extending the value of your PL/SQL skills a top priority for you?
Scott chimes in:
I know that my problem is simply trying to get a handle on all the new technologies and guess which one(s) I need to invest my time and money in. Is it even possible to be “just” a PL/SQL developer anymore?
Scott has a point. Nowadays, being an Oracle developer means more than just knowing SQL and PL/SQL (which is always expected from an Oracle database developer). If you really want to enhance your “Oracle developer” career you have to learn and have experience with other languages and technologies. For example, it’s important to learn XML and related technologies, Java and Web technologies (HTML, CSS, JavaScript…), and others.
What do you think? is knowing just PL/SQL enough to have a successful career? What does being an “Oracle developer” mean to you? What advice do you give to Jason?
Filed in Oracle with 10 Comments | Tags: developer, pl/sqlOracle Database 11g introduced a new feature called PL/Scope. A while back, I wrote about Dan Morgan’s experience when he compiled the package STANDARD for PL/Scope. I also wrote about Oracle’s answer, in which they said “…the reason to compile STANDARD would be to make its identifiers available in the new DBA_Identifiers view family (see PL/Scope). Our script to do this sadly missed the release. We’re about to post it on OTN.”
In fact, the PL/Scope documentation mentions a utlirplscope.sql script:
A database that has been upgraded from a release of Oracle Database that did not yet support PL/Scope will have no PL/Scope metadata—nor will a new Oracle Database 11g environment that has been ordinarily created. The DBA can rectify this by running the utlirplscope.sql script.
In an email, Bryn Llewellyn, Oracle’s PL/SQL Product Manager, was kind enough to point me to this page on OTN:
On the Subject of the utlirplscope.sql script
It turns out that a script is not needed after all:
Instead of simply providing a script to recompile STANDARD and DBMS_STANDARD for PL/Scope, we would first like to clear up some misconceptions about the usefulness of such a script and make sure you really do need to perform such an operation.
I suggest you read this OTN piece if you want to know the answers to the following questions:
Important notes to take from this:
In conclusion, I agree with Bryn that PL/Scope is a fine feature. Just get your DBA to read the OTN reference above if your use case would benefit from having STANDARD and DBMS_STANDARD identifier data.
Filed in Oracle with Comments Off | Tags: 11g, pl/sql, plscopeThe document below contains patterns of bad SQL and PL/SQL code that Gojko Adzic has repeatedly found in various applications and databases. Some of the bad practices include:
Use of magic numbers and strings instead of NULL.
When invoking a PL/SQL procedure or function, you can specify the value of its parameters using either positional, named, or mixed notation. Let’s review what each notation means and then demonstrate the best way to add new parameters to an existing subprogram without breaking existing code.
Consider this simple package as an example:
CREATE OR REPLACE PACKAGE ed_test_pkg
AS
PROCEDURE proc1 (
param1_in IN NUMBER,
param2_in IN NUMBER,
param1_out OUT NUMBER
);
END ed_test_pkg;
/
CREATE OR REPLACE PACKAGE BODY ed_test_pkg
AS
PROCEDURE proc1 (
param1_in IN NUMBER,
param2_in IN NUMBER,
param1_out OUT NUMBER
)
AS
BEGIN
param1_out := param1_in + param2_in;
END proc1;
END ed_test_pkg;
/
Positional Notation:
When you invoke a procedure using positional notation, you specify the parameters in the same order as the procedure declares them. For example:
DECLARE
l_param1_out NUMBER;
BEGIN
ed_test_pkg.proc1 (1, 2, l_param1_out);
DBMS_OUTPUT.put_line ('l_param1_out = ' || l_param1_out);
END;
Named Notation:
When you invoke a procedure using named notation, you specify the name and value of each parameter, using the association operator =>. The order of parameters is irrelevant. For example:
DECLARE
l_param1_out NUMBER;
BEGIN
ed_test_pkg.proc1 (param2_in => 2,
param1_out => l_param1_out,
param1_in => 1
);
DBMS_OUTPUT.put_line ('l_param1_out = ' || l_param1_out);
END;
Mixed Notation:
When you invoke a procedure using mixed notation, you start with positional notation, then use named notation for the remaining parameters. For example:
DECLARE
l_param1_out NUMBER;
BEGIN
ed_test_pkg.proc1 (1, param2_in => 2, param1_out => l_param1_out);
DBMS_OUTPUT.put_line ('param1_out = ' || l_param1_out);
END;
Which one to use?
After this quick notations refresher, the question is which one to choose as your preferred style when invoking PL/SQL programs? For me, I prefer, and almost always use, the named notation, and you should too. OK, I admit there is more typing involved in named notation, but it is easier to read and maintain than the other two. Moreover, if the program’s parameter list changes, you avoid changing the code that invokes the program using named notation.
Adding a new parameter
Speaking of changing the parameter list, let’s say you have to add a new param3_in IN parameter to the procedure ed_test_pkg.proc1. Which one of the following two approaches do you use?
Approach 1:
PROCEDURE proc1 (
param1_in IN NUMBER,
param2_in IN NUMBER,
param3_in IN NUMBER DEFAULT 1,
param1_out OUT NUMBER
);
Approach 2:
PROCEDURE proc1 (
param1_in IN NUMBER,
param2_in IN NUMBER,
param1_out OUT NUMBER,
param3_in IN NUMBER DEFAULT 1
);
As you know, adding DEFAULT to the IN parameter means that you do not have to pass in a value to this parameter, and if you don’t provide a value, the default value is used.
Now, you may be tempted to keep all the IN parameters together, however, the second approach is better. Adding the parameter to the end of the parameter list is safer. Let me show you why.
Let’s say you have a program that calls ed_test_pkg.proc1 using the positional notation. When you add a new IN parameter using the first approach, and if you do not make any further modifications to the code, you’ll get an error:
SQL> DECLARE
2 l_param1_out NUMBER;
3 BEGIN
4 ed_test_pkg.proc1 (1, 2, l_param1_out);
5 DBMS_OUTPUT.put_line ('l_param1_out = ' || l_param1_out);
6 END;
7 /
ed_test_pkg.proc1 (1, 2, l_param1_out);
*
ERROR at line 4:
ORA-06550: line 4, column 4:
PLS-00306: wrong number or types of arguments in call to 'PROC1'
ORA-06550: line 4, column 4:
PL/SQL: Statement ignored
But, you will not get this error if you use approach 2 or if you use the named notation when calling ed_test_pkg.proc1.
Conclusion
So, in general, as a best practice, always use named notation when invoking PL/SQL procedures and functions. To avoid breaking existing code, add new parameters with DEFAULT values to the end of the parameter list of existing procedures or functions.
Source and Resources:
As mentioned on OracleCommunity.net, Oracle SQL Developer 1.5 Early Adopter 1 (EA1) is now available for download. As with any new release, there are new features and bug fixes. But, they are not yet detailed in the release notes.
However, I know that the one feature that I have been eagerly waiting for is finally here, it is the brand new and enhanced SQL Formatter (screenshots below). Not bad at all!
I’ll be using SQL Developer 1.5 as my main SQL and PL/SQL development tool for the next few days and weeks. Let’s see if this release will convince me to ditch Quest’s SQL Navigator.




