Module 04: Interacting with the Oracle Database Server

Interacting with the Oracle Database Server 

01. Write down the Guidelines for Retrieving Data in PL/SQL

Guidelines:

a. Terminate each SQL statement with a semicolon (;).

b. Every value retrieved must be stored in a variable using the INTO clause. 

c. The WHERE clause is optional and can be used to specify input variables, constants, literals, or PL/SQL expressions. However, when you use the INTO clause, you should fetch only one row and the usage of WHERE clause is a must in such cases.

d. Specify the same number of variables in the INTO clause as database columns in the SELECT clause. Be sure that they correspond positionally and that their data types are compatible.

e. Use group functions, such as SUM, in a SQL statement, because group functions apply to groups of rows in a table.

02. Write down about the requirements ‘Queries Must Return Only One Row’ of SELECT Statements in PL/SQL.

Queries Must Return Only One Row:

a. SELECT statements within a PL/SQL block fall into the ANSI classification of embedded SQL, for which the following rule applies: queries must return only one row. A query that returns more than one row or no row generates an error.

b. PL/SQL manages these errors by raising standard exceptions, which you can handle in the exception section of the block with the NO_DATA_FOUND and TOO_MANY_ROWS exceptions.

03. How to Retrieve Multiple Rows from a Table and Operate on the Data?

A SELECT statement with the INTO clause can retrieve only one row at a time. If your requirement is to retrieve multiple rows and operate on the data, then you can make use of explicit cursors.

 

04. What is SQL Cursor? And write down the types cursors.

A cursor is a pointer to the private memory area allocated by the Oracle server.

There are two types of cursors:

a. Implicit cursors: Created and managed internally by the Oracle server to process SQL statements.

b. Explicit cursors: Explicitly declared by the programmer.

05. Write down the SQL Cursor Attributes for Implicit Cursors.

Using SQL cursor attributes, you can test the outcome of your SQL statements.