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 Thursday, 17th September 2009 by kwatog
I only know two ways of getting the Oracle DB version you are currently connected to. They are as follows:
SELECT *
FROM product_component_version
SELECT *
FROM v$version
This is quite handy if your program has SQL commands that are not applicable for earlier versions of Oracle Database.
Posted in Basic, Oracle | Comments (0)
Posted on Sunday, 6th September 2009 by captainmi2
You can declare variables and constants in the declarative part of any PL/SQL block, subprogram, or package. Declarations allocate storage space for a value, specify its datatype, and name the storage location so that you can reference it.
Variables can have any SQL datatype, such as CHAR, DATE, or NUMBER, or a PL/SQL-only datatype, such as [...]
Posted in Basic, Oracle | 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 Sunday, 6th September 2009 by captainmi2
How to use null in PL\SQL
below are example:
declare
var_a varchar2(10) := null;
begin
if var_a = null then
dbms_output.put_line(’empty’);
else
dbms_output.put_line(’ not empty’);
end if;
end;
the above statement will output
“not empty” because the null is not use accordingly.
if the statement were use as below
declare
var_a varchar2(10) := null;
begin
if var_a is null then
dbms_output.put_line(’empty’);
else
dbms_output.put_line(’not empty’);
end if;
end;
this will return “empty”
This 2nd statement is the correct way to [...]
Tags: Oracle PL\SQL Null
Posted in Basic, Oracle | Comments (0)
Posted on Wednesday, 2nd September 2009 by kwatog
SYNTAX
alter table
table_name
rename to
new_table_name;
Example
alter table
employee
rename to
employee_bak;
Tags: Oracle, rename table
Posted in Basic, Oracle | Comments (0)
Posted on Thursday, 6th August 2009 by captainmi2
The lower function in Oracle PL\SQ converts all letters in the specified string to lowercase. If there are characters in the string that are not letters, they are unaffected by this function.
The syntax for the lower function is:
lower( str )
str is the string to convert to lowercase.
see example below.
SQL> select lower(’AbCD56efGHI45′) col1 from dual;
COL1
————-
abcd56efghi45
SQL>
Tags: Oracle PL\SQL statement for Lower Function
Posted in Basic, Oracle, Oracle PL\SQL Functions, Oracle PL\SQL String Functions | Comments (0)
Posted on Thursday, 6th August 2009 by captainmi2
The upper function in Oracle PL\SQL converts all letters in the specified string to uppercase. If there are characters in the string that are not letters, they are unaffected by this function.
The syntax for the upper function is:
upper(str)
str is the string to convert to uppercase.
see example below.
SQL> select upper(’abcd2efgh3′) col1 from dual;
COL1
———-
ABCD2EFGH3
SQL>
Tags: Oracle PL\SQL statement for upper function
Posted in Basic, Oracle, Oracle PL\SQL Functions, Oracle PL\SQL String Functions | Comments (0)
Posted on Saturday, 20th June 2009 by captainmi2
Oracle SQL statement for truncating the table
This will removes all records in the database.
Truncate statement does not need the commit or rollback transaction, once executed, this will remove all the record in a table without chances of getting the previous record
For example:
Table A1 has 1000 records.
In SQL:
SQL> select count(1) as count_rec from A1;
count_rec
——–
1000
SQL> [...]
Tags: Oracle SQL statement for truncating the table
Posted in Basic, Oracle | Comments (0)
Posted on Saturday, 20th June 2009 by captainmi2
Oracle SQL statement for counting the records in a table
For example:
Table A1 has 1000 records.
In SQL:
SQL> select count(1) as count_rec from A1;
count_rec
——–
1000
Tags: Oracle SQL statement for counting the records in a table
Posted in Basic, Oracle | Comments (0)
