We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

Simple PL SQL Stored Procedure

NiciNin
NiciNin asked
on
Medium Priority
401 Views
Last Modified: 2013-12-07
Hi All,

I just want to create a simple SP that will return some rows...

This is my table...

Christopher      Jones      01/11/1970
Maria      Marshall      02/01/1977
Jonathan      Campbell      09/08/19754      
Daemon      Thompson      11/02/1969
Christopher      Jones      01/11/1970

I can't seem to create one that return the results of this table

Thanks,
Nici
Comment
Watch Question

what are your table and field names?

Author

Commented:
test_table
and

fname, lname and dob respectively

Thanks
http://www.devshed.com/c/a/Oracle/Developing-Simple-PL-SQL-Stored-Procedures-for-CRUD-Operations/

Create Or Replace Test_Proc()
AS
BEGIN
  Select fname, lname, dob from test_table;
END;

Open in new window

Author

Commented:
should this read create or replace procedure?

"Create Or Replace" is Oracle's way of saying "change it if it's there or add it if it isn't."  I wish MS SQL Server had something similar ...

Author

Commented:
No, sorry I understand that. This doesn't work. If I add create or replace PROCEDURE, which I think is the correct syntax I get

Error: PLS-00428: an INTO clause is expected in this SELECT statement

I've tried place it into a variable, and declaring it as type OUT but I was getting an "incorrect number of arguments" error
OK, I learned something!
http://asktom.oracle.com/tkyte/ResultSets/index.html


Create Or Replace Test_Proc( p_cursor in out types.cursorType )
AS
BEGIN
  Open p_cursor for Select fname, lname, dob from test_table;
END;

Open in new window

Author

Commented:
This doesn't work - I don't think it's the right syntax - did you run this?
No, I haven't tried it.  What's it doing instead?  Syntax error?

What version of Oracle are you using?
Commented:
this is it...


/** From Oracle 9 */
create or replace procedure test( p_deptno IN number
                                , p_cursor OUT SYS_REFCURSOR)
is
begin
  open p_cursor FOR
  select *
  from   emp
  where  deptno = p_deptno;
end test;


/* Strong type */

create or replace procedure test( p_deptno IN number
                                , p_cursor OUT REFCURSOR_PKG.STRONG
REF_CURSOR)
is
begin
  open p_cursor FOR
  select *
  from   emp
  where  deptno = p_deptno;
end test;

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.