Monthly Archives: January 2011

Certified!

So I had the pleasure of taking my first Oracle certification exam today: “Oracle Database: SQL Certified Expert”. I passed pretty easily, but am not very happy with the exam and the experience.

First, the test. I am sure I missed my share of question by not paying close enough attention to the details. However, I found myself staring blankly during at least 10 of the 70 questions. Not that I didn’t know the details, but rather I either didn’t really understand what was being asked or couldn’t make out at all what some of the answers were supposed to mean. I felt the language was extremely confusing. There was at least one question asking if something can be done with a function and I had to pick between 1) an answer of something I knew could be done with the function, but nobody in their right mind would use it that way or 2) an answer that sounded kind of right, but really I had no clue what the answer was getting at. I wish I was allowed to give specific examples. So to recap, I feel Oracle did a very poor job with this test. There are plenty of things I don’t know about the database, I don’t also need to miss an extra 7 or 8 questions because the question is written poorly.

Yet, somehow the test was still better than my experience. Literally in the middle of the test (question 34 of 70) my screen went black and I could hear several computers in the building beep like they were restarting. NOOOO!! Luckily my screen came back on and still had my test at the same spot, but was now frozen. The admin came and got me out of the testing room to sit while they had to reset their main computer system. To do this required their “computer guy” who was apparently in the back of the office. So I sat. and sat. and sat. Finally the computer guy comes strolling up after 45 minutes. He was talking to another employee about how often they have power surges that do this and basically bring their whole office down. He goes on to mention that while we were waiting on him, he was just in the back eating his lunch. After no more than 5 minutes of work of turning machines on and logging in, everything was back online. So to recap, not only do they not have anything like UPS backup, he also couldn’t take a 5 minute break in his lunch to come fix the problem and get the whole office back online. Sure I don’t mind extending my lunch another hour so you can eat yours without interruption.

Fun times!

PL/SQL Error Messages Part II

A continuation of a my previous post, I am looking more into the capabilities of DBMS_UTILITY.FORMAT_ERROR_STACK and DBMS_UTILITY.FORMAT_ERROR_BACKTRACE. This time I will look more at what FORMAT_ERROR_BACKTRACE can do. These tests are on a 10.2.0.4 database.

First, I do a simple test to see what FORMAT_ERROR_BACKTRACE even returns:

BEGIN
    raise_application_error(-20001, 'Test Error Message');
EXCEPTION
    WHEN OTHERS
    THEN
        dbms_output.put_line('BACKTRACE Output: '||dbms_utility.format_error_backtrace);
END;
BACKTRACE Output: ORA-06512: at line 2

Not really what I was expecting.. So it looks like to get the full message you need to combine both FORMAT_ERROR_STACK and FORMAT_ERROR_BACKTRACE. I guess that could explain why FORMAT_ERROR_STACK has the line break at the end:

BEGIN
    raise_application_error(-20001, 'Test Error Message');
EXCEPTION
    WHEN OTHERS
    THEN
        dbms_output.put_line('Output: '||dbms_utility.format_error_stack||dbms_utility.format_error_backtrace);
END;
Output: ORA-20001: Test Error Message
ORA-06512: at line 2

So now I want to look at how nesting my error through different procedures changes things:

declare
	procedure p1
	is
	begin
		raise_application_error(-20001, 'Test Error Message');		
	end p1;
--
	procedure p2
	is
	begin
		p1();	
	end p2;
begin
	p2();
exception
	when others
	then
		dbms_output.put_line(dbms_utility.format_error_stack||dbms_utility.format_error_backtrace);
end;
ORA-20001: Test Error Message
ORA-06512: at line 5
ORA-06512: at line 11
ORA-06512: at line 14

As you can see from the output, FORMAT_ERROR_BACKTRACE does a good job of showing how the error was raised up through the various programs:

  • Line 14 is the call to p2()
  • Line 11 is the call to p1()
  • Line 5 is where the error originated

Now I want to look at how my output is changed by the often overlooked 3rd parameter of RAISE_APPLICATION_ERROR. This parameter is a boolean called keeperrorstack.

