• 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

Help required on Stored Procedure

 
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Hi All,
I�m not able to figure out how to send parameter to stored procedure so that it could be used in the in clause of select query.

I have a written some sample code �

Sample procedure
=======================
Create or Replace procedure p_test(PARAM1 IN VARCHAR) AS
NAME VARCHAR(20);
CURSOR CTAB(INCLAUSE VARCHAR) IS SELECT TNAME FROM TAB WHERE TNAME IN (INCLAUSE);
BEGIN
DBMS_OUTPUT.PUT_LINE('PARAM');
DBMS_OUTPUT.PUT_LINE(PARAM);
OPEN CTAB(PARAM);
IF(CTAB%FOUND) THEN
LOOP
FETCH CTAB INTO NAME;
EXIT WHEN CTAB%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('TABLE NAME');
DBMS_OUTPUT.PUT_LINE(NAME);
END LOOP;
ELSE
DBMS_OUTPUT.PUT_LINE(�No records found�);
END IF;
END;

Procedure call
=====================
EXECUTE P_TEST('''MAIN'',''TEST'',''TEST2''');

Result
==========
No Records Found
 
Ranch Hand
Posts: 823
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi ManavChowdary Chalasani,

Welcome to JavaRanch!

I think I can see why your query is not working, and it's not to do with the parameter you're passing.

Please correct me if I'm wrong but your dbms_output writes out the contents of the input parameter correctly as you're expecting. The problem is that the cursor doesn't return any rows, right?

What you're trying to do is something that I recall trying to do myself on more than one occasion in the past, i.e. use a dynamic IN clause. I'm pretty sure that this is not legal in Oracle.

You can get round this if you REALLY need to by using dynamic SQL (i.e. EXEC (query_string)). That may be your best option, especially in this simple case, but beware that it is considerably less efficient than executing the same query directly in a stored procedure. (i.e. Oracle has to parse your SQL and generate a query plan each time your dynamic SQL is executed, instead of just once the first time).

HTH

Jules
 
Ranch Hand
Posts: 867
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
hi ManavChowdary Chalasani
welcome
First of all, you want to print something out in open sql*plus or other's application
DO the following

Secondly, What do you want to do in your parameters passing?

You want to pass one parameter or three parameters?
if only one parameter, the query INCLAUSE that you want to extract the data out, it should match the value of varchar PARAM1 (''MAIN'',''TEST'',''TEST2'')
And I think that if your program can run probably :roll: , I suggest that please check the logic of the query careful and you may find out the answer.

[ August 17, 2004: Message edited by: siu chung man ]
 
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
Chalasani,
Yours is a very FAQ. If you haven't already seen it, I refer you to the following Web page: How can I do a variable "in list".

Good Luck,
Avi.
 
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi,

I am not sure why the stored procedure should work in first place. I see two issues. First, The procedure is declared to use one parameter and three parameters are being passed in execute statement.

Secondly, to achieve the dynamic sql, one can declare a dynamic cursor in the for loop like the example below.
c1 should be declared as Ref Cursor. (You should be able to get more help on Ref Cursor on google etc). The sql statement is just a sql string which you can generate for ex.

sql_string := ' select tname from tab where tname in ( ' || param_list || ') ';

for c1 in sql_string
loop
end loop;

Based on your current declaration (with one param) the user can supply a comma seperated string of table names to search.

If you want more info you can send me a mail at aniskasmani@yahoo.com as I do not check the group frequently.

Hope this helps.

Thanks,
Anis
 
reply
    Bookmark Topic Watch Topic
  • New Topic