What is ref cursor in oracle sql plsql interview question and answer?

-

 What is ref cursor in oracle?

Cursor variables are called as ref cursor. It is said to be the powerful,dynamic,flexible,scalable ways or methods to return query results or output from the oracle database to the client application. It is the plsql data type whose assigned value is the memory address of the query work area. It is pointer which is handling the result set on the database. It may be represented through the OracleRefCursor ODP.Net Class.

Client must be connected to the ref cursor during the lifetime in order to access the information as per required input instructions. Accurate or wanted or desired data cannot be retrieved unless it is opened and requested by the user to read and access it.It is not updatable, and outcome or results are only readable. In other words, one can say that database is not updated through this. It can create and return inside the PLSQL block. It is not backward scrollable, and the data represented by it can be accessed in the forward and serial wise. You cannot use the pointer to point the random records in the result set.

It is mostly preferred to use this for dynamic SQL queries creation and execution. Structure of the query or result set is not known, or table is not known at the compile time. It can be reusable in the code which prevents data duplication and improve maintenance. It supports complex data manipulation operations require multiple queries or the data sources. It is used like the parameter passing which takes the query results passed to the procedures or functions which is modular and flexible in nature.

It is used in server side.

Cursor_variable are used with Ref cursor to fetch and retrieve data dynamically:

PLSQL block is created that use the cursor variable with REF CURSOR to dynamically fetch and display the data from the employees table.PLSQL block is opened for the dynamic query which select all the columns from the employees table, fetch the data into variables emp_id,ename and then display each employees id and name using the DBMS_OUTPUT.put_line function. Cursor is closed to release the resources.Loop fetches and display the data from the result set as form of output which is displayed in the below screenshot.

create table employees(emp_id INT PRIMARY KEY, ename varchar2(20));

insert into employees(emp_id,ename) values(1,'Rahul');
insert into employees(emp_id,ename) values(2,'Ram');
insert into employees(emp_id,ename) values(3,'Sohan');

select * from employees;

set serveroutput on;


--PLSQL BLOCK with cursor variable.
DECLARE 
TYPE ref_cursor_type IS REF CURSOR;
cursor_variable ref_cursor_type;
emp_id1 employees.emp_id%TYPE;
emp_name1 employees.ename%TYPE;
BEGIN
--Dynamic query using cursor variable
OPEN cursor_variable FOR 'SELECT * FROM employees';
--Fetch and displays the data.
LOOP
FETCH cursor_variable INTO emp_id1,emp_name1;
EXIT WHEN cursor_variable%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Employee_ID:-' || emp_id1 ||', Employee_name:-' || emp_name1);
END LOOP;
--Close the cursor
CLOSE cursor_variable;
END;
/















Cursor variable is passed using as parameter to the procedure:

PLSQL procedure employees_data_display is created that accepts the cursor variable as IN OUT parameter. Procedure should fetch and display the employees data (emp_id,ename) from the cursor variable.PLSQL block is created to open the cursor for the dynamic query which select all the columns from the employees table and will call the employees_data_display procedure with the cursor variable as the parameter.Thus,cursor is closed to release resources.

create or replace procedure employees_data_display(
p_cursor_variable IN OUT SYS_REFCURSOR
)
IS
emp_id1 employees.emp_id%TYPE;
emp_name1 employees.ename%TYPE;
BEGIN
LOOP
FETCH p_cursor_variable INTO emp_id1,emp_name1;
EXIT WHEN p_cursor_variable%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Employee_ID:-' || emp_id1 ||', Employee_name:-' || emp_name1);
END LOOP;
END;
/
insert into employees(emp_id,ename) values(4,'Raman');
insert into employees(emp_id,ename) values(5,'Sohail');

DECLARE 
TYPE ref_cursor_type IS REF CURSOR;
cursor_variable ref_cursor_type;
BEGIN
OPEN cursor_variable FOR 'SELECT * FROM employees';
employees_data_display(p_cursor_variable=>cursor_variable);
CLOSE cursor_variable;
END;
/








Comments

Post a Comment

Popular posts from this blog

Difference between union and union all in oracle sql plsql

-
Image
"This is one of the most important questions that interviewer might be asking in an interview.Infyosis,Linked group,Kyndryl,Vodafone Idea, IBM and Mphasis etc." INTRODUCTION: It is said to be set operators which is used to combine, add or concatenate two or more result sets. It is used in the multiple select statements where to combine to produce the final desired results. In other words, multiple select statements of similar column name in same order and having same data type to produce the unified result. It merges the content of the two structurally compatible organized tables into single table.  In MIS datalake,when you are adding the modules of GST reconciliation which is to be used in micros in Excel or VB script, union and union all set operator is preferred for eliminating duplicate records or keeping duplicate records as per business requirement. There are several alternatives like bulk collect,join,set operators like intersect & minus for combining the data from...
Read more

Explain oracle pl sql datatypes interview question and answer for fresher and experienced.

-
Image
 Oracle PLSQL Data Types : PLSQL constant,value,variable,parameter,function which return the value are having the data type. It will determine the storage space information occupied and occurred in the memory or storage format and range constraints for valid values and operations as per specified size limit. It will determine how the data will be going to stored, handled and treated by the oracle engine during the period of processing and storage. It is used in the PLSQL blocks. It allows you to define your sub-types. PLSQL codes are embedded into the java program. Subtypes are used to make the data types compatible with data types used in the PLSQL program while embedding the PLSQL code into the java program. Subtype will be compatible with ANSI/ISO & IBM. It provides the pre-defined data types. Pre-defined data types are categorized into the four types: Composite LOB Reference Scalar Scalar Datatypes: It stores the values without the internal components. It holds the single ...
Read more

Difference between full join and cross join-oracle sql plsql

-
Image
 Difference between full join and cross join in sql:- They behave differently to combine the rows from the two tables. FULL OUTER JOIN: It is also called as the full join .It will return all the rows when there is either match in the left table or right table. If there is no match, it will return NULL for the rows of the columns which is lacking the matching rows. Syntax: select c.*,d.*  from tableC c FULL OUTER JOIN tableD d on c.id=d.id; select e.ename,d.dname from emp e FULL OUTER JOIN dept d ON e.deptno=d.deptno; When you want to retain the unmatched rows i.e operations which is not present in the emp table and having deptno as 40.So,ename is appeared as NULL. CROSS JOIN: It returns the cartesian product of both the tables i.e. Every row of the table A is combined with every row of the table B.ON condition is not used due to not match rows. Syntax: select c.*.d.* from tableC c CROSS JOIN tableD d; select e.ename,d.dname from emp e CROSS JOIN dept d; ENAME ...
Read more