Results 1 to 9 of 9
  1. #1
    Join Date
    May 2009
    Posts
    6

    Red face Unanswered: Creating procedure and get returned data

    Hi everyone,

    I want to create procedure that will return a set of data (to execute a select statement) and i want to view those returned data.

    How to create this procedure and to view returned data?

    Thnx.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >How to create this procedure and to view returned data?
    Some folks would use vi while other might use notepad.
    What you use is up to you.

    By the way, we do not do homework assignments for folks.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    May 2009
    Posts
    6
    Well it is not for homework and i know it is something simple.
    I know how to do it in SQL Server but now i have an application that uses Oracle and i dont know how to create this procedure that will return the data selected from tables.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Select * From My_table;
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    May 2009
    Posts
    6
    Well guys i know this SQL.
    But to create procedure with this SQL and return that data.

  6. #6
    Join Date
    May 2009
    Posts
    6
    OK guys here is an example in SQL Server

    CREATE PROCEDURE proc_name
    @fdate datetime,
    @tdate datetime
    AS
    SELECT col1, col2 FROM table_name where table_date between @fdate and @tdate.

    This result i need in Oracle

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Contents

    There are many fine coding examples at http://asktom.oracle.com
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  8. #8
    Join Date
    May 2009
    Posts
    6
    Thanks you guys!

  9. #9
    Join Date
    Feb 2008
    Posts
    3

    Re: Creating procedure and get returned data

    Hi,

    I hope this will help you..

    A sample Stored Procedure in Oracle to store/return the SQL query result..

    CREATE OR REPLACE PROCEDURE proc_name
    (
    -- some input parameters
    in_param1 IN NUMBER,
    in_param2 IN NUMBER,
    out_param OUT SYS_REFCURSOR --OUT parameter of the type REFCURSOR
    )

    AS
    BEGIN

    OPEN out_param FOR
    SELECT ENAME,
    SAL
    FROM EMP
    WHERE EMPNO=in_param1
    AND MGR_ID=in_param2
    ;

    -- the query result will be stored in the out_param variable

    END proc_name;

    ---- end of procedure ----

    You can use this out_param in your java,where you call the stored procedure.


    let me know for any clarifications..

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •