1) Q:   How can I view constraints on a table 

A:   

SELECT constraint_name, table_name, r_owner, r_constraint_name

FROM all_constraints

WHERE table_name = 'table_name' and owner = 'owner_name';

Here all_constraints is a view in the data dictionary which has the above columns.There is another view called ALL_CONS_COLUMNS which presents information about the columns that are incorporated into constraints on a table. 

2) Q: How can i change the password for my username? 

A:   

  You can change you

            alter user user_name idenified by new_password;

  so, if you are logged in as scott and want to change your password to lion, you can use

            alter user scott identified by lion; 

3) Q:  How can I select every N th row from a table? 

A:   

  One can easily select all even, odd, or Nth rows from a table using SQL queries like this:

  Method 1: Using a subquery

            SELECT *

            FROM emp

            WHERE (ROWID,0) IN (SELECT ROWID, MOD(ROWNUM,4)

            FROM emp);

  Method 2: Use dynamic views (available from Oracle7.2):

            SELECT *

            FROM ( SELECT rownum rn, empno, ename

            FROM emp

            ) temp

            WHERE MOD(temp.ROWNUM,4) = 0;

Please note, there is no explicit row order in a relational database. However, these queries are quite fun and may even help in the odd situation. 

4) Q: How do I delete duplicate records in a table 

A:   

            DELETE FROM emp e

            WHERE e.row_id <> (SELECT ee.rowid

            FROM emp ee

            WHERE ee.emp_no = e.emp_no); 

5) Q:  How do I delete duplicate rows when primary keys of the table cannot be identified? 

A:   

  Create a temporary table by executing the following statement . This will eliminate the duplicates. Drop the original shippers table and rename the temp table to original tablename.

CREATE TABLE shippers_temp

AS SELECT DISTINCT *

FROM shippers

6) Q: How to select distinct first field data but sorting on second field data ?

A:

select distinct userid ,ass_time from shankar where rowid in (

select min(rowid) from shankar group by userid) order by 2

/

or

select distinct userid,to_char(ASS_time,'YYYY-MM-DD HH24:MI:SS')

from shankar SO

where ass_time > all

(select ass_time from shankar SI where SO.ASS_TIME != SI.ASS_TIME AND SO.userID=SI.userID ) order by 2;

7) Q: How to select 3 different count values from the same table.

A:

select a.* , b.* , c.* 

from (select count(*) from exam) a, (select count(*) from exam where result='F') b,

     (select count(*) from exam where result='S') c;

8) Q: How to print a String value which is > than 256 chars ?

A:

dbms_output.put_line(substr(<sql_stmt>,<start_vindex>,<num_of_chars>))

eg:- dbms_output.put_line(substr(sql_stmt,1,200));

9) Q: How to use Package and Cursors ?

A:

CREATE OR REPLACE PACKAGE srini AS

            TYPE cursor_type IS REF CURSOR; --type def cursor

            PROCEDURE Check_srini                  --Procedure signature

                  (

                  in_name       IN VARCHAR2,

                  in_num IN NUMBER,

                  out_refcursor OUT cursor_type

                  ) ;

            FUNCTION my_fun(in_num IN NUMBER) RETURN NUMBER; --Function signature

END srini;

/

