What is the difference between a PL SQL cursor and PL SQL ref cursor?

What is the difference between a PL SQL cursor and PL SQL ref cursor?

2 Answers. A cursor is really any SQL statement that runs DML (select, insert, update, delete) on your database. A ref cursor is a pointer to a result set. This is normally used to open a query on the database server, then leave it up to the client to fetch the result it needs.

What is ref cursor in PL SQL?

A REF CURSOR is a PL/SQL data type whose value is the memory address of a query work area on the database. In essence, a REF CURSOR is a pointer or a handle to a result set on the database. REF CURSOR s are represented through the OracleRefCursor ODP.NET class.

What is strong and weak ref cursor?

A strongly typed ref cursor always returns a known type, usually from a declared TYPE object. A weakly typed ref cursor has a return type that is dependant on the SQL statement it executes, i.e. only once the cursor is opened is the type known (at runtime).

Why do we use bulk collect in Oracle?

Oracle PL/SQL provides the functionality of fetching the records in bulk rather than fetching one-by-one. The main advantage of using BULK COLLECT is it increases the performance by reducing the interaction between database and PL/SQL engine.

What is difference between any and all in SQL?

ANY means that the condition will be satisfied if the operation is true for any of the values in the range. ALL means that the condition will be satisfied only if the operation is true for all values in the range.

Which is better in or exists SQL?

The EXISTS clause is much faster than IN when the subquery results is very large. Conversely, the IN clause is faster than EXISTS when the subquery results is very small. Also, the IN clause can’t compare anything with NULL values, but the EXISTS clause can compare everything with NULLs.

What is the difference between bulk collect and forall?

Bulk collect extract data from multiple rows in a single fetch thus improving the speed of data retrieval. FORALL is used to perform DML operation of the fetched data. It quickly INSERTs, UPDATEs, and DELETEs that use collections to change multiple rows of data.

What are REF CURSOR types in PL/SQL?

PL/SQL has two forms of REF CURSOR typeS: strong typed and weak typed REF CURSOR. The following shows an example of a strong REF CURSOR. DECLARE TYPE customer_t IS REF CURSOR RETURN customers%ROWTYPE; c_customer customer_t;

What is a weakly typed REF CURSOR?

A weakly typed ref cursor has a return type that is dependant on the SQL statement it executes, i.e. only once the cursor is opened is the type known (at runtime). The compiler cannot determine the types until it is ran, so care must be taken to ensure that the cursor result set is handled properly to avoid runtime errors.

What is the difference betweenrefref cursor and sys_refcursorsor type?

REF CURSOR and SYS_REFCURSOR type is used interchangeably in PL/SQL program. With respect to functionality or interpretation by processing engine there is no visible difference between them. SYS_REFCURSOR available from Oracle 9i onwards as part of standard package and weak reference created for programmer easiness.

What is the advantage of weak cursor over strong cursor?

Advantage of Weak cursor type over Strong type: Weak cursor type is more flexible than the strong type because they can be used with any query and with any ROWTYPE structure. But it does not provide compile time verification whether correct type of record has been used for fetch cursor or not.