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;

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;

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.

Case Sensetivity in Oracle Text Search

Word queries are case-insensitive by default. This means that a query on the term dog returns the rows in your text table that contain the word dog, Dog, or DOG.

You can enable case-sensitive searching by enabling the mixed_case attribute in your BASIC_LEXER index preference. With a case-sensitive index, your queries must be issued in exact case.

Querying with MATCHES [CTXRULE INDEX]

When you create an index of type CTXRULE, you must use the MATCHES operator to classify your documents.

SELECT classification FROM querytable WHERE MATCHES(query_string,:doc_text) > 0;

Putting it all-together,

create table queries (query_id number,query_string varchar2(80));
insert into queries values (1, 'oracle');
insert into queries values (2, 'larry or ellison');
insert into queries values (3, 'oracle and text');
insert into queries values (4, 'market share');
create index queryx on queries(query_string)
indextype is ctxsys.ctxrule;

select query_id from queries where matches(query_string, 'Oracle announced that its market share in databases increased over the last year.')>0

This query will return queries 1 (the word oracle appears in the document) and 4 (the phrase market share appears in the document).

Querying with Oracle Text [CTXCAT INDEX]

When you create an index of type CTXCAT, you must use the CATSEARCH operator to issue your query. The operators available for CATSEARCH queries are limited to logical operations such as AND or OR. The operators you can use to define your structured criteria are greater than, less than, equality, BETWEEN, and IN

SELECT FROM auction WHERE CATSEARCH(title, 'camera', 'order by bid_close desc')>0;

For example, assuming that category_id and bid_close have a sub-index in the ctxcat index for the AUCTION table, you can issue the following structured query:

SELECT FROM auction WHERE CATSEARCH(title, 'camera', 'category_id=99 order by bid_close desc')> 0;

Querying with Oracle Text [CONTEXT INDEX]

Querying with CONTAINS:
When you create an index of type CONTEXT, you must use the CONTAINS operator to issue your query. With CONTAINS, you can also use the ABOUT operator to search on document themes.

SELECT SCORE(1), title from news WHERE CONTAINS(text, 'oracle', 1) > 0;

The CONTAINS operator must always be followed by the > 0 syntax, which specifies that the score value returned by the CONTAINS operator must be greater than zero for the row to be returned. When the SCORE operator is called in the SELECT statement, the CONTAINS operator must reference the score label value in the third parameter as in the previous example.

Structured Query with CONTAINS:
SELECT SCORE(1), title, issue_date from news WHERE CONTAINS(text, 'oracle', 1) > 0 AND issue_date >= ('01-OCT-97')
ORDER BY SCORE(1) DESC;

Managing DML Operations for a CONTEXT Index

Viewing Pending DML:
SELECT pnd_index_name, pnd_rowid, to_char(pnd_timestamp, 'dd-mon-yyyy hh24:mi:ss') timestamp FROM ctx_user_pending;

Synchronizing the Index:
The following example synchronizes the index with 2 megabytes of memory:
begin
ctx_ddl.sync_index('myindex', '2M');
end;

You can set CTX_DDL.SYNC_INDEX to run automatically at regular intervals using the DBMS_JOB.SUBMIT procedure.The location of this script is:

$ORACLE_HOME/ctx/sample/script/drjobdml.sql

you must be the index owner and you must have execute privileges on the CTX_DDL package. For example,to set the index synchronization to run every 360 minutes on myindex,

SQL> @drjobdml myindex 360