Sql – Oracle DB (PL/SQL) Refactoring Tools

oracleplsqlrefactoring

I was wondering if there are any good refactoring tools for Oracle databases and especially for PL/SQL. I'm working on a project where half the developers are working with c# and the other half on the db where there is a large and complex schema and a large code base in pl/sql. We also use sql server but the database team work exclusively with Oracle.

This code base is being worked on actively and is being refactored gradually but I was quite surprised to see how bad the refactoring tools for oracle seem to be (or at least in comparison to Eclipse and Resharper).

The developers are using Toad which only has basic refactoring support (probably as its seems to be targeted more towards db admin then development). I've used Toad myself before but always had assumed for pl/sql there were more features for working with code but had never looked into it. Now as far as I can see the only refactorings that it provides are extract method (procedure) and variable renaming. I read an article comparing sql developer and pl/sql developer and these do not appear to have any better support for refactoring. I had a quick look at the eclipse data tools project but couldn't see that they've added refactoring support since I last used it (a few years ago).

For Sql Server there exists Sql Refactor which when table structures are changed gets propagated through to the stored procedures. This for me is much cleaner and less error prone than having someone manually update all stored procedures. I used an older version of these tools on a project in the past and they seemed to be quite ok but a bit slow but that was a few years ago and it was still usable.

Something similar for Oracle would already be a big help for our developement team so I'd appreciate some pointers to good refactoring tools for Oracle if they do exist.
Thanks,
Crocked

Best Answer

As it happens the "D" in TOAD stands Developer not DBA. In fact many DBAs anathematise TOAD and other such tools.

You are out of luck. As far as I know there are no tools for refactoring PL/SQL. The root problem is that refactoring as a concept comes from the OOP paradigm, and PL/SQL is not object oriented. It does not support inheritance or polymorphism (*). This means that many of the techniques which underpin classical refactoring practice (say as defined by Fowler) - abstraction, interfaces, etc - have no analogue in PL/SQL.

The corollary of this is that people who are used to having refactoring as part of their conceptual toolbox tend to avoid programming with PL/SQL. I once got into a heated debate on the TDD list over this. The upshot being that even developers who need and appreciated the virtues of PL/SQL would rather develop in languages with better tool support, clause #1 of the Agile Manifesto notwithstanding.

The most important tool for refactoring is automated unit testing. Although TOAD does not (I think) have integrated unit testing, the next release of Oracle SQL Developer will. There are also standalone unit test tools. I recently mentioned a couple of them in another SO thread.

In terms of refactoring PL/SQL to match changes in the database, arguably most interaction with tables ought to be underaqtken by generated table APIs rather than being embedded in transactional PL/SQL. In this happy realm there is no need for refactoring tools, we just need to re-generate the relevant APIs. The post I linked to above also mentions QCGU, a tool which can do this. Of course, when we have a PL/SQL codebase which isn't organised in such a fashion then life is harder. You won't be surprised to learn that there isn't a lot of tool support for implementing Feathers's WELC in PL/SQL.

(*) Yes, I know Oracle has Types but they are (a) SQL not PL/SQL and (b) how many people out there are actually building APIs using them?

Related Solutions

Sql – Get list of all tables in Oracle

SELECT owner, table_name
  FROM dba_tables

This is assuming that you have access to the DBA_TABLES data dictionary view. If you do not have those privileges but need them, you can request that the DBA explicitly grants you privileges on that table, or, that the DBA grants you the SELECT ANY DICTIONARY privilege or the SELECT_CATALOG_ROLE role (either of which would allow you to query any data dictionary table). Of course, you may want to exclude certain schemas like SYS and SYSTEM which have large numbers of Oracle tables that you probably don't care about.

Alternatively, if you do not have access to DBA_TABLES, you can see all the tables that your account has access to through the ALL_TABLES view:

SELECT owner, table_name
  FROM all_tables

Although, that may be a subset of the tables available in the database (ALL_TABLES shows you the information for all the tables that your user has been granted access to).

If you are only concerned with the tables that you own, not those that you have access to, you could use USER_TABLES:

SELECT table_name
  FROM user_tables

Since USER_TABLES only has information about the tables that you own, it does not have an OWNER column – the owner, by definition, is you.

Oracle also has a number of legacy data dictionary views-- TAB, DICT, TABS, and CAT for example-- that could be used. In general, I would not suggest using these legacy views unless you absolutely need to backport your scripts to Oracle 6. Oracle has not changed these views in a long time so they often have problems with newer types of objects. For example, the TAB and CAT views both show information about tables that are in the user's recycle bin while the [DBA|ALL|USER]_TABLES views all filter those out. CAT also shows information about materialized view logs with a TABLE_TYPE of "TABLE" which is unlikely to be what you really want. DICT combines tables and synonyms and doesn't tell you who owns the object.

Sql – How to limit the number of rows returned by an Oracle query after ordering

You can use a subquery for this like

select *
from  
( select * 
  from emp 
  order by sal desc ) 
where ROWNUM <= 5;

Have also a look at the topic On ROWNUM and limiting results at Oracle/AskTom for more information.

Update: To limit the result with both lower and upper bounds things get a bit more bloated with

select * from 
( select a.*, ROWNUM rnum from 
  ( <your_query_goes_here, with order by> ) a 
  where ROWNUM <= :MAX_ROW_TO_FETCH )
where rnum  >= :MIN_ROW_TO_FETCH;

(Copied from specified AskTom-article)

Update 2: Starting with Oracle 12c (12.1) there is a syntax available to limit rows or start at offsets.

SELECT * 
FROM   sometable
ORDER BY name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

See this answer for more examples. Thanks to Krumia for the hint.

Best Answer

Related Solutions

Sql – Get list of all tables in Oracle

Sql – How to limit the number of rows returned by an Oracle query after ordering

Related Topic