Re: Oracle 11g SQL + PL/SQL + SQL*Plus + RDBMS syntax support

[William Robertson]

On Monday, August 27, 2012 10:35:19 AM UTC+1, William Robertson wrote:
> On Sunday, August 26, 2012 3:06:26 PM UTC+1, Szilard Barany wrote:
> > Hi,
> >
> > In the current vim releases the Oracle RDBMS SQL syntax support is from 2005 and PLS/SQL support is from 2002; kind of outdated.
> > I have developed a syntax highlight file that supports the most recent Oracle RDBMS version, 11g R2. This includes SQL, PL/SQL (including Oracle supplied packages), SQL*Plus and RDBMS (init parameters, dynamic performance views, etc.).
> > I used this syntax highlight support for 4+ years, and it seems to me OK by now. It might not be perfect, but I hope that other people who use vim for Oracle script/file editing can help me improve it.
> > I would like to submit it to vim hoping that it will be included in the next releases. I would like to find out what to do now; how to submit it to the code base, etc.

Now I check, I have updates for
sql.vim (maintainer David Fishburn <fishburn at ianywhere dot com>)
sqloracle.vim (maintainer Paul Moore <pf_moore AT yahoo.co.uk>)
plsql.vim (maintainer Jeff Lanzarotta <jefflanzarotta at yahoo dot com>)

I refactored mine so that for example sqloracle.vim and plsql.vim only contain SQL and PL/SQL keywords respectively (plsql.vim sources sqloracle.vim) instead of both containing everything.

[Szilard Barany]

On Monday, 27 August 2012 22:13:18 UTC+8, William Robertson wrote:

> Now I check, I have updates for
> sql.vim (maintainer David Fishburn <fishburn at ianywhere dot com>)
> sqloracle.vim (maintainer Paul Moore <pf_moore AT yahoo.co.uk>)
> plsql.vim (maintainer Jeff Lanzarotta <jefflanzarotta at yahoo dot com>)

Well, since the files haven't been updated for a decade or so, I am not sure if these people still consider (or can consider) themselves as maintainers. Actually, _I_ would like to be a maintainer (or one of them).
Nonetheless, I will try to contact them.

>
> I refactored mine so that for example sqloracle.vim and plsql.vim only contain SQL and PL/SQL keywords respectively (plsql.vim sources sqloracle.vim) instead of both containing everything.

Since I often have anonymous PL/SQL blocks in SQL scripts (plus SQL*Plus commands) and since there is definitely a lot of SQL in PL/SQL code, I thought there is no point to have several files; all syntax info can be in one. Easier to maintain, and might be easier even for vim (less stuff to work with while displaying the source code).

[David Fishburn]

On Tue, Aug 28, 2012 at 12:22 AM, Szilard Barany <szi...@gmail.com> wrote:
> On Monday, 27 August 2012 22:13:18 UTC+8, William Robertson wrote:
>
>> Now I check, I have updates for
>> sql.vim (maintainer David Fishburn <fishburn at ianywhere dot com>)
>> sqloracle.vim (maintainer Paul Moore <pf_moore AT yahoo.co.uk>)
>> plsql.vim (maintainer Jeff Lanzarotta <jefflanzarotta at yahoo dot com>)
>
> Well, since the files haven't been updated for a decade or so, I am not sure if these people still consider (or can consider) themselves as maintainers. Actually, _I_ would like to be a maintainer (or one of them).
> Nonetheless, I will try to contact them.

Not _all_ those files are stale.

Mine, ftplugin/sql.vim, was updated in May 2012.

syntax/sql.vim was last updated in 2005, but is a wrapper which will
source the appropriate SQL dialect for the RDBMS you use. So it will
source sqloracle.vim, sqlanywhere.vim (mine Jan 2012), mysql.vim and
so on.

This defaults to Oracle, though anyone can override that behaviour
through their .vimrc as described in:
:help ft_sql.txt

Specifically, :h sql-type-default, shows you can do the following:
let g:sql_type_default = 'sqlanywhere'
let g:sql_type_default = 'sqlinformix'
let g:sql_type_default = 'mysql'

Or, if you want a temporary change while editing a file you can do the
following:
:SQLSetType sqloracle
:SQLSetType sqlanywhere
:SQLSetType sqlinformix
:SQLSetType mysql


Please do contact Jeff Lanzarotta and Paul Moore. Most maintainers,
unless active, would be happy to pass along the responsibility.


Szilard, I see your comments about merging syntax/sqloracle.vim and
syntax/plsql.vim, this sounds like a good idea.

