Alter Table With Default Constraint

case1:-

========

When you are adding a column into an existing table with default constraint then it takes lot of time to add, if the table is big.

Why because adding column with default value means you are updating the total table with that value.

ex: ALTER TABLE Scott.EMP ADD (emp_flag VARCHAR2(1) DEFAULT 'N');

case2:-

========

When you are adding a column into an existing table without default constraint,

it doesnot take time even though you are adding it into a big table.

Why because adding a column without default value means it doesnot go for updating your table. It just adds a column with null values.

ex: ALTER TABLE Scott.EMP ADD (emp_flag VARCHAR2(1));

Note:- Based on your requirement go for adding.

How to Compile a Schema in oracle

Use the supplied package procedure DBMS_UTILITY.COMPILE_SCHEMA. It Compiles all PLSQL objects in a schema.

EXECUTE DBMS_UTILITY.COMPILE_SCHEMA('SCHEMA_NAME');

ex:- EXECUTE DBMS_UTILITY.COMPILE_SCHEMA('SCOTT'); 

NOTE: Recompiles all stored packages, procedures and functions in the specified schema in dependency order.

NOTE:- It applies to 9.2.0.8 and later.

OUT parameters in oracle Stored functions along with return Value

While writing PL/SQL functions we can use get values through return statement as well as using out parameters. Generally people don’t use out parameters in stored functions, but it is always pretty much possible to use them in functions also. Based on your requirement you can make use of them. Just go through below example:

CREATE OR REPLACE FUNCTION test_fun (var1 NUMBER, var2 OUT VARCHAR2,var3 out number)

   RETURN NUMBER

IS

   v_sal   NUMBER;

BEGIN

   SELECT ename, sal,deptno

     INTO var2, v_sal,var3

     FROM EMP

    WHERE empno=var1 and ROWNUM = 1;

   RETURN v_sal;

END;

/

Call the function like below:

DECLARE

   retval   NUMBER;

   var1     NUMBER;

   var2     VARCHAR2 (200);

   var3     NUMBER;

BEGIN

   var1 := 7369;

   var2 := NULL;

   var3 := NULL;

   retval := TEST_FUN (var1, var2, var3);

   DBMS_OUTPUT.put_line ('VAR2:' || var2);

   DBMS_OUTPUT.put_line ('VAR3:' || var3);

   DBMS_OUTPUT.put_line ('RETVAL:' || retval);

   COMMIT;

END;

/

Your output will be like below:

VAR2:SMITH

VAR3:20

RETVAL:800

Printing Camel Case Names while creating Database Objects in Oracle

If you want to put the CamelCase names to your database objects, your create object script should have been with in double quotes like below:

create table "temp_2"

(“col1”  number constraint "temp_2_pk" primary key, col2 number);

select * from "temp_2"

select * from user_tables where table_name ='temp_2'

Without double-quotes Oracle helpfully converts all identifiers to uppercase.

Save points in oracle

SAVEPOINT- name Marks a savepoint within the current transaction

ROLLBACK - ROLLBACK ends the current transaction by discarding all pending

                     data changes

ROLLBACK TO SAVEPOINT name-ROLLBACK TO SAVEPOINT rolls back the current transaction to the specified savepoint, thereby discarding any changes and or savepoints created after the savepoint to which you are rolling back. If you omit the TO SAVEPOINT clause, the ROLLBACK statement rolls back the entire transaction. As savepoints are logical, there is no way to list the savepoints you have created.

Syntax:
Savepoint <savepoint_name>;

Example:-
SQL> savepoint s1;
SQL> insert into student values(1, ‘a’, 100);
SQL> savepoint s2;
SQL> insert into student values(2, ‘b’, 200);
SQL> savepoint s3;
SQL> insert into student values(3, ‘c’, 300);
SQL> savepoint s4;
SQL> insert into student values(4, ‘d’, 400);

Before rollback 

SQL> select * from student;

NO NAME MARKS
--- ------- ----------
1 a 100
2 b 200
3 c 300
4 d 400

SQL> rollback to savepoint s3;
Or
SQL> rollback to s3;

This will rollback last two records.
SQL> select * from student;

NO NAME MARKS
--- ------- ----------
1 a 100
2 b 200

Example1:-

CREATE OR REPLACE PROCEDURE pp

IS

BEGIN

   UPDATE emp SET ename = ename || '1' WHERE empno = 7788;

   SAVEPOINT 1;

   UPDATE emp SET ename = ename || '2' WHERE empno = 7788;

   SAVEPOINT 2;

   UPDATE emp SET ename = ename || '3' WHERE empno = 7788;

   SAVEPOINT 3;

   UPDATE emp SET ename = ename || '4' WHERE empno = 7788;

   SAVEPOINT 4;

  

   ROLLBACK to 2;

  

   COMMIT;

END;

/

U will get compilation error like below:

PL/SQL: ORA-00933: SQL command not properly ended

Sol:- you can’t establish your savepoints using numbers, u should give characters.

Example2:-

CREATE OR REPLACE PROCEDURE pp

IS

BEGIN

   UPDATE emp SET ename = ename || '1' WHERE empno = 7788;

   SAVEPOINT a;

   UPDATE emp SET ename = ename || '2' WHERE empno = 7788;

   SAVEPOINT b;

   UPDATE emp SET ename = ename || '3' WHERE empno = 7788;

   SAVEPOINT c;

   UPDATE emp SET ename = ename || '4' WHERE empno = 7788;

   SAVEPOINT d;

  

     Commit;

   ROLLBACK to b;

  

   COMMIT;

END;

/

U will get run time error like below:

ORA-01086: savepoint 'B' never established

Sol:- you can’t establish your savepoints after commit statement, why because already transactions are committed using commit, so it is impossible to rollback.

Example3:-

CREATE OR REPLACE PROCEDURE pp

IS

BEGIN

   UPDATE emp SET ename = ename || '1' WHERE empno = 7788;

   SAVEPOINT a;

   UPDATE emp SET ename = ename || '2' WHERE empno = 7788;

   SAVEPOINT b;

   UPDATE emp SET ename = ename || '3' WHERE empno = 7788;

   SAVEPOINT c;

   UPDATE emp SET ename = ename || '4' WHERE empno = 7788;

   SAVEPOINT d;

  

    ROLLBACK to 2;

  

   COMMIT;

END;

/

Sol:- Here upto b transactions will be rollback, and before b whatever transactions are there those will be committed. Means SCOTT will become SCOTT12.