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;
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).
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 ]
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 firstname.lastname@example.org as I do not check the group frequently.