Difference between strong ref cursor and weak ref cursor.

-

 Difference:

Oracle provides the special type of the datatype for cursor i.e. sys_refcursor which declares the weak ref cursor without declaring it.


Strong ref cursor

 

Weak ref cursor

·        It will always return the value.

         It will not always return the value.

·        Compiler know the structure during the compile time.

         Compiler does not know the structure during the compile time.

·        Dynamic query is not possible.

         Dynamic query is possible.

·        It will always return the know type from usually the declared type object.

         It has return type which is dependent on the SQL statement which executes only once the cursor is opened is the type known at the runtime.

·        Compiler can figure out or determine or find out the problem in the PLSQL block by comparing the types returned to and how they are used.

         Compiler cannot determine the type until it is run. Cursor result set should be cared and handled properly to avoid runtime errors.

·        It is used with select statement that return the result whose data type matches with the one that you have fixed during the STRONG REF cursor declaration.

         It is mostly used with REF cursor which is open for all select statements.

·        Syntax:

 Declare

TYPE ref_cur_name IS REF CURSOR

RETURN return_type;

          Syntax:

Declare

TYPE ref_cur_name is REF CURSOR

 

·        It is also called as the static structure type.

        It is also called as the dynamic structure type.

·        If return type is specified,REF Cursor type and cursor variable are strong.

        If return type is specified, REF Cursor type and cursor variable are strong.

·        Cursor structure is predetermined.

Query cannot be done by using emp%rowtype.

         Cursor structure is not predetermined.

Query can be done by using any structure.

·        It is less error prone.

          It is more error prone.

Strong ref cursor:


 DECLARE
TYPE emprefcur IS REF CURSOR RETURN emp%rowtype;
empc emprefcur;
v_empc empc%rowtype;
BEGIN
open empc for select * from emp;
LOOP
fetch empc into v_empc;
exit when empc%notfound;
DBMS_OUTPUT.PUT_LINE('Employee_Name:-'||v_empc. ename ||', '||'Employee_No:-'||v_empc.empno);
END LOOP;
CLOSE empc;
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