How to get the INIT.ORA parameter values

Here we see the differenet methods to get the init.ora parameter values

1. Using built in DBMS_UTILITY.GET_PARAMETER_VALUE
2. Show parameter
3. Using V$PARAMETER

Method 1: DBMS_UTILITY.GET_PARAMETER_VALUE
DBMS_UTILITY.GET_PARAMETER_VALUE (arg1, arg2, arg3) takes 3 parameters, arg1 takes IN mode we pass the parameter name for which the value needs to be knows. arg2 and arg3 takes IN OUT mode, arg2 returns the integer value if arg1 is of integer type, arg3 returns the string value if arg1 is of string value. If arg1 of string type then arg3 retruns the string value and arg2 returns the length of the arg3 value.

Here is the sample block to illustrate how to use it.

srikar@ORCL> ed
Wrote file afiedt.buf

  1  declare
  2     l_partype  number;
  3     l_intval   number;
  4     l_strval   varchar2(2000);
  5  begin
  6     dbms_output.put_line ('Enter the parameter name: ');
  7     l_partype :=  dbms_utility.get_parameter_value ('&l_param4',l_intval,l_strval);
  8     dbms_output.put('The parameter type is:');
  9     if (l_partype = 1) then
 10     dbms_output.put('String ');
 11     else
 12     dbms_output.put('Number ');
 13     end if;
 14     dbms_output.put(' and the value is: ');
 15     if (l_partype = 1) then
 16     dbms_output.put_line(l_strval);
 17     else
 18     dbms_output.put_line(l_intval);
 19     end if;
 20     dbms_output.put_line('Both the inout values are: l_intval: '
 21                           ||l_intval
 22                           ||' l_strval: '
 23                           ||l_strval ) ;
 24* end;
srikar@ORCL> /
Enter value for l_param4: control_files
old   7:    l_partype :=  dbms_utility.get_parameter_value ('&l_param4',l_intval,l_strval);
new   7:    l_partype :=  dbms_utility.get_parameter_value ('control_files',l_intval,l_strval);
Enter the parameter name:
The parameter type is:String  and the value is: C:\APP\SRIKARDASARI\ORADATA\ORCL\CONTROL01.CTL
Both the inout values are: l_intval: 46 l_strval: C:\APP\SRIKARDASARI\ORADATA\ORCL\CONTROL01.CTL

PL/SQL procedure successfully completed.

Method 2: SHOW PARAMETER
This is simple and straight forward. We pass the parameter name to SHOW PARAMETER let say for control_files,

srikar@ORCL> show parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      C:\APP\SRIKARDASARI\ORADATA\OR
                                                 CL\CONTROL01.CTL, C:\APP\SRIKA
                                                 RDASARI\FLASH_RECOVERY_AREA\OR
                                                 CL\CONTROL02.CTL 

Method 3: V$PARAMETER
We can query against the V$PARAMETER view to get the parameter name.

srikar@ORCL> select name, value from v$parameter where name='control_files';
NAME    VALUE
-------------------------------------------------------------------------------------------control_files C:\APP\SRIKARDASARI\ORADATA\ORCL\CONTROL01.CTL, C:\APP\SRIKARDASARI\FLASH_RECOVERY_AREA\ORCL\CONTROL02.CTL

srikar@ORCL> select name, value from v$spparameter where name='control_files';

NAME VALUE
-------------------------------------------------------------------------------------------control_files
C:\app\SrikarDasari\oradata\orcl\control01.ctl

control_files
C:\app\SrikarDasari\flash_recovery_area\orcl\control02.ctl

SQL%BULK_ROWCOUNT and SQL%BULK_EXCEPTIONS

SQL%BULK_ROWCOUNT and SQL%BULK_EXCEPTIONS are used in bulk opeartions with FORALL

SQL%BULK_ROWCOUNT
Let say, we are processing a bulk operation, need to know how many records are processed by each interation for the given DML (Update, Delete and Insert).

We have SQL%BULK_ROWCOUNT attribute to know the number of records processed in each iteration in a bulk operation.

The follwoing example shows how to use it.

srikar@ORCL> create table t_emp2 as select * from emp
  2  /

Table created.

srikar@ORCL> select deptno, count(*)
  2     from t_emp2
  3     where deptno in (10, 30)
  4     group by deptno
  5  /

    DEPTNO   COUNT(*)
---------- ----------
        10          3
        30          6

