DBMS Questions and Answers for Interview (Part-II)

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;

Advantage over DELETING:

a] It is a DDL statement and generates NO ROLLBACK information.

b] Doesn’t fire the tables DELETE TRIGGER.

c] Truncating the master table of a snapshot doesn’t record any changes in the tables snapshot log.

d] It’s more convinient than dropping and recreating the table.

e] D/R invalidates the table’s dependent objects than truncating the object.

f] D/R requires you to REGRANT the privileges on the table while truncating doesn’t.

g] D/R requires you to RECREATE the INDEXES, INTEGRITY CONSTRAINTS, TRIGGERS and STORAGE PARAMETER while truncating doesn’t.

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.

18. What is the differnce between REPLACE and TRASLATE ?

Syntax : REPLACE(string,if,then)

REPLACE replaces a character or characters in a string with 0 or more characters, if is a character or characters. Everytime it appears in a string, it is by the contents of then.

Eg: REPLACE(‘ADAH’,’A’,’BLAH’) - BLAHDBLAHH (Result)

Syntax: TRANSLATE(string,if,then)

TRANSLATE looks at each character in string, and then checks if to see if that character is there, if it is, TRANSLATE notes the position in if where it found the character, and then looks the same position in then. Whatever character it finds there it substitutes the character in string

Eg: TRANSLATE(‘RAMESH’,’RAM’,’SUR’) - SURESH(Result)

19. What is a LEVEL ?

LEVEL is a pseudo column, used with CONNECT BY. It is equal to 1 for a root, 2 for a child of root, 3 for a child of a child of a root and so on.

20. What is anonymous block in PL/SQL ?

The text of an Oracle Forms trigger is an anonymous PL/SQL block. It consists of

three sections :

• A declaration of variables, constants,cursors and exceptions which is optional.
• A section of executable statements.
• A section of exception handlers, which is optional.

Syntax: DECLARE

--- declarartive statements ( optional )

BEGIN

--- executable statements ( required )

EXCEPTION

--- exception handlers ( optional )

END;

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 ?

PL/SQL is the language used for both client-side Oracle forms applications and server-side database triggers and stored procedures and there is a PL/SQl engine in both Oracle forms Runform and the Oracle7 Server.

This means that you can take advantage of application patitioning to execute application code on either the client or the server.

Application partitioning allows you to optimize performance and resource usage by storing and executing procedures either locally or at the server, which makes the most sense for your particular application and configuration.

28. Difference between a STORED PROCEDURE and a STORED FUNCTION ?

Unlike procedures, FUNCTIONS returns a VALUE to the caller. This value is returned thro’ the RETURN command/keyword within the function.

Functions don’t use the IN, OUT | IN OUT arguments, which are available for PROCEDURES.

29. How to RUN PROCEDURES from SQL PROMPT ?

Use EXECUTE Procedure_name command.

30. How to TRAP ERRORS in procedures ?

Use SHOW_ERRORS. this will display all the errors associated with the most recently created procedural object. This command will check the VIEW_ERRORS data dictionary for the ERRORS associated with the most recent compilation attempt for that procedural object.

SHOW_ERRORS will display the LINE and COLUMN NO. for each error, as well as the text of the error message. Eg: SELECT LINE, POSITION,TEXT FROM USER_ERRORS WHERE

NAME = ‘balance_check’ AND

TYPE = PROCEDURE/FUNCTION/PACKAGE

ORDER BY SEQUENCE;

NOTE: We can use ALL_ERRORS & DBA_ERRORS to view errors.

TRAPPING ERORS: DBMS_OUTPUT package allows you to use 3 debugging functions within your package. You must set ‘SERVER OUTPUT ON’ before executing the procedure object you will be debugging.

PUT - Puts multiple o/p’s on same line.

PUT_LINE - Puts each o/p on a separate line.

NEW_LINE - Used with PUT; Signals the END of current o/p line.