Functions returning Collections

Suppose you have a table and this table contains only one row, for instance a record with some application settings. You have the QDA packages in place and you want to retrieve this single row. But you don’t know what the primary key will be. On your development system it can be something else than your test system, let alone your client installations.

When you are using the QDA, there is a function ALLROWS available that returns all rows in a table. This function takes no input parameters. But what if you only interested in the first row.

You could ofcourse declare a couple of variables to catch all the rows and then select the first record and use its values.
DECLARE
  l_emp_col emp_tp.emp_tc;
  l_emp_row emp_tp.emp_rt;
BEGIN
  l_emp_col := emp_qp.allrows;
  l_emp_row := l_emp_col(1);
  dbms_output.put_line(l_emp_row.ename);
END;
/

But the variable holding the single record is not really needed, so we want to get rid of this one. We can do this by directly addressing the first element in the collection, just the way we would do to assign it to the variable.
DECLARE
  l_emp_col emp_tp.emp_tc;
BEGIN
  l_emp_col := emp_qp.allrows;
  dbms_output.put_line(l_emp_col(1).ename);
END;
/

After this exercise of removing a variable, I wondered if I could remove the collection as a whole and just call the function and use it’s results. So I tried the following code:
BEGIN
  dbms_output.put_line(emp_qp.allrows(1).ename);
END;
/

But this resulted in the following error:
ORA-06550: line 2, column 24:
PLS-00306: wrong number or types of arguments in call to ‘ALLROWS’
ORA-06550: line 2, column 3:
PL/SQL: Statement ignored

This is because the ALLROWS function doesn’t take any arguments. How can we tell the PL/SQL engine not to use the index as an argument. What we actually want is to send in an empty list of arguments. And after that empty list, we can use the index as we did before. So I came up with the following code:
BEGIN
  dbms_output.put_line(emp_qp.allrows()(1).ename);
END;
/

And that worked :-).
Of course, this approach is only useful if you only need one value of the record. If you need more values, it is better to catch the record in a variable and use that variable for further processing. But then again, you can do without the variable to hold the collection in this case too.
DECLARE
  l_emp_row emp_tp.emp_rt;
BEGIN
  l_emp_row := emp_qp.allrows()(1);
  dbms_output.put_line(l_emp_row.ename);
  dbms_output.put_line(l_emp_row.job);
END;
/

Leave a Reply

Your email address will not be published. Required fields are marked *