SQL ,PLSQL , Unix

whoami The command lets you know the user name of the current shell you are at. Example: root@Microknoppix:/home/knoppix# whoami root root@Microknoppix:/home/knoppix# su knoppix knoppix@Microknoppix:~$ whoami knoppix su The su command is used to become another user during a login session. Invoked without a username, su defaults to becoming the superuser. The optional argument - may be used to provide an environment similar to what the user would expect had the user logged in directly. Example : root@Microknoppix:/home/knoppix# su knoppix password: knoppix@Microknoppix:~$ knoppix@Microknoppix:/root$ su - password: root@Microknoppix:~# knoppix@Microknoppix:/root$ su password: root@Microknoppix:~# finger The finger displays information about the system users. Example: root@Microknoppix:~# finger knoppix Login: knoppix Name: Knoppix User Directory: /home/knoppix Shell: /bin...

Dear readers, Apologies! I have been away so long since my last post.  Here you go! I will try to continue my blogging at least frequently if not everyday. Thank you! Enjoy learning!!!

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