First with the default value (which is FALSE):

declare
	procedure p1
	is
	begin
		raise_application_error(-20001, 'Test Error Message');		
	end p1;
--
	procedure p2
	is
	begin
		p1();	
	exception
		when others
		then
			raise_application_error(-20001, 'New Message');
	end p2;
begin
	p2();
exception
	when others
	then
		dbms_output.put_line(dbms_utility.format_error_stack||dbms_utility.format_error_backtrace);
end;
ORA-20001: New Message
ORA-06512: at line 15
ORA-06512: at line 18

So, the default message makes it look like the first error that was ever raised was the ‘New Message’ error, and it came from line 15 (which is the raise_application_error call in the exception block of p2).

Now I will look at if the value is set to TRUE:

declare
	procedure p1
	is
	begin
		raise_application_error(-20001, 'Test Error Message');		
	end p1;
--
	procedure p2
	is
	begin
		p1();	
	exception
		when others
		then
			raise_application_error(-20001, 'New Message', TRUE);
	end p2;
begin
	p2();
exception
	when others
	then
		dbms_output.put_line(dbms_utility.format_error_stack||dbms_utility.format_error_backtrace);
end;
ORA-20001: New Message
ORA-06512: at line 15
ORA-20001: Test Error Message
ORA-06512: at line 15
ORA-06512: at line 18

So now I can see that there was actually a second error message, however, it also appears to have originated from line 15, which is not correct. That is better but still less than ideal.

Last test… What happens when I just re-raise an error instead of raising a new error, which is much more likely for me to do:

declare
	procedure p1
	is
	begin
		raise_application_error(-20001, 'Test Error Message');		
	end p1;
--
	procedure p2
	is
	begin
		p1();	
	exception
		when others
		then
			RAISE;
	end p2;
begin
	p2();
exception
	when others
	then
		dbms_output.put_line(dbms_utility.format_error_stack||dbms_utility.format_error_backtrace);
end;
ORA-20001: Test Error Message
ORA-06512: at line 15
ORA-06512: at line 18

This is what I was afraid of. Now I keep my original error message, but again it looks like it originates from line 15, which is the RAISE; statement.

I would be interested to see if any of this changed in 11g, so if I get time I will run these tests again on an 11gR2 db.

Notes for 1Z0-047 Part V

Continuation of my notes to review before taking the 1Z0-047 certification.

Hierarchical Queries

  • fork is the term for a node that has 2 or more children
  • If you have both JOIN and CONNECT BY, the join is processed before the connect by
  • Order of START WITH and CONNECT BY clauses don’t matter with respect to each other, but both must be after WHERE and before ORDER BY
  • Keyword prior can be used in seelct list to return values from the previous row in the hierarchy (which may or may not be the previous row in query output)
  • ORDER BY will order all rows across all levels
    • ORDER SIBLINGS BY will order rows just within the context of the current level
  • Filter criteria can be added to CONNECT BY clause to exclude entire branches of the tree
    • This will exclude node with employee_id = 10 and all of its children in hierarchy tree:

      select *
      from employees
      start with manager_id is null
      connect by manager_id = prior employee_id
      and employee_id <> 10;

Regular Expressions

  • POSIX character classes must be all lower case, otherwise will throw a syntax error ([:digit:] works, [:DIGIT:] does not)
  • If conflicting match parameters are specified at same time, last one is used
    • ‘ic’ would result in case-sensitive matching from the “c”, even though case-insensitive matching was also specified by the “i”
  • Parenthasis are used to look for exact strings and allows string to be combined with metacharacters. A Pipe inside the parenthasis acts as an OR between exact strings
    • ‘[[:alpha:]]+(ing)’ will return first word ending in “ing”
    • ‘[[:alpha:]]+(ing|ed)’ will return first word ending in “ing” or “ed”

