[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 388
  • Last Modified:

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
0
NiciNin
Asked:
NiciNin
  • 5
  • 5
1 Solution
 
Daniel WilsonCommented:
what are your table and field names?
0
 
NiciNinAuthor Commented:
test_table
and

fname, lname and dob respectively

Thanks
0
 
Daniel WilsonCommented:
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

0
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
NiciNinAuthor Commented:
should this read create or replace procedure?

0
 
Daniel WilsonCommented:
"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 ...
0
 
NiciNinAuthor 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
0
 
Daniel WilsonCommented:
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

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

What version of Oracle are you using?
0
 
NiciNinAuthor 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;
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now