SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
Formatting PL/SQL

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
Formatting PL/SQL
Author Message
rprastein



Joined: 01 Oct 2012
Posts: 6
Country: United States

Post Formatting PL/SQL Reply with quote
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
Mon Oct 01, 2012 9:12 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6499

Post Reply with quote
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?
Mon Oct 01, 2012 9:57 pm View user's profile Send private message
rprastein



Joined: 01 Oct 2012
Posts: 6
Country: United States

Post Reply with quote
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.
Tue Oct 02, 2012 2:42 pm View user's profile Send private message
rprastein



Joined: 01 Oct 2012
Posts: 6
Country: United States

Post Arrgh. Don't never mind after all. Reply with quote
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
Tue Oct 02, 2012 4:24 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6499

Post Reply with quote
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.
Tue Oct 02, 2012 4:46 pm View user's profile Send private message
rprastein



Joined: 01 Oct 2012
Posts: 6
Country: United States

Post Reply with quote
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
Tue Oct 02, 2012 4:48 pm View user's profile Send private message
rprastein



Joined: 01 Oct 2012
Posts: 6
Country: United States

Post Not getting Beyond Compare for comparison. Reply with quote
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
Tue Oct 02, 2012 5:23 pm View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 1394

Post Reply with quote
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?
Tue Oct 02, 2012 5:29 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6499

Post Reply with quote
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?
Tue Oct 02, 2012 5:39 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6499

Post Reply with quote
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.
Tue Oct 02, 2012 5:44 pm View user's profile Send private message
rprastein



Joined: 01 Oct 2012
Posts: 6
Country: United States

Post Reply with quote


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
Tue Oct 02, 2012 6:42 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6499

Post Reply with quote
Please upgrade to version 6.3 which features more control over code comparison and enables using external compare tools in more places.
Mon Nov 05, 2012 11:16 am View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.