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