Best Oracle pl/sql interview questions
Source: www.oraclepassport.com

1. Is it possible to delete a column in table with data in Oracle?

Yes, possible

2 . Is it possible to delete a column in table without data?

Yes

3. Is it possible to delete a column in table with indexes in Oracle? (Source: www.oraclepassport.com)

We can delete a column with indexes. But if there is a Primary or foreign key present make sure, foreign keys are deleted first and then column with Primary key.

4. While doing an ascending order sort on a column having NULL values, where does the NULLs show up in the result set? At the beginning or at the end?

Ascending order sort - NULLs come last because Oracle treats NULLs are the largest possible values

Descending order sort - NULLs come first

5. How to make NULLs come last in descending order sort? Add NULLS LAST to the order by desc clause

Eg: select col1 from table1 order by col1 desc NULLS LAST

6. Optimal way to insert data from one table to another across databases?

There are many ways in which we could copy data across DBs. All depends on the Table/Data size, Type, Schema privileges etc. The programmer needs to identify, which way he needs to do it.


1) DB Links - create table tablename select * from ...(Very Less data)
2) Insert Scripts (Less data)
3) Flat files + SQL Loader (or UTL_FILE) (Medium data)
4) Export + Import (Any size of Data)

7. UTL_FILE - Oracle UTL_FILE Package - Read write external files
Source: http://oraclepassport.com/forums/index.php

The Oracle utl_file package allows Oracle SQL and PL/SQL to read and write directly from flat files on the server.

Writing custom messages to the Oracle alert log requires the following steps:

1 - Locate the background dump directory (the location of the alert log).
2 - Set the utl_file_dir initialization parameter.
3 - Execute utl_file.fopen to open the file for write access.
4 - Use dbms_output.put_line to write the custom message to the alert log.
5 - Execute utl_file.fclose to close the file

FCLOSE Procedure Closes a file

FCLOSE_ALL Procedure Closes all open file handles

FCOPY Procedure Copies a contiguous portion of a file to a newly created file

FFLUSH Procedure Physically writes all pending output to a file

FGETATTR Procedure Reads and returns the attributes of a disk file

FGETPOS Function Returns the current relative offset position within a file, in bytes

FOPEN Function Opens a file for input or output

FOPEN_NCHAR Function Opens a file in Unicode for input or output

FREMOVE Procedure Deletes a disk file, assuming that you have sufficient privileges

FRENAME Procedure Renames an existing file to a new name, similar to the UNIX mv function

FSEEK Procedure Adjusts the file pointer forward or backward within the file by the number of bytes specified

GET_LINE Procedure Reads text from an open file

GET_LINE_NCHAR Reads text in Unicode from an open file

GET_RAW Function Reads a RAW string value from a file and adjusts the file pointer ahead by the number of bytes read

IS_OPEN Function Determines if a file handle refers to an open file

NEW_LINE Procedure Writes one or more operating system-specific line terminators to a file

PUT Procedure Writes a string to a file

PUT_LINE Procedure Writes a line to a file, and so appends an operating system-specific line terminator

PUT_LINE_NCHAR Writes a Unicode line to a file

PUT_NCHAR Procedure Writes a Unicode string to a file

PUTF Procedure A PUT procedure with formatting

PUTF_NCHAR Procedure A PUT_NCHAR procedure with formatting, and writes a Unicode string to a file, with formatting

PUT_RAW Function Accepts as input a RAW data value and writes the value to the output buffer

8. How to Kill a Session in Oracle?
Source: www.oraclepassport.com

First find the session id and serial number from v$session. Make sure it's your session only

Use this query.


SELECT sid,
serial#,
osuser,
program
FROM v$session


Now to Kill the session :

ALTER SYSTEM KILL SESSION 'sid,serial#'

FORCE KILL: by adding the IMMEDIATE keyword:

ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;