CODEWITHSUNDEEP
sqldatabaseoracle-databasestored-proceduresoracle11g
user3671390
user3671390

Reputation: 79

I am executing following procedure in oracle but get compilation error

Actually I want to get usernames from table using Stored Procedure 

    CREATE OR REPLACE PROCEDURE GetRecord
    ( 
       p_ID   IN  integer       ,                  
      p_user OUT VARCHAR2  
    ) 
    AS 
    BEGIN  
       SELECT  
       USERNAMES   
       INTO   
        p_user  
        FROM   tblUsers
     WHERE  ID  = p_ID  ; 

     END  GetRecord;


 BEGIN
  DECLARE A VARCHAR2
 EXECUTE GetRecord(21,A);
 END

When I run the above procedure I got following errors

Error starting at line : 17 in command -
BEGIN
DECLARE A VARCHAR2
EXECUTE GetRecord(21,A);
END
Error report -
ORA-06550: line 3, column 1:
PLS-00103: Encountered the symbol "EXECUTE" when expecting one of the following:

   := . ( @ % ; not null range default character
The symbol ";" was substituted for "EXECUTE" to continue.
ORA-06550: line 4, column 1:
PLS-00103: Encountered the symbol "END" when expecting one of the following:

   begin function pragma procedure subtype type <an identifier>
   <a double-quoted delimited-identifier> current cursor delete
   exists prior
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

Actually I want to get usernames from table using Stored Procedure

Upvotes: 0

Views: 360

Answers (1)

Justin Cave
Justin Cave

Reputation: 231651

The code that you are using to execute your procedure appears to be incorrect. It sounds like you want

DECLARE
  l_usernames tblUsers.usernames%type;
BEGIN
  GetRecord( 21, l_usernames );
END;

In general, I'd strongly object to your naming convention. It makes no sense to use a plural usernames as a column name. A procedure named GetRecord tells you nothing about what it does-- GetUsername would be much more meaningful. Your anonymous block also appears to be declaring a local variable a that stores the returned username which also makes no sense from the standpoint of using meaningful identifiers.

I'd also suggest that a procedure is the wrong approach here. If your goal is to return a single value, use a function. Functions return things, procedures do not. A function can potentially be used in a SQL statement, a procedure cannot be.

Upvotes: 3

Related Questions