Formatting PL/SQL

I'm interested in using SQL Assistant to compare revisions to existing PL/SQL code. I'm currently using SQL Assistant in conjunction with Notepad++, and trying out different combinations of SQL Assistant formatting and find/replace in Notepad++ to get something that will "compare" without highlighting a bunch of stuff that isn't really different.

I have some fairly lengthy libraries that I am trying it out on, and am getting caught up by things like one revision stacking assignments and the other one not, or line breaks in different places where there are concatenations of multiple strings.

I see that there is the capability of customizing various formatting parameters (like line wrap, parentheses, and so forth), as well as certain types of statements identified by keywords, but I am not finding anything specific for dealing with ":=" or "||". Am I missing something?

Also, is there any sort of reference document (besides the user manual and trial and error) to better understand the syntax and interactions of the keyword-based statement formats?

Thanks,

Rebeccah

Can you post a small example demonstrating what you are trying to compare and what kind of differences you want to ignore during the comparison?

Huh.

Well, never mind.

If I compare the two original files in SQL Assistant, it gets totally thrown off by the voluminous comments in one of the files; plus, I found the compare UI a little hard on the eyes. So, I formatted both files using the PL/SQL Default style, then used BeyondCompare (my favorite text diff program), and I got things like

	Code:
	THEN -- not a really bad error just log it webutil_core.LOG ( 'CLIENT_IMAGE.READ_IMAGE_FILE: Failed to delete ' \|\| imagename \|\| ' from workarea', TRUE ) ; END IF;

in one file, and

	Code:
	THEN -- not a really bad error just log it WebUtil_Core.log( 'CLIENT_IMAGE.READ_IMAGE_FILE: Failed to delete ' \|\| imageName \|\| ' from workarea', TRUE ) ; END IF;

in the other (different line breaks in the concatenation of strings), or

	Code:
	v_javahandle := webutil_core.getproperty (webutil_core.wuo_package, 'WUO_OLE2_CREATE_ARGLIST')

in one and

	Code:
	v_javaHandle := WebUtil_Core.getProperty(WebUtil_Core.WUO_PACKAGE, 'WUO_OLE2_CREATE_ARGLIST')

in the other (assignment value on the same line vs the next line after the assignment operator).

I never did try going back to the SQL Assistant Compare feature after doing the formatting and before posting my question.

Now, when I do go back to the SQL Assistant Compare, using the formatted files, it only finds one difference - an extra blank line at the end of one of the files. That's amazing. I am curious, though, why the formatter doesn't format the two files the same.

Rebeccah

Edited to add:

Other formatting differences:

	Code:
	TYPE afunctionhandle IS TABLE OF webutil_c_api.functionhandle INDEX BY BINARY_INTEGER;

in one, and

	Code:
	TYPE aFunctionHandle IS TABLE OF WEBUTIL_C_API.FUNCTIONHANDLE INDEX BY BINARY_INTEGER;

in the other (INDEX BY clause on the same line vs the next line following the rest of the type declaration)

	Code:
	param := webutil_c_api.ADD_PARAMETER ( args, webutil_c_api.c_char_ptr, webutil_c_api.param_inout, vcversion, 40 )

in one, and

	Code:
	param := WEBUTIL_C_API.Add_parameter( args, WEBUTIL_C_API.C_CHAR_PTR, WEBUTIL_C_API.param_INOUT, vcVersion, 40 )

in the other (assignment value on the same line vs the next line after the assignment operator; also space before the opening parenthesis in the parameter list)

	Code:
	vcdebugstring := '<' \|\| LPAD(TO_CHAR(debuglevel), 2, 0) \|\| '> ' \|\| vcdatestamp \|\| RPAD(loggingcall \|\| ':', 30) ;

in one, and

	Code:
	vcDebugString := '<' \|\| LPAD(TO_CHAR(DebugLevel), 2, 0) \|\| '> ' \|\| vcDateStamp \|\| RPAD(LoggingCall \|\| ':', 30) ;

