Oracle PL SQL interview question CURSOR Vs REFCURSOR

Oracle PL SQL interview question CURSOR Vs REFCURSOR
sivakacademy.b...
Click here to subscribe to my youtube channel / @sivaacademy
Oracle Interview questions and Answers at • oracle interview quest...
Oracle 18C New features at • Oracle 18C New Feature
Oracle 12C New features at • Oracle 12C New Feature...
Oracle PLSQL records and collections at • oracle plsql records a...

[@Kasturi_walunj]

hi siva.I have refer all your videos and i have cleared interview and got the offer. Thank you.

[@SivaAcademy]

Best wishes 👏👏👍👍 Have a great job ahead 👍💐

[@venkatasubbareddyonteru4581]

Bro i have some doubts.. Can you please please share your email address?

[@dheerajvarthi4657]

All of my doubts regarding the concept clarified in it . I'm really for finding such a helpful playlist of 76 videos to revise all the important concepts of PL SQL before attending interview

[@SivaAcademy]

my pleasure

[@bhushanpalse516]

Sir u have made this language easy to learn, thanks..

[@SivaAcademy]

My pleasure bro 💐👍🙏

[@sathurmukil6308]

The session was very useful and thanks for your efforts! It really helps.

[@SivaAcademy]

Welcome bro

[@medhakhairnar6142]

Hi Sir, superb explanation..can you please make a vedio on join methods like hash join,sort merge, nested loop

[@SivaAcademy]

Thank you, sure please stay tuned

[@mustansarhussain4807]

what will happen if ref cursor return number but we create varchar veritable to receive value.

[@arshadansari7905]

Can we have difference between normal cursor and loop cursor

[@dheerajvarthi4657]

Can I get programs in the clip on your blog .If yes , Please share link

[@WorldEye88]

Hi, at 16:40 you don't close the cursor while in the written sample 16:47 you close it. Does it make a difference? Thank you.

[@amitkumaryadav9479]

No difference. Just that cursor wasn't closed so if you try to open the same cursor again it'll give a runtime error.

[@WorldEye88]

Thank you sir.

[@vikaskumar-qr5tj]

@Siva sir can you provide any practice pdf having problems based on cursor covering real case scenario happy new year in advance..

[@dineshvashisth1334]

@siva I think 4 point is just opposite what you said..we can't declare named/simple cursor at package level, however refcursor can be declared/defined at package level or outside of PLSQL subprograms.

[@SivaAcademy]

@Dinesh, explained the point no 4 with example in the last video

[@dineshvashisth1334]

@@SivaAcademy seems you didn't understand where I'm coming from..I meant we can declare ref cursor type..like TYPE refcursor is REF CURSOR; your program gives an error because you are creating cursor variable inside package specification. remove variable declaration part and then compile it will compile successfully without any error.

[@SivaAcademy]

@Dinesh, the point no 4 is to highlight normal cursor can be global, and ref cursor variable cannot be global, i understand removing the refcursor variable declaration will make the program to compile. , but the examples were shown to make the understanding clear...

[@dineshvashisth1334]

@@SivaAcademy no..4 no is written as ref cursor cannot be declared outside of the procedure or function. I think this needs to be corrected.

[@SivaAcademy]

@Dinesh, i meant as declaring cursor variable outside of procedure or function....yeah i should have mentioned it bit more clearly...thanks for mentioning....probably from next video, i will try to mention more clearly.....

[@SAK-y6j]

Thanks a lot Siva.

[@SivaAcademy]

Welcome bro

[@pradeepgonaguntla9469]

Hi Siva - your videos are very helpful to understand with examples. I have a question ... Can we insert data into cursor pointer ? As if to use further in programs .. ..

[@SivaAcademy]

Thank you bro, no we cannot insert data to cursor data, its read only copy of query result

[@pradeepgonaguntla9469]

@@SivaAcademy thanks Siva. Thought there might be a way to do it.

[@dfhwze]

