SQL ,PLSQL , Unix

clear screen set serveroutput on accept limit_val prompt 'Enter no of Febnocci elements to be populated:' declare i number; j number; k number; inp number; begin i := 1; j := 1; dbms_output.put_line(i); dbms_output.put_line(j); inp := '&limit_val'; for l in 3..inp loop k := i +j; dbms_output.put_line(k); i := j; j := k; end loop; end; /

Supply pstree command on Unix or linux

/** Pre-requisite : Understanding on Triggers Mutating table error occurs when update action in trigger is processed and the records are fetched by a SELECT statement for validation. Because the inconsistency is on Select statement values.... **/ create or replace trigger emp_update after insert or update of sal,ename on emp for each row declare dummy number; begin if :new.ename is not null and ( :new.sal > :old.sal or :old.ename :new.ename ) then begin select sal into dummy from emp where ename= :new.ename and sal > :new.sal; exception when no_data_found then dbms_output.put_line('sdfsdfsdfdsfsdfsdf'); end; end if; end; / Select * from emp; insert into emp values('SUSIL',9000); commit; update emp se...

1. cat -b filename --Number the file output excluding blank lines 2. cat -n filename --Number the file output irrespective of blank lines it has 3. finger username --List more information about the user specified 4. ls -hl --List directory contents ( files, directories ) in human readable format ( for ex. size in KB, MB ,GB ) 5. cat -s filename --Shrinks blank lines

set serveroutput on Declare cursor cur1 is Select * from employee; c1 cur1%rowtype; begin --IMPLICIT cursor -- a Cursor with name SQL is auto-created whenever an SQL is processed update employee set emp_id='' where 1=2; --Explicit cursor --A programmer explicitly declares it and loops through it for processing open cur1; loop fetch cur1 into c1; exit when cur1%notfound; end loop; dbms_output.put_line('EXPLICIT cursor has processed '||cur1%ROWCOUNT||' row(s)'); close cur1; dbms_output.put_line('IMPLICIT cursor has processed '||SQL%ROWCOUNT||' row(s)'); end; / anonymous block completed EXPLICIT cursor has processed 1005 row(s) IMPLICIT cursor has processed 0 row(s)

--To list the privileges you have in your session SELECT * FROM session_privs; --Active users connected to Database Select Username, machine, osuser, process, program Third_Party_tool from v$session where audsid !=0 and status='ACTIVE'; --To view the role granted to you select * from dba_role_privs where grantee=user; or Select * from user_role_privs; --To list Segment statistics Select statistic_name, tablespace_name, value from v$segment_statistics; --To view SGA components statistics Select * from v$buffer_pool_statistics; Select * from v$shared_server_monitor; Select * from v$pgastat;

Question: I am writing a block of code. I am inserting the statement 'Select * from employee' in PLSQL block. Is this possible? If yes, how can you do it? If no, What is the error thrown during compilation? Answer: Answer is both YES and NO. Dont get confused. If you are to say 'YES', go with the below answer. Yes. It can be done using DYNAMIC SQL concept. You can either use DBMS_SQL or EXECUTE IMMEDIATE set serveroutput on begin execute immediate 'Select * from dual'; end; / If you are to say 'NO', justify as below. No. Because a Select statement should have INTO clause with statement structure. set serveroutput on begin Select * from dual; end; / Error report: ORA-06550: line 2, column 1: PLS-00428: an INTO clause is expected in this SELECT statement 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. *Action:

There are a number of ways to do that.. 1.When the table is not huge Select * from employee order by rowid desc; 2. Simple SQL query (To get the recent 10 records inserted in Employee table) Select * from (Select e.*, rank() over (order by rowid asc) rowid_rank from employee e) where rowid_rank > (Select count(1)-10 from employee); 3. PLSQL block of code /*** Apply your programming logic with the oracle-stored-unique-code.. See below piece of code. The below code will fetch you the recent 10 records inserted in EMPLOYEE table **** clear screen set serveroutput on DECLARE rid varchar2(30); rec employee%rowtype; v NUMBER; i NUMBER; BEGIN v:=10; SELECT MAX(rowid) INTO rid FROM employee; FOR i IN 1..v LOOP SELECT * INTO rec FROM employee e WHERE e.rowid=rid; dbms_output.put_line(rec.emp_id||','||rec.emp_name||','||rec.dept_no); SELECT MAX(rowid) INTO rid FROM employee WHERE rowid END LOOP; Exception When others then ...

--Use the functions SQLERRM and SQLCODE to print error messages in PLSQL clear screen set serveroutput on declare v1 number; begin Select (10/0) into v1 from dual; exception when others then dbms_output.put_line(' SQL Code: '||SQLCODE||' Error Message: '||SQLERRM); end; / Output: SQL Code: -1476 Error Message: ORA-01476: divisor is equal to zero

Here is a piece of code!!!! What does it do? It compiles all objects by the specified user or Schema. It is the easiest method to compile all objects for a schema. set serveroutput on begin dbms_utility.compile_schema('SUSIL'); end; / Select * from all_objects where last_ddl_time > sysdate-1;

1. A function is expected to return values and can be assigned to a variable Example : substr() is a string function which is expected to return a value Can be assigned to a variable: var1 := substr('Susil Kumar',1,5); Procedure cannot be assigned to a variable. 2. A function can be called inside a procedure. Reverse is not possible unless you use 'Execute Immediate' to generate dynamic code which is executed during runtime. 3. Global variables can only be declared in Pacakage 4. If you want to do overloading of functions or procedures, you should seal them in a package. If not, you cannot overload functions or procedures 5. Though functions allow insert statements in it, it throws error when you use the function in select statement. Procedure goes good with Insert statements. Example: SQL> create or replace function func_with_insert 2 return number 3 as 4 begin 5 insert into emp values(...

Advanced SQL IDEs such as TOAD, SQL Developer provide ways to view DDL statements for objects. There is another way you can accomplish the task using GET_DDL method defined in DBMS_METADATA package. See usage below. Usage : dbms_metadata.get_ddl('Object Type','Object Name','Owner') SELECT dbms_metadata.get_ddl('FUNCTION','GEN_NUMBERS','SUSIL') FROM dual; Output: CREATE OR REPLACE FUNCTION "SUSIL"."GEN_NUMBERS"( n IN NUMBER DEFAULT NULL) RETURN array PIPELINED AS BEGIN FOR i IN 1 .. NVL(n,999999999) LOOP pipe row(i); END LOOP; RETURN; END; SELECT dbms_metadata.get_ddl('TABLE','EMP','SUSIL') FROM dual; Output: CREATE TABLE "SUSIL"."EMP" ( "ENAME" VARCHAR2(10), "SAL" NUMBER(7,2) ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE ( INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCR...

See below snippets: Select * from dba_synonyms where synonym_name='DUAL'; OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME DB_LINK PUBLIC DUAL SYS DUAL (NULL) We know that DUAL table is accessible by users. Lets create a table named DUAL under our schema (USER). CREATE TABLE dual AS SELECT * FROM dual; Select * from dual; insert into dual values('Y'); commit; Select * from dual; DUMMY ---------- X Y The Synonym is not referred unless the Synonym name is not used by any object under the USER/SCHEMA... So , When creating tables in your schema, take a look at SYNONYMs declared as PUBLIC and try not to use them.

----Block 1 SET serveroutput ON DECLARE e_id employee.emp_id%type; BEGIN SELECT emp_id INTO e_id FROM employee WHERE emp_id = 'EEEE'; EXCEPTION WHEN no_data_found THEN dbms_output.put_line('No data found for the input Employee Id'); WHEN OTHERS THEN dbms_output.put_line('No data found for the input Employee Id--Others block!!!'); END; / Output: anonymous block completed No data found for the input Employee Id ----Block 2 SET serveroutput ON DECLARE e_id employee.emp_id%type; BEGIN SELECT emp_id INTO e_id FROM employee WHERE emp_id = 'EEEE'; EXCEPTION WHEN OTHERS THEN dbms_output.put_line('No data found for the input Employee Id--Others block!!!'); WHEN no_data_found THEN dbms_output.put_line('No data found for the input Employee Id'); END; / Error report: ORA-06550: line 6, column 1: PLS-00370: OTHERS handler must be last among the exception handlers of a block ORA-06550: line 0, colu...

--create a type with number datatype SQL> create or replace type type1 2 is table of number; 3 / Type created. --Create function for generating numbers and assign them in the type TYPE1 --Return the list SQL> create or replace function function1(n IN number) 2 return type1 3 as 4 list_1 type1:= type1(0); 5 i number; 6 begin 7 for i in 1..n 8 loop 9 list_1(i):=i; 10 list_1.extend; 11 end loop; 12 13 return list_1; 14 end; 15 / Function created. --Now table() casting would help us list the values in table-like format SQL> Select * from table(function1(10)) 2 where column_value is not null; COLUMN_VALUE ------------ 1 2 3 4 5 6 7 8 9 10 10 rows selected. SQL>

Increase employees salary by 10% update employee set salary=salary+(salary*(10/100)) where emp_id in (Select emp_id from emp_details); The above can be re-written as: update employee t1 set salary=salary+(salary*(10/100)) where t1.emp_id = (Select emp_id from emp_details where emp_id=t1.emp_id);

1. Primary keys cannot contain null values. Unique key treats NULL as a value 2. A table cannot have more than one primary key on columns. A table can have multiple unique columns 3. Primary key is used on referential integrity whereas Unique key can't be used for relational integrity Interviewers ask you the difference between primary key and not-null unique key constraint Expected answer: Primary key is used in referential integrity to define entity relationship. ( Functional dependencies )

Method 1: ( Using analytical funtion ) SQL> Select * 2 from (Select ename, 3 sal, 4 dense_rank() over ( order by sal desc ) Salary_Rank 5 from emp1) 6 where salary_rank ENAME SAL SALARY_RANK ---------- ---------- ----------- RAVI 65000 1 RAJA 32322 2 SUSIL 9000 3 Method 2: (Using rownum Label with simple SQL construct) SQL> SELECT * 2 FROM emp1 3 WHERE sal > 4 (SELECT sal 5 FROM 6 (SELECT rownum r, sal FROM 7 (SELECT DISTINCT sal FROM emp1 ORDER BY sal DESC 8 ) 9 ) 10 WHERE r =4 11 ) 12 order by 2 desc; ENAME SAL ---------- ---------- RAVI 65000 RAJA 32322 SUSIL 9000

--======================================================== -- On SQLPLUS --======================================================== SQL> set timing on SQL> select * from emp; -- This works on SQLPLUS prompt... ENAME SAL ---------- ---------- SUSIL 9000 SUSIL 9000 SUSIL 9000 SUSIL 9000 RAJA 32322 RAVI 65000 RAVI 4000 SUSIL 9000 RAJA 32322 RAVI 65000 RAVI 4000 11 rows selected. Elapsed: 00:00:00.08 --======================================================== -- The best suited option is fetching the execution plan --======================================================== explain plan for select * from emp; Select * from table(dbms_xplan.display);

I want my SQL to return only records from 101 to 200. See below options which would do the required.. Select * from (Select rownum rownumber,employee.* from employee) Where rownumber between 101 and 200; -- Using rownum label in oracle Select * from (Select employee.*, row_number() over (order by emp_id desc) Rownumber from employee) where Rownumber between 101 and 200; --Using the analytical function row_number Select * from (Select employee.*, row_number() over (order by emp_id desc) Rownumber from employee) where Rownumber between :Fromnumber and :tonumber; -- lets user decide the recordset chunk... Hope this is helpful!!!!!!

Take a look at the following SQL statements... Select * from employee; -- the table has 1000 records Insert into employee(emp_id,emp_name,salary) values('E_101','Susil Kumar',20000); Insert into employee(emp_id,emp_name,salary) values('E_102','Sukumar',20000); Insert into employee(emp_id,emp_name,salary) values('E_103','Ramuk Lisus',10000); -- We have not committed the inserts yet create table employee as Select * from employee; -- errors out with exception 'name is already used by an existing object' rollback; -- We are trying to roll back the data insert operation Select * from employee where emp_id='E_102'; -- This query returns row though we didnt explicitly commit the changes. It is because we ran Create table statement ( DDL ) which did the trick and ran a commit on this session .... Conclusion : Be careful when writing DDL statements in a session. Make sure you have rolled back the changes if need be.