Dept 30 have 6 rows and dept 10 have 3 rows.

srikar@ORCL> declare
  2  
  3     type lt_dept_arr is table of number;
  4     l_dept_arr lt_dept_arr := lt_dept_arr(10, 30);
  5  
  6  begin
  7  
  8     forall i in l_dept_arr.first..l_dept_arr.last
  9     delete from t_emp2 where deptno = l_dept_arr(i);
 10  
 11     for j in l_dept_arr.first..l_dept_arr.last loop
 12        dbms_output.put_line ('The number of records deleted in '
 13                               || j || 'th iteration are  '
 14                               ||sql%bulk_rowcount(j) );
 15     end loop;
 16  
 17  end;
/
The number of records deleted in 1th iteration are  3
The number of records deleted in 2th iteration are  6

PL/SQL procedure successfully completed.


Handling exceptions in FORAll using SQL%BULK_EXCEPTIONS
PL/SQL provides a mechanism to handle exceptions that were raised during the FORALL bulk operation using SQL%BULK_EXCEPTIONS.

SQL%BULK_EXCEPTIONS argument saves all the exceptions raised during the bulk operation provided FORALL statement should have SAVE EXCEPTIONS added.

SQL%BULK_EXCEPTIONS.count gives the number of exceptions raised in the last FORALL bulk opearation

SQL%BULK_EXCEPTIONS.ERROR_INDEX gives the iteration number in which the exception is raised

SQL%BULK_EXCEPTIONS.ERROR_CODE gives the error code, using which we can find the error message as ERRM( - SQL%BULK_EXCEPTIONS ). noted that '-' is added at the beginning
of the SQL%BULK_EXCEPTIONS in the SQLERRM function.

Lets take an example to illustrate the functionality.

srikar@ORCL> create table t_emp as select * from emp;

Table created.

We have created a table 't_emp' whice is similar to emp table.

Now, we append each empname with ' Oracl' due to which error is raised in the bulk operation as the empname column maximum size is 10 characters and the append is not allowed for few columns,so the records whose length is crossing 10 characters would raise an exception.

In the follwoing code, if an exception is raised then the exception is saved and processed with the next records.


srikar@ORCL> ed
Wrote file afiedt.buf

  1  declare
  2         type lt_emp_arr is table of t_emp.empno%type;
  3         l_emp_arr    lt_emp_arr;
  4         errors       number;
  5  begin
  6         select empno bulk collect into l_emp_arr from t_emp;
  7         forall i in l_emp_arr.first..l_emp_arr.last save exceptions
  8         update t_emp set ename = ename||' Oracl' where empno = l_emp_arr(i) ;
  9  exception
 10       when others then
 11        errors := sql%bulk_exceptions.count;
 12        dbms_output.put_line ('The total number of errors occured are '
 13                                  || errors
 14                             );
 15        for j in 1..errors loop
 16           dbms_output.put_line ('The error iteration is  '
 17                                  || sql%bulk_exceptions(j).error_index
 18                                  || ' and the error code is '
 19                                  || sql%bulk_exceptions(j).error_code
 20                                  || ' and the error message is '
 21                                  || sqlerrm (- sql%bulk_exceptions(j).error_code)
 22                                );
 23        end loop;
 24* end;
 25  /
The total number of errors occured are 11
The error iteration is  1 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , ma
ximum: )
The error iteration is  2 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , ma
ximum: )
The error iteration is  4 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , ma
ximum: )
The error iteration is  5 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , ma
ximum: )
The error iteration is  6 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , ma
ximum: )
The error iteration is  7 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , ma
ximum: )
The error iteration is  8 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , ma
ximum: )
The error iteration is  10 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , m
aximum: )
The error iteration is  11 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , m
aximum: )
The error iteration is  12 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , m
aximum: )
The error iteration is  14 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , m
aximum: )

PL/SQL procedure successfully completed.

srikar@ORCL> select ename from t_emp;

ENAME
----------
SMITH
ALLEN
WARD Oracl
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING Oracl
TURNER
ADAMS
JAMES
FORD Oracl
MILLER

14 rows selected.

What if there is no Oracle installed in your system

You want to test a SQL query. But you don't have a oracle client or server available at your place - no oracle products are installed at your place.

If you are blessed to have an internet connection, you are empowered, can test all your SQL queries.