Can we declare a ref cursor and populate it with a named cursor?

[@sriharichary8339]

Hi sir,
Can we use 2 procedures at time like pro_get_list and proc_print_list

[@shelkevikram]

Very good explanation! 👍

[@SivaAcademy]

Thank you so much :-)

[@avulavenkatesh6921]

Hi Siva sir, awesome explanation... can you please make a video on PIPELINED function.
thank you so much sir...

[@SivaAcademy]

Sure Please stay tuned. its already in my todo list,
will post soon

[@dineshvashisth1334]

@siva please make a video on polymorphic table function.

[@SivaAcademy]

@Dinesh, sure, will post this.

[@ur8946]

Hi Sir,
I m facing issue with ref cursor
example : I have below table and procedure
create table ord_dtls
(
order_id varchar(25),
product_id varchar(25),
product_desc varchar(50),
qty number
);
CREATE OR REPLACE PROCEDURE prc_get_order_details (
i_order_id IN NUMBER
, o_ord_dtls OUT SYS_REFCURSOR) IS
BEGIN
OPEN o_ord_dtls FOR
SELECT order_id, product_id, product_desc, qty
FROM ord_dtls
WHERE order_id = i_order_id;
END;
While executing the below anonymous block
"GettingError" :ORA-06504: PL/SQL: Return types of Result Set variables or query do not match ORA-06512: at line 14
DECLARE
i_order_id NUMBER;
rec_ord_dtls ord%ROWTYPE;
o_ord_dtls SYS_REFCURSOR;
BEGIN
i_order_id := 65;
prc_get_order_details (i_order_id, o_ord_dtls);
COMMIT;
LOOP
FETCH o_ord_dtls INTO rec_ord_dtls;
EXIT WHEN o_ord_dtls%NOTFOUND;
DBMS_OUTPUT.put_line ('ORDER_ID =>' || rec_ord_dtls.order_id);
DBMS_OUTPUT.put_line ('PRODUCT_ID =>' || rec_ord_dtls.product_id);
END LOOP;
END;
Could you pls tell me why it is not working or correct me if I missing anything

[@SivaAcademy]

@ur, Everything is correct, except the rowtype variable declaration.
TABLE NAME IS INCORRECT
Declare "rec_ord_dtls ord_dtls%ROWTYPE;" instead of "rec_ord_dtls ord%ROWTYPE;"

[@mugilans18]

I can able to execute if you correct the table name

[@bharathipandian45]

Where can I get the content of the slides you prepared, no time to write....

[@SivaAcademy]

Sure, I am posting few of the queries in a blog link, however I will try to post key points to remember also from next video on wards.

[@CuriousVoyage7]

What is static vs dynamic query?

[@SivaAcademy]

Will cover soon, please stay tuned

[@CuriousVoyage7]

@@SivaAcademy thhanks for reply… also I asked few questions on email. Could you please check and reply that?

[@SivaAcademy]

Sure bro

[@sateeshbabu5792]

Super sir

[@SivaAcademy]

Welcome Sateesh

[@sridhartamilselvan]

Nice sir...

[@SivaAcademy]

@sridhar, Thanks bro

[@ranajparida5412]

Hello Siva,
Could you please help for getting the query for below scenario
Table Student
---------------------------
Name Mark01 Mark02
Ranaj 75 79
Bharat 78 89
Expected output is :
================
Name Mark
--------------------------------
Ranaj 75
Ranaj 79
Bharat 78
Bharat 89
Regards,
Ranaj Parida

[@SivaAcademy]

Here is the query, I will explain how this works in next subscriber comments reply video, please stay tuned.
with student as ( select 'Ranaj' name, 75 mark1,79 mark2 from dual
union
select 'Bharat' name, 78 mark1,89 mark2 from dual)
select name, decode(l,1,mark1,mark2)
from student
cross join (select level l from dual connect by level

[@ranajparida5412]

Hello Siva,
Many thanks for your help.
Regards,
Ranaj