Tools_plsql_code_formatiing..

sqlinform tool seems to sell for $50. Does TOAD give you same results as "sqlinform".. I will try it tomorrow. I think it only does 100 lines for free. our code is mostly using owa web toolkit and many unaligned htp.p statements and SQL cursors.

Yes Toad does the formatting in almost similar manner which sqlinform does..

You just have to select all your code in your Toad SQL Editor window and have to press CTRL+SHIFT+F7(Quick Default Shortcut for formatting) or after selecting the code you want to format, do a right click and select the format option from the dropdown.. your code will get auto-formatted as per the formatter rules.. You can even customise(not all) few of the formatter plus(in built formatter of toad) rules

The dpriver does not seem good. I tried this sample and it did not format it correctly.

select sysdate from dual;

IF (l_lname = 'mike') then
htp.p('good');
else
htp,p('bad');
end if;

I also tried the above sample using SQLFormat and Pool SQL and result is not aligned or good. Try it.

This code:

IF (l_lname = 'mike') then
htp.p('good');
else
htp,p('bad');
end if;

is not valid PL/SQL code, so I'm not surprised it wasn't formatted correctly.  If you wrapped it with a BEGIN/END, then it would have been formatted like this by the dpriver site:

BEGIN 
  IF (l_lname = 'mike') THEN 
    htp.P('good'); 
  ELSE 
    htp,p('bad'); 
  END IF; 
END;

Select allOpen in new window

That looks correct to me.

Note the comma on this line "htp,p('bad');" is interpreted as if it were a field. Typo in the example? sqldeveloper parses the same when that comma is replaced with a dot.

Yes, the formatting is different after adding BEGIN...END. I am surprised it did not say it is not valid instead.

I will try this tomorrow with some real production programs I have to see the output.

I fixed the comma typo issue in htp.p statement.

>> Yes, the formatting is different after adding BEGIN...END.
sqldeveloper (see above) is formatting correctly as far as I can see (but for the typo).

There seems to be a big difference in formatting rules using those tools.

I took a 4000 line procedure I have and used TOAD to format it. I ended up with 3700 lines.
It did make thing clearer but I ended up with about 3700 lines and still many blank lines.

I used the SQLformat tool and the formated file was 2000 lines. It remvoved all the blank lines.

I am not sure which would be better. In some cases it might be good to have some lines and in other cases it might be better to remove all blank lines. This has to be manual work because tool will either keep it or remove it.

What are your ideas on the best tool or formatting rules?

>>better?
That depends on what your requirements are. For readability, blank lines are of value. Any guidelines you have to work with?  If not, just choose one and work with that from now on.

No I dont really have any guidelines. I just want to make our code delivery to the customer more readable and look good.

If you don't have or must use any guidelines, just pick a tool (I'm using sqldeveloper). As long as you use the same tool and format for all code you deliver, now and next time.

Running the formatting one procedure one at a time is getting cumbersome. Is there a way for TOAD or other tools to run on files in a directory on filesystem or all files in the database schema.

sorry, I forgot to close this one.

Can you add the buttons for accepting multiple answers and assign points so I can close properly.