Oracle has provided a web based environment, all what need to do is, very first time use the URL to create an account with minimal details and accept the conformation sent to your mail id, log in with your credentials that you supply during creation of an account (password will be sent to your mail id and first time log in will ask you to change the password) and work. Thats all about it.

The advantage is, you only create account once and can use that any time, no need of creation of account all the time. You will have separate database that is used only by you. Everything is independent.

So, when ever you want to test something go to the URL, log in and test

Have the account created now, you never know, you may require to test something remotely.

new_value in sql plus

new_value is a keyword in oracle sql plus that selects value to the user variable. This is mostly useful in sql scripting.
Here we see, the basic steps how to use it


A sample source table which holds some data
srikar@ORCL> create table t_test_from(a number);
srikar@ORCL> insert into t_test_from values(2);
srikar@ORCL> commit;

A sample table which holds the data that we insert
srikar@ORCL> create table t_test_to(b number);

Here is the syntax <>, the column_name value is selected to the user_variable
srikar@ORCL> column a new_value v_a
srikar@ORCL> select a from t_test_from;

A
----------
2

srikar@ORCL> insert into t_test_to values (&v_a);
srikar@ORCL> commit;
srikar@ORCL> select * from t_test_to;

B
----------
2

If the above query -- "select a from t_test_from" returns more than one rows, then our variable (user_variable) holds the last value

It needs to be included in single quotes for strings ('&user_variable')

srikar@ORCL> alter table t_test_from add (name varchar2(20));
srikar@ORCL> insert into t_test_from values (20,'Here we go ');
srikar@ORCL> commit;
srikar@ORCL> column name new_value v_name
srikar@ORCL> select name from t_test_from;

NAME
--------------------

Here we go
srikar@ORCL> alter table t_test_to add (name varchar2(20));
srikar@ORCL> insert into t_test_to(name) values('&v_name');
srikar@ORCL> commit;
srikar@ORCL> select * from t_test_to;

B NAME
---------- --------------------
Here we go


Here is the example --practical script that Tom Kyte has written (Read the below to know what does the script will do)
----------------------------------------------------------------------------------------
A DBA frequently needs to become another user to test something or verify a problem. Short of having to gain acess to that users password, we are asked can I su to that account, sort of like root does on unix.

This is an 'su.sql' script:


whenever sqlerror exit
column password new_value pw

declare
l_passwd varchar2(45);
begin
select password into l_passwd
from sys.dba_users
where username = upper('&1');
end;
/

select password
from sys.dba_users
where username = upper( '&1' )
/

alter user &1 identified by Hello;
connect &1/hello
alter user &1 identified by values '&pw';
show user
whenever sqlerror continue

it starts by testing your access to the sys.dba_users table -- if that fails -- it exits SQLPlus. If zero rows returned -- it exits SQLPlus.

It then selects the 'password' from the dba_users table and stuffs it into a macro variable "&pw"

We alter the user you want to become to have a known password (if that fails, we exit).
--------------------------------------------------------------------------------------

Thats all about it.

refcursor- reparse

The refcursor makes the underlying query to go for reparsing all the time when run though the query is not changed.

Let say the following procedure is created successfully and ran 6 times.

CREATE OR REPLACE PROCEDURE "SRIKAR"."P"
as
l_cursor sys_refcursor;
l_rec dual%rowtype;
cursor c is select * from dual d2;
begin
open l_cursor for select * from dual d1;
fetch l_cursor into l_rec;
close l_cursor;
open c;
fetch c into l_rec;
close c;
end;

The above procedure is executed 6 times.
When I look at the TKPROF output,



SELECT * FROM DUAL D1 is parsed 6 times as below

Authid

What is the need of 'authid' keyword in pl/sql.

'Authid' is a keyword in pl/sql which is used with proceuders (create procedure), packages (create package), functions (create function) and types (create type).
A stored procedure runs eiether with rights of the caller (authid current_user) or with the rights of owner (authid definer)
The default value for 'authid' is 'definer'. It immediatly follows the 'create procedure' keyword.

The generic syntax as below

SQL> ed
Wrote file afiedt.buf

1 create or replace procedure sp_authid_currentuser
2 --[authid current_user|define]
3 as
4 v_value varchar2(100);
5 begin
6 select a into v_value from t;
7 dbms_output.put_line(v_value);
8* end;
9 /

Let say there are two users (DASARIS, CISBATCH) and a table (T) which is created in both the schemas.

