Oracle 10g new features for developers

10g Limit less LOB
  Since first introduced the Oracle LOB type has been limited to 4GB (enough for most uses)
• Oracle 10g allows LOB data to be limited only by tablespace page size
• Current limit: – 8–128 terabytes
• Supported environments:
– PL/SQL using DBMS_LOB
– Java using JDBC
– C/C++ using OCI

10g R2 DML Error Logging


Insert, Update, Delete, and Merge add ERROR logging allowing you to capture DML errors and log
them
INSERT … /* or UPDATE, DELETE, MERGE */
LOG ERRORS
[ INTO [schema.] table ]
[ (simple_expression) ]
[ REJECT LIMIT { integer | UNLIMITED }
– Default error table defined by DBMS_ERRLOG package: ERR$_ followed by first 25 characters of DML target table
– Simple expression is value to be used as statement tag (may be result of SQL function call)
– Reject limit default is zero

Creating the Error Log Table


Oracle provides a PL/SQL packaged procedure to create the logging table (for each table to be logged)
execute DBMS_ERRLOG.CREATE_ERROR_LOG('myemp', 'myemplog');
– myemp Table DML is being applied to
– myemplog Logging table for rejected rows
• Creates a database table containing:
– ORA_ERR_NUMBER$ Error number
– ORA_ERR_MESG$ Error message
– ORA_ERR_ROWID$ Rowid of impacted rows
– ORA_ERR_OPTYP$ Operation type (I,U,D,M)
– ORA_ERR_TAG$ Text from LOG_ERRORS
– All column values (good & bad) as varchar2(4000)

Error Log Output


insert into emp select * from myempbig
log errors into myemplog ('Log test3')
reject limit unlimited;
0 rows created.
ORA_ERR_NUMBER$ 12899
ORA_ERR_MESG$ ORA-12899: value too large for column
"JOHN"."EMP"."JOB" (actual: 13, maximum: 9)
ORA_ERR_ROWID$
ORA_ERR_OPTYP$ I
ORA_ERR_TAG$ Log test3
EMPNO 6543
ENAME STEPHENSON
JOB WEB DEVELOPER
MGR 7369
HIREDATE 03-SEP-06
SAL 3000
COMM
DEPTNO 40

10g SQL*Plus Misc


SET SERVEROUTPUT ON now works immediately within PL/SQL block where executed
• DBMS_OUTPUT.PUT_LINE text line maximum increased from 255 to 32767 bytes
• Recycle Bin keeps deleted database objects until Purged
• DESCRIBE automatically attempts to validate invalid objects before display
• White space now allowed in file names
• Substitution variables allowed in SET PROMPT
• Three pre-defined SQL*Plus variables added:
– _DATE Current date or a user defined fixed string.
– _PRIVILEGE Privilege level of connection
(AS SYSDBA, AS SYSOPER or blank)
– _USER Currently connected userid
• APPEND, CREATE, REPLACE extensions to SPOOL

SET SERVEROUTPUT ON

It is common for PL/SQL developers to use the DBMS_OUTPUT.PUT_LINE procedure to write to the console during testing and debugging
• To enable output from DBMS_OUTPUT.PUT_LINE you must enable SERVEROUTPUT
• In Oracle 10g this command has been enhanced to include a default of UNLIMITED buffer size eliminating the need to specify a buffer size
• You may also specify “WORD_WRAPPED” to cause DBMS_OUTPUT.PUT_LINE output to be wrapped at clear word breaks
set serveroutput on size 1000000 –- size limited
set serveroutput on unlimited -- size unlimited
set serveroutput on -- size unlimited (default)

10g Using Recyclebin


Careful! Dropping tables no longer really drops them… This might be a problem for applications with
lots of “temp”-type tables

drop table myTable;

show recyclebin
ORIGINAL    RECYCLEBIN NAME     TYPE  DROP TIME
myTable RB$$41506$TABLE$0   TABLE 2004-04-01:22:11:13

flashback table myTable to before drop;

drop table myTable purge;

purge recyclebin;

10g Regular Expressions

Oracle now has three functions that allow the use of POSIX-compliant regular expressions 

in SQL

– REGEXP_LIKE Allows pattern matching

– REGEXP_INSTR Search for string matching pattern and return position

– REGEXP_REPLACE Find string matching pattern and replace it

– REGEXP_SUBSTR Search for string matching pattern and return substring

Regular Expression Examples

select employee_id,phone_number
from hr.employees
where REGEXP_LIKE(phone_number,
'[[:digit:]]{3}[[:punct:]][[:digit:]]{2}[[:punct:]]');

Example

select first_name, last_name
from hr.employees
where REGEXP_LIKE (first_name, '^ste(v|ph)en$');

LIKE vs REGEXP_LIKE


Here are two statement that generate exactly the same output and nearly the same execution plan

select prod_id , substr(prod_name,1,20) prod_name , substr(prod_desc,1,30) prod_desc
from sh.products
where prod_name like ('E%') 
or prod_name like ('P%')
order by prod_id;

select prod_id, substr(prod_name,1,20) prod_name, substr(prod_desc,1,30) prod_desc
from sh.products
where regexp_like (prod_name,'^E|^P')
order by prod_id;