Explain the difference between a FUNCTION, PROCEDURE and
PACKAGE.

Answers were Sorted based on User's Feedback



Explain the difference between a FUNCTION, PROCEDURE and PACKAGE...

Answer / esakkiraja

One more diff between function and procedure

Functions can be used in sql query but procedure we can't
used in sql query

For eg:
tax is a function we can use like

select ename,tax(sal) from emp where ename='saki';


In this we can use.....

Is This Answer Correct ?    64 Yes 6 No

Explain the difference between a FUNCTION, PROCEDURE and PACKAGE...

Answer / aleena

PROCEDURE:

cannot return a value

SYNTAX:
CREATE PROCEDURE procedure_name [argument datatype]
AS
BEGIN
sql statements
END;
/

FUNCTION:

returns a value

SYNTAX:

CREATE FUNCTION function_name [argument[IN] datatype]
RETURN datatype
AS
BEGIN
sql statements
END;
/


PACKAGES

Pakages are groups of functions,procedures,sql blocks
goruped together in a single unit.

there are two parts to creating a PACKAGE --1>CREATE
PACKAGE & 2> CREATE PACKAGE BODY

SYNTAX :
CREATE PACKAGE :

CREATE PACKAGE package_name
AS

FUNCTION function_name(datatype);
PROCEDURE procedure_name (datatype);
.......
.......(sql/plsql statements )
........
END;

CREATE PACKAGE BODY :

CREATE PACKAGE BODY packagebody_name
AS
FUNCTION function_name (datatype)
RETURN datatype
AS
.....
.....
.....
END function_name;
PROCEDURE procedure_name (datatype)
AS
......
.....
.....
END procedure_name
END PACKAGE;



Is This Answer Correct ?    46 Yes 5 No

Explain the difference between a FUNCTION, PROCEDURE and PACKAGE...

Answer / suresh

Function must return one value
Procedure may or may not return one or more values.
Can call functions in Sql statements
we can't call a procedure in sql statements
Functions can not return images
procedure returns images

Is This Answer Correct ?    24 Yes 1 No

Explain the difference between a FUNCTION, PROCEDURE and PACKAGE...

Answer / subhasish dutta

If no write (i.e. insert,delete,update,merge) operation is
made in a function then that function can be used sql
query. if only read operation (i.e. Select) is used in a
function, that can be called from sql query.

Is This Answer Correct ?    19 Yes 2 No

Explain the difference between a FUNCTION, PROCEDURE and PACKAGE...

Answer / deepak

Note:- procedure may or may not return value.function must
return value
Procedure return one or more value & function return only
single value.
Pakages are groups of functions,procedures,sql blocks
goruped together in a single unit.
Package & packagebody are two different thngs. Variable
defined in packages are global & they can use in anywhere
in packagebody. Variable defined in packagebody have
limited scope. & they can used in defined limit only.

Is This Answer Correct ?    16 Yes 1 No

Explain the difference between a FUNCTION, PROCEDURE and PACKAGE...

Answer / rashmipriya

procedure call is pl/sql statement by itself.

function call is part of an expression.

Is This Answer Correct ?    16 Yes 3 No

Explain the difference between a FUNCTION, PROCEDURE and PACKAGE...

Answer / karunakar remala

Procedure and functions contains scope with in package
only..but coming to it contains scope out side of the
package also.i.e we can access package elements from out
side of the package also.

Is This Answer Correct ?    11 Yes 3 No

Explain the difference between a FUNCTION, PROCEDURE and PACKAGE...

Answer / ezhumalai

A Procedure that performs an action.
A Function that computes a value.

We can call a Function in SELECT Statement.
We can't call a Procedure in SELECT Statement.

Is This Answer Correct ?    5 Yes 3 No

Explain the difference between a FUNCTION, PROCEDURE and PACKAGE...

Answer / san

can access package elements from out
side of the package only if its declare in package specification

Is This Answer Correct ?    4 Yes 2 No

Explain the difference between a FUNCTION, PROCEDURE and PACKAGE...

Answer / kalaiselvan.j

The Function will return a value where procedure wont.

Is This Answer Correct ?    13 Yes 31 No

Post New Answer

More Oracle AllOther Interview Questions

A scenario for amount correction & how to use amount correction functionality in AP invoices

0 Answers  


What view would you use to determine free space in a tablespace?

0 Answers  


HOW TO SAVE UNIVERSES AND DOCUMENTS IN THE REPOSITORY?

0 Answers  


What are different types of transactions in ar? : oracle accounts receivable

0 Answers  


Differentiate between earned discounts and unearned discounts? : oracle accounts receivable

0 Answers  






what is semphore explian it?

0 Answers   IBM,


how to write a query without using where? Example- select * from employee where empno=10 I need to write this query without where clause.

1 Answers   Alshaya,


i have marks in table,how to find total?

1 Answers  


what is correlated subquery

3 Answers  


Should you take the backup of Logfiles if the database is running in ARCHIVELOG mode?

4 Answers  


How can you rebuild an index?

1 Answers  


