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

External Table and the whole story

Oracle External Tables is introduced in Oracle 9i. External tables are used to load data in to database. This is similar to oracle SQLLDR mechanism to load data.

In Oracle 10g, using external tables, we can also unload data.
There are the following scenarios where we cant use Oracle external tables.
1. If the source need to be loaded via network. External table requires the source file need to be in the database server. If not we need to use SQLLDR to load data.
2. LOBs cant be loaded using external tables

The following are the advantages of external tables over SQLLDR.
1. External tables open up the entire SQL set of functionality to data loading21
2. The ability to use complex WHERE conditions to selectivity load data. SQLLDR has WHEN clause but there are limitations like only = and 'AND' can be used. We cant use 'OR' or
other relational operator.
3. The ability to MERGE data.
4. Easier multiple inserts using INSERT.

The external table synax would be as below
CREATE DIRECTORY statements needed for files
------------------------------------------------------------------------
CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS 'D:\srikar\oracle'


CREATE TABLE statement for external table:
------------------------------------------------------------------------
CREATE TABLE "SYS_SQLLDR_X_EXT_EMP2"
(
"EMPNO" NUMBER,
"EMPNAME" VARCHAR2(2)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET WE8MSWIN1252
BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'empdata.bad'
LOGFILE 'logg.txt_xt'
READSIZE 1048576
FIELDS TERMINATED BY "," LDRTRIM
REJECT ROWS WITH ALL NULL FIELDS
(
"EMPNO" CHAR(255)
TERMINATED BY ",",
"EMPNAME" CHAR(255)
TERMINATED BY ","
)
)
location
(
'empdata.csv'
)
)REJECT LIMIT UNLIMITED


INSERT statements used to load internal tables:
------------------------------------------------------------------------
INSERT /*+ append */ INTO EMP2
(
EMPNO,
EMPNAME
)
SELECT
"EMPNO",
"EMPNAME"
FROM "SYS_SQLLDR_X_EXT_EMP2"

The above code is obtained from the control file in the command line with below command

D:\srikar\oracle>sqlldr scott/abcd@orcl control=emp_ctl.txt log=logg.txt external_table=generate_only

That produces the whole DDL in the logg.txt file
Here, 'external_table' takes 3 parameters
NOT_USED, GENERATE_ONLY, EXECUTE (Default NOT_USED).
'GENERATE_ONLY' creates the code as if it is executed (It does not execute the code)
'EXECUTE' executes the code hence loads the data.

If we look at the create external table script,

CREATE TABLE "SYS_SQLLDR_X_EXT_EMP2"
(
"EMPNO" NUMBER,
"EMPNAME" VARCHAR2(2)
)
ORGANIZATION external --> this is to make the table as external
(
TYPE oracle_loader
--> driver to load data,'DATA_PUMP' is used to unload data in 10g
DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 --> directory name created
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET WE8MSWIN1252
BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'empdata.bad'
LOGFILE 'logg.txt_xt'
READSIZE 1048576 --> Buffer size to load the input file if we use shared memory - --the size is taken from SGA else from PGA
FIELDS TERMINATED BY "," LDRTRIM --LDRTRIM is loader trim, we can also use ltrim, -- rtrim, lrtrim
REJECT ROWS WITH ALL NULL FIELDS
(
"EMPNO" CHAR(255)
TERMINATED BY ",",
"EMPNAME" CHAR(255)
TERMINATED BY ","
)
)
location
(
'empdata.csv'
)
)REJECT LIMIT UNLIMITED

If we explicitly create load data, first of all we need to create directory
the syntax as below

create directory dd as 'D:\srikar'
The oracle software is the one which loads/writes data. So the oracle software must have permission ('read' in windows, 'read and execute' in UNIX) to read the directory. 'Write' permission on the directory where we save 'log', 'bad' and 'discard' file

Unindexed foreign keys

Unindexed foreign keys makes the child table get locked and also it makes the performance down.
There is a very beautiful article written by Tom kyte on Unindexed foreign kyes.
The link is here

and if you are not able to access, the content goes here
-------------------------------------------------------------------------------
Unindexed Foreign Keys

Having Unindexed foreign keys can be a performance issue. There are two issues associated with unindexed foreign keys. The first is the fact that a table lock will result if you update the parent records primary key (very very unusual) or if you delete the parent record and the child's foreign key is not indexed.

To read about this issue, please see the Concepts Guide the section on Maintaining Data Integrity/Concurrency Control, Indexes, and Foreign Keys.

