Skip navigation.

Blogs

Oracle REF CURSORs

Below is an example of how to setup a REF CURSOR and how to call it from a PL/SQL block.

Why would you want to use REF CURSOR's? REF CURSORs can often provide greater performance when working with data. Much of these comes from the fact that packages are stored into the SHARED POOL of the Oracle database or otherwise known as PINNED to memory.

What I did find interesting was that the process that executes this procedure never needs to know what tables the view touches or even be able to see the view. The procedure simply returns the requested columns.

Also: It would be entirely feasible, using REF CURSOR design, to have queries stored in a LONG column in a table. Remembering that the length of the data cannot exceed 32K. These queries can then be retrieved at package execution time. This could often reduce the number of production moves regarding packages. You could further enhance it by versioning your queries, so that only the most recent would be retrieved.

Is my database/ OS running 32-bit or 64-bit software?

DBAs often need to know if they should install Oracle 32-bit or 64-bit software on a given server. If the operating system support 64-bit applications, the 64-bit Oracle distribution can be loaded. Otherwise, the 32-bit distribution must be installed. The following Unix commands will tell you whether your OS is running 32-bits or 64-bits:

Solaris - isainfo -v (this command doesn't exist on Solaris 2.6 because it is only 32-bits)

HP-UX - getconf KERNEL_BITS

AIX - bootinfo -K

If you need to know if Oracle 32-bit or 64-bit software is currently installed on a system, connect using a command line utility like sqlplus and look at the banner. If you are running 64-bit software, it will be mentioned in the banner. If nothing is listed, you are running on a 32-bit base.

Oracle Listener Protection

Database listeners can be stopped remotely from any operating system account (not just oracle). To prevent this, all listeners on production machines MUST be password protected.

Oracle Default Listener

by Nidhi Jain

Prior to Oracle 8i, a listener was statically configured (listener.ora) to service a given set of SIDs. From 8i, PMON dynamically registers a database service with the listener.