CODEWITHSUNDEEP
oracle-databaseplsqloracle-sqldeveloper
navid sedigh
navid sedigh

Reputation: 281

how to compile all procedures with standard format in oracle sql developer

I have a lot of procedures and functions in a schema in oracle SQL developer, I want to know how to compile all procedures and functions with standard format (that after that all of them have the same format like when press Ctrl + F7 manually) in oracle SQL developer automatically?

Upvotes: 1

Views: 10521

Answers (4)

Farshid Shekari
Farshid Shekari

Reputation: 2449

The best way to solve this problem in oracle 19c/18c (tested) is to run this script on your DB server machine:

SQL> @Oracle_home/rdbms/admin/utlrp.sql

The link to the reference is here.

Upvotes: 0

thatjeffsmith
thatjeffsmith

Reputation: 22457

The 'best' way to look at this is via source control, and hopefully the source of truth is a subversion or Git project.

You can feed all of the files in a directory to our CLI with the FORMAT command. It will then go through each file in that folder, format the code, and write it to the supplied output directory.

You would then check those files in to your source control system.

c:\Program Files\Oracle\sqldev\18.1\sqldeveloper\sqldeveloper\bin>sdcli format input=c:\users\jdsmith\unformatted output=c:\users\jdsmith\formatted
Command Completed.

enter image description here

So here I go from 3 files unformatted to 3 files formatted, and if I open the same 'object' before and after...

enter image description here

All this is nice, but know as soon as another developer checks out a file, they will immediately change the way it looks due to personal preferences. I'm not sure I've ever seen a successful 'formatting rules' system where everyone agrees to format the code the same. But, formatting it as it goes in your VCS seems to work OK...and will also help with DIFFs/Deltas.

You could also theoretically also write some js and use SQLcl to grab each object, format it, and then compile it. Some examples are here.

I don't like the idea of compiling objects w/o looking at them first, but that's just me.

Upvotes: 0

Barbaros Özhan
Barbaros Özhan

Reputation: 65363

Alternatively, you may use a public synonym referencing a procedure to compile through the DB by commands, created and authorized as below :

$ sqlplus / as sysdba 
SQL> Create or Replace Procedure SYS.Pr_Compile_All Is
  v_command varchar2(1500);
Begin
  For c in
          (
            Select 'alter '||o.object_type||' '||o.owner||'.'|| o.object_name|| ' compile' command1,
                   'alter PACKAGE '||o.owner||'.'|| o.object_name|| ' compile' command2,
                   'alter PUBLIC SYNONYM '|| o.object_name|| ' compile' command3,
                   object_type,
                   owner
              From dba_objects o
             Where o.status = 'INVALID'
           )
  Loop
   Begin
     v_command := c.command1;
     If c.object_type in ('FUNCTION','PROCEDURE','TRIGGER') Then v_command := v_command ||' debug';      End If;
     If c.object_type in ('PACKAGE BODY')                   Then v_command := c.command2||' debug body'; End If;
     If c.object_type in ('SYNONYM') and c.owner = 'PUBLIC' Then v_command := c.command3; End If;
     Execute Immediate v_command;
     Exception When Others Then null;
   End;
  End Loop;
End;

SQL> Create or Replace Public Synonym Pr_Compile_All For SYS.Pr_Compile_All;
SQL> grant execute on Pr_Compile_All to public;
SQL> conn myschema/pwd
SQL> begin Pr_Compile_All end; -- call from any schema you'd like, in this way.

Upvotes: 1

MT0
MT0

Reputation: 168232

I have a lot of procedures and functions in a schema in oracle SQL developer, I want to know how to compile all procedures and functions

In the "Connections" view:

  • expand the connection to the schema
  • right click on "Procedures" (or "Functions")
  • in the context menu that pops up, chose "Compile All"
  • if you wish, you can view the PL/SQL block that is going to be run by looking at the "SQL" tab
  • press the "Apply" button to recompile everything.

I want to know how to [have] all procedures and functions with standard format

This is nothing to do with (re)compiling. You can apply whatever formatting (whitespace/case/etc.) rules you want to your code and so long as the code remains syntactically correct then it does not affect whether the code will recompile.

Go to "Tools" > "Preferences..." > "Database" > "SQL Formatter" and edit the appropriate formatting to your specification.

Then right-click on the procedure's/function's code and select "Format" (or press Ctrl + F7).

You will need to do this for each procedure and function as there does not appear to be a SQL Developer option to apply it to all objects in a schema.

Upvotes: 5

Related Questions