If you guys would like to take some of this offline (or leave on list,
that is fine) we can discuss the basic setup of the SQL support in
Vim.

David Fishburn

[William Robertson]

On Tuesday, August 28, 2012 1:48:02 PM UTC+1, David Fishburn wrote:
>
> Not _all_ those files are stale.

Apologies David, I may have got carried away with the SQL-related .vim listing there. sql.vim is indeed up to date - I think I saw a new filedate on my copy and forgot it was because I'd downloaded it. I'd love to help updating the others though.

Also I have a new colours file - any idea how to submit it for review etc?

[David Fishburn]

On Mon, Aug 27, 2012 at 5:35 AM, William Robertson
<wil...@williamrobertson.net> wrote:
...
> In my setup I have an edited sql.vim and plsql.vim - although I could never get the autocompletion feature working so I had to comment some stuff out, and that makes me feel unqualified to volunteer as a maintainer.

Did you edit these files in place or copy them to your .vim (vimfiles
- windows) directory _first_ and then modified them?

Can you be specific on what you commented out and why?
Were you getting errors?
If so, of course, what were they? And what were you doing when you got them?

As I pointed out in a different response, what SQL dialect are you
using? Oracle too, I assume.
:SQLGetType
Will show this information when you are editing a SQL file.


> I also simplified the highlighting so there is only one kind of "keyword", to remove the jumble you get where SELECT and AND etc all come out different colours (pink and green or whatever the default is). Also I have a colours file that's a bit more muted and 21st century IMHO than the supplied set.

Typically, "SELECT" is a "sqlStatement" and an "AND" is a
"sqlOperator". A sqlStatement begins a statement, and operators are
AND, OR, BETWEEN, ...

You can see the linkage in your syntax/sqloracle.vim file here:
HiLink sqlOperator Operator
HiLink sqlStatement Statement

There is no reason why you can't change your colour file to indicate
both of those should be highlighted the same colour.

In my colorscheme file I have the following two lines:
hi Operator term=bold ctermfg=Yellow guifg=#ffff00 gui=NONE
hi Statement term=bold ctermfg=Yellow guifg=#ffff00 gui=NONE

So, they are coloured the same way.


> How do I get involved?

Depends on whether the existing maintainers want to continue to maintain.
They could make changes based on your request and their own ideas.
Or if they are too busy and you are willing you can take over as the
maintainer agreed upon by Bram.

There is certainly a lot of help on the vim_use forum to get you
through the learning curves.

HTH,
Dave

[William Robertson]

On Tuesday, August 28, 2012 6:32:37 PM UTC+1, David Fishburn wrote:

> On Mon, Aug 27, 2012 at 5:35 AM, William Robertson wrote:
>
> ...
>
> > In my setup I have an edited sql.vim and plsql.vim

Apologies again, I meant sqloracle.vim and not sql.vim. The issues I had with autocompletion were a couple of months back and I forget the details - I'll recheck and post in a separate thread if needed so as not to hijack Szilard's, but yes I use $HOME\vimfiles (or $HOME/.vim on Mac).

> > I also simplified the highlighting so there is only one kind of "keyword", to remove the jumble you get where SELECT and AND etc all come out different colours (pink and green or whatever the default is). Also I have a colours file that's a bit more muted and 21st century IMHO than the supplied set.
>
> Typically, "SELECT" is a "sqlStatement" and an "AND" is a
> "sqlOperator". A sqlStatement begins a statement, and operators are
> AND, OR, BETWEEN, ...
>
> You can see the linkage in your syntax/sqloracle.vim file here:
>
> HiLink sqlOperator Operator
> HiLink sqlStatement Statement
>
> There is no reason why you can't change your colour file to indicate
> both of those should be highlighted the same colour.
>

That would certainly work, and I have done something similar for numeric and string literals (using "hi link Number String"). However I want my colour scheme to work across all languages including those - if they exist - where there is a useful highlighting distinction to be made between operators and other keywords.

Anyway this forum is a great find and I'll certainly be back with more questions.

[葛布林]

Hi, where can I get your script? I write PLSQL in Vim every day. I am willing to try your script instead.

--
You received this message from the "vim_use" maillist.
Do not top-post! Type your reply below the text you are replying to.
For more information, visit http://www.vim.org/maillist.php

--
Our enemy shall fall.

Death to all who oppose us!!

[Alessio B.]

