Procedure also written the values using out parameter than why we need functions?

Rakesh057

 Profile Answers by Rakesh057 Questions by Rakesh057

• Jul 1st, 2014

Because procedure is pre compiler and function is not pre compiler so that the reason we go for an calculation part in function

Madhav

• Jul 1st, 2014

Procedure also written in out parameter but we can not use procedure in Sql statement.

But if we wrote function and ita cant contain DML operation then we can access this function in SQL queries.

Answer Question  Select Best Answer

Raviteja

 Profile Answers by Raviteja Questions by Raviteja

• Aug 23rd, 2014

Instead of cursor is not there,, instead of trigger is used incase of complex views. when we use views with joins

Kumar

• Jul 25th, 2014

Its instead of trigger not cursor and it is used to apply trigger on views.

Answer Question  Select Best Answer

I have 3 tables , same no of columns ,i want to insert whole the data from all these table into a single table,which also having same no of column and the same data type.

sateeshchandra

• Oct 14th, 2014

"sql INSERT ALL INTO suppliers (supplier_id, supplier_name) VALUES (1000, IBM) INTO suppliers (supplier_id, supplier_name) VALUES (2000, Microsoft) INTO customers (customer_i...

J. Ali (PL/Sql)

• Nov 29th, 2013

INSERT INTO DYMMY (VAL)
SELECT COLUMN_VALUE val FROM table (tab_contents_nt (1, 2, 3))
UNION ALL
SELECT COLUMN_VALUE val FROM table (tab_contents_nt (4, 5, 6))
UNION ALL
SELECT COLUMN_VALUE val FROM table (tab_contents_nt (7, 8, 9))

Answer Question  Select Best Answer

In Emp table there is a column which consists of gender (Male or Female) ? How should one

Can update the Values of Male to Female and Female to Male with a single query ?

sandeep

• Nov 22nd, 2014

I need to update in the table not to select can u guys show me how to change directly in the database

NARSIMHA

• Nov 13th, 2014

SELECT DECODE(GENDER,M,F,F,M,NULL) FROM EMP

Answer Question  Select Best Answer

Hi ,can any one tel pls what is use ofprocedures and packages? why we use can anyone tell me with one real time example

HEMA NAGARAJU

• Dec 20th, 2014

Procedures are dataobjects which are stored in a database. We can define an operation in procedure and we call the procedure many times in the application. Packages are collection of subprograms, pro...

Answer Question  Select Best Answer

Write a query to retrieve one entire column data of multiple rows into one single column of single row?

hema nagaraju

• Dec 20th, 2014

SELECT listagg (ename, ,) within group( order by ename) FROM emp

Geet

• Dec 4th, 2014

Code
SELECT REPLACE(EMP_NAME , ,, ) "EMPLOYEE NAME"  
FROM (SELECT STRAGG(EMP_NAME) EMP_NAME 
FROM EMPLOYEE);

Answer Question  Select Best Answer

suvijyo

• Feb 2nd, 2015

"c declare s1 char(10):=name; s2 varchar2(10):=name; begin if s1=s2 then dbms_output.put_line(both are equal); else dbms_output.put_line(both are equal); end if; dbms_ou...

chinnu

• Jan 27th, 2015

Char datatype is fixed,but it cant be reused. eq: char(10) -- elena,first 5 characters will be filled remaining field are padded.
varchar2 datatype fixed,but it can be reused. eq: varchar2(10) -- elena, first 5 characters will be filled and remaining fields are reused.

Answer Question  Select Best Answer

ace1234567

 Profile Answers by ace1234567 Questions by ace1234567

• Mar 4th, 2015

Please find the template. Using out type parameter we can return more than one value from a function.

Code
CREATE OR REPLACE FUNCTION FUN_TEST(var1 VARCHAR2,var2 VARCHAR2,result OUT VARCHAR2) RETURN NUMBER
IS
.
.
BEGIN
.
.
END;

shyam

• Mar 2nd, 2015

Yes, we return more than one value in function using package. Example::

Code
create package pack
is
type mul_val is record
(emp_id employees.employee_id%type,
emp_name employees.last_name%type);
end pack;

and the return type of function is that pack type.....

Answer Question  Select Best Answer

Abhijit Musale

• Mar 23rd, 2015

We can create a table in procedure by executing a string statement using EXECUTE IMMEDIATE or using DBMS_SQL.EXECUTE.

veera

• Nov 25th, 2013

It is possible by using Dynamic SQL.
By using EXECUTE IMMEDIATE.

Answer Question  Select Best Answer

 %  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% type,ename emp ename %type);     e_rec emp% ROWTYPE     cursor...

Nupur

• Apr 30th, 2015

This is my understanding: Declare : eno employee.emp_id%type - specifically tells you that the variable type or data that it will be holding will be same as th e employee_id type in the employee tabl...

Nupur

• Apr 24th, 2015

When we declare a var as %row type, then the variable holds all the column type attributes. Developer does not need to know what the individual column types are. Type record is used when we declare a variable to be of the records of the table.?

Answer Question  Select Best Answer

