Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Differences at compilation of PL/SQL objects between SQLPlus and PL/SQL-Developer

4296114Jul 27 2020 — edited Jul 28 2020

Question 1:

I have the following types of files which I would like to compile or execute on Oracle DB 12.1.0.2

*.tps (PL/SQL-Specs for Object-types)

*.tpb (PL/SQL-bodies for Object-types)

*.pgs (PL/SQL-Specs for packages)

*.pgb (PL/SQL-bodies for packages)

*.sql (Scripts, views and materialized views)                                              

Until now I have executed and compiled the files with PL/SQL-Developer. The Problem is the speed. It lasts hours when I have a lot of new sources and files which have to be executed. Therefore I decided to do it completely outside of PL/SQL-developer using sqlplus. But then the problem occurs that due to compile errors the broken PL/SQL-Code(e.g. packages, objectytpes, etc) is not in the DB at all, therefore it is not possible to recompile the code with PL/SQL-developer, which would fix the whole thing because some errors do not appear when compiling it via PL/SQL-developer.

Some of the errors which do appear in sqlplus but not in PL/SQl-developer are the following:

ORA-06550: line 55, column 1: /PLS-00103: Encountered the symbol "STATIC"

Here I have the keyword “FORCE” in creating an object or view. Without “Force” it works in sqlplus, in PL/SQL it works with and without FORCE.

ERROR at line 33:

ORA-01720: grant option does not exist for 'SYS.USER_JOBS'

Question 2:

Why do I get an error when I want to create an object-type with the keyword FORCE with sqlplus but not with PL/SQL-Developer? Example:

CREATE OR REPLACE FORCE TYPE BODY EF_OT_NT_KD_TEC_RAPPORT_DOC IS…

Does anybody has an idea how to solve that problem or knows a tool how to get this files with speed into the DB (recompilation would not be a problem)?

Thank you very much for help.

Kai

Comments
Post Details
Added on Jul 27 2020
7 comments
839 views