Using ALL_SOURCE table to get packages sources

[mbedna]

Hello Alec,

Using Vorax3 to get package source code which exists in different schema through visual explorer causes ORA-31603 error if current schema does not have SELECT_CATALOG_ROLE. Reason is that Vorax3 uses DBMS_METADATA.GET_DLL function. Alec like you suggested could you please in Vorax4 use ALL_SOURCE table instead?

Thanks,

Marcin

[talek]

Hi Marcin,

Thanks for let me know about this limitation! I will definitely take care of it in Vorax4. I will let you know as soon as I'll have something available for beta-testing.

Thanks,
Alec.

[talek]

Hi Marcin,

Vorax4 is now smart enough to get the package spec from ALL_SOURCE in case we want to peek into somebody else definition.

[mbedna]

Hi Alec,

I have connectivity issues with vorax4-beta. I use cygwin in Windows8 system. Here is my log:

2013-06-15 09:39:57.825 [vim] - vorax#ruby#SqlplusFork() extra params=[]
2013-06-15 09:39:58.075 [vim] - vorax#sqlplus#Connect: cstr='system@XE'
2013-06-15 09:39:58.075 [rby] - parse connection string "system@XE"
2013-06-15 09:39:58.076 [vim] - {'role': '', 'prompt_for': 'password', 'db': 'XE', 'user': 'system', 'password': ''}
2013-06-15 09:40:00.636 [rby] - parse connection string "system/123456@XE"
2013-06-15 09:40:00.637 [vim] - {'role': '', 'prompt_for': '', 'db': 'XE', 'user': 'system', 'password': '123456'}
2013-06-15 09:40:00.637 [vim] - vorax#ruby#SqlplusFork() extra params=[]
2013-06-15 09:40:01.357 [vim] - vorax#sqlplus#Exec: command='set tab off\nset appinfo "VoraX"\nset arraysize 50\nset linesize 10000\nset timing on\nset echo on\nset time on\nset +++ null ""\nset serveroutput on format wrapped\nset sqlblanklines on\nstore set /tmp/vQudGPC/0.opts replace\nset define "&"\nset pause off\nset termout on' extra_args=[]
2013-06-15 09:40:01.376 [rby] - default_convertor=nil
2013-06-15 09:40:01.377 [rby] - exec: command="set tab off\nset appinfo "VoraX"\nset arraysize 50\nset linesize 10000\nset timing on\nset echo on\nset time on\nset null +++ ""\nset serveroutput on format wrapped\nset sqlblanklines on\nstore set /tmp/vQudGPC/0.opts replace\nset define "&"\nset pause off\nset termout on" params={:pack_file=>"/ +++ tmp/vQudGPC/1.sql"}
2013-06-15 09:40:30.942 [] - 0.5.0

Could you please give me some hint where to look at to fix this?

Thanks,

Marcin

[talek]

Hi Marcin,

Thanks for let me know about this issue. I will definitely look at this bug. Meanwhile, any good reason not to switch to the plain gVim on Windows?

[mbedna]

Yep. I program mostly in java and I use vim + console commands as my IDE. I used gVim before but you know: too many colors ;-)

[talek]

Bad news! For the moment, I'm going to drop Cygwin support in Vorax4, because:

1. there are some nasty integration problems between the Cygwin environment and SqlPlus. Sqlplus is just a Windows binary and knows nothing about the Cygwin path style (e.g. under Cygwin try to run sqlplus @/tmp/myscript.sql). Even I can convert every well known sql script file Vorax is using internally (like it is done in Vorax3), this is messy and doesn't cover the case when you want to run your own script file.
2. performance problems. On my Windows7 64bit, running under VirtualBox, the Cygwin performance is terrible. I spent the entire day trying to figure out the reason why it is soooooooo slooooow... No solution so far! Do you have any hints other than the well known ones like removing mapped drives from PATH or get rid of bash completion?

Both issues above are annoying, but the first one is my main concern. The fact that there's no Cygwin version of sqlplus (at least the sources to compile it on this platform), it's a big show stopper. Now, I have second thoughts if it really pays off the trouble of integrating Vorax with Cygwin.

[mbedna]

Hello Alec,

Ad1) To find out Cygwin path usually below function has to be used:

function! s:CygwinPath(path)
    return substitute(system("cygpath -m ".a:path), '\r', '', 'g')
endfunction

I stole this from @ervandew whose Eclim project has great Cygwin support.

Ad2) Unfortuanatelly I have never used Cygwin on Virtualbox so I will not be able to help about this issue. I remember indeed when I started using Cygwin few years ago I had performace issues. Do not remember exactly how I solved this but indeed I remember this was caused by Cygwin trying to connect to some network resources. Sorry.

About Cygwin support:

If adding Cygwin support is annoying please do not add it and focus on features. This is open source project. It has to be fun, right ;) Maybe me or someone else will send you later pull request with Cygwin support.

For testing I will install VirtualBox with Ubuntu to check if this feature you added works like expected.

Thanks,

Marcin

[talek]

Hi Marcin,

Thanks a lot for your feedback!

Ad1) I use the same approach in Vorax3 to convert Cygwin paths in windows paths using the "cygpath" utility:

def convert_path(path)
  win_path = `cygpath -w #{shellescape(path)}`
  return win_path.gsub(/\r?\n/, '')
end

Ad2) For the moment I don't have a plain Windows installation but as soon as I get one I will try to install Cygwin once again to see if it makes any difference.

As far as the Cygwin support is concerned, from my point of view it sounds lovely that you or somebody else would be willing to contribute with this kind of integration. Yes, Vorax is an open source project and any kind of involvement is highly appreciated.

