The return type for a cursor must be a record type.It can be declared explicitly as a user-defined or %ROWTYPE can be used. eg TYPE t_studentsref IS REF CURSOR
select level, min('col_name') from my_table where level = '&n' connect by prior ('col_name') <
group by level;
Given a table called emp with the following columns:
-- id number
-- name varchar2(20)
-- sal number
-- For the second lowest salary:
-- select level, min(sal) from emp
-- where level=2
-- connect by prior sal < sal
-- group by level
select max(sal)"nth min sal" from(select distinct sal from emp order by sal) where rownum<=&N
Oracle does not allow a user to specifically locate tables, since that is a part of the function of the RDBMS. However, for the purpose of increasing performance, oracle allows a developer to create a CLUSTER. A CLUSTER provides a means for storing data from different tables together for faster retrieval than if the table placement were left to the RDBMS.
it's a temporary and logical memory allocation where we can put several tables.
Functions are named PL/SQL blocks that return a value and can be called with arguments procedure a named block that can be called with parameter. A procedure all is a PL/SQL statement by itself, while a Function call is called as part of an expression.
Function and Procedure both are PL/SQL blocks, main difference between function and procedure is -
Function has to return some value using return clause whereas procedure may or may not return any value( no out parameter).
We can use functions in SQL query but can't use procedure.
the following data types are used to store graphics or binary data
2)binary large object(BLOB)