O'Reilly logo

Oracle PL/SQL Programming, Third Edition by Bill Pribyl, Steven Feuerstein

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Performing Essential PL/SQL Tasks

Let’s turn to the highlights of creating, running, deleting, and otherwise managing PL/SQL programs, using SQL*Plus as the front end. Don’t expect to be overwhelmed with detail here; treat this section as a glimpse of topics that will be covered in much greater detail in the chapters ahead.

Creating a Stored Program

To build your own stored PL/SQL program, you use one of SQL’s CREATE statements. For example, if you want to create a stored function named wordcount that counts words in a string, you can do so using a CREATE FUNCTION statement:

CREATE FUNCTION wordcount (str IN VARCHAR2)
   RETURN PLS_INTEGER
AS
   declare local variables go here
BEGIN
   implement algorithm here
END;
/

As with the simple BEGIN-END blocks shown earlier, running this statement from SQL*Plus requires a trailing slash on a line by itself.

Assuming that the database administrator has granted you Oracle’s CREATE PROCEDURE privilege, this statement will cause Oracle to compile and store this stored function in your schema; if your code compiles, you’ll probably see a success message such as:

Function created.

If a table or stored program named wordcount already exists in your Oracle schema, CREATE FUNCTION will fail with the error message ORA-00955: name is already used by an existing object. That is one of the reasons that Oracle provides the OR REPLACE OPTION, which you will want to use probably 99% of the time.

CREATE OR REPLACE FUNCTION wordcount (str IN VARCHAR2) RETURN PLS_INTEGER ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required