IN versus EXIST

EXISTS is more faster than IN because EXISTS returns a Boolean value whereas IN returns a value.

DIfference between varchar and nvarchar in Oracle

varchar2(20 char) means you can store 20 characters -- whereas varchar2(20) means you can store 20 bytes. the varchar2(20 char) might take 20, 40, 60, 80 or more bytes to hold a string. The maximum length of a varchar2 is still 4000 bytes so varchar2(4000 char) is "misleading" in that the field will max out at 4000 bytes -- not 4000 characters.

Generalizing Cursors with Parameters

I really don't want to write a separate cursor for each different category −−  that is definitely not a data−driven approach to programming. Instead, I would much rather be able to change the joke cursor so that it can accept different categories and return the appropriate rows. The best (though not the only) way to do this is with a cursor parameter:

DECLARE
/*
|| Cursor with parameter list consisting of a single
|| string parameter.
*/
CURSOR joke_cur (category_in VARCHAR2)
IS
SELECT name, category, last_used_date
FROM joke
WHERE category = UPPER (category_in);
joke_rec joke_cur%ROWTYPE;
BEGIN
/* Now when I open the cursor, I also pass the argument */
OPEN joke_cur (:joke.category);
FETCH joke_cur INTO joke_rec;

Column Aliases in Cursors

The SELECT statement of the cursor includes the list of columns that are returned by that cursor. Just as with any SELECT statement, this column list may contain either actual column names or column expressions, which are also referred to as calculated or virtual columns.

A column alias is an alternative name you provide to a column or column expression in a query. You may have used column aliases in SQL*Plus in order to improve the readability of ad hoc report output. In that situation, such aliases are completely optional. In an explicit cursor, on the other hand, column aliases are required for calculated columns when:

· You FETCH into a record declared with a %ROWTYPE declaration against that cursor.
· You want to reference the calculated column in your program.

DECLARE
CURSOR comp_cur IS
SELECT company_name, SUM (inv_amt) total_sales
FROM company C, invoice I
WHERE C.company_id = I.company_id
AND I.invoice_date BETWEEN '01−JAN−1994' AND '31−DEC−1994';
comp_rec comp_cur%ROWTYPE;
BEGIN
OPEN comp_cur;
FETCH comp_cur INTO comp_rec;
...
END;

Query to Find Unindexed Foreign Key Constraints

Now that we know unindexed foreign key constraints can cause severe problems, here is a script that I use to find them for a specific user (this can easily be tailored to search all schemas):

SELECT * FROM (
SELECT c.table_name, cc.column_name, cc.position column_position
FROM user_constraints c, user_cons_columns cc
WHERE c.constraint_name = cc.constraint_name
AND c.constraint_type = 'R'
MINUS
SELECT i.table_name, ic.column_name, ic.column_position
FROM user_indexes i, user_ind_columns ic
WHERE i.index_name = ic.index_name
)
ORDER BY table_name, column_position;

Where to go in Kuakata

Kuakata is known as Sagar Konna in Bangladesh and the most beautiful thing is that you can see the sunset and sunrise from the same beach. Some great attractions are,

1) Beach :
Most of the hotels are just beside the beach. But you can see only sunset from this beach. Motocycle are abvaialable to hire to go any place in kuakata. they made a contract like a package tour...sami temple, gongamotir chor(from where you can see the sunrise).It will cost 200 taka.
2) GongaMotir Chor and Jhauban
3) Temple:
one of two temples is just beside the beach. Its just a walking distance. Another one is sami temple or misri para temple.
4) Fatrar ban:
Its part of sundarban. A tour agency is there to bring you in fatrarban. You can collect the tickets from the beach with 100 taka.

How to go Kuakata

You can go by Bus or Launch.
Bus:
Direct bus route from Dhaka to Kuakata. “Sakura” costs around 450 Taka (US $8.00). There are some other buses like Druti, Soudia etc. But You have to know the last destination clearly. Because some of the buses goes to khepupara and then you have to buy another ticket towards kollapara(kuakata) worth 30 taka per person.

Launch:
Dhaka to Barisal by large river boat locally known as “Launch”. They leave from Dhaka Sadarghat launch terminal between 6 to 9 PM. This comfortable overnight journey to Barisal will cost around 500 to 700 per person in a comfortable cabin (double is around 900 to 1400 taka).

