0

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.

Improve this question
2
  • 1
    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. Commented Feb 3, 2021 at 15:24
  • Thank you, but Toad indicates the procedure compiled correctly. It shows an "X" next to the procedure name if it does not compile correctly. Commented Feb 4, 2021 at 16:20

2 Answers 2

Reset to default
3

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;
/
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Okay that worked. Second issue is adding a parameter. <code> CREATE OR REPLACE PROCEDURE JSWEENEY.SP_INSERT_SQL ( pSQL TEMP_SQL.SQL%TYPE ) IS BEGIN INSERT INTO JSWEENEY.TEMP_SQL(SQL) VALUES(pSQL'); COMMIT; END SP_INSERT_SQL; / </code> This compiles correctly Then I run it with <code> BEGIN JSWEENEY.SP_INSERT_SQL('Testing Insert'); END; </code> and get an error Object JSWEENEY.SP_INS
That won't compile correctly. You have a stray quote in VALUES(pSQL'). Toad should be telling you that there's a problem when your execute the create, and should show it as invalid. No idea why it isn't - maybe it's confused if you're working across schemas <shrug>. If you still can't get it to work you should ask a new question; the original issue is resolved, and code doesn't really work in comments. But it looks like a typo.
Looks like it also isn't happy with the column being called SQL, in the pSQL TEMP_SQL.SQL%TYPE part (or pSQL JSWEENEY.TEMP_SQL.SQL%TYPE if you're being consistent *8-). SQL is a keyword, so not a good choice as a name.
0

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;
Improve this answer

1 Comment

Thank you, but the example is supposed to illustrate inserting a literal string into the table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.