Home » SQL & PL/SQL » SQL & PL/SQL » Dynamic Cursor in Procedure (10g)
Dynamic Cursor in Procedure [message #356197] Wed, 29 October 2008 22:57 Go to next message
bo_reno
Messages: 4
Registered: October 2008
Junior Member
Hi,

I have a need to open a cursor in a stored procedure. The cursor will be populated with a select statement. I want to iterate through the rows and fix some of the fields in the set. Then, I want to pass the repaired cursor data set. Is this possible? Are the fields in the cursor updatable?

Thanks...
Re: Dynamic Cursor in Procedure [message #356260 is a reply to message #356197] Thu, 30 October 2008 01:49 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Do you want to change the data in the table, or just the fetched data?
If you only want to modify the fetched data, and leave the data in the table as it was, there are several options.
If your set is relatively small, you can use a plsql table (if you don't know what that is, search for PLSQL COLLECTIONS).
Another option might be to create a pipelined function.
Re: Dynamic Cursor in Procedure [message #356261 is a reply to message #356197] Thu, 30 October 2008 01:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And you should seriously investigate if you can't do it in a single UPDATE statement.

Regards
Michel
Re: Dynamic Cursor in Procedure [message #356283 is a reply to message #356261] Thu, 30 October 2008 03:16 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Michel Cadot wrote on Thu, 30 October 2008 07:51
And you should seriously investigate if you can't do it in a single UPDATE statement.

Regards
Michel


Or, if it's just the fetched data (so, no update is done at all), if you can't do the data-manipulation during the initial select.
If you can, you can return that as a REF CURSOR
Re: Dynamic Cursor in Procedure [message #356365 is a reply to message #356283] Thu, 30 October 2008 09:10 Go to previous messageGo to next message
bo_reno
Messages: 4
Registered: October 2008
Junior Member
Yes, I am trying to work with the fetched data. Thanks so much for your comment and I am on my way now to learn about the PLSQL tables. I have used PL/SQL and Transact SQL, but only to get what I need done. Smile
Re: Dynamic Cursor in Procedure [message #356372 is a reply to message #356365] Thu, 30 October 2008 09:33 Go to previous messageGo to next message
bo_reno
Messages: 4
Registered: October 2008
Junior Member
Okay, now I understand what a collection is. Cool

I think that they are scoped to the procedure. If this is true, how do I make the maniulated data available outside of the procedure? I was thinking of a ref cursor, but these have to point to SQL datasets?

Thanks,
Robert
Re: Dynamic Cursor in Procedure [message #356545 is a reply to message #356372] Fri, 31 October 2008 01:53 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
To where do you need to provide this data? To another pl/sql-procedure?
If so, you can send the pl/sql-table as a parameter.
Re: Dynamic Cursor in Procedure [message #356614 is a reply to message #356545] Fri, 31 October 2008 09:14 Go to previous message
bo_reno
Messages: 4
Registered: October 2008
Junior Member
No, I am using this procedure in Business Objects. The data in the database just isn't right enough, so I am trying to fix it before it gets to the report.
Previous Topic: Problem in Insert all in Oracle10g
Next Topic: How to select numeric values from a column
Goto Forum:
  


Current Time: Sat Dec 03 09:43:19 CST 2016

Total time taken to generate the page: 0.15460 seconds