Answer / nagaraju bhatraju

Basically, Reference cursor is datatype.Reference cursor
works
as a cursor variable.the advantage of using reference cursor
is it pass the result sets to the subprograms
(ie,procedures,packages and functions etc).
example for reference cursor usage:
type r_cursor is ref cursor;
c_emp r_cursor;
ename1 emp.ename%type;
begin
open c_emp is select ename from emp;
loop
fetch c_emp into ename1;
exit when c_emp% notfound;
dbms_output.put_line(ename1);
end loop;
close c_emp;
end;
--------------


A REF CURSOR have a return type and it as 2 type Strongly
Typed Cursor and Weakly Typed Cursor

but Cursor doesn't have return type

Ex:

TYPE ref_type_name IS REF CURSOR RETURN return_type;

return_type represents a record in the database

DECLARE TYPE EmpCurType IS REF CURSOR RETURN emp%ROWTYPE;



Cursor doesn't have a return type but
A Reference Cursor have a return type and it as 2 type one
is
Strongly Typed Cursor and Weakly Typed Cursor.



Another difference is REF curson can be assigned
dynamically while Normal cursor once defined you cann't
change it



ref cursor can be associated with many no. of sql
statements where cursor can be associated only with one sql
statement.

ref cursor is dynamic,cursor is static.

ref cursor points to a location.




Reference cursors have 2 types.

1 is strong cursors and 2 one is week-cursor

in stron cursor we given return type. in week cursor no
return type

A REF CURSOR have a return type and it as 2 type Strongly
Typed Cursor and Weakly Typed Cursor

but Cursor doesn't have return type

Ex:

TYPE ref_type_name IS REF CURSOR RETURN return_type;

return_type represents a record in the database

DECLARE TYPE EmpCurType IS REF CURSOR RETURN emp%ROWTYPE;
***********************************

Cursor doesn't have a return type but
A Reference Cursor have a return type and it as 2 type one
is
Strongly Typed Cursor and Weakly Typed Cursor.


**************************************************


ref cursor can be associated with many no. of sql
statements where cursor can be associated only with one sql
statement.

ref cursor is dynamic,cursor is static.

ref cursor points to a location.

CURSOR

In cursor there are 2 types explicit and implicit cursor

Explicit cursor

Explicit cursors are SELECT statements that are DECLAREd
explicitly in the declaration section of the current block
or in a package specification. Use OPEN, FETCH, and CLOSE
in the execution or exception sections of your programs.

IMPLICIT CURSOR

Whenever a SQL statement is directly in the execution or
exception section of a PL/SQL block, you are working with
implicit cursors. These statements include INSERT, UPDATE,
DELETE, and SELECT INTO statements. Unlike explicit
cursors, implicit cursors do not need to be declared,
OPENed, FETCHed, or CLOSEd.

REFERENCE CURSOR

A cursor variable is a data structure that points to a
cursor object, which in turn points to the cursor's result
set. You can use cursor variables to more easily retrieve
rows in a result set from client and server programs. You
can also use cursor variables to hide minor variations in
queries.

The syntax for a REF_CURSOR type is:

TYPE ref_cursor_name IS REF CURSOR [RETURN
record_type];If you do not include a RETURN clause, then
you are declaring a weak REF CURSOR. Cursor variables
declared from weak REF CURSORs can be associated with any
query at runtime. A REF CURSOR declaration with a RETURN
clause defines a "strong" REF CURSOR. A cursor variable
based on a strong REF CURSOR can be associated with queries
whose result sets match the number and datatype of the
record structure after the RETURN at runtime.

To use cursor variables, you must first create a REF_CURSOR
type, then declare a cursor variable based on that type.

The following example shows the use of both weak and strong
REF CURSORs:

