There are 2 tables, a and b having 3 rows each. What will be result on executing the following query? : SQL> select * from a,b
Can anybody tell me a sample oci function which will be able to call from tourbo cthanx!!
OCI means::oracle call interface.is a interface api used to access oracle database from C programs. Data Guard stands for it is multiple DB servers environment configured for Physical as well as Logic...
What is difference between % rowtype and type record ?
% rowtype is to be used whenever query returns a entire row of a table or view. type rec record is to be used whenever query returns columns of differenttable or views and variables. e.G. type r_emp is record (eno emp.Empno%...
row type is used to hold the total row values
%rowtype:- %rowtype means it is will fetch entire row values.
Type record: Type record means we can fetch record from more than one table.
In what ways can data be copied from one table to another?
You can copy the data from one table to another with the help of below Query:
Suppose dummy is the main table and we need to copy the data from dummy_120413 table.
Create tabel dummy_120413 as Select * from dummy;
How can we debug stored procedures in PL/SQL?
use following dbms package to get error line no in oracle 11g:
dbms_utility.format_error_backtrace
Using dbms_ouptput.put_line and logging different parts of the procedure.
Where would you use implicit & explicit cursors?
An implicit cursor is one created "automatically" for you by Oracle when you execute a query. It is simpler to code, but suffers from inefficiency (the ANSI standard specifies that it must fetch twic...
Implicit Cursor:- It is Oracle pre-defined type.. when we fetch only one record then we need to use implicit cursor.
Explicit cursor:- It is user defined cursor.. when we need to fetch more the one records we need to use explicit cursor..
Transaction management in triggers
Can we give commit or rollback within a trigger?
Using pragma autonomous_transaction we can use the commit and rollback.
Although you cant give commit or rollback in trigger directly, still there are ways to give them
You can use the autonomus transactions to do that
What is the impact of using a stored proc or a function on the performance? Which has a better performance? Also, why is it not possible to use dml in the functions called from a select query?
Implicit cursor is created automatically,opened automatically and closed automatically.it holds exactly one record. in case none or more than 1 record are retrieved, an exception is raised and that re...
A function is a store procedure indeed, and you can use autonomous transactions to do some DML
What is the data type of null?
Null is not a value and not a string. It is unknown it is maximum in descending order and minimum value in ascending order.
char(0)
What is difference between a procedure & function ?
a function is always returns a value using the return statement. a procedure may return one or more values through parameters or may not return at all.
Answered by: krishnaindia2007
View all answers by krishnaindia2007
Member Since Sep-2007 | Answered On : May 3rd, 2008
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.
What is the difference between system procedure and system function?.
Also Procedure can be called from triggers but Function cannot be called from trigger. As function returns value. And no value can be returned to trigger.
What are the advantages of bulk binding in PL/SQL?
Bulk binding reduces the context switches between SQL and pl/SQL engines. It enhances the performance but thr memory consumption would be high.
Difference between truncate and delete
truncate is a ddl...delete is dml
cannot rollback data in truncate...rollback data in delete
truncate -delete entire data not structure...it delete specific datas
By using delete we have to delete particular records only..
If we use truncate we cannot retrieve the data permanently...
What is an autonomous transaction?
The AUTONOMOUS_TRANSACTION pragma changes the way a subprogram works within a transaction. A subprogram marked with this pragma can do SQL operations and commit or roll back those operations, without ...
BY USING AUTONOMOUS TRANSACTION
WE GET THE FOLLOWING ADVANTAGES
WE PROCEDURE HAVING TRANSACTIONS STATEMENTS WE CALL PROCEDURE AUTOMATICALLY EFFECT THE ABOVE TRANSACTIONS ALSO TO OVERCOME THAT PROBLEM WE HAVE TO USE AUTONOMOUS TRANSACTION..
What are the types of collection in PL/SQL? What is the advantage of nested tables?
Varrays,nested tables,index by tables are oracle collection.Index by tables also known as associative array similar to hashtables in java. Varrays are like arrays in java static in nature need to d...
What is the use of nocopy parameter in Oracle procedure
Hi, what is nocopy parameter in Oracle procedure. What is the use of it. In which situation,we can use the nocopy parameter.Thanks,saravanan.P
In procedure,Function there are three types of parameter is there. eg-IN, OUT, INOUT. IN parameter is call by reference and OUT & INOUT are call by value. Always call by reference is faster than call ...
Pass by reference: "IN" Case
Pass by value : "Out" or "IN OUT" Case which have the overhead of copying the value to new procedure parameter.
In order to make to Pass by reference we will use NOCOPY
Pass by reference: "OUT NOCOPY" or "IN OUT NOCOPY" which deals which the actual reference value.
State the difference between implict and explict cursor's
Implicit cursors are automatically generated,when a sql statement is processed where as
Explicit cursors are user defined.
Implicit cursors returns only single record..where as explicit cursor multiple records. But it process record by record.
Can a type body be dropped without dropping the type specification?
When you drop a type body, the object type specification still exists, and you can re-create the type body. Prior to re-creating the body, you can still use the object type, although you cannot call the member functions.
With what ways can we find out instance locks?
v$lock contains details of locks
cursor for loop implicitly declares %rowtype as loop index,opens a cursor, fetches rows of values from active set into fields in the record and closeswhen all the records have been processed. eg. For emp_rec in c1 loop ...
Cursor for loop is the one by using this we need not to perform open, close, fetch operations of a cursor..
If we use explicit cursor we need to open the cursor and fetching the data and close the cursor.
If we use cursor for loop cursor will open the cursor and fetching data and close the cursor automatically.
Populating tables in Oracle 11g
How do you populate tables or data in Oracle 11g step by step?
We can use SQL Loader too
Depending on the situation one can use
external tables
or
UTL_FILE package
9 rows will be retreive
Cross join. Total 9 records will come