-
Hi all
How to execute the following procedure from SQL prompt
CREATE OR REPLACE PACKAGE Apack AS
TYPE RefCurTyp IS REF CURSOR;
END APACK;
/
CREATE OR REPLACE PROCEDURE demo_ref (
refcurvar IN OUT Apack.RefCurTyp,
choice NUMBER) IS
BEGIN
IF choice = 1 THEN
OPEN refcurvar FOR SELECT * FROM emp;
ELSIF choice = 2 THEN
OPEN refcurvar FOR SELECT * FROM dept;
ELSIF choice = 3 THEN
OPEN refcurvar FOR SELECT * FROM salgrade;
END IF;
END demo_ref;
/
When execute using exec demo_ref(2) It gave error. It is not even taking null as first parameter. How should I pass the first parameter
ERROR at line 1:
ORA-06550: line 1, column 13:
PLS-00306: wrong number or types of arguments in call to 'DEMO_REF'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
Amol
-
variable x refcursor
exec demo_ref(:x, 2)
-
Thanks Pando
Can I know How it is called from Java file?
In case of function we need to pass only one variable that is 'choice' and we can place question mark there but here there are parameters of type cursor.
Amol
-
Oh , Nobody is answering
Can I atleast know how to use Call function in Java over here
Amol
-
To call the procedure you declared from java:
Code:
int choice = 1;
String query = "begin demo_ref(?,?); end;";
CallableStatement cstmt = conn.prepareCall(query);
cstmt.registerOutParameter(1,OracleTypes.CURSOR);
cstmt.setInt(2,choice);
cstmt.execute();
ResultSet rset = (ResultSet)cstmt.getObject(1);
while(rset.next())
{ ... }
cstmt.close();
I think nobody's answering because it's not very easy to understand you need:
In case of function we need to pass only one variable that is 'choice' and we can place question mark there but here there are parameters of type cursor.
Do you mean function cannot have more than one parameter? Not true. It's the same as procedure and the difference is that function can return a value through it's name not just through a parameter as procedure does. If you had a function declared with refcursor return type and one parameter for choice you'd call it:
Code:
String query = "begin ? := func_demo_ref(?); end;";
...
where the first question mark is a placeholder for return value - refcursor and the second one is a placeholder for choice. The rest of the code is the same as in the example above.
If it is still unclear please post more detailed question.
HTH,
Ales
-
i didnt answer coz I dont have a clue about java
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|