[From DASARIS]
SQL> create table t(a varchar2(100));

Table created.

SQL> insert into t values('from srikar');

1 row created.

SQL> commit;

Commit complete.


[From CISBATCH]
SQL> create table t (a varchar2(100));

Table created.

SQL> insert into t values('from cisbatch');

1 row created.

SQL> commit;

Commit complete.

First, we would create a procedure in 'DASARIS' schema which simply displays the content of the table 'T' with 'authid' as 'definer'

SQL> ed
Wrote file afiedt.buf

1 create procedure sp_authid
2 as --Here we dint mention anything so default is 'authid definer'
3 v_value varchar2(100);
4 begin
5 select a into v_value from t;
6 dbms_output.put_line(v_value);
7* end;
SQL> /


Here is what we get when we run the procedure from 'DASARIS' schema

SQL> begin
2 sp_authid;
3 end;
4 /
from srikar

PL/SQL procedure successfully completed.

no surprise !

Lets try to run the same procedure from 'CISBATCH' schema
SQL> ed
Wrote file afiedt.buf

1 begin
2 dasaris.sp_authid;
3* end;
SQL> /
dasaris.sp_authid;
*
ERROR at line 2:
ORA-06550: line 2, column 9:
PLS-00904: insufficient privilege to access object DASARIS.SP_AUTHID
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored

mmmm, we need to grant privileges to 'CISBATCH'


[From DASARIS]
SQL> grant execute on sp_authid to cisbatch;

Grant succeeded.

lets try one more time

[From CISBATCH]
SQL> ed
Wrote file afiedt.buf

1 begin
2 dasaris.sp_authid;
3* end;
4 /
from srikar

PL/SQL procedure successfully completed.

Here we got the output as 'from srikar' thouh the table 'T' in schema 'CISBATCH' is holding 'from cisbatch', we got such result as the procedure ran with the rights of the definer i.e. 'DASARIS'

thats all about 'authid definer'


Lets now go to 'authid current_user'

the general syntax for 'authid current_user' is
SQL> ed
Wrote file afiedt.buf

1 create or replace procedure sp_authid_currentuser
2 authid current_user
3 as
4 v_value varchar2(100);
5 begin
6 select a into v_value from t;
7 dbms_output.put_line(v_value);
8* end;
9 /


lets run this procedure from two schemas,

[From DASARIS]
SQL> begin sp_authid_currentuser; end;
2 /
from srikar

PL/SQL procedure successfully completed.

SQL> grant execute on sp_authid_currentuser to cisbatch;

Grant succeeded.

[From CISBATCH]
SQL> begin dasaris.sp_authid_currentuser; end;
2 /
from cisbatch

PL/SQL procedure successfully completed.

so, the procedure run from 'CISBATCH' schema gives us the rusult from its own table, it just like running procedure which is in the same schema.

Lets try to drop the table 'T' in 'CISBATCH' schema and run both the procedures from 'CISBATCH' schema,

[From CISBATCH]
SQL> drop table t;

Table dropped.

SQL> begin
2 dasaris.sp_authid_currentuser;
3 end;
4 /
begin
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "DASARIS.SP_AUTHID_CURRENTUSER", line 6
ORA-06512: at line 2

SQL>
SQL> begin
2 dasaris.sp_authid;
3 end;
4 /
from srikar

PL/SQL procedure successfully completed.


The second procedure is run successfully though the table is dropped, because the procedure is ran with the rights of the owner in which the table is available

Shortcut for SQL*Plus

I was more intrested to have a short cut of sqlplus.exe (command line version of sqlplus) on desktop.
So, when i click on the short cut, that logs me to database with the specified schema details (Here we configure for specific database for a specific usename and password).

Here are the steps.
1. Right click on desktop-> New -> Shortcut
2. On Create Shortcut popup to fill 'Type the location of the item', click 'Browse' and point to the sqlplus.exe (In my system the path is : C:\Ora928\bin\sqlplus.exe)
3. Click 'Next' on the create short cut window
4. Give a name for 'Type a name for this shortcut'
5. Click 'Finish'
6. Right click on the shortcut -> Properties -> Shortcut (tab)->at 'Target' write the Database name, username and password
(e.g: C:\Ora928\bin\sqlplus.exe scott/tiger@orcl) -> Click 'Ok'

Thats it we are done to have a short cut that points to the specified database all the time