Posted on Tuesday, 8th December 2009 by kwatog
Another oracle sql statement that you may need in order to simplify and optimize your code is the use of CASE statement. Here’s how it is being used.
Syntax #1
CASE [ expression ]
WHEN condition_1 THEN result_1
WHEN condition_2 THEN result_2
…
WHEN condition_n THEN result_n
ELSE result
END
Example
SELECT [...]
Posted in Intermediate, Oracle, Oracle PL\SQL Functions | Comments (0)
Posted on Sunday, 6th September 2009 by captainmi2
RAISE_APPLICATION_ERROR is used to create a user defined error messages.
The user defined messages are associated with the user defined number (SQLCODE) using RAISE_APPLICATION_ERROR.
DECLARE
a NUMBER := 0;
b NUMBER := 10;
BEGIN
IF (a = 0) THEN
RAISE_APPLICATION_ERROR(-20000,’ Cannot Divide by Zero’);
ELSE
b:= b/a;
dbms_output.put_line(to_char(b));
END IF;
END;
this will output
ORA-20000: Cannot Divide by Zero
Tags: Oracle Raise_Application_Error
Posted in Intermediate, Oracle | Comments (0)
Posted on Thursday, 16th April 2009 by kwatog
If you can monitor SQL Server processes you can bet your dog that you can also do that with Oracle without using sophisticated but uberly bloated software. The query below will help you with that.
SELECT sess.sid, sess.serial#, sess.sql_child_number, sess.sql_exec_id,
sess.process, sess.status, sess.username, sess.osuser, sess.program,
sess.schemaname, sql.sql_text
FROM v$session sess,
v$sql sql
WHERE sql.sql_id(+) = sess.sql_id
AND sess.type = ‘USER’
–AND sess.osuser = [...]
Posted in Intermediate, Oracle, Performance Tuning | Comments (0)
Posted on Sunday, 12th April 2009 by kwatog
TOAD for Oracle and PL/SQL Developer are two indespensable developer tools. They have built-in tools to check the query plan of your SQL scripts. But in case you only have SQL*Plus as your editor, you ultimately need to know how it is done manually.
First Step : create plan
EXPLAIN PLAN FOR
your_query_to_optimize
a sample of which is
EXPLAIN [...]
Tags: Explain Plan, Oracle, Query Plan, SQL*Plus
Posted in Intermediate, Oracle, Performance Tuning | Comments (0)
