CODEWITHSUNDEEP
sqloracle-databasefunctionplsql
Craig
Craig

Reputation: 47

PL SQL function problems

I'm trying to write a PL SQL function however I'm having some issues with the formatting and general syntax.

This is my current code:

create or replace function getName(name IN varchar, organisation OUT varchar, 
                   xcoord OUT number, ycoord OUT number, errormsg OUT varchar)
return varchar2
as
begin
  Select name, LOCX, LOCY
  from BBT_POI
  WHERE (UPPER(name) LIKE UPPER('%'|| NAME || '%'));
  return NAME;
end;
/

 variable input varchar;
 Variable errormsg varchar;
 Variable organisation varchar;
 Variable x NUMBER;
 Variable y NUMBER;


 execute getName ('yach', organisation, x, y, errormsg);

and these are the error messages when I run the create or replace function command: 

FUNCTION GETNAME compiled
Errors: check compiler log

Error(5,3): PLS-00428: an INTO clause is expected in this SELECT statement

and when I run the execute line I get this:

Error starting at line : 40 in command -
execute getName ('yach', organisation, x, y, errormsg)
Error report -
ORA-06550: line 1, column 24:
PLS-00201: identifier 'ORGANISATION' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

The procedure should take one IN parameter (name or part of a name), and return four OUT parameters (the name of the organisation, X-coord, Y-coord, error message(or 'none' if no error message is thrown)).

I know currently the code doesn't look for errors however I am just trying to get a simple version working which I can add more functionality to after.

Upvotes: 1

Views: 468

Answers (2)

Guneli
Guneli

Reputation: 1731

I do not have a tool right now to check whether it works for sure, but try this:

create or replace function getName(p_name IN varchar, organisation OUT varchar, 
                   xcoord OUT number, ycoord OUT number, errormsg OUT varchar)
return varchar2
as
begin
  Select name, LOCX, LOCY
  INTO p_name, xcoord, coord 
  from BBT_POI
  WHERE (UPPER(p_name) LIKE UPPER('%'|| p_name || '%'));
  return p_name;
end;
/

 declare
    input varchar;
    errormsg varchar;
    organisation varchar;
    x NUMBER;
    y NUMBER;
 begin
    input:=getName ('yach', organisation, x, y, errormsg);
 end;

Upvotes: 1

mmmmmpie
mmmmmpie

Reputation: 3039

There are couple of problems with your code: First you are experiencing the PLS-00428 error which means you are not giving an INTO clause.
Example:

create or replace function getName(name IN varchar, organisation OUT varchar, 
                   xcoord OUT number, ycoord OUT number, errormsg OUT varchar)
return varchar2
as
begin
  Select name, LOCX, LOCY
  INTO name, XCOORD, YCOORD -- I assume this is how you need them kept
  from BBT_POI
  WHERE (UPPER(name) LIKE UPPER('%'|| NAME || '%'));
  return NAME;
end;
/

Next your next block must be defined as an anonymous block. Oracle doesn't know you are trying to use PL/SQL unless you specifically tell it you are.
So your variable stuff needs to be in a block (also you don't need to say variable):

DECLARE
 input varchar;
 errormsg varchar;
 organisation varchar;
 x NUMBER;
 y NUMBER;
BEGIN
  getName('yach', organisation, x, y, errormsg);
END;
/

Upvotes: 2

Related Questions