ºÝºÝߣ

ºÝºÝߣShare a Scribd company logo

PL/SQL11g Question #1

•
•
Kai Liu
Kai Liu

The document discusses the types of data that can be used with the RESULT_CACHE keyword in Oracle 11g for PL/SQL functions. It states that large objects, object types, and cursor variables cannot be used in the parameter list or return clause of a result cached function. It lists the data types that can be returned but not used as parameters, such as associative arrays, nested tables, and records.

1 of 7
McDOUG Question #1 A key Oracle11g enhancement for PL/SQL is the function result cache. Which of the following types of data can be used in the parameter list or return clause (but not necessarily both) of a function defined using the RESULT_CACHE keyword?
Your Choices? 1. Cursor variables 2. Nested tables and varrays 3. Associative arrays 4. Object types 5. Large objects (BLOB, CLOB, BFILE) 6. Records (user-defined and %ROWTYPE) 7. Scalars (numbers, dates, strings, Booleans)
Answer You cannot use any of the following datatypes in either the parameter list and RETURN clause of a function that includes the RESULT_CACHE clause:  Large objects (BLOB, CLOB, BFILE)  Object types  Cursor variables  If you try to use these anywhere in the function header, you will get this compile error: PLS-00999: implementation restriction (may be temporary) RESULT_CACHE is disallowed on subprograms with IN parameter of (or
Continue  You can, on the other hand, return the following types of data (but they may not appear in the parameter list of the function):  Associative arrays  Nested tables  Varrays  Records (user-defined and %ROWTYPE)
Restrictions on Result-Cached Functions  To be result-cached, a function must meet all of the following criteria:  It is not defined in a module that has invoker's rights or in an anonymous block.  It is not a pipelined table function.  It has no OUT or IN OUT parameters.
Restrictions on Result-Cached Functions  No IN parameter has one of the following types: – BLOB – CLOB – NCLOB – REF CURSOR – Collection – Object – Record  The return type is none of the following: – BLOB – CLOB – NCLOB – REF CURSOR – Object – Record or PL/SQL collection that contains one of the preceding unsupported return types
Q&A  Use RESULT CACHE Wisely?  Thank You.

More Related Content

PL/SQL11g Question #1

  • 1. McDOUG Question #1 A key Oracle11g enhancement for PL/SQL is the function result cache. Which of the following types of data can be used in the parameter list or return clause (but not necessarily both) of a function defined using the RESULT_CACHE keyword?
  • 2. Your Choices? 1. Cursor variables 2. Nested tables and varrays 3. Associative arrays 4. Object types 5. Large objects (BLOB, CLOB, BFILE) 6. Records (user-defined and %ROWTYPE) 7. Scalars (numbers, dates, strings, Booleans)
  • 3. Answer You cannot use any of the following datatypes in either the parameter list and RETURN clause of a function that includes the RESULT_CACHE clause:  Large objects (BLOB, CLOB, BFILE)  Object types  Cursor variables  If you try to use these anywhere in the function header, you will get this compile error: PLS-00999: implementation restriction (may be temporary) RESULT_CACHE is disallowed on subprograms with IN parameter of (or
  • 4. Continue  You can, on the other hand, return the following types of data (but they may not appear in the parameter list of the function):  Associative arrays  Nested tables  Varrays  Records (user-defined and %ROWTYPE)
  • 5. Restrictions on Result-Cached Functions  To be result-cached, a function must meet all of the following criteria:  It is not defined in a module that has invoker's rights or in an anonymous block.  It is not a pipelined table function.  It has no OUT or IN OUT parameters.
  • 6. Restrictions on Result-Cached Functions  No IN parameter has one of the following types: – BLOB – CLOB – NCLOB – REF CURSOR – Collection – Object – Record  The return type is none of the following: – BLOB – CLOB – NCLOB – REF CURSOR – Object – Record or PL/SQL collection that contains one of the preceding unsupported return types
  • 7. Q&A  Use RESULT CACHE Wisely?  Thank You.