CODEWITHSUNDEEP
plsql
camdixon
camdixon

Reputation: 882

SQL - PL/SQL Writing a basic function and running it to query a result

I have the table 'Company' populated with multiple tuples/rows. One of which is Microsoft for the 'Company_name' field and the 'City' field is populated as Redmond.

I have compiled this PL/SQL file.

-- Create a function that will return the city where company X is located.
-- X will be the parameter supplied by the caller.
-- Test your function with the company of your choice.

CREATE OR REPLACE FUNCTION company_location_city(x IN company.company_name%TYPE)
return company.city%TYPE IS
  company_location company.city%TYPE;
BEGIN
  select city into company_location
  from company 
  where company_name = x;

  return company_location;
END;

and ran it with this line.

select company_location_city('Microsoft') from dual;

I get this error, and I don't understand what it is trying to tell me. Of course we encountered select.

I am running the Oracle flavor.

Error(12,1): PLS-00103: Encountered the symbol "SELECT"

Also the query result showing an error should help.

ORA-06575: Package or function COMPANY_LOCATION_CITY is in an invalid state 06575. 00000 - "Package or function %s is in an invalid state" *Cause: A SQL statement references a PL/SQL function that is in an invalid state. Oracle attempted to compile the function, but detected errors. *Action: Check the SQL statement and the PL/SQL function for syntax errors or incorrectly assigned, or missing, privileges for a referenced object. Error at Line: 16 Column: 8

Ran in Oracle Sql Developer in the Worksheet Window

Upvotes: 1

Views: 435

Answers (1)

Ilia Maskov
Ilia Maskov

Reputation: 1898

The PLS prefix of the error says that it is pl\sql engine error. Hence it means that your sql code was treated as pl\sql because of the function ddl code which is pl\sql. You mixed up sql and pl/sql , you should split them by delimiter or remove one of the types of code.

Upvotes: 1

Related Questions