SQL ,PLSQL , Unix

/** 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...