error when creating a procedure without parameters, Oracle , PL/SQL

Question

The procedure that makes 1 SELECT(Output data types in the user package). Outside of the SELECT procedure, it works fine. Here is the code:

СREATE OR REPLACE PROCEDURE get_types
    IS
    BEGIN
        select name, type
         from user_identifiers
         where object_name = 'MY_TYPES'
         and usage = 'DECLARATION'
         and type != 'PACKAGE'
         order by name;
    END get_types;
    /

Error:

SP2-0734: unknown command beginning "СREATE OR ..." - rest of line ignored. SQL> SP2-0042: unknown command "IS" - rest of line ignored.

Littlefoot · Accepted Answer · 2021-03-19 11:11:01Z

There's something strange with create (as if contains some garbage). I deleted it and typed that word, and code looks OK. However, as it is a PL/SQL procedure, select requires into, such as:

CREATE OR REPLACE PROCEDURE get_types
IS
   l_name  user_identifiers.name%TYPE;
   l_type  user_identifiers.TYPE%TYPE;
BEGIN
     SELECT name, TYPE
       INTO l_name, l_type
       FROM user_identifiers
      WHERE     object_name = 'MY_TYPES'
            AND usage = 'DECLARATION'
            AND TYPE != 'PACKAGE'
   ORDER BY name;
END get_types;

This would work if select returned exactly one row. Otherwise, if it does not, that code will return no_data_found. If it returned more than a single row, you'd get too_many_rows. What to do? It depends on what you want to do. You could select into a collection. Or a refcursor. Or use a loop. There are various options, but actual one depends on you.

Collectives™ on Stack Overflow

error when creating a procedure without parameters, Oracle , PL/SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related