in the other (the concatenation issue and the assignment issue both in one statement)

	Code:
	LOOP iptrstart :=( INSTR( source_string \|\| vcsepbuffer, delimiter, 1, field_position - 1 ) - 1 ) + LENGTH(delimiter) ; EXIT WHEN iptrstart > 0 ; vcsepbuffer := vcsepbuffer \|\| delimiter ; END LOOP;

in one, and

	Code:
	LOOP iPtrStart := ( INSTR( Source_string \|\| vcSepBuffer, Delimiter, 1, Field_Position -1 ) -1 ) + LENGTH(Delimiter) ; EXIT WHEN iPtrStart > 0 ; vcSepBuffer := vcSepBuffer \|\| Delimiter ; END LOOP;

in the other ( arithmetic operator "+" on the same line as the closing parenthesis vs on the next line following it)

	Code:
	iptrstart := INSTR(vcsourcestrcopy, delimiter, 1, field_position - 1) + LENGTH(delimiter) ;

in one, and

	Code:
	iPtrStart := INSTR(vcSourceStrCopy, Delimiter, 1, Field_Position -1) + LENGTH(Delimiter) ;

in the other (arithmetic operator "+" on the same line vs the next line following the first operand)

	Code:
	RETURN jni.call_object_method ( TRUE, NULL, 'java/io/File', 'createTempFile', '(Ljava/lang/String;Ljava/lang/String;Ljava/io/File;)Ljava/io/File;', args ) ;

in one, and

	Code:
	RETURN JNI.CALL_OBJECT_METHOD( TRUE, NULL, 'java/io/File', 'createTempFile', '(Ljava/lang/String;Ljava/lang/String;Ljava/io/File;)Ljava/io/File;', args ) ;

in the other (opening parenthesis on the same line vs the next line following the function name in a return statement)

	Code:
	IF GET_ITEM_PROPERTY(startpoint, block_name) = GET_ITEM_PROPERTY(hdummy, block_name)

in one, and

	Code:
	IF GET_ITEM_PROPERTY(startPoint, BLOCK_NAME) = GET_ITEM_PROPERTY(hDummy, BLOCK_NAME)

in the other (line break vs no line break following equality operator)

	Code:
	WHEN webutil_core.bean_not_registered THEN webutil_core.erroralert ( webutil_core.getimplclass (webutil_core.wul_package) \|\| ' bean not found. WEBUTIL_C_API.CREATE_PARAMETER_LIST will not work' ) ; RAISE form_trigger_failure ;

in one, and

	Code:
	WHEN WebUtil_Core.BEAN_NOT_REGISTERED THEN WebUtil_Core.ErrorAlert( WebUtil_Core.getImplClass(WebUtil_Core.WUL_PACKAGE) \|\| ' bean not found. WEBUTIL_C_API.CREATE_PARAMETER_LIST will not work' ) ; RAISE FORM_TRIGGER_FAILURE ;

in the other (opening parenthesis on the same line vs the next line following procedure name in a WHEN/THEN statement)

	Code:
	RETURN webutil_db_local.openblob (blobtable, blobcolumn, blobwhere, openmode, chunksize)

in one, and

	Code:
	RETURN WebUtil_DB.OpenBlob(blobTable, blobColumn, blobWhere, openMode, chunkSize)

in the other (this is actually different content; I'm not sure why the SQL Assistant Compare doesn't pick it up!)

That seems to be all of the types of formatting differences between my two files.

