ORA-00947 : Not Enough Values while putting values into a type inside a procedure

Question

I have created some Oracle types:

create or replace TYPE "RESULTS_ADMIN" AS OBJECT 
(
  ROWNUMBER NUMBER,
  ASSET_ID VARCHAR2(1000 CHAR),
  BOOK_ID VARCHAR2(10 CHAR),
  asset_name VARCHAR2(50 CHAR) ,
  book_author VARCHAR2(30 CHAR) , 
  asset_location VARCHAR2(30 CHAR), 
  asset_cat VARCHAR2(50 CHAR), 
  asset_type VARCHAR2(10 CHAR), 
  publisher_name VARCHAR2(50 CHAR),
  books_available NUMBER
);

create or replace TYPE "RESULT_ADMIN_TEMP" is table of LMS.results_admin;

Here is a procedure which uses them:

create or replace PROCEDURE "RETRIEVE_ASSET_ADMIN" (
     aid IN asset_details.asset_id%TYPE,
      aname IN asset_details.asset_name%TYPE,
      acat IN asset_details.asset_cat%type,
      atypeid IN asset_details.asset_type_id%type,
      bauthor IN asset_details.book_author%type,
      aloc IN asset_details.asset_location%type,
      pub IN asset_publisher.publisher_name%type,
      pagenumber IN number,
      asset_cur OUT SYS_REFCURSOR    )
is 
    v_fpgnbr number;
    v_pgsize number;
    v_frec number;
    v_lrec number;
    v_totrows number;
    result_admin_temp_table result_admin_temp;
begin
  v_fpgnbr := pagenumber;
  v_pgsize :=10;
  v_frec := (v_fpgnbr - 1 ) * v_pgsize;
  v_lrec := ( v_fpgnbr * v_pgsize + 1 ); 
  select  row_number() over (order by a.asset_id) as rownum,
          a.asset_id, 
          a.book_id, 
          asset_name ,
          book_author , 
          asset_location, 
          asset_cat , 
          l.asset_type, 
          p.publisher_name,
          c.books_available
      bulk collect into result_admin_temp_table
  from asset_details a join asset_count c 
      on a.book_id=c.book_id 
      join asset_lookup_details l 
      on a.asset_type_id=l.asset_type_id 
      join asset_publisher p
      on a.book_id = p.book_id
  where (aid is NULL or a.asset_id = aid ) and 
      (aname is NULL or asset_name like '%'||aname||'%') and 
      (bauthor is NULL or book_author like '%'||bauthor||'%') and 
      (aloc like '%SELECT%' or asset_location like '%'|| aloc ||'%') and 
      (acat like '%SELECT%' or asset_cat like '%'||acat||'%') and 
      (atypeid = 0 or a.asset_type_id = atypeid) and
      (pub is NULL or p.publisher_name like '%'||pub||'%');
  open asset_cur for
  select 
      asset_id, 
      book_id, 
      asset_name ,
      book_author , 
      asset_location, 
      asset_cat , 
      asset_type, 
      publisher_name,
      books_available 
  from table(result_admin_temp_table)
  where 
  rownum > v_frec and rownum < v_lrec
  order by rownum asc;
end RETRIEVE_ASSET_ADMIN;

While I am compiling the code, I am getting ORA-00947: Not enough values error at the "bulkcollect into result_admin_temp_table" point. I have tried a lot to search over the internet to find out a resolution to this. Can you please tell me where am I going wrong?

Answer 1

Here is why you get that message: you are selecting into a nested table. Oracle won't cast a result set to a type for you: you need to do it yourself.

select RESULTS_ADMIN(
          row_number() over (order by a.asset_id),
          a.asset_id, 
          a.book_id, 
          asset_name ,
          book_author , 
          asset_location, 
          asset_cat , 
          l.asset_type, 
          p.publisher_name,
          c.books_available )
  bulk collect into result_admin_temp_table
  from asset_details a join asset_count c 
      on a.book_id=c.book_id 
      join asset_lookup_details l 
      on a.asset_type_id=l.asset_type_id 
      join asset_publisher p
      on a.book_id = p.book_id)

Some additional observations:

1. Don't use rownum as an alias. ROWNUM is an Oracle keyword, and so your query won't work the way you think it will.
2. You are populating a nested table with the entire result set. Collections sit in session memory. If the query returns many results - and given how vague the parameters are I would guess sometimes it will - the collection might grow too large and blow the PGA limit.
3. Running paginating queries in the database is such a bad idea. Nobody wants to put business logic in the database yet everybody wants to put presentation logic there. shakes head

Finally, you could avoid the overhead of the collection with a simple in-line query:

open asset_cur for
  select 
      asset_id, 
      book_id, 
      asset_name ,
      book_author , 
      asset_location, 
      asset_cat , 
      asset_type, 
      publisher_name,
      books_available 
  from (  select  row_number() over (order by a.asset_id) as rn,
              a.asset_id, 
              a.book_id, 
              asset_name ,
              book_author , 
              asset_location, 
              asset_cat , 
              l.asset_type, 
              p.publisher_name,
              c.books_available
          bulk collect into result_admin_temp_table
      from asset_details a join asset_count c 
          on a.book_id=c.book_id 
          join asset_lookup_details l 
          on a.asset_type_id=l.asset_type_id 
          join asset_publisher p
          on a.book_id = p.book_id
      where (aid is NULL or a.asset_id = aid ) and 
          (aname is NULL or asset_name like '%'||aname||'%') and 
          (bauthor is NULL or book_author like '%'||bauthor||'%') and 
          (aloc like '%SELECT%' or asset_location like '%'|| aloc ||'%') and 
          (acat like '%SELECT%' or asset_cat like '%'||acat||'%') and 
          (atypeid = 0 or a.asset_type_id = atypeid) and
          (pub is NULL or p.publisher_name like '%'||pub||'%')
          ) t
  where t.rn > v_frec and t.rn < v_lrec
  order by t.rn asc;