10. Can we pass a PARAMETER to CURSOR ? What is SQL%ROWCOUNT ?
We can pass parameter to CURSOR. Eg: OPEN CUSOR(`VASAN').
SQL%ROWCOUNT is used to count the number of rows returned by an SQL DML statement. It
will return zero if the DML statement doesn't return any row.
15. Can we use a funtion inside an INSERT statement ?
Yes. Eg: INSERT INTO EMP(COMM ) VALUES ( SAL*0.05 ) WHERE DEPTNO = 20;
16. What is TRUNCATE table ?
TRUNCATE table is a DDL command used to remove all the rows from the specified
table or cluster instantly. Eg: TRUNCATE TABLE table_name;
17. What is ROWID ? What are its components ?
ROWID is the logical address of a row, and it is unique within the database.The ROWID
is broken into three sections: left,middle,, and right (corresponding to 00001F20,000C, AND
0001, just shown). The numbering is in hexadecimal notation.
The left section is the block in the file, the middle is the row sequence number within the
block(numbering starts with 0, not 1), and the right is the file number within the database. Note
that the file numbers are uniquewithin the whole database. The tablespace they are
in is not
relevant to the ROWID.
ROWID can be selected, or used in a where clause, but cannot be changed by an insert,
update, or delete. However it can changeif the table it is in is exported and imported.
21. Name any ORACLE defined EXCEPTION ?
CURSOR_ALREADY_OPEN, NO_DATA_FOUND, INVALID_NUMBER.
22. Can we define our OWN EXCEPTION ? How to raise it ?
In the DECLARATION part define a variable of type exception. In the excecution part call
the exception using RAISE exception_name. In the exception part handle the exception using
WHEN exception_name.
23. What is a PRAGMA ?
It is a directive to the COMPILER, rather than a piece of executable code. Eventhough it
appears in the program, it is not executable. It gives instructions to the compiler.
24. Difference between CHAR and VARCHAR2 ?
CHAR(size) - It is a fixed length character data, size characters long. It is padded with
BLANKS ON RIGHT to the full length of size. DEFAULT - 1 bytes, MAXIMUM - 255 bytes.
VARCHAR2(size) - It is a varable length char string having a maximum of size bytes.
MAXIMUM - 2000 bytes.
25. What is a CURSOR FOR LOOP ?
The CURSOR FOR LOOP lets you implicitly OPEN a cursor, FETCH each row returned
by the query associated with the cursor and CLOSE the cursor when all rows have been processed.
26. What are the possible CONSTRAINTS defined on a TABLE ?
NOT NULL, UNIQUE KEY, PRIMARY KEY, FOREIGN KEY and CHECK constraints.
27. What is APPLICATION PARTITIONING ?
|
|
