i found that in toad for oracle 12.8 or this beta 123.9.0.39 the code pick list does not behave well,
in general what i found after many tries that it works with normal select statements but not within create view statements or inside procedures and and packages .
ziad
Please provide a sample. I tried a simple create view and it was OK.
as below it will not work
create or replace view test as
select x.*
from p x
inner join c y on x.id = y. – brings all tables not table columns
but just make one line or let the select statement alone without the create view it will work
create or replace view test as
select x.*
from p x
inner join c y on x.id = y. – here it will bring the columns
i installed 64 bit from latest beta.
also tried with this real public synonyms and it behaves the same
create or replace view test as
select t. – no list
from user_tables t
inner join USER_TAB_COLUMNS c on c. – tables list
create or replace view test as
select t. – wroks
from user_tables t
inner join USER_TAB_COLUMNS c on c. – works
This is fixed for next beta.
after it has been fixed in 12.9.0.41 now it returned back in 12.9.0.43
I tried with your sample and it’s working for me after the “t.” and “c.” Do you have a new statement that you’re trying with?
create or replace view test as
select t. – wroks
from user_tables t
inner join USER_TAB_COLUMNS c on c. – works
check this, i tried to get the scenario…
select t.*
from user_tables t
inner join USER_TAB_COLUMNS c on c. – does not work
left join USER_TAB_COLUMNS c1 on c. – works
It’s working for me in this example as well. In this screenshot I’ve invoked Code Insight after each “c.” in the SELECT as a select and as a create view. In all 4 cases I got the expected results. I changed the first USER_TAB_COLUMNS to USER_OBJECTS for easier confirmation, but it’s working with USER_TAB_COLUMNS as well.
From: zmallah_375 [mailto:bounce-zmallah_375@toadworld.com]
Sent: Wednesday, March 23, 2016 4:47 AM
To: toadoraclebeta@toadworld.com
Subject: RE: [Toad for Oracle - Beta Discussion Forum] Code pick list
Reply by zmallah_375
check this, i tried to get the scenario…
select t.*
from user_tables t
inner join USER_TAB_COLUMNS c on c. -- does not work
left join USER_TAB_COLUMNS c1 on c. -- works
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or
Unsubscribe from Toad for Oracle - Beta notifications altogether.
Toad for Oracle - Beta Discussion Forum
Flag
this post as spam/abuse.
you are right it brings the list,
but once you start typing column letters columns disappear in the first “c.”
but in the second “c.” it show the list and filter columns correctly.
select t.*
from user_tables t
inner join USER_objects o on o. – does not work (columns filter)
left join USER_TAB_COLUMNS c on c. – works
Thanks, I can reproduce.
Try again next beta. I think that all of these cases are OK now, hopefully everything else is still OK! ANSI join syntax presents a bit of an issue for Code Insight to say the least. There is still some work to be done here. Your most recent sample will display columns for “c.” when you invoke it after the “o.” which is wrong.
now it works in the sql editor, but it is not working inside plsql,
now i am trying in package and the pick list works some times and some times not.
even with regular joins not ansi joins.
also i found something in plsql that if u have internal blocks in code using “begin … end”
the grouping and collapse works incorrectly.
Please provide samples. I just tried a cursor in PL/SQL and Code Insight appears to be OK when invoked in various clauses (select list, from, where) using both Oracle and ANSI join syntax. Similarly sample text for the folding would be helpful.
sample below with comments in the place of the issue…
CREATE OR REPLACE PACKAGE BODY test123
AS
x varchar2(100) := ‘AR’;
PROCEDURE test11 (
p1 IN Number,
p2 IN NVARCHAR2,
p3 IN Number,
p4 OUT resultset,
p5 OUT resultset,
p6 OUT ResultSet
)
IS
v_pto date;
v_pc date;
v_plotid number(15);
begin
select o. – filter column does not work
max(o.OBJECT_NAME) t. – does not work because no comma after previous column and so all below will not work
into v_pto
from user_objects o
inner join user_tables t on t.TABLE_NAME = o.OBJECT_NAME
inner join user_tab_columns c on c.TABLE_NAME = t.TABLE_NAME
left join user_indexes i on i.TABLE_NAME = c.TABLE_NAME
where o.OBJECT_NAME = ‘’
group by o.OBJECT_TYPE;
if v_pc > v_pto then
select max(o.OBJECT_NAME)
into vv
from user_objects o
inner join user_tables t on t.TABLE_NAME = o.OBJECT_NAME
inner join user_tab_columns c on c.TABLE_NAME = t.TABLE_NAME and c.DATA_TYPE = ‘’
left join user_indexes i on i.TABLE_NAME = c.TABLE_NAME
where o.OBJECT_NAME = ‘’
order by o.OBJECT_TYPE;
end if;
Exception
when no_data_found then null;
end;
PROCEDURE test12 (
p1 IN Number,
p2 IN NVARCHAR2,
p3 IN Number,
p4 OUT resultset,
p5 OUT resultset,
p6 OUT ResultSet
)
IS
begin – try to fold here
begin
OPEN pownerdetailsdata FOR
select
Case When x = ‘EN’ Then
Case When o.OBJECT_NAME = 1 Then o.OBJECT_NAME Else Null End
Else
Case When o.OBJECT_NAME = 1 Then o.OBJECT_NAME Else Null End
End AS ss – nested case is the issue
from user_objects o
inner join user_tables t on t.TABLE_NAME = o.OBJECT_NAME
inner join user_tab_columns c on c.TABLE_NAME = t.TABLE_NAME
left join user_indexes i on i.TABLE_NAME = c.TABLE_NAME
where o.OBJECT_NAME = ‘’;
end;
begin
OPEN pownerdetailsdata FOR
select * from (
select o.OBJECT_NAME
from user_objects o
inner join user_tables t on t.TABLE_NAME = o.OBJECT_NAME
inner join user_tab_columns c on c.TABLE_NAME = t.TABLE_NAME and c.DATA_TYPE = ‘’
left join user_indexes i on i.TABLE_NAME = c.TABLE_NAME
where o.OBJECT_NAME = ‘’
group by o.OBJECT_NAME
ORDER BY sum (percentownership) over (partition by o.OwnerId, ownershipdeedacquisitiontype) desc
) b
where b.OBJECT_NAME = ‘’ ;
end;
end;
PROCEDURE test13 (
p1 IN Number,
p2 IN NVARCHAR2,
p3 IN Number,
p4 OUT resultset,
p5 OUT resultset,
p6 OUT ResultSet
)
IS
begin
begin
OPEN pownerdetailsdata FOR
select
distinct o.OBJECT_NAME
from user_objects o
inner join user_tables t on t.TABLE_NAME = o.OBJECT_NAME
inner join user_tab_columns c on c.TABLE_NAME = t.TABLE_NAME
left join user_indexes i on i.TABLE_NAME = c.TABLE_NAME
where o;
end;
begin
OPEN pownerdetailsdata FOR
select o.OBJECT_NAME
from user_objects o
inner join user_tables t on t.TABLE_NAME = o.OBJECT_NAME
inner join user_tab_columns c on c.TABLE_NAME = t.TABLE_NAME and c.DATA_TYPE = ‘’
left join user_indexes i on i.TABLE_NAME = c.TABLE_NAME
where o.OBJECT_NAME = ‘’
order by o.OBJECT_NAME;
end;
end;
end;
/
On folding… I couldn’t reproduce, at first. ToadWorld has no ability to mark code so that your formatting is retained in posts. As usual, I ran it through Toad’s formatter. Folding is OK. I tried again taking the copied code as-is and I see what you mean. It doesn’t like the “case” and “when” on the same line looks like. Once your “when” clauses are moved down a line it’s OK. I’ll log and have a look when I have some time.
The two cases of Code Insight not working are a known limitation. It comes up a bit, has been logged, and is being investigated. CI uses the parser to digest your code and make sense of everything. In most cases code is syntactically invalid because it it not yet complete. The parser does a decent job with many syntax errors; however, in some cases the errors are enough to trip it up and Toad does not know of the tables referenced so it cannot provide assistance. These two cases illustrate the issue.
Michael