The second issue has to do with performance in general of a parent child relationship. Consider that if you have an on delete cascade and have not indexed the child table (eg: EMP is child of DEPT. Delete deptno = 10 should cascade to EMP. If deptno in emp is not indexed -- full table scan). This full scan is probably undesirable and if you delete many rows from the parent table, the child table will be scanned once for each parent row deleted.

Also consider that for most (not all, most) parent child relationships, we query the objects from the 'master' table to the 'detail' table. The glaring exception to this is a code table (short code to long description). For master/detail relationships, if you do not index the foreign key, a full scan of the child table will result.

So, how do you easily discover if you have unindexed foreign keys in your schema? This script can help. When you run it, it will generate a report such as:


SQL> @unindex

STAT TABLE_NAME COLUMNS COLUMNS
---- ------------------------------ -------------------- --------------------
**** APPLICATION_INSTANCES AI_APP_CODE
ok EMP DEPTNO DEPTNO



The **** in the first row shows me that I have an unindexed foreign key in the table APPLICATION_INSTANCES. The ok in the second row shows me I have a table EMP with an indexed foreign key.
------------------------------------------------------------------------

The script that Tom is used can be found here to find the unindexed foreign keys

select table_name,
constraint_name,
cname1 || nvl2(cname2, ',' || cname2, null) ||
nvl2(cname3, ',' || cname3, null) ||
nvl2(cname4, ',' || cname4, null) ||
nvl2(cname5, ',' || cname5, null) ||
nvl2(cname6, ',' || cname6, null) ||
nvl2(cname7, ',' || cname7, null) ||
nvl2(cname8, ',' || cname8, null) columns
from (select b.table_name,
b.constraint_name,
max(decode(position, 1, column_name, null)) cname1,
max(decode(position, 2, column_name, null)) cname2,
max(decode(position, 3, column_name, null)) cname3,
max(decode(position, 4, column_name, null)) cname4,
max(decode(position, 5, column_name, null)) cname5,
max(decode(position, 6, column_name, null)) cname6,
max(decode(position, 7, column_name, null)) cname7,
max(decode(position, 8, column_name, null)) cname8,
count(*) col_cnt
from (select substr(table_name, 1, 30) table_name,
substr(constraint_name, 1, 30) constraint_name,
substr(column_name, 1, 30) column_name,
position
from user_cons_columns) a,
user_constraints b
where a.constraint_name = b.constraint_name
and b.constraint_type = 'R'
group by b.table_name, b.constraint_name) cons
where col_cnt > ALL
(select count(*)
from user_ind_columns i
where i.table_name = cons.table_name
and i.column_name in (cname1, cname2, cname3, cname4, cname5,
cname6, cname7, cname8)
and i.column_position <= cons.col_cnt
group by i.index_name)


Thanks Tom for all the help

Select privileges on SYS schema views and objects

Here, I explain about 'select_catalog_role' and 'select any dictionary' privileges and the difference between them.

SQL> show user;
User is "scott"

SQL> select * from dba_objects where rownum<=1;
select * from dba_objects where rownum<=1
ORA-00942: table or view does not exist

SQL> select * from v$session where rownum<=1;
select * from v$session where rownum<=1
ORA-00942: table or view does not exist
Above, SCOTT was not able to access sys schema views (v$session, dba_objects)

SQL> conn system/YYYY@orcl
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as system

SQL> show user
User is "system"

SQL> grant select_catalog_role to scott;
Grant succeeded

[from scott schema]
SQL> select count(*) from v$session;
COUNT(*)
----------
44

SQL> select count(*) from dba_objects;

COUNT(*)
----------
72517

Now, scott is able to access sys schema views.

So, select_catalog_role grants user to select sys schema views (only views, no other objects like table)

[from scott schema]
SQL> select count(*) from sys.OBJ$;
select count(*) from OBJ$
ORA-00942: table or view does not exist

scott is not able to access sys schema objects

[from system schema]
SQL> revoke select_catalog_role from scott;
Revoke succeeded

SQL> grant select any dictionary to scott;
Grant succeeded

[from scott schema]
SQL> select count(*) from v$session;
COUNT(*)
----------
45

SQL> select count(*) from sys.obj$;

COUNT(*)
----------
73425

So, SCOTT is able to select rows from sys schema views and tables

So, the conclusion is,

'select_catalog_role' provides access to all SYS schema views only
'select any dictinaly' provides access to all SYS schema objects (views and tables etc..)