Oracle Interview Questoins - Part 2

1) Replace query result null value to NA

Set NULL ‘N/A’

2)What are common pseudo columns

SYSDATE,
USER ,
UID,
CURVAL,
NEXTVAL,
ROWID,
ROWNUM

3)What is output of sign funciton

1 for positive value,
0 for Zero,
-1 for Negative value

4)How to Convert Date to string

to_char(sysdate,'yyyymmdd')

5)What is Aggregate Function

Aggregate function returns a single value, using values in a table column

6)What is a dual table

a single row table provided by oracle for selecting values and expressions

7)What are the large object types supported by oracle

Blob and Clob

8)What is call by value and call by reference in plsql procedure

In Call By value ,the copy of actual parameter is passed to the formal parameter,So any changes to the formal parameter doesn't affect the actual parameter.


In Call By reference,the address of actual parameter is passed to the formal parameter,so any changes to the formal parameter will change the actual parameter also,because both of them are pointing to the same memory location.
Here no copying is required.

9)what is the maximum size of the message that we can give in dbms_output.putline()

maximum output is determined by the size you specified in dbms_output.enable(<size>).

32767 byte

10)General Step to use SQL Loader

MyData.csv

Load.ctl
Load data
Infile ‘c:\data\mydata.csv’
Into table emp
Fields terminated by “,” optionally enclosed by ‘”’
(empno, empname,sal,deptno)


>sqlldr scott/tiger@vis
control=loader.ctl log= xxlog.log bad=xxbad.bad discard=xxdis.dsc

11)General Steps in using UTL_FILE

Declare
vsFile utl_file.file_type;
vNewLine varchar2(20);
Begin

vsFile := utl_file.fopen('path',file_name,'r');

if utl_file.is_open(vsfile)
then

loop
utl_file.get_line(vsFile,vNewLine);

if vNewLine is null
then
exit;
end if;
end loop;
end if;
end

12)Write an Associative array

DECLARE

TYPE Assoc_Array IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;
V_Assoc_Array Assoc_Array;

BEGIN

V_Assoc_Array(1) := 'somejunk';
DBMS_OUTPUT.PUT_LINE(V_Assoc_Array(1));

END;

13) How to write Pivot SQL

seqno profile u_access_report
1 A N
2 D N
3 E N
4 F N
1 B Y
2 C Y

N A D E F
Y B C

SELECT U_ACCESS_REPORTS
,MAX(THE_VAL1) THE_VAL1
,MAX(THE_VAL2) THE_VAL2
,MAX(THE_VAL3) THE_VAL3
,MAX(THE_VAL4) THE_VAL4
FROM
(
SELECT U_ACCESS_REPORTS
, DECODE(SEQNO,1, PROFILE, NULL) THE_VAL1
, DECODE(SEQNO,2, PROFILE, NULL) THE_VAL2
, DECODE(SEQNO,3, PROFILE, NULL) THE_VAL3
, DECODE(SEQNO,4, PROFILE, NULL) THE_VAL4
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY U_ACCESS_REPORTS ORDER BY PROFILE ) SEQNO
, PROFILE
, U_ACCESS_REPORTS
FROM USER_PROFILES
ORDER BY U_ACCESS_REPORTS
,PROFILE
)
)GROUP BY U_ACCESS_REPORTS

14)Print "Oracle" one below other or logic to loop a sql statement

T
--
O
R
A
C
L
E

select SUBSTR('ORACLE',rownum,1)T from all_objects where rownum<=LENGTH('ORACLE')

15)Change the current date format

alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss'

16)How to Grant Privileges

GRANT select, insert, update
ON student_grades
TO manager
WITH GRANT OPTION

17)Which aggregate functions can be applied on DATE datatype

count(); min()

18)Add a primary key to an existing table

ALTER TABLE students
ADD CONSTRAINT stud_id_pk PRIMARY KEY (student_id);

19)DateTime Datatype

TIMESTAMP
INTERVAL DAY TO SECOND

20)To know object privilages granted to user on the table

user_tab_privs

21)To know object privilages grated to the user on specific column

user_col_privs