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.
Posts
