Quite often I hear a question: can I design my stored PL/SQL with Data Modeler too? I want to design the privileges etc. and I feel I should do it in Data Modeler.

You are right: you should. Any code that is stored in the database is a database object just like a table or a view and should be designed and managed. But because a stored PL/SQL is an Oracle specific feature you can only find them in Physical Model in the Browser.

You can edit a Stored Procedure by right-clicking and selecting Edit.

 

And you can define the Properties and Privileges by double-clicking the name or right-clicking and selecting Properties.

 

Sometimes it is somebody else in the project that manages the content (logic) of the code or you rather use SQL Developer for it. Then the best way is simply reverse engineer the code from either the DDL file or the Data Dictionary. You can do that by selecting File|Import|DDL File or File|Import|Data Dictionary.

You can also compare the version of code you have in Data Modeler to the one in DDL or Data Dictionary. Simply select File|Import|DDL File or File|Import|Data Dictionary as you did while reverse engineering but since you already have the objects in you Physical Model instead of creating them the tool now shows how they are different from each other:

If you want to see the code that would be imported to the design, press DDL Preview. If you want to import the changes made to the version in DDL or Data Dictionary, press Merge.

If I have my stored PL/SQL in Data Modeler, I am able to design and define the privileges for them. What else? For instance if I am changing the table Orders I can first check if I have any code that needs to be changed because of that. I do that using the Search functionality. Right-click on the name of your Physical Model and select Search.

Type the table name you are planning to change and press Enter:

Select Stored Procedure from the Filter:

And using the Report button and a custom report template you can generate a report showing the code that might need change after your change in table structure: