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;