CREATE OR REPLACE procedure XXPROC_OPENINT(errbuf OUT VARCHAR2, retcode OUT VARCHAR2) AS --Cursor Declaration cursor inv_cur IS SELECT INVI.INVOICE_ID ,INVI.INVOICE_TYPE_LOOKUP_CODE ,INVI.INVOICE_DATE ,INVI.VENDOR_ID ,INVI.VENDOR_SITE_ID ,INVI.INVOICE_AMOUNT ,INVI.INVOICE_CURRENCY_CODE ,INVI.STATUS ,INVI.GROUP_ID ,INVI.SOURCE ,INVI.ORG_ID ,INVI.DESCRIPTION ,INVI.CREATION_DATE ,INVI.CREATED_BY ,INVL.INVOICE_ID ,INVL.LINE_NUMBER ,INVL.LINE_TYPE_LOOKUP_CODE ,INVL.DESCRIPTION ,INVL.ORG_ID ,INVL.INVENTORY_ITEM_ID ,INVL.ACCOUNTING_DATE ,INVL.AMOUNT ,INVL.CREATION_DATE ,INVL.CREATED_BY ,INVL.PO_HEADER_ID ,INVL.PO_LINE_LOCATION_ID ,INVL.ITEM_DESCRIPTION FROM XXAP_INVOICES_INTERFACE_STG INVI ,AP_INVOICE_LINES_INTERFACE_STG INVL WHERE INVI.INVOICE_ID=INVL.INVOICE_ID AND INVI.ORG_ID=INVL.ORG_ID; l_currency_code varchar2(25); l_flag varchar2(2); l_error_msg varchar2(100); l_err_msg varchar2(100); l_err_flag varchar2(10); l_count number(9) default 0; BEGIN for rec_cur in inv_cur loop l_count:=l_count+1; --Currency Code Validation BEGIN SELECT currency_code INTO l_currency_code FROM FND_CURRENCIES WHERE currency_code ='USD'; EXCEPTION WHEN others THEN l_flag:='E'; l_error_msg:='Currency Code Does not Exists'; fnd_file.put_line(fnd_file.log,'Inserting Data Into The Interface Table'||'-'||l_count||' '||l_error_msg); END; IF l_flag!='E' THEN fnd_file.put_line(fnd_file.log,'Inserting Data Into The Interface Table'); INSERT INTO AP_INVOICES_INTERFACE( INVOICE_ID ,INVOICE_TYPE_LOOKUP_CODE ,INVOICE_DATE ,VENDOR_ID ,VENDOR_SITE_ID ,INVOICE_AMOUNT ,INVOICE_CURRENCY_CODE ,STATUS ,GROUP_ID ,SOURCE ,ORG_ID ,DESCRIPTION ,CREATION_DATE ,CREATED_BY) VALUES (rec_cur.INVOICE_ID ,rec_cur.INVOICE_TYPE_LOOKUP_CODE ,rec_cur.INVOICE_DATE ,rec_cur.VENDOR_ID ,rec_cur.VENDOR_SITE_ID ,rec_cur.INVOICE_AMOUNT ,rec_cur.INVOICE_CURRENCY_CODE ,rec_cur.STATUS ,rec_cur.GROUP_ID ,rec_cur.SOURCE ,rec_cur.ORG_ID ,rec_cur.DESCRIPTION ,rec_cur.CREATION_DATE ,rec_cur.CREATED_BY); INSERT INTO ap_invoice_lines_interface ( INVOICE_ID ,LINE_NUMBER ,LINE_TYPE_LOOKUP_CODE ,DESCRIPTION ,ORG_ID ,INVENTORY_ITEM_ID ,ACCOUNTING_DATE ,AMOUNT ,CREATION_DATE ,CREATED_BY ,PO_HEADER_ID ,PO_LINE_LOCATION_ID,ITEM_DESCRIPTION) VALUES (rec_cur.INVOICE_ID ,rec_cur.LINE_NUMBER ,rec_cur.LINE_TYPE_LOOKUP_CODE ,rec_cur.DESCRIPTION ,rec_cur.ORG_ID ,rec_cur.INVENTORY_ITEM_ID ,rec_cur.ACCOUNTING_DATE ,rec_cur.AMOUNT ,rec_cur.CREATION_DATE ,rec_cur.CREATED_BY ,rec_cur.PO_HEADER_ID ,rec_cur.PO_LINE_LOCATION_ID ,rec_cur.ITEM_DESCRIPTION); END IF; l_flag:=NULL; l_err_MSG:=NULL; END LOOP; COMMIT; END XXPROC_OPENINT; / PLS-00402: alias required in SELECT list of cursor to avoid duplicate column names

0 Answers  


Categories
  • Oracle General Interview Questions Oracle General (1789)
  • Oracle DBA (Database Administration) Interview Questions Oracle DBA (Database Administration) (261)
  • Oracle Call Interface (OCI) Interview Questions Oracle Call Interface (OCI) (10)
  • Oracle Architecture Interview Questions Oracle Architecture (90)
  • Oracle Security Interview Questions Oracle Security (38)
  • Oracle Forms Reports Interview Questions Oracle Forms Reports (510)
  • Oracle Data Integrator (ODI) Interview Questions Oracle Data Integrator (ODI) (120)
  • Oracle ETL Interview Questions Oracle ETL (15)
  • Oracle RAC Interview Questions Oracle RAC (93)
  • Oracle D2K Interview Questions Oracle D2K (72)
  • Oracle AllOther Interview Questions Oracle AllOther (241)