ORA-00947: Недостаточно значений при помещении значений в тип внутри процедуры

Я создал несколько типов Oracle:

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;

Вот процедура, которая использует их:

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;

Пока я компилирую код, я получаю ORA-00947: Not enough values error at the "bulkcollect into result_admin_temp_table" point, Я много пробовал искать в Интернете, чтобы найти решение этой проблемы. Можете ли вы сказать мне, где я иду не так?

1 ответ

Решение

Вот почему вы получаете это сообщение: вы выбираете во вложенную таблицу. Oracle не приведёт набор результатов к типу для вас: вам нужно сделать это самостоятельно.

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)

Некоторые дополнительные наблюдения:

  1. Не использовать rownum как псевдоним. ROWNUM - это ключевое слово Oracle, поэтому ваш запрос не будет работать так, как вы думаете.
  2. Вы заполняете вложенную таблицу полным набором результатов. Коллекции сидят в памяти сессии. Если запрос возвращает много результатов - и учитывая, насколько расплывчаты параметры, я думаю, что иногда это произойдет - коллекция может стать слишком большой и превысить предел PGA.
  3. Запускать нумерацию страниц в базе данных - плохая идея. Никто не хочет помещать бизнес-логику в базу данных, но все хотят поместить туда логику представления качает головой

Наконец, вы можете избежать накладных расходов на коллекцию с помощью простого встроенного запроса:

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;
Другие вопросы по тегам