Cannot execute a stored procedure in Oracle

0
votes

Here is a simple example using Toad for Data Analysts 3.0.1.1734. I have full permissions on the schema JSWEENEY.

Create the table

CREATE TABLE JSWEENEY.TEMP_SQL
(
    SQL VARCHAR2(3000)
);

Create the procedure

CREATE OR REPLACE PROCEDURE JSWEENEY.SP_INSERT_SQL
IS
BEGIN   
   INSERT INTO JSWEENEY.TEMP_SQL(SQL) VALUES('SELECT * FROM TEMP_SQL');
   COMMIT;
END JSWEENEY.SP_INSERT_SQL;    
/

Execute the procedure:

BEGIN
    JSWEENEY.SP_INSERT_SQL;
END;

The first error:

ORA-06550: line 2, column 11:
PLS-00905: object JSWEENEY.SP_INSERT_SQL is invalid

ORA-06550: line 2, column 2: PL/SQL: Statement ignored

Execute the procedure:

BEGIN
    EXECUTE JSWEENEY.SP_INSERT_SQL;
END;

The second error:

ORA-06550: line 2, column 10:
PLS-00103: Encountered the symbol "JSWEENEY" when expecting one of the following: := . ( @ % ; immediate The symbol ":=" was substituted for "JSWEENEY" to continue.

Any suggestions would be greatly appreciated.

2
oraclestored-proceduresplsqltoad
The error you're getting when you run the procedure indicates that the create procedure statement failed with a syntax error. If you go back and re-create the procedure, you should get a list of errors. - Justin Cave
Thank you, but Toad indicates the procedure compiled correctly. It shows an "X" next to the procedure name if it does not compile correctly. - Joe Sweeney

2 Answers

3
votes

When you compile the procedure you will get an error; if your client doesn't display that then you can query the user_errors view (or all_errors if you're creating it in a different schema) to see the problem. Here it will be complaining that:

LINE/COL ERROR
-------- -----------------------------------------------------------------
6/13     PLS-00103: Encountered the symbol "." when expecting one of the following:
         ;

It's valid to use the schema name in the create call; but not as part of the end. So if you need to specify the schema at all - which you don't if you're creating an object in your own schema, but your reference to permissions makes it sound like you aren't - then it should be:

CREATE OR REPLACE PROCEDURE JSWEENEY.SP_INSERT_SQL
IS
BEGIN   
   INSERT INTO JSWEENEY.TEMP_SQL(SQL) VALUES('SELECT * FROM TEMP_SQL');
   COMMIT;
END SP_INSERT_SQL;    
/

Your second error is because execute on its is a client command (in SQL*Plus and relations), not a PL/SQL statement. The error refers to immediate because PL/SQL does have an execute immediate statement which is used for dynamic SQL, not for making static calls to procedures. Your first syntax to run the procedure is correct, once the procedure itself is valid:

BEGIN
    JSWEENEY.SP_INSERT_SQL;
END;
/
0
votes

try this edited the SQL statement.

create table TEMP_SQL ( col1 varchar2(100));

CREATE OR REPLACE PROCEDURE SP_INSERT_SQL
AS
BEGIN   
   INSERT INTO TEMP_SQL SELECT * FROM TEMP_SQL;
   COMMIT;
END  SP_INSERT_SQL;