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 [...]
Posted in Basic, 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)
Posted on Thursday, 14th May 2009 by captainmi2
Concat function in Oracle PL\SQL allows you to concatenate two strings together.
The syntax for the concat function is:
concat( string1, string2 )
string1 is the first string to concatenate.
string2 is the second string to concatenate.
Example:
SQL> select concat(’abcd’,'efgh’) as Col_1 from dual;
COL_1
——–
abcdefgh
Tags: Add new tag, Oracle PL\SQL Concat Function
Posted in Basic, Oracle PL\SQL Functions, Oracle PL\SQL String Functions | Comments (0)
Posted on Thursday, 14th May 2009 by captainmi2
Trim function removes all specified characters either in the beginning or ending of the a string.
The syntax for the Trim function is:
trim( [ leading | trailing | both [ trim_character ] ] string1 )
leading – remove trim_string from the front of string1.
trailing – remove trim_string from the end of string1.
both – [...]
Tags: Oracle PL\SQL Trim Function
Posted in Basic, Oracle, Oracle PL\SQL Functions, Oracle PL\SQL String Functions | Comments (0)
