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

Laravel mail error `Swift_TransportException in StreamBuffer.php line 269: Connection could not be established with host smtp.gmail.com [ #0]` -

i developing website contact-us page guest users can send me email using gmail . anytime try send mail using contact-us form, error swift_transportexception in streambuffer.php line 269: connection not established host smtp.gmail.com [ #0] this mail.php return [ 'driver' => env('mail_driver', 'smtp'), 'host' => env('mail_host', 'smtp.gmail.com'), 'port' => 587, 'from' => ['address' => '', 'name' => ''], 'encryption' => env('mail_encryption', 'tls'), 'username' => env('mail_username'), 'password' => env(mail_password), 'sendmail' => '/usr/sbin/sendmail -bs', 'pretend' => env('mail_pretend', false), ]; this .env mail_driver=smtp mail_host=smtp.gmail.com mail_port=587 mail_username=afecjaped@gmail.com mail_password=********...
Read more

c# SetCompatibleTextRenderingDefault must be called before the first -

i tried search exception couldn't find solution on case i'am using code below invoke .net application : assembly assem = assembly.load(data); methodinfo method = assem.entrypoint; var o = activator.createinstance(method.declaringtype); method.invoke(o, null); the application invoked has form , in entrypoint of application : [stathread] static void main() { application.enablevisualstyles(); application.setcompatibletextrenderingdefault(false); //exception application.run(new form1()); } setcompatibletextrenderingdefault must called before first iwin32window object created in application. edit : assembly = assembly.load(data); methodinfo method = a.gettype().getmethod("start"); var o = activator.createinstance(method.declaringtype); method.invoke(o, null); you should create new method skips initialization...
Read more