Read Best Answer

Editorial / Best Answer

Answered by: answertoyourquery

• Nov 9th, 2005

---

Yes, we can use the TCL commands(commit/rollback) in the exception block of a stored procedure/function. The code in this part of the program gets executed like those in the body without any restriction. You can include any business functionality whenever a condition in main block(body of a proc/func) fails and requires a follow-thru process to terminate the execution gracefully!

Leena Roja

• Jul 13th, 2015

Correct Subhash..

Susil Kumar Nagarajan

• Jan 21st, 2012

Yes. You can very well use COMMIT or ROLLBACK in exception block. But it is not a good idea to use, as it will commit the previous transactions in the same PLSQL block. Make use of creating SAVEPOINTS and commiting them when required.

Improve Answer

Ram

• Oct 29th, 2015

You can create n number of triggers on a table but maximum types of triggers you can create are 12.

Ratnesh gupta

• Apr 2nd, 2015

There is no technical limit on the number of triggers you define (or, at least, not one that any rational person has ever hit). It generally wouldnt make sense to have more than one trigger of a given...

Answer Question  Select Best Answer

What are the advantages of bulk binding in PL/SQL?

sachin rasal

• Nov 12th, 2015

A new feature called "bulk binds" was added to PL/SQL back in Oracle 8i. Bulk binds enable a PL/SQL program to fetch many rows from a cursor in one call instead of fetching one row at a time. Bulk bin...

amala

• Jun 24th, 2015

It retreives multiple rows with a single fetch...It increases the performance by reducing no.of context switches

Answer Question  Select Best Answer

Read Best Answer

Editorial / Best Answer

krishnaindia2007  

 Profile Answers by krishnaindia2007 Questions by krishnaindia2007

• Member Since Sep-2007 | May 6th, 2008

---

A trigger may be a
1. DML Trigger on tables
2. Instead of triggers on views
3. System triggers on database or schema

Based on the way it executes statements  triggers are of two types
1. Statement leve trigger
2.  Row level trigger

A trigger fires for three actions
1. Insert
2. Delete 
3.Update

and the trigger can the fired
1. Before action
2. After action.

Pradeep

• Feb 20th, 2016

Basically 5 main types in Oracle Triggers
1. DML Trigger (3 statement * 2 timing * 2 level = 12 types).
2. DDL Trigger.
3. Instead of Trigger (Fired Complex View).
4. System Trigger.
5. Compound Trigger (Introduced by 11g)

tarini sankar das

• Feb 7th, 2014

Actual answer is we have 14 types of triggers in pl sql. For statement level :- 1-before insert 2-before update 3-before delete 4-after insert 5-after update 6-after delete For row level :- 7-before ...

Improve Answer

Pritty

• Feb 29th, 2016

The GROUP BY keyword helps to retrieve the unique values without using DISTINCT keyword.

Malyadri M

• Jun 22nd, 2015

Code
SELECT ROWID, E.*
FROM EMP E
WHERE ROWID = ( SELECT MAX(ROWID)
                           FROM EMP1 E1
                           WHERE E.ENO = E1.ENO
                        );

Answer Question  Select Best Answer

What are the advantages of packages over standalone procedure ?

Habib Ali

• May 9th, 2016

Packages can be cached inside the SGA, resulting in faster execution than a normal stored procedure.

abhijit musale

• Feb 18th, 2015

You can wrap/encrypt the package code .

Answer Question  Select Best Answer

Habib Ali

• May 9th, 2016

Stand Alone Proc: Block of PLSQL code but not defined in the data dictionary, hence not reusable

Stored Procedure: Block of PLSQL code Defined in data dictionary, implements reusability. Check in user_source table

Praveen kumar

• Jan 8th, 2016

Both are same

Answer Question  Select Best Answer

Is Dual Table updatable? If we update, will it impact the health of the database?

Habib Ali

• May 10th, 2016

DUAL table is owned by SYS, and is updatable only under SYS account. But never make any changes to DUAL, as it is frequently used by Oracle optimizer to test Execution Plans.

vishal

• Mar 29th, 2016

No we can't update dual table because it is a dummy table. But it is possible to create a table named dual and you can insert, update and delete that table.

Answer Question  Select Best Answer

Habib Ali

• May 10th, 2016

PRAGMA is instruction to compiler
AUTONOMOUS TRANSACTION means running an SQL transaction inside a PLSQL block of code independently i.e Making changes to DB regardless of other transactions & their locks/latches

sujata

• Jan 19th, 2016

Pragma AUTONOMOUS_TRANSACTION instructs the compiler to treat the PL/SQL block following the pragma as autonomous (independent) from the calling transaction. Pragma can be applied on:- 1) Anonymous PL...

Answer Question  Select Best Answer

Delete duplicate records without using sub query? Is That possible

Aak

• Sep 15th, 2016

Use a temp table, store distinct rows. Either delete the old table and rename this new table with the old table name or truncate old table and insert rows from temp table to the old table.

kadari ashok

• Jul 14th, 2016

We can do through co-related sub query

Answer Question  Select Best Answer