• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Finding difficulity in Parameter utilisation in procedure..!

 
Ranch Hand
Posts: 96
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Experts,
We have a procedure with 3 in parameter. The parameter p3 has got multipule value, we select the values from application,

create procedure proc_A(p1 IN integer,p2 IN integer,p3 IN varchar)
declare
a integer;
b varchar2(4000);
begin
b := replace('a|b|c|d',|',''', '''); -- where p3 is 'a|b|c|d'
b := chr(39) || b || chr(39); -- where b will be having value of p3 in formated mulitple entry ('a', 'b', 'c', 'd')
insert into taba(c1,c2,c3) select a,c,x from tabb where tabb.x in ( b) -- since b will be ('a', 'b', 'c', 'd') like list of values, this is the place where we find difficulty.
end;
/

The table tabb has values:

select x from tabb;
a
b
c
d
e
f

Our problem is that when i execute the procedure it doesn't insert any records into the table taba when you pass p3 parameter as 'a|b|c|d'.
Instead of passing the parameter p3 as formated b, if i do hard code value in the place of insert query in the procedure, it executes perfectly, but requirement is such that, the user select the list of values where we cannot define the values what he/she going to select.

insert into taba(c1,c2,c3) select a,c from tabb where tabb.x in ( 'a', 'b', 'c', 'd' )

we pass parameter from application to this procedure and the parameter p3 is multipe selection in application

execute proc_A(5,1,'a|b|c|d')

it executes successfully without any insert. We found out the difficulties but unable to solve the same.
the difficulty is that in insert statement it takes the full p3 parameter value as single value, instead of considering as list of values.

Do require your suggestion to solve this issue
 
ranger
Posts: 17347
11
Mac IntelliJ IDE Spring
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Well, I don't have a direct answer for you, but can help with debugging.
You can try to use the EXCEPTION section to catch exceptions, and see which one returns.
You can have as many BEGIN/EXCEPTION/END in your code as you want, so if you don't have a way to use a real debugger on your procedure you can use this and catch WHEN OTHERS and return some string that you can see and then find out which line is giving you problems.
Hope that helps
Mark
 
Ranch Hand
Posts: 1143
1
Eclipse IDE Oracle Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Raja,
You need to use "native dynamic SQL" (NDS). Here are the results of a search for NDS on Oracle's OTN Web site, and these are the results of the same search on the Ask Tom Web site.
You can also find details in Oracle's "PL/SQL Guide" (part of the documentation for Oracle).
Good Luck,
Avi.
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic