![SoftTree Technologies](templates/GreenTech/images/logo.gif) |
SoftTree Technologies
Technical Support Forums
|
|
Author |
Message |
Larkov
Joined: 06 Oct 2008 Posts: 26 Country: Germany |
|
%rowtype |
|
PL/SQL-Developer / Oracle 9.2
After defining a variable in a procedure as
VAR Table%Rowtype;
SQL-Assistant does not open popup after typing "VAR." to show the fields of the table. I think the momentan functionality of Assistant 4 beta is not really thought through.
Larkov
|
|
Wed Oct 08, 2008 9:27 am |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
VAR Table%Rowtype; is not a valid declaration. Please provide more meaningful code fragment so we can assess the situation.
|
|
Wed Oct 08, 2008 9:31 am |
|
![](templates/GreenTech/images/spacer.gif) |
Larkov
Joined: 06 Oct 2008 Posts: 26 Country: Germany |
|
Declaration |
|
In the declaration-part of a procedure I declare a variable like
vAdr T_ADRESS%ROWTYPE;
basing on the rowtype of the table T_ADRESS.
After that I can use it in the code-part like
vAdr.Name := 'Michael';
vAdr.Street := 'Mainstreet 9';
vAdr.Town := 'Hamburg';
etc.
or like
SELECT *
INTO vAdr
FROM T_ADRESS TA
WHERE TA.ID = 4;
The codeassistant of PL/SQL-Developer recognizes that if I type "vAdr." that there are variables behind it and popups the variable-set. I think if I switch off the codeassistant and use SQL-Assistant instead, it should be able to do the same. Or else it is not making sense to use SQL-Assistant if the functionality goes down and not up. :-)
Larkov
|
|
Wed Oct 08, 2008 10:22 am |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
Your code fragment is still invalid. Where is the DECLARE part of this block or if this is a procedure/function where is AS/IS begins? Is there a BEGIN section?
To make it easier for everybody and to save time, please post the complete code
|
|
Wed Oct 08, 2008 10:30 am |
|
![](templates/GreenTech/images/spacer.gif) |
Larkov
Joined: 06 Oct 2008 Posts: 26 Country: Germany |
|
Code |
|
First create a table
-- Create table
create table T_ADRESS
(
NAME varchar2(30),
STREET varchar2(30),
Town varchar2(30),
ID number(11) not null
)
;
-- Create/Recreate primary, unique and foreign key constraints
alter table T_ADRESS
add constraint PK_T_ADRESS primary key (ID);
Next start this
-- Created on 08.10.2008
declare
-- Local variables here
vAdr t_adress%ROWTYPE;
begin
-- Test statements here
vAdr{here type a dot and next a popup should popup to show the variables}
end;
You don't have to run any code to test or understand it. It's something during editing. :-)
|
|
Wed Oct 08, 2008 10:43 am |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
Ok. now I can understand it.
This is completely inappropriate and unsupported. You expect SQL Assistant to "see" complex references to T_ADRESS table via the indirect definition of it in "vAdr t_adress%ROWTYPE" variable. Basically you are referring to columns of the table using special ROWTYPE syntax. Only a PL/SQL compiler compiling your code can resolve it back to columns of the T_ADRESS table using some internal reference hashes. SQL Assistant is not a PL/SQL compiler, it is a code assistant, reading the text from the editor and data from the database and it can see only stuff referenced directly. See below for details.
To get assistance with columns of T_ADRESS table, type T_ADRESS then type dot character. to get assistance with variable name, in the popup, expand local variables section and you will see "vAdr t_adress%ROWTYPE" in the list. When this item selected, the popup will insert "vadr" into the code.
Hope this helps.
|
|
Wed Oct 08, 2008 11:01 am |
|
![](templates/GreenTech/images/spacer.gif) |
Larkov
Joined: 06 Oct 2008 Posts: 26 Country: Germany |
|
%Rowtype |
|
I try to explain my view of it, but I am not a native English, so please apologize my inability to be clear enough:
The SQL-Assistant reads all information about the tables into cache. The newest version 4 even recognizes in which procedure it is to show local variables and you already accepted the idea of an enhancement that the actual packagevariables can be seen also. So there should not be a problem to show indirect information about %Rowtype-Variables too, because the informations (means: tablefields) are already in cache.
The reason why I think it is nessecary to implement is, that SQL-Assistant tries not only to be an "editor for SQL", but tries to replace the code-assistants of some products. Otherwise you would just need to offer an extra "editor-window" for typing SQL-syntax (like notepad) without recognizing windowclasses of certain applications like PL/SQL-Developer. You would not offer local variables. You would not offer help- and synatxfeatures for PL/SQL-programming.
BUT (and it is a big BUT) f.e. if the code-assistant of PL/SQL-Developer has to be switched off (so the popups do not confuse each other), I would loose some important features, SQL Assistant does not have up to now.
So the only way (and you ARE already on that way with showing local procedure-variables) is, to give the developer the basic features of a codeassistant. We developers are using SQL "inside" PL/SQL in Oracle, so we need also the basic features of a code-assistant.
I think it should not be a problem to implement it, because it is already in Cache.
Larkov
|
|
Thu Oct 09, 2008 7:28 am |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
It works fundamentally differently from your expectations, in the sense that SQL Assistant performs lexical parsing of the SQL script while the compiler performs syntaxical and grammatical parsing and compilation. SQL Assistant does not care how you define your variable, whether you make a typing error in data type name declaration or not, it doesn't matter. It picks the line with the declaration and adds it to the list. Same with tables, procedural parameters, and other stuff. It a table definition appears directly in the script, it reads its column definitions, line by line and adds them to the internal cache of table column definitions retrieved from the database (just a lexical parsing is done here). Again, if you make a typo in a column data type name, SQL Assistant will still pick it as it doesn't know whether it is a typo or just some new data type declared in some other place or created dynamically, etc…
Theoretically it is possible to look for special %ROWTYPE% syntax and from there try to find the reference to table, then try to find this table in the database, or try to find it in the same script, etc… but %ROWTYPE% is just one case of an indirect reference. Oracle supports many others. For example, you can declare a type mapped to a table row, then declare a variable of that declared type, then type "var." The mentioned type can be declared in a package declaration script and not in a package body script that your work on. There is potentially infinite number of ways how stuff can be referenced indirectly. I'm not even bringing up cases of dynamic SQL.
The bottom line, SQL Assistant currently doesn't support ROWTYPE references. You are welcome to add an enhancement request for supporting parsing and resolving of ROWTYPE references. There is sure some value in it
|
|
Thu Oct 09, 2008 10:02 am |
|
![](templates/GreenTech/images/spacer.gif) |
Larkov
Joined: 06 Oct 2008 Posts: 26 Country: Germany |
|
Ok |
|
Ok, I understand it is not as easy as I hoped to implement.
Please put it on your enhancementlist. :-)
|
|
Thu Oct 09, 2008 10:14 am |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
Will do.
|
|
Thu Oct 09, 2008 10:21 am |
|
![](templates/GreenTech/images/spacer.gif) |
maxpesola
Joined: 26 Nov 2007 Posts: 51
|
|
|
|
Sql server management 2005.
If is not possible now, from the second join into a query, after write only another table name and write the "ON" and space, i would like to see as the first join , in other words to see first the foreign keys and "colum name matches" of last table with all the previous tables already joined, then the columns of last table, then the columns of previous tables, then only for last the other joins that you visualize for first now (but now , if i select a join into list , the query resulting is wrong...).
Another request , now after selecting a brackets (also begin/end) if you move into the window with the scrolls vertical or horrizontal the selection is lost, i know that is possible to move to begin or end of brackets clikking on it but if it's possible not to lost the selections...
Then clikking with ctrl on a table, to see the structure of it like the sps
|
|
Tue Oct 28, 2008 5:31 am |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
![](templates/GreenTech/images/quote_header.gif) |
![](templates/GreenTech/images/spacer.gif) |
it's possible not to lost the selections |
This is very difficult to do. Basically SQL Assistant is not part of the editor, it is a monitoring application watching for and reacting to user keystrokes and some mouse events occurring in the edit box of the editor window, I mean the visible part of the SQL code which is the edit control sitting on the window. SQL Assistant only has access to that edit control, and doesn't have access to the internal memory structures of the editor and doesn't have access to all internal events that trigger scrolling and screen redraws. That's why drawing something on the screen which is context dependent and yet sticky is extremely complicated. Moreover, different editors have different internal implementations and work differently making it almost impossible to find a solution that works for all of them.
![](templates/GreenTech/images/quote_header.gif) |
![](templates/GreenTech/images/spacer.gif) |
Then clikking with ctrl on a table, to see the structure of it like the sps |
-
this is in our enhancements list for future releases. Unfortunately no simple solution exists that could be used in a consistent way for all supported database systems. Yet, there are certain things that you can do now to help yourself. For example, if you work with SQL Server, you can customize the "DDL (MSSQL)" query in SQL Assistant options and change it to the following
![](templates/GreenTech/images/code_header.gif) |
![](templates/GreenTech/images/spacer.gif) |
if (select isnull(max(type), '?') from /*db.*/dbo.syscomments where id = :OBJECT_ID) = 'U'
exec sp_help @objname = :OBJECT_NAME
else
select text
from /*db.*/dbo.syscomments
where id = :OBJECT_ID
order by colid |
This will return either table structure or procedural code when you use View Procedure Code menu and click on a table.
|
|
Tue Oct 28, 2008 10:01 am |
|
![](templates/GreenTech/images/spacer.gif) |
maxpesola
Joined: 26 Nov 2007 Posts: 51
|
|
|
|
![](templates/GreenTech/images/code_header.gif) |
![](templates/GreenTech/images/spacer.gif) |
if (select isnull(max(type), '?') from /*db.*/dbo.syscomments where id = :OBJECT_ID) = 'U'
exec sp_help @objname = :OBJECT_NAME
else
select text
from /*db.*/dbo.syscomments
where id = :OBJECT_ID
order by colid |
thanks but i've tried this code into "DB OPTIONS-->DB QUERIES-->DDL Code (MSSQL)" but return (if i do the "show procedure code" on table):
"/*
Cannot find source code for 'nometable'.
Check the "DDL Code (MSSQL)" query in SQL Assistant Options.
*/"
And about the change of list returned after the second "inner join ... on " ?(maybe, is it not clear?)
Last edited by maxpesola on Wed Oct 29, 2008 4:13 am; edited 1 time in total |
|
Tue Oct 28, 2008 1:08 pm |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
Sorry, I entered the query as a tip to show how to customize the output and behavior of different actions, but didn't actually try it. As a result I made an error
select isnull(max(type), '?') from /*db.*/dbo.syscomments where id = :OBJECT_ID
is supposed to be
select isnull(max(type), '?') from /*db.*/dbo.sysobjects where id = :OBJECT_ID
Even better is to do something like the following. Again this is just a tip, I leave it to others to test it and make the output look pretty
![](templates/GreenTech/images/code_header.gif) |
![](templates/GreenTech/images/spacer.gif) |
select
'Column_name: ' + name + ' ' +
'Type: ' + ISNULL(type_name(xusertype), '') + ' ' +
'Computed: ' + case when iscomputed = 0 then 'NO' else 'YES' end + ' ' +
'Length: ' + ISNULL(convert(varchar, length), '(n/a)') + ' ' +
'Prec: ' + case when charindex(type_name(xtype), N'tinyint,smallint,decimal,int,real,money,float,numeric,smallmoney') > 0
then ISNULL(convert(char(5),ColumnProperty(id, name, 'precision')), '')
else ' ' end + ' ' +
'Scale: ' + case when charindex(type_name(xtype), N'tinyint,smallint,decimal,int,real,money,float,numeric,smallmoney') > 0
then ISNULL(convert(char(5),OdbcScale(xtype,xscale)), '')
else ' ' end + ' ' +
'Nullable: ' + case when isnullable = 0 then 'NO' else 'YES' end + ' ' +
'TrimTrailingBlanks: ' + case ColumnProperty(:OBJECT_ID, name, 'UsesAnsiTrim')
when 1 then 'NO'
when 0 then 'YES'
else '(n/a)' end + ' ' +
'FixedLenNullInSource: ' + case
when type_name(xtype) not in ('varbinary','varchar','binary','char')
Then '(n/a)'
When status & 0x20 = 0 Then 'NO'
Else 'YES' END + ' ' +
'Collation: ' + ISNULL(collation, '')
from syscolumns
where id = :OBJECT_ID
and number = 0
order by colid |
|
|
Tue Oct 28, 2008 1:45 pm |
|
![](templates/GreenTech/images/spacer.gif) |
pvdm
Joined: 11 Mar 2008 Posts: 22 Country: New Zealand |
|
|
|
Hi, would it be possible to somehow show data types for variables. Maybe as a mouse over tool-tip or colour coding? Its really difficult to scroll up and down searching variable declarations on 2000+ line stored procedures that you did not write.
|
|
Tue Oct 28, 2008 3:35 pm |
|
![](templates/GreenTech/images/spacer.gif) |
|
|
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
|
|
|