|
| |||
Oracle, SQL, PL/SQL, Microsoft SQL Server and T-SQL related Tips and Techniques |
|
Numbers are usually converted into characters implicitly by Oracle. However
If you can to convert number into some specific format you can use Oracle’s
TO_CHAR function.
Note: While converting to a format the format mask must cover the entire length of
the number. If the number is larger than the mask then you will get ######## returned
which means the [...]
Oracle 8 does not support TIMESTAMP datatype. It only knows the DATE datatype.
So while displaying data from remote table Oracle 8 can not translate the data column
which is declared as TIMESTAMP.
A solution to this problem can be using a view. Create a view in the remote database where
you will convert the TIMESTAMP column to DATE [...]
TIMESTAMP is datatype of date family in Oracle introduced with Oracle 9i.
This datatype can display/store dates with fractional seconds and time zones.
Oracle supports three form of TIMESTAMP.
1. TIMESTAMP with fractional seconds
2. TIMESTAMP with fractional seconds and time zone
3. TIMESTAMP with fractional seconds and local time zone
For Date conversion example check out
SQL Tutorial
Oracle 11g logon ia by default case sensitive. To make it non case sensitive you need
the change init.ora parameter to
sec_case_sensitive_logon = FALSE
Yes it can, but bear in mind that Oracle 11g username/pwd are case sentisitve by default.
A correlated is a query in where the inner query is executed for each record returned
by the outer query.
For Example the query:
select * from emp a
where exists (select 1 from dept b
where a.deptno = b.deptno);
Will execute the sub query for each record returned from the first query (table emp).
If the employee is assigned a deptno [...]
There are four attributes in Oracle cursor namely:
%ISOPEN
%FOUND
%NOTFOUND
%ROWCOUNT
Atrribute usage:
%ISOPEN – Returned Values TRUE or FALSE.
Returns TRUE if the cursor is open.
Retruns FALSE if cursor is not open.
%FOUND – Returned Values TRUE or FALSE.
Returns TRUE if the cursor returned values.
Retruns FALSE if cursor does not return values.
%NOTFOUND – Returned Values TRUE or FALSE.
Returns FALSE if the [...]
In this posting I will show you (BCP IN) how easily you can transfer your data from your operating system file to Microsoft SQL Server database.
Learn about Oracle DBMS_LOCK package: DBMS_LOCK is an Oracle supplied in-built package which provides interface to Oracle’s Lock management services. Using this package you can lock an object in specific mode, give a name to the lock and release it when lock is no more required.
However the subject of this posting is not locks but Seep procedure in the DBMS_LOCK package.
This article will show you (with example) how to use BCP OUT in Microsoft SQL Server.
