Results 1 to 15 of 15
  1. #1
    Join Date
    May 2003
    Posts
    7

    Question Unanswered: Stored Procedure Question

    Hi All:

    I'm an Oracle-PL/SQL rookie. I'm somewhat emabarrassed to ask this question, but here goes:

    Can anyone give me an example of a simple Stored Procedure that does a basic "SELECT *..." and returns the result set.

  2. #2
    Join Date
    Jan 2004
    Posts
    492

    Re: Stored Procedure Question

    Originally posted by seevo
    Hi All:

    I'm an Oracle-PL/SQL rookie. I'm somewhat emabarrassed to ask this question, but here goes:

    Can anyone give me an example of a simple Stored Procedure that does a basic "SELECT *..." and returns the result set.
    Code:
    create or replace procedure test_procedure as
    
    cursor c_test is
    select ename, empno, sal
    from emp;
    
    begin
    for r_test in c_test
    loop
    dbms_output.put_line(ename || ' ' || empno || ' ' || sal);
    end;
    Thats the basic format you will want - I usually dont create stand-alone procedures (usually put them in packages) so I think thats the correct syntax - either way it will give you a good start.

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Stored Procedure Question

    Or perhaps you want it returned to the calling program:

    create or replace procedure get_emps
    ( p_cur out sys_refcursor
    )
    is
    begin
    open p_cur for select * from emp;
    end;
    /

  4. #4
    Join Date
    May 2003
    Posts
    7

    Question

    Thanks! I saw a sample like this before, but wasn't sure if it was valid. Maybe I'm just confused and using procedures for what I'm trying to do is incorrect.

    Is it proper practice in an Oracle world to use procedures the same way they're used in MS Sql Server?

    For example, in a Sql Server environment, I'd write a procedure to return a resultset of records matching a particular where clause - this resultset could then be displayed on a web app, etc.

    Do procedures still serve this purpose in Oracle, or do Packages do this job?

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Oracle works very differently from SQL Server. "Ref cursors" are the nearest equivalent to the way SQL Server works. PL/SQL is more "formal" than T-SQL (is that the right name?), in that all outputs from stored procedures must be defined as parameters or function return values. You can't "just select" data and have the calling program catch it.

    Packages are a (very good) way of managing stored procedures. It is good practice to put all procedures and functions into packages.

  6. #6
    Join Date
    May 2003
    Posts
    7

    Red face

    Let's say I get a procedure written that I want to test. How do I execute it to try it out?

    Another rookie question...

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    In SQL Plus you can do this (for example):

    var x number
    var y number
    exec myproc( 42, :x, :y )

    or another way:

    declare
    x number;
    y number;
    begin
    myproc( 42, x, y );
    end;
    /

  8. #8
    Join Date
    May 2003
    Posts
    7

    Still a rookie

    OK, I have this procedure:

    CREATE OR REPLACE PROCEDURE "HR"."USP_PAULS_TEST"
    (p_cur out sys_refcursor)
    IS
    begin

    open p_cur for SELECT * from EMPLOYEES;

    end USP_PAULS_TEST;

    1. What tool do I use to execute (Is the a Query Analyzer (Sql Server) equivalent?)?
    2. How do I execute to see the records returned?

  9. #9
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    It is past time for you to RTFM...
    http://download-west.oracle.com/docs...a96624/toc.htm

    Also you might try looking at the examples in
    $ORACLE_HOME//plsql/demo folder.
    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.

  10. #10
    Join Date
    May 2003
    Posts
    7
    Originally posted by anacedent
    It is past time for you to RTFM...
    http://download-west.oracle.com/docs...a96624/toc.htm

    Also you might try looking at the examples in
    $ORACLE_HOME//plsql/demo folder.
    I'm sorry, I don't understand the first part of your reply.

  11. #11
    Join Date
    Apr 2004
    Posts
    8

    Post try this!

    In SQL* Plus window do this.

    VAR rc REFCURSOR
    EXEC USP_PAULS_TEST(:rc);
    PRINT rc;

  12. #12
    Join Date
    Sep 2004
    Posts
    2

    Question Will that work on 8i?

    I'm running the following on 8i . . .

    CREATE OR REPLACE PROCEDURE PROC_TEST
    (p_cur out sys_refcursor)
    IS
    begin

    open p_cur for SELECT * from TABLENAME;

    end PROC_TEST;


    and getting the following error . .


    Warning: Procedure created with compilation errors.

  13. #13
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Sql> Show Error
    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.

  14. #14
    Join Date
    Sep 2004
    Posts
    2

    error message

    Errors for PROCEDURE PROC_TEST:

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    0/0 PL/SQL: Compilation unit analysis terminated
    2/12 PLS-00201: identifier 'SYS_REFCURSOR' must be declared


    thanks for any help.

  15. #15
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    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.

Posting Permissions

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