What is a correlated sub query?
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 then only that record will be displayed.
What are the Oracle Cursor Attributes ?
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 cursor returned values.
Retruns TRUE if cursor does not return values.
%ROWCOUNT – Returns a number.
This attribute will retrun the number of records fetched by the cursor.
BCP Utility – using BCP IN in Microsoft SQL Server
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. If you have not read my posting on BCP OUT then click here BCP Utility – using BCP OUT in Microsoft SQL Server to read it.
The Syntax diagram for the BCP command is shown below:
DBMS_LOCK – Sleep functionality (Procedure) in Oracle
HTML clipboardDBMS_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.
In most of the programming languages you will find some form of technique where you can wait/sleep for some specified amount of time. This SLEEP/WAIT functionality may be required for different number of reason. For example, suppose you want to insert a record into a table every 5 seconds. In Microsoft SQL Server you will just use “waitfor” command which is a part of the T-SQL programming language. But how will you do that in Oracle. Here is how:.
Example: This example will insert a record in table t_sleep_test. Then wait for 5 seconds. Then insert another record.
1. Create table t_sleep_test
BCP Utility – using BCP OUT in Microsoft SQL Server
BCP is a tool developed by Microsoft which can be easily used to migrate data from one database to another or to copy data from the SQL Server database to a operating system file or vice versa. This is a very powerful tool that I found very useful while working on my development work using SQL Server databases. To export data using BCP you your BCP OUT and to import data you will use BCP IN. In this posting I will show you (BCP OUT) how easily you can transfer your data from your Microsoft SQL Server database to an operating system file.
To find out whether you have bcp utility properly installed/configured in your machine (of course with SQL Server database) just open a DOS prompt and type bcp abd hit ENTER key as shown below:
C:\>bcp
usage: bcp {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide character type]
[-N keep non-text native] [-V file format version] [-q quoted identifier]
[-C code page specifier] [-t field terminator] [-r row terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"]
Recent Comments