asked on

Simple PL SQL Stored Procedure

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

Daniel Wilson

what are your table and field names?

NiciNin

ASKER

test_table
and

fname, lname and dob respectively

Thanks

Daniel Wilson

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

NiciNin

ASKER

should this read create or replace procedure?

Daniel Wilson

"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 ...

NiciNin

ASKER

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

Daniel Wilson

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

NiciNin

ASKER

This doesn't work - I don't think it's the right syntax - did you run this?

Daniel Wilson

No, I haven't tried it. What's it doing instead? Syntax error?

What version of Oracle are you using?

ASKER CERTIFIED SOLUTION

NiciNin

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial