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


i have created oracle types:

create or replace type "results_admin" 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" table of lms.results_admin;

here procedure 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    )      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() on (order a.asset_id) 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 result_admin_temp_table   asset_details 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   (aid null or a.asset_id = aid ) ,        (aname null or asset_name '%'||aname||'%') ,        (bauthor null or book_author '%'||bauthor||'%') ,        (aloc '%select%' or asset_location '%'|| aloc ||'%') ,        (acat '%select%' or asset_cat '%'||acat||'%') ,        (atypeid = 0 or a.asset_type_id = atypeid) ,       (pub null or p.publisher_name '%'||pub||'%');   open asset_cur   select        asset_id,        book_id,        asset_name ,       book_author ,        asset_location,        asset_cat ,        asset_type,        publisher_name,       books_available    table(result_admin_temp_table)      rownum > v_frec , rownum < v_lrec   order rownum asc; end retrieve_asset_admin;

while compiling code, getting ora-00947: not enough values error @ "bulkcollect result_admin_temp_table" point. have tried lot search on internet find out resolution this. can please tell me going wrong?

here why message: selecting nested table. oracle won't cast result set type you: need yourself. 

select results_admin(           row_number() on (order 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 result_admin_temp_table   asset_details 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 alias. rownum an oracle keyword, , query won't work way think will.
  2. you populating nested table entire result set. collections sit in session memory. if query returns many results - , given how vague parameters guess - collection might grow large , blow pga limit.
  3. running paginating queries in database such bad idea. nobody wants put business logic in database yet wants put presentation logic there. shakes head

finally, avoid overhead of collection simple in-line query:

open asset_cur   select        asset_id,        book_id,        asset_name ,       book_author ,        asset_location,        asset_cat ,        asset_type,        publisher_name,       books_available    (  select  row_number() on (order a.asset_id) 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 result_admin_temp_table       asset_details 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       (aid null or a.asset_id = aid ) ,            (aname null or asset_name '%'||aname||'%') ,            (bauthor null or book_author '%'||bauthor||'%') ,            (aloc '%select%' or asset_location '%'|| aloc ||'%') ,            (acat '%select%' or asset_cat '%'||acat||'%') ,            (atypeid = 0 or a.asset_type_id = atypeid) ,           (pub null or p.publisher_name '%'||pub||'%')           ) t   t.rn > v_frec , t.rn < v_lrec   order t.rn asc;

Comments

Post a Comment

Popular posts from this blog

c# - Validate object ID from GET to POST -

i have method allows user access comment have made on site. have post method lets them update comment. my normal solution send entire comment model through, let them update it, update on database when post back. involve sending commentid through in hiddenfor , can manipulated. how can verify commentid sent in get method same i'm getting in post , not able alter comment wish? how can verify commentid sent in method same i'm getting in post? basically have validate following things in post - user logged-in user. authenticated users post comments. commentid in post , should valid commentid , should present in database. userid associated logged-in user should same userid associated comment . comment should contain userid column, can check @ time of update. to make sure update happens comment has been sent in get - hold commentid in session , in post action compare commentid value in session .
Read more

node.js - Custom Model Validator SailsJS -

when using custom model validator checks record within model. return within spread doesn't seem end control flow. //post.js model /** * custom validator **/ types: { isuservalid: function(user_id) { var promise = require('bluebird'); promise.all([ user.findone({id: user_id}) ]) .spread(function(user) { console.log(user); if (user === null || user === undefined) { console.log('failed'); return false; }else{ console.log('passed'); return true; } }); } }, my response standard validation failed response. { "error": "e_validation", "status": 400, "summary": "1 attribute invalid", "model": "post", "invalidattributes": { "owner": [ { "rule": "isuservalid", ...
Read more

php - Find a regex to take part of Email -

i have following emails need extract part from: bestellnummer xxx: 1 von xxx, 1er pack ------------- anfang der nachricht ------------- foo bar baz foo bar baz // <<<<< need text here ------------- ende der nachricht ------------- ------------- anfang der nachricht ------------- foo bar baz foo bar baz ------------- ende der nachricht ------------- there 0 unlimited occurences of ------------- anfang der nachricht ------------- ------------- ende der nachricht ------------- and i'm able extract first part regex: $re = "/------------- .*? -------------.?(.*?).?------------- .*? -------------/s"; but, i'm quite new on learning regex, i'm pretty sure there must better regex extract part (foo bar baz foo bar baz) of text between ------------- anfang der nachricht ------------- ------------- ende der nachricht ------------- as can in different languages, i'm using .? to match between hyphens. i...
Read more