Privileges

  • ALL PRIVILEGES – Keyword PRIVILEGES optional for object privileges but mandatory for system privileges:
    • Valid:
      grant all on ships to george;

    • Invalid:
      grant all to george;

    • Revoking all system privileges from a user will throw error unless that user currently has ALL system privileges
    • Revoking all object privileges from a user will not error, even if that user currently has NO privileges on that object
  • Privileges are dropped when associated objects are dropped, and must be recreated if object is recreated and you want them back
    • Exception is when using FLASHBACK TABLE to restore to before drop, which also restores object privileges as well as table
  • WITH ADMIN OPTION – Can be used when granting system privileges and roles to allow recipient to be able to grant that privilege / role to others
    • If that privilege / role subsequently revoked, all privileges / roles that user granted to others still remain
  • WITH GRANT OPTION – Can be used when granting object privileges to allow recipient to be able to grant that privilege to others
    • If that privilege subsequently revoked, all privileges that user granted to others are also revoked
  • There is no CREATE INDEX privilege; it comes as part of the CREATE TABLE privilege

This concludes my notes to review before taking the 1Z0-047 certification. Now I just need to study! 🙂

Notes for 1Z0-047 Part IV

Continuation of my notes to review before taking the 1Z0-047 certification.

Rollup / Cube / Grouping Sets

grouping(column_name) = 1 indicates a superaggregate row