You can also take a direct river boat from Dhaka sadarghat terminal to Patuakhali. This journey will cost you about the same as Barisal and these boats leaves little early around 4 to 7 PM. River boats are much nicer in Barisal route.

Nested Blocks in PL/SQL in Oracle

A block may also contain nested sub−blocks of code. The following example shows a procedure with an anonymous, nested block defined within it:

PROCEDURE calc_totals IS
year_total NUMBER;
BEGIN
year_total := 0;
/* Nested anonymous block */
DECLARE
month_total NUMBER;
BEGIN
month_total := year_total / 12;
END;
END;


Notice that I can reference the year_total variable inside the nested block. Any element declared in an outer
block is global to all blocks nested within it. Any element declared within an inner block cannot, however, be
referenced in an outer block.

Explanation for the Keywords of EXPORT command in Oracle

Keyword for EXPORT:
USERID: username/password
BUFFER: size of data buffer
FILE: output file (EXPDAT.DMP)
COMPRESS: import into one extent (Y)
GRANTS: export grants (Y)
INDEXES: export indexes(Y)
ROWS: export data rows (Y)
CONSTRAINTS: export table constraints (Y)
CONSISTENT: cross-table consistency (N)
LOG: log file of screen output (None)
STATISTICS: analyze objects (ESTIMATE)
DIRECT: Bypass the SQLcommand processing layer (N) (new in Oracle8)
FEEDBACK: Show a process meter (a dot) every X rows exported (0 – Xvalue)
HELP: Shows help listing MLS MLS_LABEL_FORMAT Used with secure Oracle; we won't cover
these.
FULL: export entire file (N)
OWNER: list of owner usernames
TABLES: list of table names
RECORDLENGTH: length of IO record
INCTYPE: incremental export type
RECORD: track incr. export (Y)
PARFILE: parameter file name

How to make HOT backup, Oracle

A hot backup, or one taken while the database is active, can only give a read-consistent copy but doesn’t handle active transactions. You must ensure that all redo logs archived during the backup process are also backed up.

Limitations on hot or on-line backups:
􀂃 The database must be operating in ARCHIVELOG mode for hot backups to work.
􀂃 Hot backups should only be done during off or low-use periods.

The hot backup consists of three processes:
1. The tablespace data files are backed up.
Make a script. Select all the talespaces, make 'alter tablespace tablespace_name begin backup'. Copy the corresponding datafiles. then 'alter tablespace tablespace_name end backup'.
2. The archived redo logs are backed up.
select the member from v$logfile and copy them.
Then 'alter system switch logfile;'.
archive log all;
see the archive destination from v$parameters. copy the files.
3. The control file is backed up.
alter database backup control file to /tape1/ora_conbackup.bac;

How to make a COLD backup, Oracle

