Skip to Main Content
Oracle PL/SQL Programming: A Developer's Workbook
book

Oracle PL/SQL Programming: A Developer's Workbook

by Steven Feuerstein, Andrew Odewahn
May 2000
Intermediate to advanced content levelIntermediate to advanced
594 pages
11h 32m
English
O'Reilly Media, Inc.
Content preview from Oracle PL/SQL Programming: A Developer's Workbook

Chapter 17. Calling Functions in SQL

Beginner

Q:

17-1.

Generally, the answer is “Yes! You can call your own PL/SQL functions from within a SQL statement.” There are, however, a number of restrictions and requirements governing how and when you can do this. The following exercises test your knowledge of these conditions.

Q:

17-2.

No. When executed inside a SQL statement, your functions can operate only on individual column values within a single row.

Q:

17-3.

First, here is the definition of the function:

CREATE OR REPLACE FUNCTION totcomp
   (sal_in IN PLS_INTEGER,
    comm_in IN NUMBER := NULL)
   RETURN NUMBER
IS
BEGIN
   RETURN (sal_in + NVL (comm_in, 0));
END;
/

Now you can apply that function back into the statement:

SELECT ename, totcomp (sal, comm) total
  FROM emp
 WHERE totcomp (sal, comm) > 1000;

Q:

17-4.

The statements are:

  1. True.

  2. False. The datatypes of the parameters and the RETURN clause of the function can only be valid SQL datatypes. You cannot, for example, return an index-by table (a.k.a., PL/SQL table). You also cannot pass in a Boolean value. The Boolean datatype is not currently supported in SQL.

  3. False. That’s downright silly. You can name your function whatever you want; it certainly cannot be the name of a table.

  4. False. You can only call one of your own functions if it is stored in the database; client-side functions are not accessible from within SQL.

  5. False. You can call a function in any non-DDL SQL statement, whether a query or a DML statement.

Q:

17-5.

No, you cannot directly access a procedure ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Oracle Database 12c PL/SQL Programming

Oracle Database 12c PL/SQL Programming

Michael McLaughlin
Oracle PL/SQL for DBAs

Oracle PL/SQL for DBAs

Arup Nanda, Steven Feuerstein
Oracle PL/SQL For Dummies

Oracle PL/SQL For Dummies

Michael Rosenblum, Paul Dorsey

Publisher Resources

ISBN: 9781449324070Supplemental ContentErrata Page