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.