On Sunday, August 26, 2012 4:06:26 PM UTC+2, Szilard Barany wrote:
> I have developed a syntax highlight file that supports the most recent Oracle RDBMS version, 11g R2. This includes SQL, PL/SQL (including Oracle supplied packages), SQL*Plus and RDBMS (init parameters, dynamic performance views, etc.).

Does this syntax file define folding regions too?

--
Alessio Bolognino

[David Fishburn]

By default I don't believe so.
What would you want folded?
I could see maybe between a
CREATE PROCEDURE ...
END

Possibly
CREATE TABLE ...(
)

Bu other than that ...


--
David Fishburn

[Szilard Barany]

> Does this syntax file define folding regions too?
>
> --
> Alessio Bolognino

Hi Alessio,

No it does not cover for code folding. I will consider that as I see benefit in it. I am not sure how difficult it is to implement yet, but it will take time. Therefore, the first version that I would like to see be released would only contain a (hopefully) full set of keywords/reserved words for proper syntax highlighting.

Regards,

Szilard

[Szilard Barany]

On Wednesday, 29 August 2012 11:35:15 UTC+8, 葛布林 wrote:
> Hi, where can I get your script? I write PLSQL in Vim every day. I am willing to try your script instead.

你好,

I do not think that the script is in a state that I can start sending it out. It works perfectly for me, but it is not yet fully integrated with vim functionality (e.g. I am not sure if it works perfectly with the SQL type selection by David).
So, I would like to make sure that is is not just a hack, but a proper component of vim before making it available.
But do not worry, I am trying to finalize it as soon as possible.

Regards,

Szilard

[Alessio Bolognino]

On Wed, Aug 29, 2012 at 12:40 PM, David Fishburn
<dfishb...@gmail.com> wrote:
> On Aug 29, 2012, at 4:26 AM, "Alessio B." <alessio....@gmail.com> wrote:
>> Does this syntax file define folding regions too?
>>
>>
> By default I don't believe so.
> What would you want folded?

I work with legacy code, packages and procedures thousands lines long, often
it is useful to me to fold control structure blocks (if/loop/case/etc) and
procedures/functions. Right now I do it (semi-)manually.

Actually few days ago I started writing a parser with the intention to
generate fold levels with fold-expr, but parsing PL/SQL is not a easy
task.

--
Alessio Bolognino

[Alessio Bolognino]

On Wed, Aug 29, 2012 at 12:59 PM, Szilard Barany <szi...@gmail.com> wrote:
> No it does not cover for code folding. I will consider that as I see benefit in it. I am not sure how difficult it is to implement yet, but it will take time. Therefore, the first version that I would like to see be released would only contain a (hopefully) full set of keywords/reserved words for proper syntax highlighting.

OK, folding PL/SQL code is not easy at all and I guess writing a
correct syntax file
can be a hairy problem, I actually tried few months ago and gave up (but I'm no
vim-syntax-files expert)

Probably writing one that does not take care of all edge cases would
be easier, but
I'm not sure that would fit in an official Vim distribution.

--
Alessio Bolognino

[William Robertson]

On Wednesday, August 29, 2012 4:35:15 AM UTC+1, 葛布林 wrote:
> Hi, where can I get your script? I write PLSQL in Vim every day. I am willing to try your script instead.
>

Hi Goblin :)

I've put it at http://www.williamrobertson.net/settings/gvim-windows if you want to try it, along with my colorscheme etc. Let me know how you get on.

William

[葛布林]

I see, and I am starting trying. Very thanks! It is much better than the original plsql.vim.

It is very handy that the color will change from white to blue after I close a quotation. But in my codes there are some occasions like 

    v_sql_where := v_sql_where ||'

                     AND a.col1 = 1 ';

 with the first quotation mark at the first row while the second quotation mark at the second row. In this condition I found it did not handle properly.

I noticed the type of  "char"  would have a pink backgroud color while the other types as " varchar2" or "number" still having the background stay the same. I am wondering why "char" is so special... 

--
You received this message from the "vim_use" maillist.
Do not top-post! Type your reply below the text you are replying to.
For more information, visit http://www.vim.org/maillist.php

[William Robertson]

On Tuesday, September 11, 2012 4:03:30 AM UTC+1, 葛布林 wrote:
> I see, and I am starting trying. Very thanks! It is much better than the original plsql.vim.
>
>
> It is very handy that the color will change from white to blue after I close a quotation. But in my codes there are some occasions like 
>
>
>
>     v_sql_where := v_sql_where ||'
>                      AND a.col1 = 1 ';
>
>
>  with the first quotation mark at the first row while the second quotation mark at the second row. In this condition I found it did not handle properly.
>
>
>