DECLARE -- Create a cursor type based on the
companies table. TYPE company_curtype IS REF
CURSOR RETURN companies%ROWTYPE; -- Create the
variable based on the REF CURSOR. company_cur
company_curtype; -- And now the weak, general approach.
TYPE any_curtype IS REF CURSOR; generic_curvar
any_curtype;The syntax to OPEN a cursor variable is:

OPEN cursor_name FOR select_statement;



Hi

Cursor (explicit cursor) are static cursors which can be
associated with onlyone SQl statement at the same timeand
this statement is known when block is compiled. A Cursor
Variable, on the other hand, can be associated with
different queries at runtime.

Static Cursor are analogus to PL/SQL constants because they
can only be associated with one runtime query, whereas
reference cursor are analogus to PL/SQL variables, which
can hold different values at runtime.

Reference Cursor can have return type.

Beacause of reference type, no storage is allocated for it
when it is declared. Before it can be used, it needs to
point to a valid area of memory, which can be created
either by allocating it to the client-side program or on
the server by PL/SQL engine.

Ref cursor can contain multiple query in single variable.
where cursor can be associated only with one sql query

Ref cursor is dynamic,cursor is static.

CURSOR IS A STATIC TYPE. BUT REF CURSOR IS DYNAMIC.

What do you mean by filter transformation?

0 Answers  

---

How can we use mapping variables in informatica? Where do we use them?

0 Answers  

---

What are the new features of informatica 9.x at the developer level?

0 Answers  

---

Can I create one wrkflw which will run different wrkflws on schedule time?

3 Answers  

---

How to create Target definition for flat files?

0 Answers   Informatica,

---

I have a text file name x With a text message. Word informatica Is repeated for n number of times. How to count the number of occurrence Of word informatica Of word informatic in this file x.

1 Answers   Wipro,

---

generate Unique sequence numbers for each partition in session with Unconnected Lookup ? Hi All, Please help me to resolve the below issue while Applying partitioning concept to my Session. This is a very simple mapping with Source, Lookup , router, and target. I need to Lookup on the target and compare with the source data, if any piece of data is new then Insert, and If any thing change in the existed data then Update. while Inserting the new records to the target table I'm generating sequence numbers with Unconnected lookup, by calling the maximum PK ID from the target table. The above flow is working fine from last one year. Now I wish to apply the Partitioning concept to the above floe(session) At source I used 4 pass through partitions.(For Each partition different filter conditions to pull the data from source) at Target I used 4 passthrough Partitions. it is working fine for some data, but for some rows for Insert Operation , it is throwing Unique key errors, because while Inserting the data it is generating the same sequence key twice. In detail : 1st row is coming from 1st partition and generated the sequence number 1 for that row. 2nd row is coming from 1st partition and generated the sequence number 2 for that row 3rd row is coming from the 2nd partition generated the sequence number 2 again for that row. (it must generate 3 for this row) the issue is becuase of generating the same sequence numbers twice for different partitions. Can any one Please help me to resolve this issue. While Applying partitions how can I generate a Unique Sequence numbers from Unconnected lookup for Each partitioned data. Regrads, N Kiran.

2 Answers   Tech Mahindra,

---

2,if we have 3 pipeline containing 3 targets and each target is connected with diff sequence generator ,let us assume first target is populated with seq no1-9,so what would be the number generated by other two sequence generator?what is the diff in o/p if when we use reusable sequence generator?what would be the diff if we place expression in between target and sequence generator?

2 Answers   Emphasis,

---

what is shared lookup&persistent lookup?

4 Answers   Accenture, Cap Gemini,

---

can we use cartesian join in informatica

3 Answers   IBM,

---

State the differences between sql override and lookup override?

0 Answers  

---

The Source coloumns are A,B,C with data row1- 10,20,30 row2- 40,50,60 row3-70,80,90 and so on. In the target I want one coloumn with the following data, Coloumn-X, row1-10,row2- 20,row3-30,row4-40,row5-50 and so on. How to achieve this?

3 Answers   Cap Gemini,

---