Building simple oracle procedure

[MrHelpMe]

Hello,

I am extremely puzzled. I am using SQLDeveloper and trying to create a stored procedure on oracle database. I have a sql server backround but not much in oracle. I've been told that it is relatively the same but I am not finding that. I will be connecting the stored procedure(oracle 9i) to crystal reports ver 9 and wanted to see if a simple procedure will work in crystal but I cannot create a simple procedure. I have created the following stored procedure;

CREATE OR REPLACE PROCEDURE test
AS
BEGIN
select first_name from shr_people;
END;

I have compiled it an run the script and it fails. Why does it fail?
So then I researched this and found that I needed to add an INTO(still have no idea why) and modified my code to read;

CREATE OR REPLACE PROCEDURE test(
fname IN OUT varchar
)
AS
BEGIN
select first_name into fname
from shr_people;
END;

Ran script and it created the procedure successfully.

Now my questions.....1) why the use of the into? 2) how do I run the procedure once completed? 3)Does oracle not return recordsets like sql server. Thanks everyone and sorry for the basic questions.

 

[SantaMufasa]

MrHelpMe,

Your questions are all well taken and very reasonable.

1) why the use of the into?

As with any SQL SELECT statement, the functionality of the command is to OBTAIN from some designated table, zero, one, or many resulting rows for a result set. Additionally, each flavour of SQL must PUT the resulting rows somewhere. For a "vanilla" SELECT (i.e., a SELECT that is not part of a procedural block such a s PL/SQL; for a SELECT executed from a native command line), the default "somewhere" to which the SELECT Puts the results is ultimately the screen.


In the case of PL/SQL, there is no "screen" directly available to which to PUT the resulting rows. Since there is no native "screen" to act as a target for SELECTed rows, we need some "memory structure" (such as a variable, an array, a cursor, or some other "collection mechanism") into which we can instruct Oracle's PL/SQL to "place" the results. In your example, you designated as your target "fname", an INput/OUTput argument for your procedure, "TEST".

[As an aside, Oracle PL/SQL's lack of a native "verb" (or some other syntactical designation) to easily effect input and output from within a PL/SQL block is one of the major PL/SQL deficiencies for which I complained bitterly during my 63-dog-year tenure at Oracle Corporation from 1988 to 1997.]

In addition to having to designate a PL/SQL "target" for your result, since the target is a single-value target and because of the syntax construct that you used ("SELECT...INTO"), you must ensure that the result of your SELECT is precisely one value: zero resulting rows or two or more resulting rows will throw an error.

For result sets that contain zero, one, or many rows, there are multiple PL/SQL constructs available (e.g., CURSORs, arrays, "bulk collect", et cetera). But in each case, note that some "memory structure" must be the target of the SELECT...there is no default target as is the case with a standard "SELECT <expression-list> FROM <table_name>;"

2) how do I run the procedure once completed?

There are multiple methods for invoking a (stored)procedure (as is the example you proffered) once it compiles successfully:


a) You may invoke the procedure from within another (either "anonymous PL/SQL block" or a named, user-defined PL/SQL FUNCTION, PROCEDURE, or PACKAGED PROCEDURE. So, in the case of your "test" example, you can invoke it thusly:

(anonymous block)
SQL>
DECLARE
   x varchar2(50);
BEGIN
   ...
   test(x);
   ...
END;
(user-defined function, procedure, or packaged procedure)
CREATE OR REPLACE [FUNCTION/PROCEDURE] <some_name>...is
   z varchar2(50);
BEGIN
   ...
   test(z);
   ...
END;

b) You may invoke your procedure from an SQL prompt:

SQL> var x varchar2(50)
SQL> exec test(:x)

In the above example, SQL*Plus's "EXEC" command simply places the code, "testx)" within an anonymous block's "BEGIN...END;" wrapping.

3)Does oracle not return recordsets like sql server?

Does Oracle return recordsets?...Yes (as per above)...like SQL Server?...not quite (or not near enough), obviously. It is simply a different "mind set". I'm sorry that ANSI-standard SQL is by no means implemented across vendors as any form of standard.

Good luck!

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[MrHelpMe]

Wow,

Thank you so much SantaMufusa for the time to write and reply. I really appreciate this. Now I understand that Microsoft may have it's set of downfalls but damn I have to say they do a good job at making there applications user-friendly. As for Oracle, it's going to take much time before I put my head around this. Sounds complicated. I hope everyone doesn;t mind the questions that may arise during my learning but I would really appreciate the help. Regardless, can I ask one more question. I've been looking at prior posts and different threads and noticed the use of cursors. In your opinion, since I am a beginner, would this be the easiest to learn as for the memory structure. Also, are their some tools that will take t-sql(Sql server) stored procedures and automatically convert them to Oracle 92i. Thanks again SantaMufusa.

 

[Turkbear]

Hi,
Welcome to Oracle - the learning curve is steep and somewhat front-loaded..

It is probably best to forget most of what you know about SqlServer - the Oracle architecture and methodology are quite different and can cause great confusion since some of the same terms ( like Database, for instance) have vastly different meanings..

( I recommend some good books - Like Thomas Kyte's expert one-on-one Oracle - and some formal training).

Bon Voyage and remember, if its not fun, be sure and get paid to do it..







To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[SantaMufasa]

Mr...

Absolutely correct (on the Cursor topic). I use CURSORs nearly exclusively for PL/SQL access of tables (especially if there is any chance that my query could return either 0 or >1 rows). About the only time that I use a "SELECT...INTO" construct in PL/SQL is if I am SELECT-ing a GROUP function such as COUNT, MIN, MAX, SUM, et cetera, which, by definition returns only one row.

Since CURSORs provide "the best of all worlds", I recommend your learning about "implied CURSORs" with "Cursor 'FOR' loops" (my favourite, due to their absolute simplicity coupled with my inherent laziness).

Here is a working example of such a construct that illustrates their simplicity:

SQL> set serveroutput on format wrap
SQL>
begin
    for r in (select last_name||', '||first_name Employee
                from s_emp
               order by Employee) loop
        dbms_output.put_line(r.Employee);
    end loop;
end;
/
Biri, Ben
Catchpole, Antoinette
Chang, Eddie
...
Smith, George
Urguhart, Molly
Velasquez, Carmen

PL/SQL procedure successfully completed.

Let us know if this provides to you a more simplified method of dealing with result sets in PL/SQL.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[MrHelpMe]

Thanks again everyone. Yes I do have great ways before I attain the level that each of you have. Thank you again.

 

[SantaMufasa]

I do have great ways before I attain the level that each of you have.

That may be true for Oracle, MrHelpMe, but there are days that I would trade a significant percentage of my paycheck for your knowledge of SQL Server.


Anyone who claims that they know everything about all database software would lie about other things, as well.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.