oracle - Output parameter value is shown as 'invalid identifier' -


i trying retrieve values temporarily created tables. return value throws error 'invalid identifier'

create or replace procedure edu_stream (input in varchar2,vals out varchar2)         inp varchar2(30);     valu varchar2(30);     begin     inp:=input;     if inp='secondary education'           execute immediate'with secedu (     (select "icse" name dual ) union     (select "cbse" name dual ) union     (select "state board" name dual)     )             select name valu from(select name          secedu order dbms_random.random)where rownum<2';          vals:=valu;      else      if inp='intermediate education'        execute immediate'with intedu (     (select "mpc" name dual ) union     (select "bipc" name dual ) union     (select "mbipc" name dual) union     (select "cec" name dual)     )             select name valu from(select name         intedu order dbms_random.random)where rownum<2';           vals:=valu;          else      if inp='graduation'        execute immediate'with gedu (     (select "ece" name dual ) union     (select "cse" name dual ) union     (select "ce" name dual) union     (select "eee" name dual)union     (select "me" name dual)union     (select "ae" name dual)union     (select "biotech" name dual)union     (select "eie" name dual)     )            select name valu from(select name         gedu order dbms_random.random)where rownum<2';           vals:=valu;           else      if inp='post-graduation'        execute immediate'with pgedu (     (select "mca" name dual ) union     (select "mtech" name dual ) union     (select "msc" name dual) union     (select "mba" name dual)     )             select name valu from(select name         pgedu order dbms_random.random)where rownum<2';           vals:=valu;         else      if inp='phd'then       execute immediate' phdedu (     (select "doctorate of philosophy" name dual ) union     (select "doctorate of medicine" name dual ) union     (select "doctorate of science" name dual) union     (select "doctorate of computer sciences" name dual)     )             select name valu from(select name         phdgedu order dbms_random.random)where rownum<2';           vals:=valu;     end if;     end if;     end if;     end if;     end if;     end;

execution:

declare  value1 varchar2(30); cv varchar2(30); begin cv:='secondary education'; edu_stream(cv,value1); dbms_output.put_line('val is'||value1); end;

error report:

error starting @ line 2 in command: declare value1 varchar2(30); cv varchar2(30); begin cv:='secondary education'; edu_stream(cv,value1); dbms_output.put_line('val is'||value1); end; error report: ora-00904: "icse": invalid identifier ora-06512: @ "datafocus_group.edu_stream", line 9 ora-06512: @ line 6 00904. 00000 - "%s: invalid identifier" *cause:
*action:

if use 'icse'instead of "icse"

error shows-

pls-00103: encountered symbol "icse" when expecting 1 of following:
error 103 * & = - + ; < / > @ in mod remainder not rem return
returning <> or != or ~= >= <= <> , or
like2 like4 likec between using || multiset bulk
member submultiset

you can avoid dynamic sql; also, due confusion created dynamic sql, using " instead of '. can re-write code as:

create or replace procedure edu_stream(input in varchar2, vals out varchar2)     inp                                     varchar2(30);     valu                                    varchar2(30); begin     inp    := input;      if inp = 'secondary education'             secedu                  ((select 'icse' name dual)                   union                   (select 'cbse' name dual)                   union                   (select 'state board' name dual))         select name           valu           (  select name                     secedu                 order dbms_random.random)          rownum < 2;          vals    := valu;     else         if inp = 'intermediate education'                     intedu                      ((select 'mpc' name dual)                       union                       (select 'bipc' name dual)                       union                       (select 'mbipc' name dual)                       union                       (select 'cec' name dual))             select name               valu               (  select name                         intedu                     order dbms_random.random)              rownum < 2;              vals    := valu;         else             if inp = 'graduation'                             gedu                          ((select 'ece' name dual)                           union                           (select 'cse' name dual)                           union                           (select 'ce' name dual)                           union                           (select 'eee' name dual)                           union                           (select 'me' name dual)                           union                           (select 'ae' name dual)                           union                           (select 'biotech' name dual)                           union                           (select 'eie' name dual))                 select name                   valu                   (  select name                             gedu                         order dbms_random.random)                  rownum < 2;                  vals    := valu;             else                 if inp = 'post-graduation'                                     pgedu                              ((select 'mca' name dual)                               union                               (select 'mtech' name dual)                               union                               (select 'msc' name dual)                               union                               (select 'mba' name dual))                     select name                       valu                       (  select name                                 pgedu                             order dbms_random.random)                      rownum < 2;                      vals    := valu;                 else                     if inp = 'phd'                                             phdedu                                  ((select 'doctorate of philosophy' name dual)                                   union                                   (select 'doctorate of medicine' name dual)                                   union                                   (select 'doctorate of science' name dual)                                   union                                   (select 'doctorate of computer sciences' name dual))                         select name                           valu                           (  select name                                     phdgedu                                 order dbms_random.random)                          rownum < 2;                          vals    := valu;                     end if;                 end if;             end if;         end if;     end if; end;

also, please notice variables inp , valu not strictly necessary: can use parameters check input value , build output parameter.

in case need use dynamic sql (not here, maybe in future) right way like:

declare     number; begin     execute immediate 'select 1 dual' a; end;

Comments

Post a Comment

Popular posts from this blog

php - How to add and update images or image url in Volusion using Volusion API -

actually have store importing products store product data insert , update image or image url not insert , update. this xml code name datapro.txt. <products_joined> <productcode>3710_012t</productcode> <vendor_partno>eah5450silent/di/1gd3(lp)</vendor_partno> <productname>test product ta</productname> <hideproduct>n</hideproduct> <stockstatus>20</stockstatus> <lastmodified>1/5/2016 10:25:00 am</lastmodified> <lastmodby>2</lastmodby> <productweight>0.9</productweight> <productprice>100</productprice> <productmanufacturer>asus tek</productmanufacturer> <vendor_price>32.69</vendor_price> <numproductssharingstock>0</numproductssharingstock> <categoryids>107</categoryids> <producturl>http://tebkq.mvlce.servertrust.com/productdetails.asp?productcode=3710_012t</producturl>
Read more

javascript - jQuery UI Splitter/Resizable for unlimited amount of columns -

using jqueryui splitter or resizable functions want create layout allows unlimited amount of columns added , columns need resizable. so fiddle: https://jsfiddle.net/f3glh3mw/ but in fiddle html contains out of nested sets instead of inline. limits possibilities above fiddle can have equal widths 2,4,8,16,32 etc columns. in between shows crooked can see in fiddle. when researching came on question: jquery ui , splitter if @ top answer shows structure like: <div class="wrap"> <div class="resizable resizable1"></div> <div class="resizable resizable2"></div> </div> full fiddle: http://jsfiddle.net/8qztj/86/ which structure want javascript hardcoded 2 resizable items. so here question: how modify last fiddle works amount of .resizable divs. or know of plugin can this. thanks helping! so far found plugin: http://www.bacubacu.com/colresizable/ which says , close functionality want. in
Read more

javascript - IE9 error '$'is not defined -

Image
<!--[if lte ie 9]><html class="lte9"><![endif]--><!--[if gt ie 9]><html><![endif]--> <!--[if !ie]><!--> <html> <!--<![endif]--> blah blah <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.1/jquery.min.js"></script> <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script> <script src="{{ asset('js/mypage.js') }}" type="text/javascript"></script> </body> </html> when load page, ie9 brings error '$' not defined . how can solve error? +++ mypage.js $(function(){ //when load page, //ie9 tag not moving. $(window).load(function() { $('div.mask').hide(); }); // when click , willshow show. //for not ie 9 if (!$('html').hasclass('lte9')) { var = $('ul.about > li > span.activebtn');
Read more