#

| Chapter 1 Oracle PL/SQL Built-ins Pocket Reference |

## 1.5 RESTRICT REFERENCES Pragmas

A pragma is a directive to the PL/SQL compiler. Pragmas pass information to the compiler; they are processed at compile time but do not execute. If you include a call to a built-in package in a SQL statement, you must include a RESTRICT REFERENCES pragma in your code. This pragma tells the compiler the purity level (freedom from side effects) of a packaged program. The purity levels available are:

- WNDS -- Writes no database state

- RNDS -- Reads no database state

- WNPS -- Writes no package state

- RNPS -- Reads no package state

DBMS_LOB Programs | WNDS | RNDS | WNPS | RNPS
--- | --- | --- | --- | ---
COMPARE | ✓ | ✓ | ✓ | ✓
FILEEXISTS | ✓ | ✓ | ✓ | ✓
FILEISOPEN | ✓ | ✓ | ✓ | ✓
GETLENGTH | ✓ | ✓ | ✓ | ✓
INSTR | ✓ | ✓ | ✓ | ✓
SUBSTR | ✓ | ✓ | ✓ | ✓

DBMS_OUTPUT Programs | WNDS | RNDS | WNPS | RNPS
--- | --- | --- | --- | ---
DISABLE | ✓ | ✓ |  |
ENABLE | ✓ | ✓ |  |
GET_LINE | ✓ | ✓ |  |
GET_LINES | ✓ | ✓ |  |
NEW_LINE | ✓ | ✓ |  |
PUT | ✓ | ✓ |  |
PUT_LINE | ✓ | ✓ |  |

DBMS_PIPE Programs | WNDS | RNDS | WNPS | RNPS
--- | --- | --- | --- | ---
CREATE_PIPE | ✓ | ✓ |  |
NEXT_ITEM_TYPE | ✓ | ✓ |  |
PACK_MESSAGE | ✓ | ✓ |  |
PACK_MESSAGE_RAW | ✓ | ✓ |  |
PACK_MESSAGE_ROWID | ✓ | ✓ |  |
PURGE | ✓ | ✓ |  |
RECEIVE_MESSAGE | ✓ | ✓ |  |
REMOVE_PIPE | ✓ | ✓ |  |
RESET_BUFFER | ✓ | ✓ |  |
SEND_MESSAGE | ✓ | ✓ |  |
UNIQUE_SESSION_NAME | ✓ | ✓ | ✓ |
UNPACK_MESSAGE | ✓ | ✓ |  |
UNPACK_MESSAGE_RAW | ✓ | ✓ |  |
UNPACK_MESSAGE_ROWID | ✓ | ✓ |  |

DBMS_ROWID Programs | WNDS | RNDS | WNPS | RNPS
--- | --- | --- | --- | ---
ROWID_BLOCK_NUMBER | ✓ | ✓ | ✓ | ✓
ROWID_CREATE | ✓ | ✓ | ✓ | ✓
ROWID_INFO | ✓ | ✓ | ✓ | ✓
ROWID_OBJECT | ✓ | ✓ | ✓ | ✓
ROWID_RELATIVE_FNO | ✓ | ✓ | ✓ | ✓
ROWID_ROW_NUMBER | ✓ | ✓ | ✓ | ✓
ROWID_TO_ABSOLUTE_FNO | ✓ |  | ✓ | ✓
ROWID_TO_EXTENDED | ✓ |  | ✓ | ✓
ROWID_TO_RESTRICTED | ✓ | ✓ | ✓ | ✓
ROWID_TYPE | ✓ | ✓ | ✓ | ✓
ROWID_VERIFY | ✓ |  | ✓ | ✓

DBMS_SESSION Program | WNDS | RNDS | WNPS | RNPS
--- | --- | --- | --- | ---
UNIQUE_SESSION_ID | ✓ | ✓ | ✓ |

