Oracle : Procedure throws Invalid Datatype Error during execution ORA-00902: invalid datatype - 


   create table t1 (emp_name varchar2 (40));      insert t1          values ('vinoth');      commit;      create table t2 (emp_name varchar2 (40));      create or replace package test_pkg_v            procedure p_main (p_status out varchar2);         type t1_type record (emp_name t1.emp_name%type);         type t1_tbl table of t1_type;     end test_pkg_v;     /      create or replace package body test_pkg_v            procedure p_main (p_status out varchar2)                  lv_t1_tbl   t1_tbl := t1_tbl ();            cursor t1_cur                        (select emp_name t1);         begin           open t1_cur;            loop              fetch t1_cur                 bulk collect lv_t1_tbl                 limit 10000;               insert t2 (emp_name)                 select emp_name table (lv_t1_tbl);               exit when t1_cur%notfound;           end loop;            commit;        exception           when others                        p_status := 'fail';              raise;        end p_main;     end test_pkg_v; / declare var varchar2(4000); begin test_pkg_v.p_main(var); end;

while executing procedure throws ora-00902: invalid datatype. if comment out insert statement inside procedure, running fine. problem here , me resolution.

for able that, types have created outside of package. here corrected version. downside is, types beeing created, if alter table change type of column, might forget modify type.

create type t1_type object ( emp_name varchar2(40)); create type t1_tbl table of t1_type;  create or replace package test_pkg_v    procedure p_main (p_status out varchar2); end test_pkg_v; /  create or replace package body test_pkg_v    procedure p_main (p_status out varchar2)          lv_t1_tbl   t1_tbl;        cursor t1_cur                (select t1_type(emp_name) emp_name t1);     begin       open t1_cur;        loop          fetch t1_cur             bulk collect lv_t1_tbl             limit 10000;           insert t2 (emp_name)             select emp_name table (lv_t1_tbl);           exit when t1_cur%notfound;       end loop;        commit;    exception       when others                p_status := 'fail';          raise;    end p_main; end test_pkg_v; /

if want types generic , adapt table, think have :

create or replace package test_pkg_v     type t1_type record (emp_name t1.emp_name%type);    type t1_tbl table of t1_type;     procedure p_main (p_status out varchar2);    function get_t1 return t1_tbl pipelined;  end test_pkg_v; /  create or replace package body test_pkg_v  cursor t1_cur (select emp_name t1);  function get_t1 return t1_tbl pipelined   lv_t1_tbl t1_tbl; begin   open t1_cur;   fetch t1_cur bulk collect lv_t1_tbl;   close t1_cur;   idx in 1..lv_t1_tbl.count loop     pipe row (lv_t1_tbl(idx));   end loop; end;  procedure p_main (p_status out varchar2) begin   insert t2 (emp_name) select emp_name table (get_t1);   commit; exception   when others     p_status := 'fail';     raise; end p_main; end test_pkg_v; /

alternatively, iterating directly on cursor :

create or replace package test_pkg_v     type t1_type record (emp_name t1.emp_name%type);    type t1_tbl table of t1_type;    procedure p_main (p_status out varchar2);  end test_pkg_v; /  create or replace package body test_pkg_v  procedure p_main (p_status out varchar2)   cursor t1_cur (select emp_name t1); begin   current_row in t1_cur loop     insert t2 (emp_name) values (current_row.emp_name);   end loop;   commit; exception   when others     p_status := 'fail';     raise; end p_main;  end test_pkg_v; /

Comments

Post a Comment

Popular posts from this blog

node.js - Node js - Trying to send POST request, but it is not loading javascript content -

i have been trying send post request website, , has not been loading full site. i guessing problem node js not loading javascript content, executing it. can see javascript in response, though never loads content javascript files producing in html. what mean is, doesn't execute javascript. how make node js code overcome this, , wait the full website load? have tried native code, , "request' module, both producing same errors. if helps, don't need native javascript. modules fine. some code have tried (request module): var options = { method: 'post', uri: 'https://url', formdata: { 'email': 'email', 'prize': '0', 'transactional': 'on' }, headers: { /* 'content-type': 'application/x-www-form-urlencoded' */ // set automatically } }; rp(options) .then(function (body) { console.log(body) }) .catch(function (err) { console.log(err) }); when fetch content...
Read more

javascript - Replicate keyboard event with html button -

i have tried few ways make happen. have javascript pinball game uses keyboard keys controls. converting application touch screen display. workaround, hoping overlay buttons when clicked simulate keyboard strokes. have done research , sounds should work. using jquery v3.2.1 here things have tried far... html element: <input type="button" class="coil"></input> $('.coil').trigger({type: 'keydown', which: 40, keycode: 40}); with this, not errors, nothing happens. var coilkey = $('body').keydown(function(e) { switch (e.keycode) { case 40: break;} }) $('.coil').click(coilkey); this 1 returns error in console... ((jquery.event.special[handleobj.origtype] || (intermediate value)).handle || handleobj.handler).apply not function below javascript game engine handles key listeners... addkeylistener: function (keyandlistener) { this.keylisteners.push(keyandlistener); }, // gi...
Read more

javascript - Web audio api 5.1 surround example not working in firefox -

i've been playing lott lately web audio api on both firefox , chrome. few days ago started experiment surround set. when trying surround audio in web audio api came notice example works fine in chrome v59 not in firefox v54. // create web audio api context var audioctx = new (window.audiocontext || window.webkitaudiocontext)(); audioctx.destination.channelinterpretation = 'discrete'; audioctx.destination.channelcountmode = 'explicit'; audioctx.destination.channelcount = 6; var oscillators = []; var merger = audioctx.createchannelmerger(6); console.log(audioctx.destination, merger); //merger.channelinterpretation = 'discrete'; //merger.channelcountmode = 'explicit'; //merger.channelcount = 6; var addoscilator = function(channel, frequency) { var oscillator = audioctx.createoscillator(); oscillator.frequency.value = frequency; // value in hertz oscillator.connect(merger,0,channel); oscillator.start(); oscillators.push(o...
Read more