Writing a function in plsql

Question

I'm querying the DB and need to parse one of the fields for specific values (using string functions). So I figured it'd be best to write a function for it. I've never written a function before in plsql, so I decided to look at some examples.

I got a copy of a simple "square" function which takes a number and returns the square of it, but am not sure how to call it from the SQL statement.

I only have read access. Will I be able to write functions and use them to retrieve the data I need? And if so, how?

netcoder · Accepted Answer · 2011-06-07 17:58:58Z

3

To be able to create a function your user needs the CREATE PROCEDURE privilege granted to it by the DBA:

grant create procedure to myschema;

If you have the privilege then you can create a function like this

create function square(n in number) return number
is
  return n*n;
end;

And you can call it from SQL like this:

select num, square(num)
from mytable;

Note: in Oracle it is usually preferred to create functions in packages, in which case the calling SQL would be like:

select num, mypackage.square(num)
from mytable;

edited Jun 7, 2011 at 17:58

netcoder

68k19 gold badges129 silver badges142 bronze badges

answered Jun 7, 2011 at 15:37

Tony Andrews

133k23 gold badges230 silver badges266 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

MxLDevs Over a year ago

Hmm so I will need more privileges for that. Guess I will have to make do with what I have.

Collectives™ on Stack Overflow

Writing a function in plsql

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related