Win a copy of Think Java: How to Think Like a Computer Scientist this week in the Java in General forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Help required on Stored Procedure

 
ManavChowdary Chalasani
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • 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
 
Julian Kennedy
Ranch Hand
Posts: 823
  • Mark post as helpful
  • send pies
  • 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
 
Francis Siu
Ranch Hand
Posts: 867
  • Mark post as helpful
  • send pies
  • 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 ]
 
Avi Abrami
Ranch Hand
Posts: 1141
1
Java Oracle
  • Mark post as helpful
  • send pies
  • 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.
 
Anees Kasmani
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • 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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic