SQL ,PLSQL , Unix

1. How can I get recent 10 records out of 100 records? SELECT * FROM (Select * from employee order by rowid desc) WHERE ROWNUM < 11 2. When would you use inline view in sub query? You may use inline view when you want to cut short multi-join of tables. 3.  How to select the recently updated records from the table?   There is no provision you can use to find out the records from the table. You may use auditing tables with the help of Triggers. 4.  Display the number value in words? Use the date function to achieve the results. select to_char(to_date(999999,'j'),'jsp') from dual; But it is better you write your own translate function because it doesnt support beyond 999999.  Higher range might work in 64-bit OS.

Consider you are working on a front-end change (.net packages or Java libraries) and you are compiling the objects. Say, the code you newly introduced makes call to DB packages which have not been touched. When rebuilding the front-end changes, you will see all the Called-packages becoming INVALID.  If this is the case, you need to check the REMOTE_DEPENDENCIES_MODE parameter's value. If it is TIMESTAMP, then change it to SIGNATURE to avoid such issues. You can do this in the following way if you have permission for Alter System . ALTER SYSTEM SET REMOTE_DEPENDENCIES_MODE = SIGNATURE; Happy learning!

/** What is a cursor? A cursor is a pointer that points a set of records for processing. Private SQL area is where cursor results are placed. Implicit cursors and Explicit cursors are two types of cursors. There are a number of ways a cursor can be declared and used based on the requirement. They are as follows: 1. Simple FOR loop cursor 2. Explicit declaration and invoking it ***/ clear screen set serveroutput on size 100000 declare --Cursor Declaration cursor cur_Customer_Data is Select * from cust_table; cur_Variable cur_Customer_Data%rowtype; -- Cursor variable to hold recordset var1 cust_table%rowtype; -- variable to hold data on FOR loop cursor begin dbms_output.put_line('-------------------------------------------------------------------'); dbms_output.put_line('FOR loop started at => '||to_char(sysdate,'hh:mi:ss')); dbms_output.put_line('-------------------------------------------------------------------'); for...

