OracleByAjay

  SQL> select index_name from user_indexes where table_name ='EMP'; no rows selected SQL> ED Wrote file afiedt.buf   1* CREATE INDEX EMPNO_IDX ON EMP(EMPNO) SQL> / Index created. SQL> select index_name from user_indexes where table_name ='EMP'; INDEX_NAME ------------------------------ EMPNO_IDX SQL> SELECT * FROM EMP WHERE EMPNO =7369;      EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM ---------- ---------- --------- ---------- --------- ---------- ----------     DEPTNO ----------       7369 SMITH      CLERK           7902 17-DEC-80        800         20 SQL> SET AUTOTRACE ON SQL> SELECT * FROM EMP WHERE EMPNO =7369;      EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM ---------- ---------- --------- ---------- --------- ---------- ----------     DEPTNO ----------       7369 SMITH      CLERK           7902 17-DEC-80        800         20 Execution Plan ---------------------------------------------------

 Temporary tables Temporary tables data can be for a temporary period of a transaction or a session. Temporary data for a transaction example :- create global temporary table emp_temp (empno number,  ename varchar2(30),  sal number,  deptno number) on commit delete rows;    Insert records to test data for a transaction:- insert into emp_temp values ( 1, 'krishna', 1000, 10); insert into emp_temp values ( 2, 'Raghu', 1000, 10); insert into emp_temp values ( 3, 'Sachin', 1000, 10); insert into emp_temp values ( 4, 'Amit', 1000, 10); Verifying table data before deleting with a commit command :- select * from emp_temp; Issuing commit to delete the records of the temporary table:- commit; Verifying table data after deleting with a commit command :- select * from emp_temp; no rows selected Temporary data for a session example :- Drop table emp_temp; create global temporary table emp_temp (empno number,  ename varchar2(30),  sal number,  deptno number) on commi

                        Oracle pl sql queries   1.Write a program to print the following format 2.Write a program to print the numbers from 1 to 100 3.write a program to print the even numbers from 1 to 100 4.Write a program to print the odd numbers from 1 to 100 5.write a program for multiplication table 6.write a program to find the sum of numbers from 1 to 100 7.Write a program to find the sum of all odd numbers from 1 to 100 8.Write a program to find the sum of all even numbers from 1 to 100 9.Write a program to accept a number and find how many digits it 10.Write a program to accept a number and find the sum of the digits 11.Write a program to accept a number and print it in reverse order 12.Write a program to accept a no and check whether it is Armstrong number or not  13.Write a porgram to generate all the Armstrong numbers from 1 to 1000 14.Write a program to generate all prime numbers between 1 to 100 15.Write a program to aceept a number and check whether it is prime number o

Most of the SQL statements you will write in PL/SQL  are  static , which means that they are parsed when you compile the block in which you wrote the SQL statements. But sometimes you don’t have all the information needed at compile time to parse your SQL statements. Perhaps the name of a column or a WHERE clause is constructed only when the program is executed. In this case, you must write  dynamic  SQL. It’s very easy to write static SQL in PL/SQL program units, and that’s one of the best things about PL/SQL. It’s also quite easy to implement dynamic SQL requirements in PL/SQL example to add a record   create or  replace procedure add_row  (p_table_name varchar2,    p_id number,   p_name varchar2,   p_loc varchar2) is begin execute immediate 'insert into '|| p_table_name || ' values  (:1, :2, :3)' using p_id, p_name, p_loc; end; example to drop a table  DECLARE      ddl_qry     VARCHAR2 (100); BEGIN      ddl_qry := 'DROP TABLE tut_83' ;      EXECUTE IMMEDIATE

Subprogram Inlining / Continue statement / Pl Sql Warnings  Subprogram inlining has been around in optimizing compilers for a long time.  With the 11g database, Oracle has included this feature in PL/SQL.   Basically subprogram inlining is where the program calls a subprogram (procedure or function) and the compiler places the actual subprogram code in place of the call to that code.  Since the subprogram code is in the main code (INLINE), the overhead of calling that subprogram is saved.  This allows you to program using a modular method, producing readable, maintainable source code, while the compiler optimizes the code to make it more efficient. -------------------   inlining subprograms --------------- Oracle 11g has taken compiler optimisation further, in particular with the concept of subprogram inlining. With subprogram inlining, Oracle will replace a call to a subroutine (such as a function) with the subroutine code itself during compilation. One of the benefits of this is that

  Pragma Introduction In my package's functions and procedure, sometimes I have some rules to control purity rules of the database,  like reading from tables only, not query the database, ...... etc  So I will  use  PRAGMA RESTRICT_REFERENCES  to control and preserve the database state in database packages. Syntax of  PRAGMA RESTRICT_REFERENCES PRAGMA RESTRICT_REFERENCES(SUBPROGRAM_NAME, IDENTIFIER) SUBPROGRAM_NAME  can be default which will affect all the subprograms in packages or subprogram name(function or procedure) in package IDENTIFIER  can be  RNDS, WNDS, RNPS, WNPS  and  TRUST . I will explain every one separately. 1- RNDS select query is restricted against any of database tables 2- WNDS DML  operations are restricted against any of database tables  3- RNPS Selection of package variables is restricted  4- WNPS Modification in packages variables is restricted 5- TRUST Asserts that the subprogram can be trusted not to violate one or m