Return basic result set from Oracle 11g?

Why you only get one record returned: You loop over the recordset, fetching one record at a time, and each time you assign this record to the output variable. So only your last record will be stored in the output variable.

Why you only get one record returned: You loop over the recordset, fetching one record at a time, and each time you assign this record to the output variable. So only your last record will be stored in the output variable. What you want to do is return a record set.

For this, you can use pl/sql tables. To easily fetch a set into a table you could use bulk collect into (a link - but google will find you a lot more). You can use the rowtype as the type for your table, or a type you make up yourself.

You could use a package variable to declare a type, or define a type in the database schema. If you use rowtype, that is not bad at all. If your table should change, then rowtype will reflect this aswell, saving you the hassle of trawling through your code to add or remove columns should you have manually declared each column in a own type.

Ex: function hand_out_money return pck_emp. T_tab_emp is tab_emp pck_emp. T_tab_emp; begin select * bulk collect into tab_emp from emp where deptno = 20; update emp set comm = comm + 10 where deptno = 20; return tab_emp; end; To be able to use this, have t_tab_emp declared in a package_spec.

This way, you can reference the returned type from where you call this code. Just put type t_tab_emp is table of emp%rowtype; in there - like I would do in package pck_emp. Your calling code could then be: declare tab_result pck.

T_tab_emp; begin tab_result := hand_out_money; end; To do your update: If you fetch all your record into a plsql table, you could thereafter do a single update statement: update X_externalaccountref set externalstatus = 1, externalchangedate = SYSDATE where externalstatus IS NULL; Also: you could just use a function if you only return this one variable, it would make sense for a getter. Also: I generally don't like function or procedures named 'getxxx' who then do DML. More proper would be to call your procedure 'activate_external_accounts' for example, which would then return the recordset.

Mind, that if you do a bulk select before the update, the externalstatus and externalchangedate won't be updated! So you don't get a resultset returned, but a pre-update set.

Perfect! It's all a bit twisted coming from SQL Server. Thanks.

– Riri Nov 16 at 15:08.

Well, basically because I only get the last row as a response. But thanks to @Tom I get why now. – Riri Nov 16 at 15:06.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.