A cold backup, that is, one done with the database in a shutdown state, provides a complete copy of the database that can be restored exactly. The generalized procedure for using a cold backup is as follows:
1)shutdown the Oracle instance(s) to be backed up.
2) Mount the first volume of the backup media.
3)Issue the proper Operating System backup command to initiate the backup.
$ tar –cvf /tape1 /ud*/oracle*/ortest1/*
4)Dismount.

Substituting PL/SQL Variables within the SQL while executing

CREATE TABLE employees_temp AS SELECT first_name, last_name FROM employees;
DECLARE
x VARCHAR2(20) := 'my_first_name';
y VARCHAR2(25) := 'my_last_name';
BEGIN
INSERT INTO employees_temp VALUES(x, y);
UPDATE employees_temp SET last_name = x WHERE first_name = y;
DELETE FROM employees_temp WHERE first_name = x;
COMMIT;
END;
/

To use variables in place of table names, column names, and so on, requires the EXECUTE IMMEDIATE statement.

select a random row in oracle database

when you run a select query everytime it will return the same rows. If you wanna make it different every time that is randomly, then you can use it,
SELECT col_name  FROM
(SELECT col_name
FROM table_name
ORDER BY dbms_random.value)
WHERE rownum = 1;

Nijhum Dip, How to go

Nijhum Dwip is an offshore island in the Bay of Bengal situated in the extreme south of Hatia island which is separated by Hatia channel. There are several approaches to the island. Passenger launches everyday plies between Sadarghat in Dhaka to Tomoruddy Ghat of Hatia upazilla. Adventure tourists can hire a trawler from Tomoruddy to Nijhum Dwip. A trawler may cost between Tk 1,200 and Tk 1,500 per day. Another approach is from Chittagong. A coastal passenger vessel plies between Chittagong and Nalchira Ghat twice a week. Our Star Holiday team took the sea-truck ride from Char Jabber Ghat to Nalchira Ghat at the the northern tip of Hatia which took around two and a half hours. For overnight stay in the island you have to contact the UNO of Hatia in advance for booking the bungalow on the island, or you can contact the DFO of Noakhali to stay at Nijhum Dwip forest office. You must carry your food and other necessities.

Nijhum Dip , The Real Beauty.

Nijhum Deep is a very precious island, as it was concealed by water for many years. The island slowly started to surface, and since 1972 the forestry department has been involved in the planting of trees and brush such as Bain, Keowra and Geowra. The forest, which is irrigated by the bay, has also attracted a great number of bird species. By 1978, wildlife was introduced to the island, and the eight spotted deer that were originally released on the island have grown to a healthy population of approximately six thousand individuals.

The spotted deer share Nijhum Deep with various other animals such as snakes, buffalo and monkeys, and it is estimated that almost thirty-five bird species are permanent residents of the island. Migratory birds also find the island the perfect resting destination as it is almost untouched and not threatened by human interaction. The beaches and sightings of crabs, sea snails and oysters create great excitement amongst tourists, as they are able to appreciate nature against the backdrop of the lush, green landscapes.

The authorities have made the island more attractive to tourists by erecting visitor facilities along the beaches. Viewing a sunset or sunrise from the shores of this tranquil and inspiring natural paradise is unforgettable. Bangladesh has realized how significant and important their wildlife and nature can be for the economy and tourism industry and is looking at creating more protected areas within the country. Nijhum Deep is the perfect example of how conservation and forestry can, not only assist and ensure the survival of animals, but enhance an industry and a country.

Family Name and Given Name

We have to face question about family name and given name Lot of time while filling the forms. Basically,
Family Name: Last name
Given Name: Rest of the part of full name.

Scope of the Loop Counter Variable

<< main>>
DECLARE
i NUMBER := 5;
BEGIN
FOR i IN 1..3 LOOP -- assign the values 1,2,3 to i
DBMS_OUTPUT.PUT_LINE( 'local: ' || TO_CHAR(i)
|| ' global: ' || TO_CHAR(main.i));
END LOOP;
END main;
/


The variable 'i' is used inside the loop as well as outside of the loop. Now if we wanna use the outer variable inside the loop having the same name as the loop counter then we have to use lebel.Here , 'main' is the name of the lebel.

Reverse FOR..LOOP in PL/SQL

Normal For Loop:
FOR k IN 1..500 LOOP
p := p + 1;
END LOOP;

Reverse For Loop:
BEGIN
FOR i IN REVERSE 1..3 LOOP -- assign the values 1,2,3 to i
DBMS_OUTPUT.PUT_LINE (TO_CHAR(i));
END LOOP;
END;
/

Exit the Loop in PL/SQL

IF and EXIT:
DECLARE
credit_rating NUMBER := 0;
BEGIN
LOOP
credit_rating := credit_rating + 1;
IF credit_rating > 3 THEN
EXIT; -- exit loop immediately
END IF;
END LOOP;
END;
/

EXIT and WHEN:
DECLARE
credit_rating NUMBER := 0;
BEGIN
LOOP
credit_rating := credit_rating + 1;
EXIT WHEN credit_rating > 3; -- exit loop immediately
END LOOP;
END;
/

Optimize a query having multiple CONTAINS clause

Consider the following multiple CONTAINS query:

SELECT title, isbn FROM booklist WHERE CONTAINS (title, 'horse') > 0 AND CONTAINS (abstract, 'racing') > 0

We can obtain the same result with section searching and the WITHIN operator as follows:

SELECT title, isbn FROM booklist WHERE CONTAINS (alltext, 'horse WITHIN title AND racing WITHIN abstract')>0;


This will be a much faster query. In order to use a query like this, we must copy all the data into a single text column for indexing, with section tags around each column's data.