• 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
  • Ron McLeod
  • Rob Spoor
  • Tim Cooke
  • Junilu Lacar
Sheriffs:
  • Henry Wong
  • Liutauras Vilda
  • Jeanne Boyarsky
Saloon Keepers:
  • Jesse Silverman
  • Tim Holloway
  • Stephan van Hulst
  • Tim Moores
  • Carey Brown
Bartenders:
  • Al Hobbs
  • Mikalai Zaikin
  • Piet Souris

help in creating procedure

 
Ranch Hand
Posts: 36
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi,

I am creating a procedure which takes an array and a numeric value as input and returns 2 arrays and a resultset , signature is as follows :



where TA_ITEM_CODE is a type :



In procedure I want to retrieve some values according to some condition, Query which i want to execute is :


In above code bar_codes is an array which i'll send from java.
When I'm trying to compile the procedure in Oracle it is giving me exception "Expected number but found TA_ITEM_CODE". I'm not able to understand why its giving an error
because itm.ITEM_NO is of type Varchar in Item table. Please help me where I'm going wrong. Just for the reference following is my java code
which i'll use to call this procedure once it will compile properly :



Thanks in Advance,
Namrta.
 
Ranch Hand
Posts: 118
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I think it doesnt like your In part of your where clause:


you will need to use the table function to create a vararray from the bar_codes table.
 
Namrta Pandey
Ranch Hand
Posts: 36
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
You are right that the problem is with bar_codes, I have already created this of table type. But the problem is this that bar_codes contains values of array and when i'm using this variable in Select statement using "in" then it is not accepting that. Although I have resolved the problem by iterating the bar_codes and putting Select statement in loop, but still for other purpose i need to use "In" clause in select statement. Is there anyway to use array passed by java in Select statement.

Thanks,
Namrta.
 
Namrta Pandey
Ranch Hand
Posts: 36
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi,

Ref. to the above code where i am passing array to the procedure :



I am getting performance issue when two users simultaleously try to run the application my weblogic server is getting down. I have more than 7000 values in array, please tell me the efficient way to pass array in procedure from java code.

Waiting for the reply.

Thanks & Regards,
Namrta.
 
Agador Paloi
Ranch Hand
Posts: 118
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
If you are passing in an array of 7000 to use in your in clause , that would kill your performance.
You could put your 7000 entries into a table with an index and do a join instead of using the in clause.
The in clause would work fine for smaller volume .
 
Namrta Pandey
Ranch Hand
Posts: 36
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks for the reply Agador, In my procedure I am not using in clause rather i am using the following code :



but my problem is not with the procedure, It is at the java end, when I am passing array values to the procedure from java code at that time server is getting down.
 
He got surgery to replace his foot with a pig. He said it was because of this tiny ad:
Thread Boost feature
https://coderanch.com/t/674455/Thread-Boost-feature
reply
    Bookmark Topic Watch Topic
  • New Topic