Oracle FAQ

1. Function is mainly used in the case where it must return a value. Where as a procedure may or may not return a value or may return more than one value using the OUT parameter. 2. Function can be called from SQL statements where as procedure can not be called from the sql statements
3. Functions are normally used for computations where as procedures are normally used for executing business logic.
4. You can have DML (insert,update, delete) statements in a function. But, you cannot call such a function in a SQL query.
5. Function returns 1 value only. Procedure can return multiple values (max 1024).
6.Stored Procedure: supports deferred name resolution. Example while writing a stored procedure that uses table named tabl1 and tabl2 etc..but actually not exists in database is allowed only in during creation but runtime throws error Function wont support deferred name resolution.
7.Stored procedure returns always integer value by default zero. where as function return type could be scalar or table or table values
8. Stored procedure is precompiled execution plan where as functions are not. 9.A procedure may modify an object where a function can only return a value The RETURN statement immediately completes the execution of a subprogram and returns control to the caller.
How will you delete duplicating rows from a base table? delete from table_name where rowid not in (select max(rowid) from table group by duplicate_values_field_name); ordelete duplicate_values_field_name dv from table_name ta where rowid <(select min(rowid) from table_name tb where ta.dv=tb.dv);
What is difference between SUBSTR and INSTR? SUBSTR returns a specified portion of a string eg SUBSTR('BCDEF',4) output BCDEINSTR provides character position in which a pattern is found in a string. eg INSTR('ABC-DC-F','-',2) output 7 (2nd occurence of '-') Find out nth highest salary from emp table SELECT DISTINCT (a.sal) FROM EMP A WHERE &N = (SELECT COUNT (DISTINCT (b.sal)) FROM EMP B WHERE a.sal<=b.sal); select sal,UPPER( (to_char(to_date(sal,'j'), 'jsp')))from emp;
--------- -----------------------------------------------------800 eight hundred1600 one thousand six hundred1250 one thousand two hundred fiftyIf you want to add some text like, Rs. Three Thousand only.SQL> select sal "Salary ",(' Rs. ' (to_char(to_date(sal,'j'), 'Jsp')) ' only.'))"Sal in Words" from emp
select * from emp where (rowid,1) in (select rowid, mod(rownum,2) from emp);Output:-135Even number of records:select * from emp where (rowid,0) in (select rowid, mod(rownum,2) from emp)Output:-246
Clustering is a method of storing tables that are intimately related and often joined together into the same area on disk. For example, instead of the BOOKSHELF table being in one section of the disk and the BOOKSHELF_AUTHOR table being somewhere else, their rows could be interleaved together in a single area, called a cluster.
The SELECT ... FOR UPDATE statement identifies the rows that will be updated or deleted then locks each row in the result set. This is useful when you want to base an update on the existing values in a row. In that case you must make sure the row is not changed by another user before the update.
FOR UPDATE clause in the cursor query is used to lock the affected rows when the cursor is opened. Because the Oracle Server releases locks at the end of the transaction you should not commit across fetches from an explicit cursor if FOR UPDATE is used.
What WHERE CURRENT OF clause does in a cursor? LOOPSELECT num_credits INTO v_numcredits FROM classesWHERE dept=123 and course=101;UPDATE studentsSET current_credits=current_credits+v_numcreditsWHERE CURRENT OF X;END LOOPCOMMIT;END;
Can cursor variables be stored in PL/SQL tables.If yes how. If not why? No, a cursor variable points a row which cannot be stored in a two-dimensional PL/SQL table.
No you can not store the cursor variable into pl/sql table
but yes the out put or cursor variable can be stored how:
set serveroutput on
declaretype v_table is table of emp%rowtype index by binary_integer;my_table v_table;i number;cursor c1 is select * from emp;begini := 1;open c1;loopfetch c1 into my_table(i);exit when c1%notfound;i := i + 1;end loop;for r in 1.. my_table.countloopdbms_output.put_line(my_table(r).empno);end loop;end;

When referencing the current row from an explicit cursor we use 'WHERE CURRENT OF'. it allows us to apply updates and deletes to the row currently been addressed.
NOTE: The cursor must have been declared with the FOR UPDATE clause so that the rows are locked on OPEN.
IN : lets you pass values to the subprogram being calledOUT: lets you return values to the caller of subprogramINOUT: lets you to pass values to the subprogram and return values to the caller of the subprogram.
What are ORACLE PRECOMPILERS? Using ORACLE PRECOMPILERS, SQL statements and PL/SQL blocks can be contained inside 3GL programs written in C,C++,COBOL,PASCAL, FORTRAN,PL/1 AND ADA.The Precompilers are known as Pro*C,Pro*Cobol,...This form of PL/SQL is known as embedded pl/sql,the language in which pl/sql is embedded is known as the host language. The prcompiler translates the embedded SQL and pl/sql ststements into calls to the precompiler runtime library.The output must be compiled and linked with this library to creater an executable.
Difference between database triggers and form triggers? Data base trigger(DBT) fires when a DML operation is performed on a data base table. Form trigger(FT) Fires when user presses a key or navigates between fields on the screenCan be row level or statement level No distinction between row level and statement level.Can manipulate data stored in Oracle tables via SQL Can manipulate data in Oracle tables as well as variables in forms.Can be fired from any session executing the triggering DML statements. Can be fired only from the form that define the trigger.Can cause other database triggers to fire.Can cause other database triggers to fire, but not other form triggers.
What is an UTL_FILE.What are different procedures and functions associated with it? UTL_FILE is a package that adds the ability to read and write to operating system files. Procedures associated with it are FCLOSE, FCLOSE_ALL and 5 procedures to output data to a file PUT, PUT_LINE, NEW_LINE, PUTF, FFLUSH.PUT, FFLUSH.PUT_LINE,FFLUSH.NEW_LINE. Functions associated with it are FOPEN, ISOPEN.
Differentiate between TRUNCATE and DELETE TRUNCATE deletes much faster than DELETETRUNCATE DELETE It is a DDL statement It is a DML statement It is a one way trip,cannot ROLLBACK One can Rollback Doesn't have selective features (where clause) Has Doesn't fire database triggers Does It requires disabling of referential constraints. Does not require
Difference between Optimizer=ALL_ROWS and Optimizer=CHOOSE Choose - Choose either Rule based or Cost based depend on theavailability of statistics. If statistics is available on the table it usesCBO and if not it uses RBO.
ALL_ROWS - Choose based optimization, statistics is needed. U couldpass as hint in ur query.
But in the majority of cases with optimizer_mode=choose ( and with a goodstatistics ) the CBO will be able to find a good execution plan foryoursqueries . Difference between HASH JOIN And MERGE JOIN Hash joing is implemented in CBO and comparatively faster. Merge joing is comparatively slow because both the row sources are to be sorted before merging. Difference Between Hash Join & Merge Join Merge Join :
Oracle performs a join between two sets of row data using the mergejoin algorithm. The inputs are two separate sets of row data. Output isthe results of the join. Oracle reads rows from both inputs in analternating fashion and merges together matching rows in order togenerate output. The two inputs are sorted on join column.
Hash Join :
Oracle performs a join between two sets of row data using hash joinalgorithm. Input and Output same as Merge Join. Oracle reads all rowsfrom the second input and builds a hash structure (like has table injava), before reading each row from the first input one at a time. Foreach row from the first input, the hash structure is probed and matchingrows generate output.
Can data be inserted/deleted/updated from CLOB,BLOB,NCLOB columns.If not why?
RE: Suppose there are two fields in table(Employee) say Name and Salary and there are in total 100 recor...-------------------------------------------------------------------------------- Question : Suppose there are two fields in table(Employee) say Name and Salary and there are in total 100 records in table. Now my query gives the name of an employee whose salary is 10th among 100 salaries. this query should be solve in SQL not in PL/SQL ?
ans: the command will be:
select name salary from employee where rowid ( select Max(rowid) from employee where rownum< 10);
where we use nested tables in our real time