Interview Questions IV

Differentiate between TRUNCATE and DELETE

RUNCATE	DELETE
It is a DDL statement	It is a DML statement
It is a one way trip, cannot ROLLBACK	We can Rollback
Doesn't have selective features (where clause)	Has
Doesn't fire database triggers	Does
It requires disabling of referential constraints.	Does not require

Display the number value in Words?

SQL> select sal, (to_char(to_date(sal,'j'), 'jsp'))

from emp;

the output like,

SAL (TO_CHAR(TO_DATE(SAL,'J'),'JSP'))

--------- -----------------------------------------------------

800 eight hundred

1600 one thousand six hundred

1250 one thousand two hundred fifty

If you want to add some text like, Rs. Three Thousand only.

SQL> select sal "Salary ",

(' Rs. '|| (to_char(to_date(sal,'j'), 'Jsp'))|| ' only.'))

"Sal in Words" from emp

/

Salary Sal in Words

------- ------------------------------------------------------

800 Rs. Eight Hundred only.

1600 Rs. One Thousand Six Hundred only.

1250 Rs. One Thousand Two Hundred Fifty only.

How to get the 25th row of a table

select * from emp where rowid=(select max(rowid) from emp where rownum<26)

OR

elect * from Emp where rownum < 26

minus

select * from Emp where rownum<25

Why equality condition does'nt work with rownum, i'm giving my explanation here?

the query
select * from emp where rownum=25;

it fetches first record makes it rownum 1 as it does'nt match the where criteria, the record discarded

it fetches 2nd record makes it rownum 1 again
that is why rownum=x does'nt work

How do we display the column values of a table?

DECLARE

CURSOR cr_data

IS

SELECT ROWID, a.*

FROM fnd_user a where rownum < 10;

l_table_name VARCHAR2(2000) := 'FND_USER';

-- IMP--This table name should be same as your from table in the above cursor

l_value VARCHAR2 (2000);

l_str VARCHAR2 (2000);

CURSOR column_names (p_table_name VARCHAR2)

IS

SELECT *

FROM all_tab_columns

WHERE table_name = p_table_name;

-- You can use order by clause here if you want.

BEGIN

FOR cr_rec IN cr_data

LOOP

-- We should pass the same Table Name

FOR cr_columc_rec IN column_names (l_table_name)

LOOP

l_str :=

'Select '

|| cr_columc_rec.column_name

|| ' from '||l_table_name||' where rowid = '||chr(39)

|| cr_rec.ROWID||chr(39);

DBMS_OUTPUT.put_line ('Query is ' || l_str);

EXECUTE IMMEDIATE l_str

INTO l_value;

DBMS_OUTPUT.put_line ( 'Column is '

|| cr_columc_rec.column_name

|| ' and Value is '

|| l_value

);

END LOOP;

END LOOP;

END;

Tell me the difference between instead of trigger, database trigger, and schema trigger?

Instead of trigger : A view cannot be updated , so if the user tries to update a view, then this trigger can be used , where we can write the code so that the data will be updated in the table, from which the view was created. Database trigger : this trigger will be fired when a database event ( dml operation ) occurs in the database table, like insert , update or delete. System triggers : this trigger will fire for database events like dtartup / shutdown of the server, logon / logoff of the user, and server errors ... and also for the ddl events, like alter, drop, truncate etc.

What is the diff between %Rowtype and %type?

---

%Rowtype means associating a single variable to a entire row.(It is one way of Declaring a composite plsql datatype "RECORD")

%type means associating a single variable to a particular column in table.

both %Rowtype and %type declarations are known as Anchored Declarations in plsql .

Wat is difference between Cursor and Ref Cursor ?

· Cursor is static one and ref cursor is dynamic with return type

Example for cursor:

declare

cursor c1 is select * from emp;

begin

for r1 in c1 loop

dbms_output.put_line(r1.empno||' '||r1.ename);

end loop;

end;

/

Example for ref cursor

CREATE OR REPLACE package emp_data is

-- Author : RPSINGH

-- Created : 8/17/2006 12:54:03 AM

-- Purpose : displaying the data from different tables

-- Public type declarations

type my_cur is ref cursor;

-- Public constant declarations

-- Public variable declarations

-- Public function and procedure declarations

procedure tabledata(tname in varchar2, v_cur out my_cur);

end emp_data;

/

CREATE OR REPLACE PACKAGE BODY EMP_DATA IS

procedure tabledata(tname in varchar2,v_cur out my_cur) is

begin

OPEN V_CUR FOR

'SELECT * FROM '||TNAME;

end tabledata;

end emp_data;

/

Now to use this code:

declare

v_cur employees%rowtype;

C_CUR EMP_DATA.MY_CUR;

begin

emp_data.tabledata('EMPLOYEES', C_CUR);

LOOP

FETCH C_CUR INTO V_CUR;

EXIT WHEN C_CUR%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(V_CUR.FIRST_NAME||' '||V_CUR.LAST_NAME);

END LOOP;

END;

/

Cursor is a structure which points to a memory locations

While Ref-cursor is a data structure which point to a object which intern points to Memory locations.

Advantage of having Ref-cursor is that we can pass dynamically the Ref-cursor as a parameter to a procedure.

Can we create a table using with Procedure or Function?

DECLARE

BEGIN

EXECUTE IMMEDIATE 'create table employee(empno number(3),ename varchar2(10))';
END;

What is the Mutating trigger error?

Mutating error:- occurs when row level trigger accesses same table on which it is based while executing or the table currently being modified by the DML statements.