For testing, you should be able to see this new feature in action on your Windows box using gVim. You may check the installation guide. I guess it would be easier for you instead of installing a new host on your VirtualBox.

[mbedna]

I am finally able to connect to database from Ubuntu installed in Virtualbox. When I invoke :VORAXEdit TABLE $table_name I receive this message:

SP2-1504: Cannot print uninitialized LOB variable "DDL_DEF"

[talek]

Is this the entire error stack?

[talek]

Ok, I was able to reproduce it only if the table does not exist. Is it the same in your test case too? I mean, does the $table_name exist?

[mbedna]

Yes. This is the same for me. I only see this message when table does not exist.

[talek]

Ok! Thanks for testing it! I've submitted a fix for this bug. Now, if you try to edit an object which doesn't exist a warning message is displayed.

[mbedna]

I am not able to see tables from different schema though. Is it possible to debug with vorax4-beta queries like in vorax3?

[talek]

Yes, you're right! There was a small bug I've just fixed. Thanks for let me know.

[talek]

I forgot to answer to your question. In Vorax4 you may set the g:vorax_debug=1 in your .vimrc, and a log file will be created in g:vorax_homedir location (the destination folder must exist).

[mbedna]

Thanks for info about debugging. I updated vorax4 to newest code however still am not able to see table declaration from different schema. Below are my logs:

2013-06-26 10:40:48.305 [vim] - vorax#output#Spit: ['', '', 'Oracle Database 11g Release 11.2.0.3.0 - 64bit Production', 'With the Automatic Storage Management option', '', 'Logged in as: PMI_I2@PMI2', '']
2013-06-26 10:41:03.719 [vim] - vorax#explorer#OpenDbObject bang='' rest=['TABLE', 'RP.ACS_LOG']
2013-06-26 10:41:03.719 [vim] - vorax#sqlplus#RunVoraxScript name=extract_def.sql


2013-06-26 10:41:03.720 [vim] - vorax#sqlplus#Exec: command='@C:\programs\vim73\vim73\runtime\bundle\VORAX4~1\vorax\sql\extract_def.sql ''RP'' ''ACS_LOG'' ''TABLE'' ' extra_args=[{'post': '@C:\Users\mbedna\AppData\Local\Temp\VIA2839.tmp.opts', 'prep': 'store set C:           \Users\mbedna\AppData\Local\Temp\VIA2839.tmp.opts replace\nset echo off', 'funnel': 0}]
2013-06-26 10:41:03.720 [rby] - default_convertor=nil
2013-06-26 10:41:03.720 [rby] - exec: command="@C:\\programs\\vim73\\vim73\\runtime\\bundle\\VORAX4~1\\vorax\\sql\\extract_def.sql 'RP' 'ACS_LOG' 'TABLE' " params={:prep=>"store set C:\\Users\\mbedna\\AppData\\Local\\Temp\\VIA2839.tmp.opts replace\nset echo off", :post=>"@C: \\Users\\mbedna\\AppData\\Local\\Temp\\VIA2839.tmp.opts"}
2013-06-26 10:41:04.514 [vim] - vorax#sqlplus#RunVoraxScript output=\n10:41:03 SQL> declare\n*\nERROR at line 1:\nORA-31603: object "ACS_LOG" of type TABLE not found in schema "RP"\nORA-06512: at "SYS.DBMS_METADATA", line 5088\nORA-06512: at "SYS.DBMS_METADATA", line         7589\nORA-06512: at line 41\n\n\nElapsed: 00:00:00.39\n\n\nElapsed: 00:00:00.20\nSP2-0267: longchunksize option 0 out of range (1 through 2000000000)\nSP2-0267: long option 0 out of range (1 through 2000000000)\n<table><tr><td>\nSP2-1504: Cannot print uninitialized LOB       variable "DDL_DEF"\n\n</td></tr></table>\n10:41:04 SQL>
2013-06-26 10:41:04.515 [vim] - vorax#explorer#OpenDbObject output='\n10:41:03 SQL> declare\n*\nERROR at line 1:\nORA-31603: object "ACS_LOG" of type TABLE not found in schema "RP"\nORA-06512: at "SYS.DBMS_METADATA", line 5088\nORA-06512: at "SYS.DBMS_METADATA", line         7589\nORA-06512: at line 41\n\n\nElapsed: 00:00:00.39\n\n\nElapsed: 00:00:00.20\nSP2-0267: longchunksize option 0 out of range (1 through 2000000000)\nSP2-0267: long option 0 out of range (1 through 2000000000)\n<table><tr><td>\nSP2-1504: Cannot print uninitialized LOB       variable "DDL_DEF"\n\n</td></tr></table>\n10:41:04 SQL> '

[talek]

According to the log, DBMS_METADATA fails with:

ORA-31603: object "ACS_LOG" of type TABLE not found in schema "RP"

Most likely, this is because of the lack of SELECT_CATALOG_ROLE. Maybe I wasn't clear enough, but what I fixed in Vorax4 is the possibility to see the package/type spec of somebody else, not tables or other database objects. Getting the definition of a table for example, is not a trivial task considering how many options might be involved. This would be a "mini-project" by itself. Of course, if one can provide a reliable replacement of DBMS_METADATA using SQL/PLSQL logic, I'd be happy to include it in Vorax4.

So, the question is: do we have the same expectations as far as this feature is concerned?...

[mbedna]

Indeed I thought that it will be possible to see table definition of different schema without extra privileges thanks to using DBMS_METADATA. Thanks for explanation.