/** Do the following exercises for learning SQL joins Pre-requisites: You must have CREATE privilege and some space allocated for you **/ create table temp1 ( cust_id number, cust_name varchar2(100), addr1 varchar2(40), addr2 varchar2(40), addr3 varchar2(40), addr4 varchar2(40), pincode varchar2(40), constraint cust_pk primary key ( cust_id ) ); / create table service_tbl ( cust_id number, service_no number, start_date date, end_date date, plan_type varchar2(10), foreign key (cust_id) references temp1(cust_id) ); / alter table temp1 rename to cust_table; Select * from cust_table; insert into cust_table values(100,'CUSTOMER 1','ADDRESS CODE 1','ADDRESS CODE 2','ADDRESS CODE 3','ADDRESS CODE 4',11111); insert into cust_table values(200,'CUSTOMER 2','ADDRESS CODE 1','ADDRESS CODE 2','ADDRESS CODE 3','ADDRESS CODE 4',11111); insert into cust_table values(300,'CUSTOMER 3...

/** In this section , lets learn the following. How to use a for loop to process multiple records... **/ set serveroutput on size 100000 begin --Select objects of table type that are valid under the schema Susil for var in (Select * from all_objects where object_name not like '%$%' and object_name in ('EMPLOYEE','EMP') and status='VALID' and owner='SUSIL') loop if var.object_type='TABLE' then /* Dbms_metadata package provides us a function get_ddl to get the data definition on object. For your understanding , i have just chosen two tables in this example If you remove Object_name condition in the above SQL , it will print DDL definitions for all tables under the specified schema **/ dbms_output.put_line('====================================================================='); dbms_output.put_line('Table name =>'||var.objec...

/*** Learn by examples: Write a PLSQL block to use Select , insert , delete , update statements in it Purpose : Use of DML statements in Oracle PLSQL SQL is the cursor placed in memory during DML operation in PLSQL block. rowcount is SQL's attribute to know how many rows affected of that particular DML query..... ***/ clear screen set serveroutput on DECLARE ENAME VARCHAR2(20); BEGIN SELECT EMP_NAME INTO ENAME FROM EMPLOYEE WHERE EMP_ID='E1'; --Syntax for Select statement in PLSQL is different. It has INTO clause to save values in variable DBMS_OUTPUT.PUT_LINE('EMP NAME '||ENAME); DBMS_OUTPUT.PUT_LINE('No of rows selected out of SELECT statement '||SQL%rowcount); delete from employee where dept_no >25; DBMS_OUTPUT.PUT_LINE('No of rows deleted out of DELETE statement '||SQL%rowcount); update employee set dept_no = 10 where dept_no DBMS_OUTPUT.PUT_...

Pre-requisite : Basic SQL Knowledge in Oracle and programming knowledge in any language Browse my blog to read the overview of PLSQL if you need it. Anonymous block ............. ........... This is where you declare Variables that you are going to use it in PLSQL block ............ Data types are chosen based on requirement. For example, if you want to process strings you go for data types of characters and for numeric operations Int, float , double or number. Begins ..... ......... ......... .......... This is where you implement programming logic and get the desired outcome. .......... .......... Ends..... We call it anonymous block because we don't name it or reference it. (Unlike Package, Stored procedure,& Other objects) Learn from examples Example 1: declare var1_num number; --Declaring number type variable var2_str varchar2; begin var1_num := 10; --Assigning 'Number' Value to the variable var2_str := 'I am a string value...

PL/SQL is Oracle's procedural language extension to SQL. It provides a server-side, stored procedural language that is easy-to-use, seamless with SQL, robust, portable, and secure. The PL/SQL compiler and interpreter are embedded in Oracle Database, providing developers with a consistent and leveraged development model on both the client and the server side. In addition, PL/SQL stored subprograms can be called from Oracle clients. PL/SQL enables you to mix SQL statements with procedural constructs. With PL/SQL, you can create and run PL/SQL program units such as procedures, functions, and packages. PL/SQL program units generally are categorized as anonymous blocks, stored subprograms, and packages. The basic units (procedures, functions, and anonymous blocks) that make up a PL/SQL program can be nested inside one another. You can place declarations close to where they are used, such as inside a large subprogram. The declarations are local to the block and cease to exist when ...

Editor's column There were days when Rooms-sized processors used for processing data. IT boomed up at the start of 21st Century and so many innumerable inventions made Computer world and few among them are still up in the markets for thier better implemented programming Alogrithms which couples with computer hardware and software ( OS ). Textpads or notepads were the repository of data and programmers had to be Experts to do data manipulation. In other words, Programmers had to use thier own logic to do insert , delete, update in data source. For instance, Consider the following: file1.txt: 1 I am line number 1 2 I am line number 2 3 I am line number 3 4 I am line number 4 5 I am line number 5 6 I am line number 6 7 I am line number 7 8 I am line number 8 9 I am line number 9 10 I am line number 10 To programmatically delete 8 th record , developer had to use the following logic with OLD file handling methods in 3rd generation language. 1. Open file1.txt, an...

/** If exception is unhandled at inner blocks, the control is given to outside blocks... see below code **/

Select length('String length') str_LENGTH from dual; STR_LENGTH ---------------------- 13 --Replace string with Select REPLACE('REPLACE String ','REPLACE','Replaced') str_REPLACE from dual; STR_REPLACE ---------------- Replaced String --default replaced value will be null if param2 is not passed Select REPLACE('REPLACE String ','REPLACE') str_REPLACE from dual; STR_REPLACE ----------- String --Print a portion of string Select SUBSTR('Sub string',1,3) str_LENGTH from dual; STR_LENGTH ---------- Sub --INSTR function Select instr('Find first occurence of a char in the string','a') str_INSTR from dual; STR_INSTR ---------------------- 25 --TO_CHAR function is mostly used with dates to convert them into desired format Select to_char(sysdate,'dd-Mon-yyyy hh:mi:ss') str_TOCHAR from dual; STR_TOCHAR -------------------- 01-F...

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.