No question at this time
DBA Top 10
1 A. Kavsek 12200
2 M. Cadot 7000
3 P. Wisse 5600
4 B. Vroman 5400
5 J. PĂ©ran 1900
6 . Lauri 1500
7 T. P 500
7 R. Pattyn 500
9 T. Boles 400
10 A. Chavan 300
10 J. Schnackenberg 300
10 D. Johnson 300
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48454
Total active users1499
Act. users last 24h5
Act. users last hour0
Registered user hits last week216
Registered user hits last month950
Go up

RefCursor vs Views
Next thread: EXECUTE DBMS_RESULT_CACHE.MEMORY_REPORT
Prev thread: Oracle11g schema table access audit

Message Score Author Date
I have got 1 app for maintenance and that app (fro...... Nishant Kariya Sep 21, 2019, 17:38
I can see multiple records in v$open_cursors but w...... Nishant Kariya Sep 21, 2019, 17:42
Q1: No. Q2: No but the application should clo...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Michel Cadot Sep 21, 2019, 19:14
Oracle core engine does not care about names of ...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Michel Cadot Sep 21, 2019, 19:19
Right Michel. Agree with you. And 1 thing more....... Nishant Kariya Sep 22, 2019, 08:46
There is no time limit. The cursors remain open...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts Michel Cadot Sep 22, 2019, 09:32
Michel, I got confused in 1 point over here. ...... Nishant Kariya Oct 08, 2019, 16:46
>>> <i> Now when I say close cursor <C1> from ap...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts Michel Cadot Oct 08, 2019, 17:47

Follow up by mail Click here


Subject: RefCursor vs Views
Author: Nishant Kariya, India
Date: Sep 21, 2019, 17:38, 59 days ago
Message: I have got 1 app for maintenance and that app (frontend is K2)fetches data from oracle via stored procs. Inside procs, refcursors are used to throw data back.

Question-1: In every proc same name of refcursor is used say open c1 for ....
Will this create problem in a session or across multiple sessions?

Question 2: Refcursor is not closed at application end. Will this create an issue? But application is running fine as of now.

Question 3: What if I re-write the logics and try to convert stored procs in views ? That will parameterized views then.
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: RefCursor vs Views
Author: Nishant Kariya, India
Date: Sep 21, 2019, 17:42, 59 days ago
Message: I can see multiple records in v$open_cursors but why they are not clashing. What I mean here is if 1 refcursor was opened by name say C1 in memory and not closed, and at the same time some other operation or click event on app invoked another procedure which inside opened another refcursor with same name (C1), why didn't oracle gave me an error that same name cursor is already opened?

Is it because first one was through fetching its data? If it would have been the case that first one was still fetching (due to more records) and another cursor command is given with same name then it would have had given error?
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: RefCursor vs Views
Author: Michel Cadot, France
Date: Sep 21, 2019, 19:14, 59 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message:
Q1: No.

Q2: No but the application should close any cursor it opens as soon as it no more uses it unless it can reach the open_cursors parameter value and get an error.

Q3: a view is just a name for a query text, anyway you will return a REF CURSOR (explicit or implicit) to your application.

Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: RefCursor vs Views
Author: Michel Cadot, France
Date: Sep 21, 2019, 19:19, 59 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message:
Oracle core engine does not care about names of REF CURSOR, it is just for you and the SQL/PLSQL compiler.
Do you see any name in v$open_cursor view?

It is just variables in procedure of a program. You can have the same variable name in different procedures (or even different block of the same procedure) called by different instances of the program without any problem, can't you?

Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: RefCursor vs Views
Author: Nishant Kariya, India
Date: Sep 22, 2019, 08:46, 58 days ago
Message: Right Michel. Agree with you.

And 1 thing more. What is the time limit for automatic closure of refcursor in memory. I mean if developer doesn't care to close the refcursors from application, will they remain open forever or will they expire in some time?
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: RefCursor vs Views
Author: Michel Cadot, France
Date: Sep 22, 2019, 09:32, 58 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message:
There is no time limit.
The cursors remain open until the application closes them or disconnects or ends (normally or abnormally).

Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: RefCursor vs Views
Author: Nishant Kariya, India
Date: Oct 08, 2019, 16:46, 42 days ago
Message: Michel,

I got confused in 1 point over here.

Lets assume you have 10 opened refcursors with same name, invoked from different procedures.

Now when I say close cursor <C1> from application code, which one it will close? Few have finished fetching data and few are still fetching.

Pardon me if I am thinking in a wrong direction.

Regards
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: RefCursor vs Views
Author: Michel Cadot, France
Date: Oct 08, 2019, 17:47, 42 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message:
>>> Now when I say close cursor <C1> from application code, which one it will close?

It will close the one that is currently in the scope of the procedure.
It is the same than if you have 10 variables with name X in your application code and you execute "Y=X", it will take the X that is the scope of procedure and has the highest precedence.

Cursor name is just like variable name.

Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here