CREATE OR REPLACE PACKAGE BODY srini AS

            --*************** Function Starts

            FUNCTION my_fun(in_num IN NUMBER) RETURN NUMBER as

                        fact number := 1;

            BEGIN

                        FOR i IN 1 .. in_num LOOP

                                    fact := fact * i;

                        END LOOP;

                        RETURN fact;

            END;

            --*************** Procedure Starts

            PROCEDURE Check_srini

                        ( 

                        in_name IN VARCHAR2,

                        in_num IN NUMBER,

                    out_refcursor OUT cursor_type

                    ) AS

            --Variable declarations

            empno number;

            --cursor to use within procedure

            CURSOR tree_cur(in_name VARCHAR2) IS SELECT sno

                                                   FROM tree

                                               WHERE sname = in_name;

            BEGIN

             OPEN tree_cur(in_name);

             FETCH tree_cur INTO check_srini.empno;

             DBMS_OUTPUT.PUT_LINE('Emp num ==> ' || check_srini.empno);

             IF tree_cur%NOTFOUND THEN

                        DBMS_OUTPUT.PUT_LINE('no rows selected');

             END IF;

             CLOSE tree_cur;

             DBMS_OUTPUT.PUT_LINE('Factorial ===> ' || my_fun(in_num));

             --DBMS_OUTPUT.PUT_LINE('Select sno, sname from Tree where sname = '''||in_name||'''');

             OPEN out_refcursor FOR 'Select * from Tree where sname = '''||in_name||'''';

             --********************************************************

             --CLOSE out_refcursor; u should not close the out cursors

             --********************************************************

            END Check_srini;

END srini;

/

10) Q: How to use Implicit cursor ?

A:

create or replace procedure curcheck(name in varchar2) as

CURSOR c1(q Varchar2) IS select sname from tree where sname = q;

begin

            FOR emp_rec IN c1(name) LOOP

                        dbms_output.put_line(emp_rec.sname);           

            END LOOP;

end;

/

11) Q: How to get the  tablespace infn ?

A:

SELECT TS#, NAME FROM SYS.TS$;   //to get table space number

select * from SYS.DBA_FREE_SPACE_COALESCED;

12) Q:  How to get the owner of the table

A:

select owner,table_name from all_tables;

13) Q: Can one rename a database user (schema)?

A : No, this is listed as Enhancement Request 158508. Workaround:

Do a user-level export of user A

create new user B

import system/manager fromuser=A touser=B

drop user A

14) Q: Can one rename a tablespace?

A: No, this is listed as Enhancement Request 148742. Workaround:

Export all of the objects from the tablespace

Drop the tablespace including contents

Recreate the tablespace

Import the objects

15) Q: Can one resize tablespaces and data files?

A:

One can manually increase or decrease the size of a datafile from Oracle 7.2 using the

            ALTER DATABASE DATAFILE 'filename2' RESIZE 100M;

command.

Because you can change the sizes of datafiles, you can add more space to your database without adding more datafiles. This is beneficial if you are concerned about reaching the maximum number of datafiles allowed in your database. Manually reducing the sizes of datafiles allows you to reclaim unused space in the database. This is useful for correcting errors in estimations of space requirements.

Also, datafiles can be allowed to automatically extend if more space is required. Look at the following command:

        CREATE TABLESPACE pcs_data_ts

                DATAFILE 'c:\ora_apps\pcs\pcsdata1.dbf' SIZE 3M

                        AUTOEXTEND ON NEXT 1M  MAXSIZE UNLIMITED

                DEFAULT STORAGE (  INITIAL 10240

                                   NEXT 10240

                                   MINEXTENTS 1

                                   MAXEXTENTS UNLIMITED

                                   PCTINCREASE 0)

                ONLINE

                PERMANENT;

16) Q: How to alter the index if it is extent exceeded its limit ?

A: Alter index <name of the index> rebuild;

17)  Preocedure to get the difference between 2 given dates

A:

create or replace procedure date_test( date1 date, date2 date) is

DateLow date;

DateHigh date;

day1 date;

day2 date;

Fraction1 number;

Fraction2 number;

days number;

HHDec number; -- Hours (decimal)

HH number; -- Hours (integer)

MMDec number; -- Minutes (decimal)

MM number; -- Minutes (integer)

SSDec number; -- Seconds (deminal)

SS number; -- Seconds (integrer)

begin

-- Put the dates in order (important when dealing with the fractions

-- of days left over from the subtraction - see later).

DateLow := least( Date1, Date2 );

DateHigh := greatest( Date1, Date2 );

-- Find the midnight preceding date1

Day1 := Trunc( DateLow );

-- Find the midnight preceding date2

Day2 := Trunc( DateHigh );

-- Calculate the days between the two dates (subtracting two DATEs gives a NUMBER)

-- A function to calculate the number of days between the two dates would return

-- this value.

Days := Day2 - Day1 - 1;

-- Now, what about the fractions of days left?

Fraction1 := Date1 - Day1;

Fraction2 := Date2 - Day2;

-- Calculate the total HH (and fractions of HH) from the

-- fractions of the days left over

HHDec := ( ( 1 - Fraction1 ) + Fraction2 ) * 24;

-- This could be >1 day

if HHDec >= 24 then

HHDec := HHDec - 24;

Days := Days + 1;

end if;

HH := floor( HHDec );

-- Here, you have the correct number of days and HH between the two dates.

-- Now obtain the MM from the fractions of HH.

MMDec := ( HHDec - HH ) * 60;

MM := floor( MMDec );

-- And the SS...

SSDec := ( MMDec - MM ) * 60;

SS := floor( SSDec );

-- You could carry on from here to get the fractions of a second......

--

dbms_output.put_line( to_char( days ) || ' Days, '

|| to_char( HH ) || ' Hours, '

|| to_char( MM ) || ' Minutes, '

|| to_char( SS ) || ' Seconds. ' );

end;

/

18) Procedure write xls data into doc

A:

--This should present in the Server's init.ora file ************ utl_file_dir = 'f:\'

-- Which is located in oracle\admin\cyber\pfile\init.ora

CREATE OR REPLACE PROCEDURE PrcCreateXls(p_err_cd  out integer,

                                                       p_err_msg out VARCHAR2) IS

CURSOR cur IS SELECT * from tree;

v_filehandle                        UTL_FILE.FILE_TYPE;

v_newline                           VARCHAR2(1000);

v_new1                  VARCHAR2(100);

v_new2                              VARCHAR2(100);

BEGIN

      V_filehandle  := UTL_FILE.FOPEN('f:\','chk.xls','W');

      DBMS_OUTPUT.PUT_LINE('File Opening chk.xls');

  --    V_filehandle  := UTL_FILE.FOPEN('f:\','rep.doc','W');

        v_new1 :=   '                          USER   REPORT';

        v_new2 :=   '              ----------------------';

      DBMS_OUTPUT.PUT_LINE('Wrinting ' || v_new1 || ' ' || v_new2);

      UTL_FILE.PUT_LINE(V_filehandle,v_new1);

        UTL_FILE.PUT_LINE(V_filehandle,v_new2);

        FOR i in cur LOOP

         DBMS_OUTPUT.PUT_LINE('Inseide loop ');

           v_newline := i.sno||'     '||i.sname||'   '||i.mgr_no||'   '||i.sini;       

         --v_newline := rpad(i.sno,30,'')||rpad(i.sname,20,'')||rpad(i.mgr_no,15,'')||i.sini;

         UTL_FILE.PUT_LINE(V_filehandle,v_newline);

      END LOOP;

EXCEPTION

       WHEN UTL_FILE.INVALID_OPERATION THEN

        UTL_FILE.FCLOSE(V_filehandle) ;

        p_err_cd   := ''    ;

        p_err_msg  := 'Invalid Operation' ;

    --    ROLLBACK TO starttrans;

   WHEN UTL_FILE.INVALID_FILEHANDLE THEN

        UTL_FILE.FCLOSE(V_filehandle) ;

        p_err_cd  := ''    ;

        p_err_msg := 'Invalid Filehandle' ;

     --   ROLLBACK TO starttrans;      

   WHEN UTL_FILE.WRITE_ERROR THEN

        UTL_FILE.FCLOSE(V_filehandle) ;

        p_err_cd  := ''   ;

        p_err_msg := 'Write Error'   ;

    --    ROLLBACK TO starttrans;      

   WHEN UTL_FILE.INVALID_PATH THEN

        UTL_FILE.FCLOSE(V_filehandle) ;

        p_err_cd  := ''  ;

        p_err_msg := 'Invalid Path'  ;

    --    ROLLBACK TO starttrans;       

   WHEN UTL_FILE.INVALID_MODE THEN

        UTL_FILE.FCLOSE(V_filehandle) ;

        p_err_cd  := ''   ;

        p_err_msg := 'Invalid Mode'  ;

     --   ROLLBACK TO starttrans;      

   WHEN OTHERS THEN

     --   ROLLBACK TO starttrans; 

        UTL_FILE.FCLOSE(V_filehandle);

        p_err_cd  := sqlcode  ;

       -- p_err_msg := 'SQL POINT ='||to_char(ln_sql_point)

--                                  ||','||p_err_cd||'-'||substr(sqlerrm,1,150)  ;

        p_err_msg := SQLERRM;

     --   dbms_output.put_line('count '||vcount);

END;

/

19) Exception handling

A:

CREATE OR REPLACE PROCEDURE excptn(in_no IN NUMBER) AS

eee EXCEPTION;

BEGIN

 DBMS_OUTPUT.PUT_LINE('Entered Number : ' || in_no);

 IF (in_no = 10) THEN

  --raise_application_error('-20799','Invalid Number : ' || in_no);

  raise eee;

 ELSE

  DBMS_OUTPUT.PUT_LINE('Entered Number :++ ' || (in_no+1));

 END IF;

 EXCEPTION

  WHEN eee THEN

               DBMS_OUTPUT.PUT_LINE('Exception !!! Invalid Number : in eee ' || in_no);

END excptn;

/

Help-15

--File handling

--exec FILEIO('e:\test','f1.txt',:a,:b);

CREATE OR REPLACE PROCEDURE FILEIO

      (

      P_FileDir                      IN     VARCHAR2,

      pfilename                      IN     VARCHAR2,

      p_err_cd                      OUT    VARCHAR2,

       p_err_msg                   OUT    VARCHAR2

      )

      IS

      V_filehandle                 UTL_FILE.FILE_TYPE;

      v_NEWLINE                           VARCHAR2(1000);

      vtime1                          VARCHAR2(4);

BEGIN

     dbms_output.put_line('opens here');

     V_filehandle := UTL_FILE.FOPEN(P_FileDir,pfilename,'R');

     vtime1       := TO_CHAR(SYSDATE,'hh24mi') + 0100;

     dbms_output .put_line('iteration here');

     LOOP

      IF ( TO_CHAR(SYSDATE,'hh24mi')  <> vtime1 ) THEN     

                   BEGIN

                              UTL_FILE.GET_LINE(V_filehandle,V_newline);

                                                     DBMS_OUTPUT.PUT_LINE('line '||V_NEWLINE);   

                              EXCEPTION

                                          WHEN NO_DATA_FOUND THEN

                                          EXIT;

                   END;

        ELSIF (TO_CHAR(SYSDATE,'hh24mi') = vtime1) THEN

                     DBMS_OUTPUT.PUT_LINE('timeout');

       END IF; 

    END LOOP;

    UTL_FILE.FCLOSE(V_filehandle);

   EXCEPTION

     WHEN UTL_FILE.INVALID_OPERATION THEN

        UTL_FILE.FCLOSE(V_filehandle) ;

        p_err_cd   := ''    ;

        p_err_msg  := 'Invalid Operation' ;

        RETURN;

     WHEN UTL_FILE.INVALID_FILEHANDLE THEN

        UTL_FILE.FCLOSE(V_filehandle) ;

        p_err_cd  := ''    ;

        p_err_msg := 'Invalid Filehandle' ;

        RETURN;

    WHEN UTL_FILE.WRITE_ERROR THEN

        UTL_FILE.FCLOSE(V_filehandle) ;

        p_err_cd  := ''   ;

        p_err_msg := 'Write Error'   ;

        RETURN;

    WHEN UTL_FILE.INVALID_PATH THEN

        UTL_FILE.FCLOSE(V_filehandle) ;

        p_err_cd  := ''  ;

        p_err_msg := 'Invalid Path'  ;

        RETURN;

    WHEN UTL_FILE.INVALID_MODE THEN

        UTL_FILE.FCLOSE(V_filehandle) ;

        p_err_cd  := ''   ;

        p_err_msg := 'Invalid Mode'  ;

        RETURN;

 /*  WHEN OTHERS THEN

        UTL_FILE.FCLOSE(V_filehandle);

        p_err_cd  := sqlcode  ;

        p_err_msg := 'SQL POINT ='||to_char(ln_sql_point)

                                     ||','||p_err_cd||'-'||substr(sqlerrm,1,150)  ;

        RETURN; */

END;

/

20) Q: How to send a mail from Oracle ?

A:

CREATE OR REPLACE PROCEDURE SEND_MAIL

   (

   msg_from      varchar2,

   msg_to           varchar2,

   msg_subject varchar2,

   msg_text       varchar2

   )

 IS

   c  utl_tcp.connection;

   rc integer;

 BEGIN

   --c := utl_tcp.open_connection('192.20.200.222', 25);       -- open the SMTP port 25 on local machine

   --c := utl_tcp.open_connection('cob353.dn.net', 25);   -- out going port address

   c := utl_tcp.open_connection('10.0.20.5', 25);   --incomming port address

    dbms_output.put_line(utl_tcp.get_line(c, TRUE));

   rc := utl_tcp.write_line(c, 'HELO localhost');

       dbms_output.put_line(utl_tcp.get_line(c, TRUE));

   rc := utl_tcp.write_line(c, 'MAIL FROM: '||msg_from);

       dbms_output.put_line(utl_tcp.get_line(c, TRUE));

   rc := utl_tcp.write_line(c, 'RCPT TO: '||msg_to);

       dbms_output.put_line(utl_tcp.get_line(c, TRUE));

   rc := utl_tcp.write_line(c, 'DATA');                 -- Start message body

       dbms_output.put_line(utl_tcp.get_line(c, TRUE));

   rc := utl_tcp.write_line(c, 'Subject: '||msg_subject);

   rc := utl_tcp.write_line(c, '');

   rc := utl_tcp.write_line(c, msg_text);

   rc := utl_tcp.write_line(c, '.');                    -- End of message body

       dbms_output.put_line(utl_tcp.get_line(c, TRUE));

   rc := utl_tcp.write_line(c, 'QUIT');

       dbms_output.put_line(utl_tcp.get_line(c, TRUE));

   utl_tcp.close_connection(c) ;                         -- Close the connection

   dbms_output.put_line('Your Message Sent Successfully......');

 EXCEPTION

   when others then

        raise_application_error(-20000,

                                'Unable to send e-mail message from pl/sql');

 END;

/

--exec SEND_MAIL('[email protected] ','[email protected]','E-Mail','Srini be Cool')  

21) Procedure to get tokens from a given String

create or replace PROCEDURE read_Value(v in varchar)

as

v_custlength number(30);

cnt number(1);

cust_char varchar2(1);

cust_val varchar2(15);

d number(3);

begin

--truncate_table('temp_vndr');

if rtrim(ltrim(v)) is not null then

--put i/p customer string into pl/sql table if customer parameter not --null

v_custlength := length(v);

for cnt in 0..v_custlength loop

cust_char := substr(v,cnt+1,1);

if cust_char != ',' then

      cust_val := cust_val||cust_char;

else

      dbms_output.put_line(cust_val);

cust_val := null;

end if;

end loop;

end if;

commit;

end;

/

22) --To select  top 3 high paid employees

select * from (select no, name, max(sal)

from tsal

group by no, name, sal order by sal desc)

where rownum <= 3

and a package  for the same

CREATE OR REPLACE PACKAGE slry AS

TYPE out_cur IS REF CURSOR;

PROCEDURE salary(out_value OUT out_cur);

END slry;

/

CREATE OR REPLACE PACKAGE BODY slry AS

PROCEDURE salary(out_value OUT out_cur) AS

CURSOR sa IS SELECT sal FROM tsal GROUP BY sal ORDER BY sal DESC;

topsal VARCHAR2(100) := '(';

cnt NUMBER := 3;

BEGIN

 FOR i IN sa LOOP

  topsal := topsal ||i.sal || ',';

  cnt := cnt - 1;

  EXIT WHEN cnt=0;

 END LOOP;

 topsal := SUBSTR(topsal,1,INSTR(topsal,',',-1,1)-1) || ')';

 DBMS_OUTPUT.PUT_LINE('Vlaue =' || topsal);

 OPEN out_value FOR 'SELECT * FROM (SELECT no, name, sal

     FROM tsal

     GROUP BY no, name, sal ORDER BY sal DESC)

     WHERE sal IN ' || topsal;

END salary;

END slry;

/

--exec slry.salary(:cr);

23) --Simple Triggers

CREATE OR REPLACE TRIGGER Psal

BEFORE DELETE OR INSERT OR UPDATE ON Emps

FOR EACH ROW

WHEN (new.id > 0)

BEGIN

    sal_diff  := :new.sal  - :old.sal;

    dbms_output.put('Old salary: ' || :old.sal);

    dbms_output.put('  New salary: ' || :new.sal);

    dbms_output.put_line('  Difference ' || sal_diff);

END;

/

create or replace trigger copycat

before insert on emps

for each row

begin

      insert into cpemp values (:new.id,:new.name,:new.desn,:new.sal);

end;

/

24)  --How to use Java Code in Oracle

//#################################

//Using Java classes in Oracle

//#################################

CREATE OR REPLACE AND COMPILE JAVA SOURCE

NAMED "StrTokenize"

AS

public class Check {

      public static int test(int num) {

                  return   num<<2;

      }

}

/

CREATE OR REPLACE FUNCTION Check_int (

input_str_ IN VARCHAR2,

delimiter_ IN VARCHAR2,

index_ IN NUMBER) RETURN NUMBER

AS LANGUAGE JAVA

NAME 'Check.test(int) return int';

/

select Check_int(5) MultipliedBy2 from dual;

==============================

SET SERVEROUT ON

CREATE OR REPLACE AND COMPILE JAVA SOURCE

NAMED "StrTokenize"

AS

import java.util.Vector;

import java.util.StringTokenizer;

public class StrTokenize {

public static String tokenize(String str, String delim, int indx) {

StringTokenizer st = new StringTokenizer(str, delim);

if ((indx > st.countTokens()) || (indx <= 0)) {

return null;

}

Vector vt = new Vector();

while (st.hasMoreTokens()) {

String sVal = st.nextToken();

vt.addElement(new String(sVal));

}

// Use - 1 as first element in a vector is 0

return vt.elementAt(indx - 1).toString();

}

}

/

CREATE OR REPLACE FUNCTION Str_Tokenize (

input_str_ IN VARCHAR2,

delimiter_ IN VARCHAR2,

index_ IN NUMBER) RETURN VARCHAR2

AS LANGUAGE JAVA

NAME 'StrTokenize.tokenize(java.lang.String, java.lang.String, int)

return java.lang.String';

/

SHOW ERRORS

/*

Usage: Uncomment this section to test

First token is always 1...

-- get fifth token

select Str_Tokenize('This/is/the/delimited/input/string/from/a/file', '/', 5)

Parsed from dual;

PARSED

----------------------------------

input

OR

SET SERVEROUT ON

-- Enumerate all tokens

DECLARE

string_ VARCHAR2(100) := 'This/is/the/delimited/input/string/from/a/file';

delim_ VARCHAR2(1) := '/';

index_ NUMBER := 1;

retval_ VARCHAR2(50);

BEGIN

LOOP

retval_ := Str_Tokenize(string_, delim_, index_);

IF (retval_ IS NULL) THEN

EXIT;

END IF;

DBMS_OUTPUT.PUT_LINE('Token ' || index_ || ' = ' || retval_);

index_ := index_ + 1;

END LOOP;

END;

/

Token 1 = This

Token 2 = is

Token 3 = the

Token 4 = delimited

Token 5 = input

Token 6 = string

Token 7 = from

Token 8 = a

Token 9 = file

PL/SQL procedure successfully completed.

*/

25) //Oracle objects

create or replace type obj is object(

n varchar2(10),

a number

)

create table obtab (

nn obj

)

insert into obtab values(obj('srini',10))

26) To set transaction

u have to commit / rollback to set the new transaction type

set transaction read only;

set transaction read { only | write };

set transaction use rollback segment <rb_name>

27) Table Space

create tablespace [name] datafile '[filepath]'

size [filesize]M default storage (initial 100k next 100k pctincrease 5);

28) //To get a snapshot of the current space availability of your Oracle7 database

select tablespace_name, sum(bytes)/1024/1024

from dba_free_space

group by tablespace_name;

//If the SYSTEM tablespace is less that 60MB,

//add another datafile to the tablespace. Do this with the SQL statement:

Alter tablespace system add datafile '[filepath]' size [filesize]M;

eg :-

alter tablespace system add datafile 'c:\orawin95\database\sys2orcl.ora' size 100M

29) //To create user

create user [name] identified by [pwd]

default tablespace [table_tablespace]

temporary tablespace temporary_data;

eg:-

create user rep_owner identified by demo

default tablespace des2 temporary tablespace temporary_data

//grant rights to account

grant [right(s)] to [account];

eg:-

grant connect, resource to rep_owner;

grant execute on dbms_lock to [owner_account];

grant execute on dbms_pipe to [owner_account];

grant create session, alter session, create table, create synonym, create sequence to [owner_account];

grant des2000_owner to [owner_account];

30)  //table with check option

Create table customer

(cust_no number(4) not null constraint pk_customer primary key,

cust_name varchar(10) not null,

credit_limit number(10,2) check ( credit_limit between 5,000 and 100000 ),

state_cd char(2) check ( state_cd in ('TX', 'CA', 'WA') ) );

//to select updateable columns from a view

select * from user_updatable_columns where table_name = 'TSAL'

//check option view

create or replace view mv as select * from tsal

where name like 'S%' or name like 's%'

with check option constraint mv_cons

//force view creation

create force view mv1 as select * from tree

alter view mv1 compile;

31) //A role is an oracle object where we can grant a set of permissions as a collection to a particular job

Create Role CUSTINFO.

Grant select on customer, cust_address, cust_hist....

Grant CUSTINFO to JON  //DCL statement

32)  //User Exception

declare

   too_many_students exception;

   var_kount number;

begin

   select count(*) into var_kount from tsal;

   If var_kount > 10 then

      raise too_many_students;

  End If;

Exception

  When too_many_students then

  dbms_output.put_line ( 'Time to create a new class schedule, Crossing the dead line for course id tsal table');

End ;

//raise_application_error

if you want to terminate the process we can raise an application error using Raise_application_error. When we use this statement in triggers all the data manipulation gets rollback automatically, the error text and number will appear to the called programs

Raise_Application_Error ( -20001, 'Customer  ' || var_customer || ' is not eligible for the Credit ' );

33) //function to convert time

Create or replace function convert_time_from_zone_to_zone

( var_local_time date,

  var_from varchar2,

  var_destination varchar2 

) return date is

var_gmt_value date;

BEGIN

     var_gmt_value := new_time ( var_local_time, var_from ,var_destination );

     return var_gmt_value;

End convert_time_from_zone_to_zone;

/

//then execute

select convert_time_from_zone_to_zone ( sysdate, 'EST','GMT') from dual;

//to see the no of sequeces created

 select * from user_sequences;

34) //CURSORS

declare

 rec_count number;

 emp_count number;

begin

 for v_c in 1..6 loop

 <<select_block>>

   select sno  into   rec_count  from tree where rownum = 1;

 <<count_block>>

 begin

      select count(*) into  emp_count from tree where sno = 1;

      dbms_output.put_line('After getting emp Result : ' || emp_count);

 end count_block;

 end loop; --<<select_block>>

 dbms_output.put_line('After getting rec Result : ' || rec_count);

end;

/

//Labeling block

<<outer_blk>>

declare

a number := 10;

b number := 20;

begin         

      <<sub_blk>>

      declare

                  c number := a; 

                  b number := 100;

      begin

          dbms_output.put_line('Inside Subblk : b=' || b);        

          dbms_output.put_line('Inside Subblk : c=' || c);         

          dbms_output.put_line('Inside Subblk : b=' || outer_blk.b);     

      end sub_blk;

end;

/

//Cursor rowtype

declare

      cursor empcur is select sno from tree;

      --emprec tree%rowtype;

      emprec empcur%rowtype;

begin

      open empcur;

        loop  

                  fetch empcur into emprec;

                  exit when empcur%notfound;

                  --select * into emprec from tree where sno =100;

                  dbms_output.put_line('SNO ' || emprec.sno);

      end loop;

      close empcur;

end;

/

//Cursor with parameters

declare

      cursor empcur(e_no NUMBER) is select sno from tree where sno = e_no for update;

      --emprec tree%rowtype;

      emprec empcur%rowtype;

begin

      open empcur(999);

        loop  

                  fetch empcur into emprec;

                  dbms_output.put_line('SNO ' || emprec.sno);

                  update tree set sno = 999 where current of empcur;

                  exit when empcur%found;

                  --select * into emprec from tree where sno =100;

      end loop;

      close empcur;

end;

//Curosr using Subquery

declare

 cursor c1 is select a.tablespace_name TSNAME,

    sum(a.tots) Tot_Size, sum(a.sumb) Tot_Free

    FROM (SELECT tablespace_name,0 tots,sum(bytes) sumb from dba_free_space group by tablespace_name

   UNION

   SELECT tablespace_name,sum(bytes) tots,0 from dba_data_files group by tablespace_name)

   a group by a.tablespace_name;

 v_pctfree PLS_INTEGER;

begin

 for r1 in c1 loop

  v_pctfree := r1.tot_free/r1.tot_size * 100;

  dbms_output.put_line(r1.tsname || ' has ' ||r1.tot_free|| ' bytes free out of total ' || r1.tot_size || ' ('||v_pctfree||'%)');

 end loop;

end;

/

//Coolections

declare

type stock_quote_rec is record  (

  symbol varchar2(10),

  bid number(10,2),

  ask number(10,2),

  vol number not null :=0,

  exchange varchar2(6) default 'NASDAQ'

 );

type detailed_quote_rec is record (

  quote stock_quote_rec, --nested record

  ts date,

  bid_size number,

  ask_size number,

  last_ticks varchar2(4)

 );

real_time_detail detailed_quote_rec;

real_time_detail1 detailed_quote_rec;

begin

 real_time_detail.bid_size := 1000;

 real_time_detail.quote.vol := 156700;

 real_time_detail1 := real_time_detail;

 dbms_output.put_line('Values #1 => ' || real_time_detail.bid_size);

 dbms_output.put_line('Values #2 => ' || real_time_detail.quote.vol);

 dbms_output.put_line('Values #11 => ' || real_time_detail1.bid_size);

 dbms_output.put_line('Values #22 => ' || real_time_detail1.quote.vol);

end;

/

//To create Type

create or replace type symbol_tab_type is varray(10) of varchar2(10);

declare

      symbol_tab symbol_tab_type;

begin

      --symbol_tab(1) := 'Srini';

      --symbol_tab(2) := 'Srinivasa';

      --symbol_tab(3) := 'Srinivasa.V';

      symbol_tab := symbol_tab_type('Srini','Srinivasa','Sriniv.V');

      display_symb(symbol_tab);

end;

create or replace Procedure display_symb(symbol_tab IN symbol_tab_type) as

begin

      dbms_output.put_line('Value #1 => ' || symbol_tab(1));

      dbms_output.put_line('Value #2 => ' || symbol_tab(1));

      dbms_output.put_line('Value #3 => ' || symbol_tab(1));

end display_symb;

declare

      type symbol_tab_type is table of varchar2(15) index by binary_integer;

      type tree_tab_type is table of tree%rowtype index by binary_integer;

      symbol_tab symbol_tab_type;

      tree_tab tree_tab_type;

      new_tree_tab tree_tab_type;

begin

      --initialize elements 147 and -3

      select * into tree_tab(147) from tree where sno = 999;

      select * into tree_tab(-3) from tree where sno = 101;               

      if(tree_tab(-3).sno = tree_tab(147).sno) then

                  dbms_output.put_line('They are Equal.......');

      end if;

      -- copy one to another

      new_tree_tab := tree_tab;

      symbol_tab(1) := 'Srini';

      symbol_tab(2) := 'Srinivasa';

      symbol_tab(3) := 'Srinivasa.V';

      dbms_output.put_line('Value ' || symbol_tab(1) ||' '||symbol_tab(2)||' '||symbol_tab(3));

      mypack.display_symb(symbol_tab);

end;

create or replace Procedure display_symb(symbol_tab) as

begin

      dbms_output.put_line('Value #1 => ' || symbol_tab(1));

      dbms_output.put_line('Value #2 => ' || symbol_tab(1));

      dbms_output.put_line('Value #3 => ' || symbol_tab(1));

end display_symb;

create or replace package mypack as

      type symbol_tab_type is table of varchar2(15) index by binary_integer;

      Procedure display_symb(symbol_tab IN symbol_tab_type);

end mypack;

create package body mypack as

      Procedure display_symb(symbol_tab IN symbol_tab_type) as

      begin

                  dbms_output.put_line('Value #1 => ' || symbol_tab(1));

                  dbms_output.put_line('Value #2 => ' || symbol_tab(1));

                  dbms_output.put_line('Value #3 => ' || symbol_tab(1));

      end display_symb;

end mypack;

=============================================

declare

      type stock_list is table of tree.sname%type;

      type top10_list is varray(10) of tree.sname%type;

      bio_tech_stocks stock_list; --nested table

      tech_10 top10_list; --varray

begin

      --invalid collections not initialised

      --bio_tech_stocks(1) := 'AMGN';

      --we can test for nulity of whole collection

      IF bio_tech_stocks is NULL then

                  bio_tech_stocks := stock_list('AMGN','BGEN','IMCL','GREN','CRA');

      end if;

      tech_10 := top10_list('ORCL','CSCO','MSFT','INTC','SUNW','IBM',NULL,NULL);

      --referenceing the elements is now valid

      if tech_10(7) is null then

                  tech_10(7) := 'CPQ';

      end if;

      tech_10(8) := 'DELL';

      for i in 1..tech_10.count loop

                  dbms_output.put_line('Value ' ||i||' ' ||tech_10(i));

      end loop;

      mypack.display_symb(symbol_tab);

end;

35) // Can one only retreive rows X to Y from a table?

SELECT

*

from

(

SELECT

Rownum rn, tab1.*

FROM

tab1

)

WHERE

rn between lower_value

and Upper_value;

eg:-SELECT * from (SELECT Rownum rn, tree.* FROM tree) WHERE rn between 2 and 5

//x - y row selection

SELECT *

FROM tab1

WHERE rowid in (

SELECT rowid FROM tab1

WHERE rownum <= 9

MINUS

SELECT rowid FROM tab1

WHERE rownum < 7);

36) To source of proce or function

SELECT TEXT FROM ALL_SOURCE WHERE NAME='FUNCTION_NAME'; 

37) //File io

DECLARE

fileHandler UTL_FILE.FILE_TYPE;

BEGIN

fileHandler := UTL_FILE.FOPEN('/tmp', 'file1', 'w');

UTL_FILE.PUTF(fileHandler, 'the lines of text\n');

UTL_FILE.FCLOSE(fileHandler);

EXCEPTION

WHEN utl_file.invalid_path THEN

raise_application_error(-20000, 'ERROR: Invalid path for file or path not in INIT.ORA.');

END;

/dependenicies

USER_DEPENDECIES,ALL_DEPENDENCIES and DBA_DEPENDENCIES

38)  //diff in dates

select floor((date1-date2)*24*60*60)/3600

|| ' HOURS ' ||

floor((((date1-date2)*24*60*60) -

floor(((date1-date2)*24*60*60)/3600)*3600)/60)

|| ' MINUTES ' ||

round((((date1-date2)*24*60*60) -

floor(((date1-date2)*24*60*60)/3600)*3600 -

(floor((((date1-date2)*24*60*60) -

floor(((date1-date2)*24*60*60)/3600)*3600)/60)*60)))

|| ' SECS ' time_difference

from table a

39)  How to select alternate rows seletion

SELECT *

FROM emp

WHERE (ROWID,0) IN (SELECT ROWID, MOD(ROWNUM,4)

FROM emp);

40)  How to delete duplicate records

DELETE FROM emp e

WHERE e.row_id <> (SELECT ee.rowid

FROM emp ee

WHERE ee.emp_no = e.emp_no);

41)  How to create  table space

CREATE TABLESPACE <name> DATAFILE <file on server>

SIZE 50M REUSE AUTOEXTEND ON  next 20M maxsize unlimited

eg :-

CREATE TABLESPACE dxtspace DATAFILE 'e:/oracle/oradata/cyber/TEMPORARY_99.DBS'

SIZE 50M REUSE AUTOEXTEND ON  next 20M maxsize unlimited

42) To c triggers name and details

select trigger_type, table_name, triggering_event

from user_triggers

where trigger_name = '<trigger_name>';

select trigger_name from user_triggers;

//to export 2gb file on to tape

Use FILESIZE parameter in export.

exp system/password file=(exp1.dmp exp2.dmp exp3.dmp) full=Y filesize=1000000000 buffer=100000000

This exp command creates 3 dump files of size 1GB which can be copied to tapes.

43)  Data Dictionary View Name (alphabetically ordered)

  --------------------------------------------------

  ALL_CATALOG   ALL_COL_COMMENTS   ALL_COL_PRIVS   ALL_COL_PRIVS_MADE   ALL_COL_PRIVS_RECD

  ALL_CONTRAINTS   ALL_CONS_COLUMNS   ALL_DB_LINKS   ALL_DEF_AUDIT_OPTS   ALL_DEPENDENCIES

  ALL_ERRORS   ALL_INDEXES   ALL_IND_COLUMNS   ALL_LABELS   ALL_MOUNTED_DBS   ALL_OBJECTS

  ALL_SEQUENCES   ALL_SNAPSHOTS   ALL_SOURCE   ALL_SYNONYMS   ALL_TABLES   ALL_TAB_COLUMNS

  ALL_TAB_COMMENTS   ALL_TAB_PRIVS   ALL_TAB_PRIVS_MADE   ALL_TAB_PRIVS_RECD   ALL_TRIGGERS

  ALL_USERS   ALL_VIEWS   AUDIT_ACTIONS

  CAT   CLU   CODE_PIECES   CODE_SIZE   COLS   COLUMN_PRIVILEGES

  DBA_2PC_NEIGHBORS   DBA_2PC_PENDING   DBA_AUDIT_EXITS   DBA_AUDIT_OBJECT   DBA_AUDIT_SESSION

  DBA_AUDI_STATEMENT   DBA_AUDIT_TRAIL   DBA_BLOCKERS   DBA_CATALOG   DBA_CLUSTERS   DBA_CLU_COLUMNS

  DBA_COL_COMMENTS   DBA_COL_PRIVS   DBA_CONSTRAINTS   DBA_CONS_COLUMNS   DBA_DATA_FILES

  DBA_DB_LINKS   DBA_DDL_LOCKS   DBA_DEPENDENCIES   DBA_DML_LOCKS   DBA_ERRORS   DBA_EXP_FILES

  DBA_EXP_OBJECTS   DBA_EXP_VERSION   DBA_EXTENTS   DBA_FREE_SPACE   DBA_INDEXES   DBA_IND_COLUMMNS

  DBA_LOCKS   DBA_OBJECTS   DBA_OBJECT_SIZE   DBA_OBJ_AUDIT_OPTS   DBA_PRIV_AUDIT_OPTS   DBA_PROFILES

  DBA_ROLE_PRIVS   DBA_ROLES   DBA_ROLLBACK_SEGS   DBA_SEGMENTS   DBA_SEQUENCES   DBA_SNAPSHOTS

  DBA_SNAPSHOT_LOGS   DBA_SOURCE   DBA_STMT_AUDIT_OPTS   DBA_SYNONYMS   DBA_SYS_PRIVS   DBA_TABLES

  DBA_TABLESPACES   DBA_TAB_COLUMNS   DBA_TAB_COMMENTS   DBA_TAB_PRIVS   DBA_TRIGGERS   DBA_TS_QUOTAS

  DBA_USERS   DBA_VIEWS   DBA_WAITERS   DBMS_ALERT_INFO   DBMS_LOCK_ALLOCATED   DEPTREE  DICT

  DICTIONARY   DICT_COLUMNS

  ERROR_COLUMNS

  GLOBAL_NAME

  IDEPTREE    IND   INDEX_HISTOGRAM   INDEX_STATS

  LOADER_COL_INFO    LOADER_CONSTRAINT_INFO   LOADER_INDCOL_INFO   LOADER_IND_INFO

  LOADER_PARAM_INFO   LOADER_TAB_INFO   LOADER_TRIGGER_INFO

  OBJ

  PARSED_PIECES   PARSED_SIZE

  PUBLIC_DEPENDENCY  

  RESOURCE_COST    ROLE_ROLE_PRIVS   ROLE_SYS_PRIVS   ROLE_TAB_PRIVS

  SEQ    SESSION_PRIVS   SESSION_ROLES   SOURCE_SIZE   STMT_AUDIT_OPTION_MAP   SYN   SYSTEM_PRIVILEGE_MAP

  TABLE_PRIVILEGES   TABLE_PRIVILEGE_MAP   TABS

  USER_AUDIT_OBJECT   USER_AUDIT_SESSION   USER_AUDIT_STATEMENT   USER_AUDIT_TRAIL   USER_CATALOG

  USER_CLUSTERS  USER_CLU_COLUMNS  USER_COL_COMMENTS  USER_COL_PRIVS  USER_COL_PRIVS_MADE

  USER_COL_PRIVS_RECD   USER_CONSTRAINTS  USER_CONS_COLUMNS  USER_DB_LINKS   USER_DEPENDENCIES

  USER_ERRORS   USER_EXTENTS  USER_FREE_SPACE   USER_INDEXES   USER_IND_COLUMNS  USER_OBJECTS

  USER_OBJECT_SIZE  USER_OBJ_AUDIT_OPTS  USER_RESOURCE_LIMITS  USER_ROLE_PRIVS

  USER_SEGMENTS  USER_SEQUENCES  USER_SNAPSHOTS  USER_SNAPSHOT_LOGS  USER_SOURCE

  USER_SYNONYMS  USER_SYS_PRIVS  USER_TABLES  USER_TABLESPACES  USER_TAB_COLUMNS

  USER_TAB_COMMENTS  USER_TAB_PRIVS  USER_TAB_PRIVS_MADE  USER_TAB_PRIVS_RECD

  USER_TRIGGERS   USER_TS_QUOTAS  USER_USERS  USER_VIEWS

44) Dynamically executing sql stmts

create or replace Procedure myProc(tab in varchar2, val1 in number) as

sql_stmt varchar2(100) := 'insert into ' || tab || ' values ('||val1||')';

begin

      EXECUTE immediate sql_stmt;

end;

45) To c to whcih databse we connected works in 8.1.6

select distinct A.name DATABASE,B.machine SERVER

from V$database A, V$session B

where b.username is null

/

//Manager selection fro the same table

select t.sname,r.sname from tree t,tree r

where r.sno(+) = t.mgr_no

/

//date manipulation

select count(*) from dual where sysdate

between to_date('2-aug-01 10:30 am' ,'dd-mon-yy hh:mi am')

and to_date('2-aug-01 12:00 pm' ,'dd-mon-yy hh:mi pm');

46) // to create sequnce

create sequence SQ_NTL_ID increment by 1 start with 1 maxvalue 1.0000E+14 cycle nocache;

CREATE SEQUENCE [schema.]sequence

           [INCREMENT BY integer]

           [START WITH integer]

           [MAXVALUE integer | NOMAXVALUE]

           [MINVALUE integer | !!under!!NOMINVALUE]

           [CYCLE | NOCYCLE]

           [CACHE integer | NOCACHE]

           [ORDER | !!under!!NOORDER]

47) //to change a table's col name

select object_id from all_objects where object_NAME='<table_name_in_Caps>'

UPDATE SYS.COL$ SET NAME='<new_col_name>' WHERE OBJ#=<obect_id> AND NAME='<old_name>'

alter system flush shared_pool;

//to create database link

create database link <name> connect to <username> identified by <password> using 'servicename';

eg : create database link srilink connect to srini identified by sri using 'cyber'

/

//to create index

create index <index_name> on <table_name>(<col_name>)