Find the current session id :

SELECT sid FROM v$mystat WHERE ROWNUM<=1
or
SELECT DISTINCT sid FROM v$mystat
or
SELECT sid FROM v$session WHERE audsid=sys_context('userenv','sessionid')
or
SELECT sid FROM V$SESSION WHERE audsid = USERENV('sessionid');

Generating rows using level keyword

A nice way of generating rows using level keyword.

with t as
(select 'Hello hi' stri
from dual
)
select substr(stri, i, 1)
from t
, (select level i
from t
connect by level <= length(t.stri)
)

or

with strr as
(select 'Hello Hi' str from dual)
select level,substr(str,level,1) from strr
connect by level

How DDL works

How DDL works

DDL is executed logically like this:
begin
COMMIT;
do the ddl;
COMMIT;
exception
when others then
ROLLBACK;
RAISE;
end;

Calling procedure form the SQL

Calling procedure form the SQL:
Can we call a procedure from SQL

The answer is no we cant, we need to use function for that.

Nulls at the top or bottom

Nulls at the top or bottom

order by asc --> by default arranges the null values at the bottom
order by desc --> by default arranges the null values at the top
we place null values at the top when 'order by asc'
and nulls at the bottom when 'order by desc' is used by using the keyword 'nulls first or nulls last' in the order by caluse

Purity level of functions

Purity level of functions:

CREATE OR REPLACE FUNCTION MY_FUNC RETURN INTEGER AS
BEGIN
MY_PACKAGE.MY_INTEGER := NVL(MY_PACK.MY_INTEGER,0) + 1;
RETURN(MY_PACK.MY_INTEGER);
END;

Then we use it in the following query:

SELECT a.*, my_func()
FROM my_table;

It is pretty easy to see that each time we run this query in a single session, we get a different result. This means the function is NON-DETERMINISTIC. The thing that makes it non-deterministic is that it UPDATES the PACKAGE STATE.

This is usually fine though, because SYSDATE is also non-deterministic, and we can use that in most places.

However, there are restrictions with non-deterministic functions. For example, you cannot use them in a Materialised View.

The Purity Level of a function tells us what it does and does not do, such as read/write database state, or read/write package state. Generally, a function that writes neither database nor package state is usually deterministic (unless it uses SYSDATE or some other non-deterministic feature).

Functions can be used in Materialised Views, Index Types, Function-based indexes, user-defined aggregation functions, and probably lots of other stuff.

Depending on the purity-level of the function, it may be banned from use in one or more of these areas, or it may be permitted in a limited fashion (eg. may not parallelise).

Deterministic function and Non Deerministic function

Deterministic function and Non Deerministic function
Deterministic functions always return the same result any time they are called with a specific set of input values.

Nondeterministic functions may return different results each time they are called with a specific set of input values.