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

Intermediate

17-6.

When you want to call a packaged function (a function defined in the specification of a package) from within an SQL statement, what statement do you need to add to that specification? What is it for?

17-7.

What are the four different purity levels you can attempt to assert about a function?

17-8.

Add the necessary code to this package specification to assert all four purity levels for the total function:

CREATE OR REPLACE PACKAGE comp
IS
   FUNCTION total
      (sal_in IN NUMBER,
       comm_in IN NUMBER := NULL)
      RETURN NUMBER;
END;
/

17-9.

What is the minimum required purity level for a packaged function to be callable inside SQL?

17-10.

Under what circumstances do you need to assert the WNPS purity level for a packaged function or procedure?

17-11.

Which purity levels can you assert about the following function if you define it inside a package?

FUNCTION betwnstr
   (str IN VARCHAR2, startat IN INTEGER, endat IN INTEGER := NULL)
   RETURN VARCHAR2
IS
BEGIN
   RETURN SUBSTR (str, startat, endat-startat+1);
END;

17-12.

Which purity levels can you assert about the following function?

FUNCTION ename_from (empno_in IN emp.empno%TYPE)
   RETURN emp.ename%TYPE
IS
   retval emp.ename%TYPE;
BEGIN
   SELECT ename INTO retval
     FROM emp WHERE empno = empno_in;
   RETURN retval;
END;

17-13.

Suppose that you define the following package in the database:

PACKAGE who_is_paying_whom IS too_low CONSTANT NUMBER := 15000; /* This is above minimum wage! */ my_salary CONSTANT NUMBER := too_low; ceo_salary NUMBER := too_low * 250; END; ...
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