> I noticed the type of  "char"  would have a pink background color while the other types as " varchar2" or "number" still having the background stay the same. I am wondering why "char" is so special... 
>

I didn't touch the quoting rules - they are still the original. I'll have a look but I doubt I can improve on them.

I assigned VARCHAR and CHAR to the TODO highlighting group so that you can easily replace them with the standard VARCHAR2, or if it's code you can't change at least you can prepare for unintended side effects. In 20 years of PL/SQL development I have seen a valid use for CHAR once, and that was in 2000.

btw did you use all of the .vim files I posted, or just plsql.vim?

William
PS I think this group likes you to bottom-post, newsgroup-style, rather than at the top email-style.

[葛布林]

On Tue, Sep 11, 2012 at 3:32 PM, William Robertson <wil...@williamrobertson.net> wrote:

I didn't touch the quoting rules - they are still the original. I'll have a look but I doubt I can improve on them.

I assigned VARCHAR and CHAR to the TODO highlighting group so that you can easily replace them with the standard VARCHAR2, or if it's code you can't change at least you can prepare for unintended side effects. In 20 years of PL/SQL development I have seen a valid use for CHAR once, and that was in 2000.

btw did you use all of the .vim files I posted, or just plsql.vim?

William
PS I think this group likes you to bottom-post, newsgroup-style, rather than at the top email-style.

--
You received this message from the "vim_use" maillist.
Do not top-post! Type your reply below the text you are replying to.
For more information, visit http://www.vim.org/maillist.php

e... I am using Gmail, and when I click the "Reply", it automatically build a top-style...

I noticed the mark of " worked well when there were multiple lines. It is in the sqlStringComment group which has a type of  "region" behind. ( I do not know how to call this. I know nothing about VimL...)  Maybe it would help.

I agree with you that CHAR will cause more problems than VARCHAR2. Alas, I have to deal with trunks of legacy codes... I appreciate the idea to highlight such types. This would remind me add a "trim" to have a try when  unintended  things happen.

I used all of your vim files except the _vimrc and _gvimrc.  Your vimrc files conflicted with my menu.vim and something else. It was hard for me to debug so I simply removed them. Your colorscheme was hard coded at the plsql.vim along with a tab = 3 spaces so that luckily it worked well even without your vimrc files.

I do not know if others have more suggestions or not. To me, it seems fulfill my requirements.

[William Robertson]

On Tuesday, September 11, 2012 6:29:35 PM UTC+1, 葛布林 wrote:
> On Tue, Sep 11, 2012 at 3:32 PM, William Robertson <wil...@williamrobertson.net> wrote:
>
>
>
>
> I didn't touch the quoting rules - they are still the original. I'll have a look but I doubt I can improve on them.
>
>
>
> I assigned VARCHAR and CHAR to the TODO highlighting group so that you can easily replace them with the standard VARCHAR2, or if it's code you can't change at least you can prepare for unintended side effects. In 20 years of PL/SQL development I have seen a valid use for CHAR once, and that was in 2000.
>
>
>
>
> btw did you use all of the .vim files I posted, or just plsql.vim?
>
>
>
> William
>
> PS I think this group likes you to bottom-post, newsgroup-style, rather than at the top email-style.
>
>
>

> e... I am using Gmail, and when I click the "Reply", it automatically build a top-style...
>
> I noticed the mark of " worked well when there were multiple lines. It is in the sqlStringComment group which has a type of  "region" behind. ( I do not know how to call this. I know nothing about VimL...)  Maybe it would help.
>
>
>
> I agree with you that CHAR will cause more problems than VARCHAR2. Alas, I have to deal with trunks of legacy codes... I appreciate the idea to highlight such types. This would remind me add a "trim" to have a try when 
> unintended  things happen.
>
>
> I used all of your vim files except the _vimrc and _gvimrc.  Your vimrc files conflicted with my menu.vim and something else. It was hard for me to debug so I simply removed them. Your colorscheme was hard coded at the plsql.vim along with a tab = 3 spaces so that luckily it worked well even without your vimrc files.
>

Interesting - the quoting issue goes away if you set filetype=sqloracle. My plsql.vim just sources that so I can't think any reason for it to have its own parsing rules for quoted text. It seems fixed if you comment out the following lines:

syn match plsqlCharLiteral "'[^']'"
syn match plsqlStringLiteral "'\([^']\|''\)*'"
syn match plsqlStringError "'.*$"

