aspose file tools*
The moose likes Oracle/OAS and the fly likes Help required on Stored Procedure Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "Help required on Stored Procedure " Watch "Help required on Stored Procedure " New topic
Author

Help required on Stored Procedure

ManavChowdary Chalasani
Greenhorn

Joined: Dec 19, 2003
Posts: 2

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

Joined: Aug 02, 2004
Posts: 823
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

Joined: Jan 04, 2003
Posts: 867
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 ]

Francis Siu
SCJP, MCDBA
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1121

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

Joined: Aug 19, 2004
Posts: 1
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
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Help required on Stored Procedure
 
Similar Threads
Callable Statement
Varchar & Varchar2 in Stored procedure
Error in jdbc while executing procedure
PL SQL cursor is throwing error...any idea?
Execution of stored procedure using for loop