How to create a procedure with a recordset return
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: How to create a procedure with a recordset return

  1. #1
    Join Date
    Sep 2001
    Posts
    99
    see follows:
    SQL> create or replace procedure sp_try
    2 as
    3 begin
    4 select * from try;
    5 end;
    6 /
    警告: 创建的过程带有编译错误。
    (warning:the procedure has a compiled error.)
    SQL> show errors
    PROCEDURE SP_TRY 出现错误:
    LINE/COL ERROR
    -------- -------------------------------------------------
    4/4 PLS-00428: 在此 SELECT 语句中缺少 INTO 子句
    4/4 PL/SQL: SQL Statement ignored
    I want this procedure return a multirows recordset. the table of try have many rows. I want these all rows can return. How should I do?
    Please help
    thanks
    ocean

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

  3. #3
    Join Date
    Aug 2001
    Location
    Waterloo, On
    Posts
    547
    You have to get the rows into a variable one by one in a loop. Use a cursor:

    Code:
    Create or replace procedure sp_try
    is
    cursor my_cursor is select * from try;
    my_try try%rowtype;
    Begin
    open my_cursor;
    loop
    fetch my_cursor into my_try;
    exit when my_cursor%notfound;
    dbms_output.put_line(my_try.column1||' '||my_try.column2);
    end loop;
    end;
    /
    show errors
    /

    Raminder Singh

    Oracle Certified DBA: Oracle 8i, 9i


    Mail me at raminderahluwalia@rediffmail.com.

  4. #4
    Join Date
    Sep 2001
    Posts
    99

    what about in a procedure?

    pando, your example is a function, return a cursor. Please understand me.
    I use jsp and oracle to build web application. In jsp , I use java.sql.CallableStatement class to call a oracle procedure or function. there is a executeQuery method where return a ResultSet calss. So I want the procedure return a resultSet like java.
    For example in sql server, I can only build a procedure(there is not function in sql server) as follows:
    Create procedure sp_try
    begin
    select * from try
    end
    In asp, I use this statement as follows
    <%
    set conn = Server.CreateObject("ADODB.Connection")
    conn.Open 'this is a connection string'
    set rs = conn.Execute("exec sp_try")
    'then rs have a recordset, this is a class "ADODB.RecordSet"
    %>
    The java.sql.ResultSet in java is equal to ADODB.Recordset in asp. But the oracle procedure is not like sql server procedure
    if I use a funciton which returns a cursor type, I will use which type in java?
    Hope you understand me.
    Please help
    thanks
    ocean

  5. #5
    Join Date
    Sep 2001
    Posts
    99

    thank you, I know all

    Pando,thank you very much. I read the article you gived. Now I know how to do. Thank you very much.
    Could you tell me how to you find this article?
    ocean

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Tom Kyte is pretty popular guy, if you read Oracle Magazine online you will see a link to his site

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