How to execute the followin procedure
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: How to execute the followin procedure

  1. #1
    Join Date
    Dec 2000
    Posts
    255
    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

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    variable x refcursor

    exec demo_ref(:x, 2)

  3. #3
    Join Date
    Dec 2000
    Posts
    255

    Wink

    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

  4. #4
    Join Date
    Dec 2000
    Posts
    255
    Oh , Nobody is answering
    Can I atleast know how to use Call function in Java over here

    Amol

  5. #5
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    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


  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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
  •  



Click Here to Expand Forum to Full Width