How can I return multiple rows of data on an Oracle Form using a cursor

I'm pretty sure there is no SQL function that will do this.  Therefore, you would need to do it yourself using a code loop.  I don't use Oracle Forms so I'm not even sure what language is used behind them so I can't provide an example.  Basically you would open the cursor and for each record, concatenate whatever columns you want into a variable and then when you run out of records, post the variable to the form.

No,  multiple records into 1 field or even 1 record will not work.
But you can concatenate results as PatHartman commented or add a detail block to the form to display more rows.
Please explain what you want to do (by code or examples)

Oracle Forms is designed to show one database record in one row in a Forms block.  If you want to do something different from that in Oracle Forms, you will need to write some custom code.  This could possibly be in a database view, or a PL\SQL function or a combination of those.  Or you could write a program unit in the form or in a Forms-side PL\SQL library, but for maximum efficiency, any SQL code or queries should be in database objects, not forms-side objects.

When you say that you want to "return multiple records in a [single] field" that sounds impossible or unreasonable.  But, maybe we aren't understanding what you mean, or the business problem you are trying to solve.

Can you post some sample records and what you would like them to look like in a form?

Thanks for the responses.  This is what I am trying to achieve.  Hope it is clearer.

Royclac_Issue.vsd

OK, so you aren't asking for "multiple records in a [single] field".  You are asking for Oracle Forms to display multiple records in a master block *AND* also display a detail record for each master block, at the same time.  Oracle Forms does not do that.

Oracle Forms can do:
1. Display multiple records in a single (or master) block.
2. Display multiple detail records for a single master record.
2. Display multiple records in one block and display multiple, unrelated records in an unrelated block.

It looks to me like you should be able to create a database view that contains the records from your master table, plus the related (or summarized?) information from your detail table.  Then you can change your form to be a single-block form based on that view.  That would allow you to have multiple master records displayed *AND* have the related detail information for each master record also displayed at the same time (in the same block).  This block can still support inserts, updates and/or deletes of the master table fields if you want that functionality.  

If you need that functionality in this form, let us know if you need help with making that work, since that will require changing some default values and writing some custom triggers.

Thank you Mark...... will I be able to achieve this using a cursor and a stored procedure where I can return multiple rows based on the procedure?????  It would not be possible for me to have a single-block form since I have a lot of operations taking place on the form.

Any assistance would be appreciated.

"It would not be possible for me to have a single-block form since I have a lot of operations taking place on the form."
I'm not convinced that is true.  I'm suggesting that incorporating the fields you included in the screen shot that you uploaded could be done in one block.  Whether you need other blocks or not to possibly include other fields, I don't know.  I have a lot of experience building Forms based on views that support all of the normal Forms features: queries, inserts updates, deletes, etc.

"will I be able to achieve this ..."?  I think you mean: "will I be able to display multiple master records *AND* have some related fields for each master records all displayed at the same time"?  The answer is definitely yes, *IF* you include those fields in the master block.  The easiest and most-efficient way to do that is by using a database view, and use that view as the "query data source" for your Forms block.

If you want to try to have the detail records in a second block, and try to have multiple records displayed there at the same time, that may be possible.  But, that could get very complex.  I don't recommend that.

Have you tried basing an Oracle Forms block on a view instead of on a table, and still have that block support all of Forms normal functionality?  This is possible.

Mark you are absolutely correct......I have several other blocks on my Form and I assumed you meant to just have one block.  Can you help me to achieve what you are suggesting.

" I'm suggesting that incorporating the fields you included in the screen shot that you uploaded could be done in one block.  Whether you need other blocks or not to possibly include other fields, I don't know.  I have a lot of experience building Forms based on views that support all of the normal Forms features: queries, inserts updates, deletes, etc."  I would like to try this suggestion.

 "Have you tried basing an Oracle Forms block on a view instead of on a table, and still have that block support all of Forms normal functionality?  This is possible."  Can you please help me to achieve this.

Thanking you for all your assistance thus far.

I have created the Database view and have included a block on the form that is based on the view.   Can you please assist me in achieving this:

"To base an Oracle Forms block on a view instead of on a table, and still have it support "normal" Oracle Forms functionality (inserts, updates and deletes) in the main table that the view is based on"

Thanks so much.

Ok Thank you.   I am confident what you suggested will work.  Thanks again.