How do I create a procedure without parameters?

[adam0101]

Trying to create a procedure without parameters gives me this error:

SQL> CREATE OR REPLACE PROCEDURE usp_GetFleetTypes
  2  AS 
  3  BEGIN 
  4  
  5  SELECT  *
  6  FROM  Fleet_Type
  7  ORDER BY ft_Description;
  8  
  9  END;
 10  /

Warning: Procedure created with compilation errors.

SQL> show errors;
Errors for PROCEDURE USP_GETFLEETTYPES:

LINE/COL ERROR
-------- --------------------------------------------------------------
5/1      PLS-00428: an INTO clause is expected in this SELECT statement

Including the parentheses doesn't help either:

SQL> CREATE OR REPLACE PROCEDURE usp_GetFleetTypes
  2  (
  3  )
  4  AS 
  5  BEGIN 
  6  
  7  SELECT  *
  8  FROM  Fleet_Type
  9  ORDER BY ft_Description;
 10  
 11  END;
 12  /

Warning: Procedure created with compilation errors.

SQL> show errors;
Errors for PROCEDURE USP_GETFLEETTYPES:

LINE/COL ERROR
-------- ----------------------------------------------------------------
3/1      PLS-00103: Encountered the symbol ")" when expecting one of the
         following:
         <an identifier> <a double-quoted delimited-identifier>
         current

Can someone help?

Adam
while(ignorance){perpetuate(violence,fear,hatred);life=life-1};

 

[lewisp]

The error is not in your procedure declaration, but in the cursor. The cursor is implicit and as such requires a variable into which the data SELECTed willl be placed.

CREATE OR REPLACE PROCEDURE usp_GetFleetTypes AS 
  l_row Fleet_Type%ROWTYPE;
BEGIN 
 
SELECT  *
INTO  l_row
FROM  Fleet_Type
ORDER BY ft_Description;

END;
/

 

[gunjan14]

Hi,

This has nothing to do with parameters. Infact you are selecting something and so it has to be put in a variable. Also if you want to select multiple records then a simple select is not going to work. (will throw the error during run time)
So either you have to use a cursor or limit your select statement such that it returns only one row.

Try this

REM I am limiting the number of records to 1 using rownum

create or replace procedure usp_getfleettypes
as
    lNum fleet_type%rowtype; 
begin 
 
    select * [b]INTO lNum [/b] 
    from   fleet_type
    where  rownum < 2;

end;
/

But if you have to select multiple records then

REM This is going to use cursor

create or replace procedure usp_getfleettypes
as
    cursor c is 
    select * from fleet_type;
begin 
 
    for lNum in c loop
        <Your code>
    end loop;

end;
/

Regards,
Gunjan

 

[SantaMufasa]

Adam,

Lewis and Gunjan suggest excellent solutions. Here is a syntactical alternative to Gunjan's explicit cursor. It uses an "in-line" or "implicit" cursor. The example below uses table data similar to yours:

CREATE OR REPLACE PROCEDURE usp_GetFleetTypes AS 
BEGIN 
    for r in (SELECT  * FROM  s_emp ORDER BY last_name) loop
        dbms_output.put_line (r.last_name);
    end loop;
END;
/

Procedure created.

set serveroutput on
exec usp_GetFleetTypes

Biri
Catchpole
Chang
Dancs
Dumas
Giljum

Let us know if this method is helpful in resolving your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
@ 01:27 (27Oct04) UTC (aka "GMT" and "Zulu"),
@ 18:27 (26Oct04) Mountain Time

 

[adam0101]

Thank you all who have responded. I forgot that Oracle requires you to use a cursor to output records. I'm looking for the simplist way to return all the records of a query to an ASP.NET application. Which method is best to accomplish that? I guess I'll have to post my question in the ASP.NET forum to find how to handle the recordset once I get it.

Adam
while(ignorance){perpetuate(violence,fear,hatred);life=life-1};