DBMS_SQL Programs | WNDS | RNDS | WNPS | RNPS
--- | --- | --- | --- | ---
BIND_ARRAY | ✓ |  |  |
BIND_VARIABLE | ✓ |  |  |
BIND_VARIABLE_CHAR | ✓ |  |  |
BIND_VARIABLE_RAW | ✓ |  |  |
BIND_VARIABLE_ROWID | ✓ |  |  |
CLOSE_CURSOR | ✓ | ✓ |  |
COLUMN_VALUE | ✓ | ✓ |  |
COLUMN_VALUE_CHAR | ✓ | ✓ |  |
COLUMN_VALUE_LONG | ✓ | ✓ |  |
COLUMN_VALUE_RAW | ✓ | ✓ |  |
COLUMN_VALUE_ROWID | ✓ | ✓ |  |
DEFINE_ARRAY | ✓ | ✓ |  |
DEFINE_COLUMN | ✓ | ✓ |  |
DEFINE_COLUMN_CHAR | ✓ | ✓ |  |
DEFINE_COLUMN_LONG | ✓ | ✓ |  |
DEFINE_COLUMN_RAW | ✓ | ✓ |  |
DEFINE_COLUMN_ROWID | ✓ | ✓ |  |
DESCRIBE_COLUMNS | ✓ |  |  |
EXECUTE_AND_FETCH | ✓ |  |  |
FETCH_ROWS | ✓ |  |  |
IS_OPEN | ✓ | ✓ |  |
LAST_ERROR_POSITION | ✓ | ✓ |  |
LAST_ROW_COUNT | ✓ | ✓ |  |
LAST_ROW_ID | ✓ | ✓ |  |
LAST_SQL_FUNCTION_CODE | ✓ | ✓ |  |
OPEN_CURSOR | ✓ | ✓ |  |
VARIABLE_VALUE | ✓ | ✓ |  |
VARIABLE_VALUE_CHAR | ✓ | ✓ |  |
VARIABLE_VALUE_RAW | ✓ | ✓ |  |
VARIABLE_VALUE_ROWID | ✓ | ✓ |  |

DBMS_STANDARD Programs | WNDS | RNDS | WNPS | RNPS
--- | --- | --- | --- | ---
DELETING | ✓ |  | ✓ | ✓
INSERTING | ✓ |  | ✓ | ✓
RAISE_APPLICATION_ERROR | ✓ | ✓ | ✓ | ✓
UPDATING | ✓ |  | ✓ | ✓

DBMS_UTILITY Programs | WNDS | RNDS | WNPS | RNPS
--- | --- | --- | --- | ---
DATA_BLOCK_ADDRESS_BLOCK | ✓ | ✓ | ✓ | ✓
DATA_BLOCK_ADDRESS_FILE | ✓ | ✓ | ✓ | ✓
GET_HASH_VALUE | ✓ | ✓ | ✓ | ✓
MAKE_DATA_BLOCK_ADDRESS | ✓ | ✓ | ✓ | ✓
PORT_STRING | ✓ | ✓ | ✓ | ✓

UTL_RAW Programs | WNDS | RNDS | WNPS | RNPS
--- | --- | --- | --- | ---
BIT_AND | ✓ | ✓ | ✓ | ✓
BIT_COMPLEMENT | ✓ | ✓ | ✓ | ✓
BIT_OR | ✓ | ✓ | ✓ | ✓
BIT_XOR | ✓ | ✓ | ✓ | ✓
CAST_TO_RAW | ✓ | ✓ | ✓ | ✓
CAST_TO_VARCHAR2 | ✓ | ✓ | ✓ | ✓
COMPARE | ✓ | ✓ | ✓ | ✓
CONCAT | ✓ | ✓ | ✓ | ✓
CONVERT | ✓ | ✓ | ✓ | ✓
COPIES | ✓ | ✓ | ✓ | ✓
LENGTH | ✓ | ✓ | ✓ | ✓
OVERLAY | ✓ | ✓ | ✓ | ✓
REVERSE | ✓ | ✓ | ✓ | ✓
SUBSTR | ✓ | ✓ | ✓ | ✓
TRANSLATE | ✓ | ✓ | ✓ | ✓
TRANSLITERATE | ✓ | ✓ | ✓ | ✓
XRANGE | ✓ | ✓ | ✓ | ✓

1.4 Built-in Functions |  | 1.6 Nonprogram Elements

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.