Data Dictionary

  • Adding Comments: Comments can be added to tables, columns, indextype, etc.:

    comment on table ships is 'Listing of all ships';
    comment on column ships.capacity is 'Maximum number of passengers';

  • Comments can’t be dropped. To achieve same result, just set to blank:
    comment on table ships is '';
  • USER_CATALOG – Dictionary view that contains all tables, views, synonyms and sequences for current user
    • Only has two columns: table_name and table_type
  • USER_CONTRAINTS – Possible values for constraint_type:
    • P – Primary Key
    • R – Referential Integrity (Foreign Key)
    • U – Unique
    • C – Check and NOT NULL
  • DICTIONARY – Dictionary view that contains all views that make up data dictionary

    Manipulating Large Datasets

    CTAS – Can work if all columns have a valid name / alias in subquery or by specifying column list in create table statement:

    create table active_ships
    (ship_name, total_capacity)
    as
    select ship_name, first_deck_capacity + second_deck_capacity
    from ships
    where status = 'Active';


    Multitable Insert

    • Unconditional – ALL keyword is required:
      INSERT ALL
      INTO table1 VALUES(column_list)
      INTO table2 VALUES(column_list)
      SELECT ...
      ;

    • Conditional – Example:

      INSERT [ALL / FIRST]
      WHEN expression THEN
      INTO table1 VALUES(column_list)
      WHEN expression THEN
      INTO table2 VALUES(column_list)
      INTO table3 VALUES(column_list)
      ELSE
      INTO table4 VALUES(column_list)
      SELECT ...
      ;

      • ALL – Default option; Executes all into clauses for all expressions that match
      • FIRST – Executes into clauses only for first expression that matches
      • VALUES Clause – Optional if number of columns and their datatypes match table in INTO exactly
      • ELSE Clause – Optional; Must be last if included

    Merge

    • Example:

      MERGE INTO table_name
      USING [table_name / view_name / subquery]
      ON condition
      WHEN MATCHED THEN UPDATE
      SET col = expression
      WHERE condition
      DELETE WHERE condition
      WHEN NOT MATCHED THEN INSERT(column_list)
      VALUES(expression_list)
      WHERE condition
      WHERE condition;

    • DELETE Clause will only delete rows that are processed by UPDATE statement and DELETE condition looks at values as they exist after the udpate

    Flashback

    Flashback Query:

    SELECT *
    FROM table
    AS OF [TIMESTAMP / SCN] expression;


    Flashback Version Query

    SELECT *
    FROM table
    VERSIONS BETWEEN [TIMESTAMP / SCN] expression AND expression;

    • expression can be keywords MINVALUE or MAXVALUE for the earliest / latest values available
    • Output shows 1 row for each version of each row in table that existed between times specified
    • Can’t be used when querying a view
    • Pseudocolumns providing identifying information for each version:
      • VERSIONS_STARTTIME / VERSIONS_ENDTIME – Start / End time when Version was created (null if created before / after lower boundary)
      • VERSIONS_STARTSCN / VERSIONS_ENDSCN – Start / End SCN when Version was created (null if created before / after lower boundary)
      • VERSIONS_XID – ID identifying transaction that created version
      • VERSIONS_OPERATION – I/U/D to identify operation (Insert/Update/Delete) that created version

    Flashback Query and Flashback Version Query can be used together:

    select s.*, versions_startscn, versions_endscn, versions_operation
    from ships s
    versions between SCN MINVALUE and MAXVALUE
    as of timestamp systimestamp - 1
    order by ship_name;

    • VERSIONS clause must come first and is constrained by AS OF clause
    • MAXVALUE will correspond to time specified by as of clause

    Flashback Transaction Query

    • Query on data dictionary table FLASHBACK_TRANSACTION_QUERY
    • TABLE contains XID, which is global transaction identifier
      • Corresponds to value in pseudocolumn VERSIONS_XID
      • Datatype is RAW; Can use RAWTOHEX to read
    • Contains column UNDO_SQL which will contain SQL statements necessary to undo statement ran that created transaction
      • For example, a delete that deletes 10 rows will result in FLASHBACK_TRANSACTION_QUERY table having 10 rows all with same XID that each have an insert statement in UNDO_SQL column
  • PL/SQL Error Messages

    I have been aware of DBMS_UTILITY.FORMAT_ERROR_STACK and DBMS_UTILITY.FORMAT_ERROR_BACKTRACE for some time now, but until recently have never found myself needing to know much about them. For my uses SQLERRM has always worked just fine. However, in a project I am currently working on, we are looking to have better error reporting, so it was finally time to look at what these functions can do. This will be a two part posting on what I find. This part will cover the differences between SQLERRM and FORMAT_ERROR_STACK.

    In one of Steven Feuerstein‘s presentations, he mentions how DBMS_UTILITY.FORMAT_ERROR_STACK is preferred since SQLERRM could possibly truncate your error messages, but I had never seen this in practice so I threw together a quick test:

    BEGIN
        raise_application_error(-20001, rpad('test', 10000, '!'));
    EXCEPTION
        WHEN OTHERS
        THEN
            dbms_output.put_line('SQLERRM: '||length(sqlerrm));
            dbms_output.put_line('FORMAT_ERROR_STACK: '||length(dbms_utility.format_error_stack));
    END;
    
    SQLERRM: 510
    FORMAT_ERROR_STACK: 1899
    

    So clearly they will both cut off some information, but FORMAT_ERROR_STACK will give you 3 times as much. While I was playing around
    with this, I saw another anomaly:

    BEGIN
        raise_application_error(-20001, rpad('test', 10, '!'));
    EXCEPTION
        WHEN OTHERS
        THEN
            dbms_output.put_line('SQLERRM: '||length(sqlerrm));
            dbms_output.put_line('FORMAT_ERROR_STACK: '||length(dbms_utility.format_error_stack));
    END;
    
    SQLERRM: 21
    FORMAT_ERROR_STACK: 22
    

    FORMAT_ERROR_STACK contains an extra character even when the message is short enough that it shouldn’t be getting truncated, so that made me curious as to what was going on:

    DECLARE
        err_msg VARCHAR2(255);
        v1 VARCHAR2(255);
        v2 VARCHAR2(255);
    BEGIN
        raise_application_error(-20001, rpad('test', 10, '!'));
    EXCEPTION
    	WHEN OTHERS
    	THEN
            -- store sqlerrm in variable so I can use it in SQL to take advantage of dump function
            -- since I don't know where extra character is
            err_msg := sqlerrm;
            -- Get results of dump
            select dump(err_msg), dump(dbms_utility.format_error_stack)
            INTO v1, v2
            from dual;
            -- display results of dump
            dbms_output.put_line(rpad('SQLERRM:', 20)||v1);
            dbms_output.put_line(rpad('FORMAT_ERROR_STACK:', 20)||v2);
    END;
    
    SQLERRM:            Typ=1 Len=21: 79,82,65,45,50,48,48,48,49,58,32,116,101,115,116,33,33,33,33,33,33
    FORMAT_ERROR_STACK: Typ=1 Len=22: 79,82,65,45,50,48,48,48,49,58,32,116,101,115,116,33,33,33,33,33,33,10
    

    As you can see from the output, the extra character is the 10 at the end which corresponds to a line feed. Something to keep in mind if you are trying to add the error message to a standardized format (like possibly writing into a file) you may need to remove that line feed first.

    Also in my testing I discovered that you can’t use SQLERRM in SQL but you can use FORMAT_ERROR_STACK. I don’t really know how that would be useful, but who knows…

    The next part will focus mostly on DBMS_UTILITY.FORMAT_ERROR_BACKTRACE to determine what information it can give you and in what situations it loses information.

    OOUG Presentation

    I presented on Explain Plans and Execution Plans at todays OOUG meeting, and had mixed emotions. I got through everything and didn’t leave out anything too important that I wanted to say, but it definitely didn’t go as smoothly as I was hoping for. I did hear some nice feedback though, so I guess it wasn’t all bad (or people are just nice to my face)! 🙂 I also got a good suggestion for improving the piece where there is audience participation, so I will have to put that in place for next time. If you attended the OOUG meeting and have any comments or suggestions on anything else I can do to improve the presentation, please leave a comment.

    If you are interested, you can download the presentation here.

    Notes for 1Z0-047 Part III

    Continuation of my notes to review before taking the 1Z0-047 certification.

    Sequences

    Rules:

    • CURRVAL can’t be invoked in a session before NEXTVAL has been called in the same session
    • If NEXTVAL used in statement, counter incremented even if statement doesn’t complete successfully
    • CURRVAL / NEXTVAL can’t be used as column default
    • CURRVAL / NEXTVAL can’t be used in subselect
    • CURRVAL / NEXTVAL can’t be used with distinct
    • CURRVAL / NEXTVAL can’t be used in WHERE clause
    • CURRVAL / NEXTVAL can’t be used in CHECK constraint
    • CURRVAL / NEXTVAL can’t be used with SET operators

    Synonyms

    Synonyms can be created for objects that don’t exist (or don’t yet exist)

    Alter Table

    • COLUMN keyword can’t be used with ADD or MODIFY
    • Drop Column: If parentheses omitted, COLUMN keyword must be used. If parenthesis used, COLUMN keyword can not be used:

      alter table ships drop column capacity;

      OR

      alter table ships drop (capacity);

    • Constraints must be dropped before columns, unless “cascade constraints” clause used
      • This includes FK constraints on other tables
    • set unused column:
      • Same effect as dropping column in that column no longer available, and can never get it back

      • can create new column with same name as unused column
      • unused columns still count toward column limit of 1000 per table
      • when dropping unused columns, it is all or none: alter table drop unused columns;
      • use case: functionality of a drop, but returns immediately. Real drop can then be run during maintenance window

      Foreign Key Options

      • on delete cascade – deletes rows in child table when parent deleted
      • on delete set null – updates column to null of child row when parent deleted

      Create Table

      The USING index clause of UNIQUE and Primary Key contsraints can be used to create indexes as well as specifying existing indexes:

      create table ships
      (
      ship_id number primary key
      using index (
      create index ships_pk on ships(ship_id)
      ),
      ship_name varchar2(50)
      );

      Flashback Table

      Options

      • To before drop
      • To SCN xxx
      • To timestamp xxx
      • To restore point xxx

      For all but “To before drop”, row movement must be enabled on the table:

      alter table ships enable row movement;


      SCN: Incremented with each commit and stored with each row. It can be identified with the pseudocolumn ora_rowscn

      Restore Point: names scn so can be referenced later. A similar idea to savepoint.

      The functions SCN_TO_TIMESTAMP and TIMESTAMP_TO_SCN can be used to get approximate values of one from the other.

      Set Operators

      • LOB fields can’t be used with SET operators
      • Order By must only appear after last select, and applies to entire result of set operation
        • Can order by position or by “reference” – column names must correspond to those of first select