Select Stored Procedure? 1

[ThatRickGuy]

Hey Guys,
I just started a new project working with an Oracle 10g back end and I'm getting stumped on something that seems like it should be simple. I'm looking for a way to have a SELECT statement in a Stored Procedure:

CREATE OR REPLACE PROCEDURE HTS.AUDIO_SELECTALL (
)IS
BEGIN

SELECT audio.slide_id, 
       audio.audio, 
       audio.language_id, 
       audio.audio_id,
       audio.createdby, 
       audio.createddate, 
       audio.modifiedby,
       audio.modifieddate
  FROM hts.audio

END AUDIO_SELECTALL;

But I am getting "PLS-00428: an INTO clause is expected in this SELECT statement". I've seen some info on the net about selecting all of the data into parameters, but I'm not sure how well that is going to work in a case like this where I'm trying to retrieve the entire table.

Is there some better way to have a select statement that is stored on the server?

Thanks,
-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[SantaMufasa]

Rick,

What do you want to actually do with the table once you have SELECTed all that data? Oracle's stored procedures expect you to actually do something with the data inside the procedure, else pass the data out in something like a "refcursor".

Here is some (Oracle PL/SQL) code that will let you syntactically execute the SELECT:

CREATE OR REPLACE PROCEDURE HTS.AUDIO_SELECTALL (
)IS
BEGIN
FOR x in (SELECT slide_id, 
                 audio, 
                 language_id, 
                 audio_id,
                 createdby, 
                 createddate, 
                 modifiedby,
                 modifieddate
            FROM hts.audio) Loop
    dbms_output.put_line(x.slide_id
        ,x.audio
        ,x.language_id
        ,x.audio_id
        ,x.createdby
        ,x.createddate
        ,x.modifiedby
        ,x.modifieddate);
END LOOP;
END AUDIO_SELECTALL;

The "dbms_output.put_line" command simply represents "doing something" with the data inside the loop.

If, as I mentioned, you simply want to pass to the calling environment, the results of your query, let me know and I'll post the code for SELECTing data into a "refcursor" and "passing" the results back to the calling environment.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[ThatRickGuy]

I think your last statement describes what I am looking to do. I have a series of CRUD functionality SQL Statements. For ease of maintenance and coding, I want to store all of those statements in the database. These stored procs will then be called from a .Net application.

Thanks!

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[SantaMufasa]

Rick,

I don't use .NET, so I can't do a proof-of-concept using it, but I can illustrate how to do what you want using native PL/SQL. In the example, below, I just used a simple two-column table (S_REGION) in place of your "hts.audio" table, but all of the priciples are the same...where I used a one-line SELECT statement, you could replace it with your original SELECT, above:

create or replace procedure selectall
    (curs out sys_refcursor) is
begin
    open curs for 'select * from s_region';
end;
/

Procedure created.

set serveroutput on format wrap
declare
    x sys_refcursor;
    y s_region%rowtype;
begin
    selectall(x);
    loop
        fetch x into y;
        exit when x%notfound;
        dbms_output.put_line(y.id||': '||y.name);
    end loop;
end;
/

1: North America
2: South America
3: Africa / Middle East
4: Asia
5: Europe

PL/SQL procedure successfully completed.

Let us know if this is what you wanted.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[ThatRickGuy]

Hi Mufasa, Thanks for the code! Question for you, is there a performance hit associated with using a loop to pull out the data vs a select statement?

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[ThatRickGuy]

Okay, after a bit more searching I think I've found what I was looking for. In order to store a SELECT query as a procedure on the server, you have to put it into a package. Why? Who knows, but this is what I finally got to work:

CREATE OR REPLACE PACKAGE HTS.pak_Audio 
AS
  TYPE CURSOR_TYPE IS REF CURSOR;
  PROCEDURE usp_Select (results_cursor OUT CURSOR_TYPE);
END; 
/
CREATE OR REPLACE PACKAGE BODY HTS.pak_Audio 
AS
  PROCEDURE usp_Select (results_cursor OUT CURSOR_TYPE)
  AS
  BEGIN
    OPEN results_cursor FOR
       SELECT audio.audio_id,
              audio.slide_id, 
              audio.audio, 
              audio.language_id,
              audio.createdby, 
              audio.createddate, 
              audio.modifiedby,
              audio.modifieddate
         FROM hts.audio;
  END;
END;

These packages look pretty handy for grouping my CRUD functionality. I've moved all of the AUDIO procs to the pak_Audio package, and you can overload proc names (so long as the signatures differ) so I can have my SelectAll and Select(primary key) procs both called "Select" and just use the parms to differentiate. But it still blows my mind that you can't have an unpackaged stored proc that returns data.

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[Thargy]

Rick,

you're on the money there! Overloading is one of the nice parts of packages. Basically, by using packages, you restrict the scope of variables to within the package, thereby providing encapsulation. This is one of the biggest bonuses of packages.

Yes, you can have a procedure which returns data, but you have to do things slightly differently. In a package, you can declare a refcursor and use it. Without a package, you have to use sys_refcursors, which are an oracle pre-declared type. Using sys ref cursors lets either a procedure or a function return datasets.

Would you like some samples of functions returning data that way?

Regards

Tharg

Grinding away at things Oracular

 

[ThatRickGuy]

Thanks Tharg,
I think I've got it down now, I just wish I could have found this solution sooner. I had no idea how to pull this off until I bumped into a WROX book sample on google. Took a few hours to find the instructions.

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[Thargy]

Rick,

that was a silly question on my part.

http://asktom.oracle.com/tkyte/ResultSets/

provides chapter and verse on this.

The "ask tom" site is hallowed ground for Oracle groupies, but is largely definitive for Oracle issues. Ask Tom is a bit like asking messrs. kernighan and Richie about C or Bjarne Stroustrup about C++.

If you want more than the above, then let us know.

Regards

Tharg

Grinding away at things Oracular

 

[ThatRickGuy]

Tharg, Thanks a ton for the link! I've been working in SQL Server for the last 3 years, so I'm still trying to get some decent resource for Oracle. Ask Tom sounds like a great place to visit.

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.