I can't see a colourscheme defined in my plsql.vim or sqloracle.vim, although I'd be interested to hear how you get on with katia.vim and gfn=Consolas:h8:cANSI. The colourscheme is PL/SQL-friendly, though should also be OK with Perl, shellscript etc (although I haven't quite worked out why Korn shell "[[" comes up red - trying to fix that...)

I'd also be interested to hear whet you think of my subtle tab character highlighting - it's set up in plsql.vim but activated in _gvimrc or an ftplugin script, with

let c_show_tabs = 1

In fact I also have an ftplugin/plsql.vim containing:

if exists("b:did_plsql_ftplugin")
finish
endif
let b:did_plsql_ftplugin = 1

setlocal textwidth=160 " 80 character lines
setlocal expandtab smarttab
setlocal ts=3 sw=3 autoindent
colorscheme katia
let c_show_tabs = 1
let c_space_errors = 1

[葛布林]

On Wed, Sep 12, 2012 at 1:59 AM, William Robertson <wil...@williamrobertson.net> wrote:

Interesting - the quoting issue goes away if you set filetype=sqloracle. My plsql.vim just sources that so I can't think any reason for it to have its own parsing rules for quoted text. It seems fixed if you comment out the following lines:

syn match       plsqlCharLiteral        "'[^']'"
syn match       plsqlStringLiteral      "'\([^']\|''\)*'"
syn match       plsqlStringError        "'.*$"

I can't see a colourscheme defined in my plsql.vim or sqloracle.vim, although I'd be interested to hear how you get on with katia.vim and gfn=Consolas:h8:cANSI. The colourscheme is PL/SQL-friendly, though should also be OK with Perl, shellscript etc (although I haven't quite worked out why Korn shell "[[" comes up red - trying to fix that...)

I'd also be interested to hear whet you think of my subtle tab character highlighting - it's set up in plsql.vim but activated in _gvimrc or an ftplugin script, with

let c_show_tabs = 1

In fact I also have an ftplugin/plsql.vim containing:

if exists("b:did_plsql_ftplugin")
   finish
endif
let b:did_plsql_ftplugin = 1

setlocal textwidth=160   " 80 character lines
setlocal expandtab smarttab
setlocal ts=3 sw=3 autoindent
colorscheme katia
let c_show_tabs = 1
let c_space_errors = 1

--
You received this message from the "vim_use" maillist.
Do not top-post! Type your reply below the text you are replying to.
For more information, visit http://www.vim.org/maillist.php

What I download is the archive named gvim-windows.zip in your link site. You hardcoded the colorscheme in the ftpplugin/plsql.vim. This was the "plsql.vim" I meant... 

My personal font is  

   set gfn=consolas:h10:cANSI

   set gfw=Yahei_Mono:h10.5:cGB2312

the normal part seems not much too different with yours.

I have binded files with a suffix of ".sql"  to filetype plsql. If I open a new buffer, I will first :set ft=plsql.  I commented the three lines you mentioned, but the quoting still not worked as ft=sqloracle.  I navigated the plsql.vim, but I did not find where you override the mark of ' besides those three lines.

About the colorscheme... actually I didt not quite get used to your colorscheme. I prefer dark background with colorful keywords. I am using a theme called "diablo3"  downloaded from vim.org.

c_show_tabs and c_space_errors are good features and I will take them. And the textwidth I will set to 240 (120 characters per line) along with ts=2, sw=2 in my personal use. This is a team code convention I have to follow...

[KKde]

On Sunday, August 26, 2012 9:06:26 AM UTC-5, Szilard Barany wrote:
> Hi,
>
> In the current vim releases the Oracle RDBMS SQL syntax support is from 2005 and PLS/SQL support is from 2002; kind of outdated.

> I have developed a syntax highlight file that supports the most recent Oracle RDBMS version, 11g R2. This includes SQL, PL/SQL (including Oracle supplied packages), SQL*Plus and RDBMS (init parameters, dynamic performance views, etc.).

> I used this syntax highlight support for 4+ years, and it seems to me OK by now. It might not be perfect, but I hope that other people who use vim for Oracle script/file editing can help me improve it.
> I would like to submit it to vim hoping that it will be included in the next releases. I would like to find out what to do now; how to submit it to the code base, etc.

> I plan to cover the next Oracle release, 12c as soon as it is out (towards the end of the year).
>
> Thanks and regards,
>
> Szilard

May I know when can we expect your updated files!