I was looking at the SQL Assistant Compare window again, and noticed the heavy commenting on BOTH sides of the compare. Somehow, the right side content ended up on the left side as well, without changing what the header says about which file is being displayed. No wonder it didn't find any differences! The sequence of events that produced this bizarre effect was:
1. Open both files in Notepad++
2. Format left file with SQL Assistant and save to a new name.
3. Format right file with SQL Assistant (well, or so I thought) and save with a new filename.
4. Open SQL Assistant Compare window from within the left-side file in Notepad++.
5. Choose the right-side filename (the new filename) from the filesystem chooser.
6. Compare - lots of differences noticed, noticed that the right-side file doesn't have the SQL Assistant formatting header in it
--> oops, I must hve clicked "PL/SQL Default Style" instead of clicking "Format", so it didn't actually format anything.
7. Leaving the compare window open, go back to the right-side file in Notepad++ and format it, making sure that I get the header indicating it really got formatted.
8. Save the formatted right-side file.
9. Back to Compare window. Nothing is updated, so choose a different right-side file, and then choose the one I really want again (to reload the file).
10. Wow, only one, insignificant, difference!
11. Post to this forum after reviewing the differences seen between the files using BeyondCompare.
12. Come back to Compare window, to look for the content difference that didn't get picked up
13. Both sides look identical - but wait, both sides have the big comments. That's not right, only one side had the extensive comments.
14. Close the Compare window
15. Re-open the Compare window from within the formatted left-side file in Notepad++, and choose the formatted right-side file from the filesystem.
16. Now there are many differences again. Check Ignore Case and Ignore Blanks. At least the comments are lined up correctly, now, so the differences I am seeing are similar to the ones I saw in BeyondCompare.

Rebeccah

Did you know that you can configure SQL Assistant to use BeyondCompare as an external code compare tool?
See Options -> Source Control -> SCS Settings -> External Compare Tool and Options -> Source Control -> SCS Settings -> External Merge Tool. In both places choose Beyond Compare from the drop-down list.

No, I didn't. Thank you for that, I'll set that up. The question of the differing formatting in the above scenarios still remains, though.

Rebeccah

I didn't choose BeyondCompare for merge, but I did choose it for Compare - but still the SQL Assistannt Compare window comes up when I compare files using the keystrokes described in my previous post. I "applied" my changes to the configuration, and I restarted Notepad++, but still not getting Beyond Compare to come up when I choose the Compare feature.

Rebeccah

	SysOp wrote:
	Did you know that you can configure SQL Assistant to use BeyondCompare as an external code compare tool? See Options -> Source Control -> SCS Settings -> External Compare Tool and Options -> Source Control -> SCS Settings -> External Merge Tool. In both places choose Beyond Compare from the drop-down list.

Interesting. Does that work for code compare initiated for two editor windows as well, or is it source code control setting only?

One thing I know for sure, the Compare tool does regular text comparison of the content as it appears in files or the editor, not the compiled code that ignores all sorts of white spaces, line breaks, and comments. so in the following case

	Code:
	v_javahandle := webutil_core.getproperty (webutil_core.wuo_package, 'WUO_OLE2_CREATE_ARGLIST')

and

	Code:
	v_javaHandle := WebUtil_Core.getProperty(WebUtil_Core.WUO_PACKAGE, 'WUO_OLE2_CREATE_ARGLIST')

it should display differences between 2 code fragments. Please note that line breaks <> white spaces, checking ignore white spaces option doesn't line breaks

I'm having difficulties reproducing your environment and the issue with "..off by the voluminous comments...."
Would you please attach a screenshot of the Code Compare window demonstrating that issue?

	Quote:
	Interesting. Does that work for code compare initiated for two editor windows as well, or is it source code control setting only?

I was under impression it should replace the built in Code Compare, but not so sure anymore, still testing.

Note that the first identified block, outlined with a red line, on the left includes the entire function declaration for client_get_file_name.
On the right, it only includes the signature and the initial comments, and omits the body of the function

This causes it to identify the start of the client_host procedure declaration on the left as a non-match, whereas in fact, it's there on the right after all that white space across from the (also incorrectly identified as a nonmatch) procedures of the client_image package declaration on the left. The whole thing is like this, and it's way too much work to try to sort through it, which is why I tried formatting both files, so that there would be enough similarities that at least the remaining differences would line up correctly.

I understand that with the formatted files, the comparison is correctly reporting that there are still differences; what I would like to address is why the two formatted files aren't formatted the same, when the semantic content is the same. I'm suspecting it has to do with the original files being formatted differently, and the formatter doing a better job of splitting long lines up than of joining short lines together... but I don't really have a good sense of how those pattern descriptions for the keyword-based statements really work.

Rebeccah

Please upgrade to version 6.3 which features more control over code comparison and enables using external compare tools in more places.