OraFAQ Forum: SQL & PL/SQL » Difference between Function and procedure

Home » SQL & PL/SQL » SQL & PL/SQL » Difference between Function and procedure

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Difference between Function and procedure [message #221746]

Wed, 28 February 2007 05:03

friendarora
Messages: 24
Registered: February 2007

Junior Member

Hi,

Is there any functionality which is not possible in Function and can only be achieved by a procedure.

Report message to a moderator

Re: Difference between Function and procedure [message #221752 is a reply to message #221746]

Wed, 28 February 2007 05:20

Maaher
Messages: 7065
Registered: December 2001

Senior Member

No. Did you bother to search the board?

MHE

Report message to a moderator

Re: Difference between Function and procedure [message #223282 is a reply to message #221746]

Thu, 08 March 2007 05:05

atulrsingh
Messages: 55
Registered: November 2006
Location: Mumbai

Member

U Can execute DDL Commands in Procedures

Report message to a moderator

Re: Difference between Function and procedure [message #223283 is a reply to message #223282]

Thu, 08 March 2007 05:12

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

Can you really....

Go on then - post a working test case showing that you can execute DDL code in a procedure without using dynamic SQL.

(Hint: the previous post was totally wrong. The only difference between Functions and Procedures is that functions have a RETURN value, and can (sometimes) be used in SQL.
You can't execute DDL in procedures without using dynamic SQL, and you can use dynamic SQL in functions too).

Report message to a moderator

Re: Difference between Function and procedure [message #223290 is a reply to message #221746]

Thu, 08 March 2007 05:40

atulrsingh
Messages: 55
Registered: November 2006
Location: Mumbai

Member

Sorry, I was wrong...

U have to use the Dynamic Sql for the same.

Example

CREATE OR REPLACE PROCEDURE EXAMPLE(A IN VARCHAR2)
IS
BEGIN

EXECUTE IMMEDIATE ' CREATE TABLE '||A|| '(ABC NUMBER(10)) ';

END;

--

EXECUTE EXAMPLE('NEW_TABLE');

Report message to a moderator

Re: Difference between Function and procedure [message #223300 is a reply to message #223290]

Thu, 08 March 2007 06:46

Maaher
Messages: 7065
Registered: December 2001

Senior Member

You can accomplish the same by using a function. You could make it return an error code ( 0 = all went well ).

MHE

Report message to a moderator

Previous Topic:	function returned without value error
Next Topic:	Query issue

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Dec 07 06:12:13 CST 2024