CODEWITHSUNDEEP
oracle-databasestored-proceduresplsqltoad
Joe Sweeney
Joe Sweeney

Reputation: 1

Cannot execute a stored procedure in Oracle

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.

Upvotes: 0

Views: 2258

Answers (2)

Alex Poole
Alex Poole

Reputation: 191235

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;
/

Upvotes: 3

Himanshu Kandpal
Himanshu Kandpal

Reputation: 1608

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;

Upvotes: 0

Related Questions