Toad World® Forums

Code pick list

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

RE: 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.

My screenshot didn't make it in the previous post. Here it is.

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