Toad World® Forums

F4 Script Tab displaying code not related to current view


#1

I just described a view and went to the script tab and was surprised to see some constraint code that doesn’t belong to this view at all. In fact, each of the 3 alter table statements is for a different table. :slight_smile:

Here is the script as it was created and the Spool SQL code below it. What is especially interesting is that when I run the code from the Spool SQL with the variables it says it is using, I don’t get any results for those constraints.

CREATE OR REPLACE VIEW ANT.WV_ADDR_F_GEOCODE

AS

SELECT “ADDRESS_STRING”,

“PARENT_ROW_WID”,

“TABLE_NAME”,

“GEOCODE_WID”,

“LATITUDE”,

“LONGITUDE”,

“CREATED_DT”,

“UPDATE_DT”,

“CONFIDENCE_LEVEL”

FROM siebel.wc_addr_with_geocoding

WHERE 1 = 1

AND table_name = ‘WC_ADDR_F’;

ALTER TABLE ANT.TAB_INSERTS ADD (

CONSTRAINT TI_NUM_INSERT_TABLES_CHK

CHECK (ti_num_insert_tables IN (1, 2)),

CONSTRAINT TI_REQ_FIELDS_CHK

CHECK (

(ti_update_type = ‘PROCEDURE’ AND ti_procedure_name IS NOT NULL) OR

ti_num_insert_tables = 1 OR

(ti_num_insert_tables = 2

AND ti_row_wid_name IS NOT NULL

AND ti_insert_view IS NOT NULL

AND ti_increment_value IS NOT NULL

AND ti_view_name IS NOT NULL)

));

ALTER TABLE ANT.VALIDATION_DEF ADD (

CONSTRAINT VDEF_NO_FAIL_CHK

CHECK (vdef_no_fail IN (‘Y’, ‘N’)));

ALTER TABLE ANT.VALIDATION_RESULTS ADD (

CONSTRAINT VRES_RESULT_CHK

CHECK (vres_result IN (‘Success’, ‘Warning’, ‘Error’, ‘Under Threshhold’)));


Session: ANT@SBLDWP.CCCI.ORG

Timestamp: 10:16:56.483

Select v.view_name, v.text_length, v.text, ‘VALID’ status

,trim(v.type_text) type_text, v.oid_text, v.view_type_owner, v.view_type

, superview_name

, editioning_view

from SYS.user_views v

where 1=1

and v.view_name = :OneObjectName

order by view_name

:OneObjectName(VARCHAR[17],IN)=‘WV_ADDR_F_GEOCODE’


Session: ANT@SBLDWP.CCCI.ORG

Timestamp: 10:16:56.493

Select object_name

from sys.DBA_OBJECTS

where owner = :own

and object_type = ‘VIEW’

and status = ‘INVALID’

and object_name = :OneObjectName

order by object_name

:own(Unknown)=

:OneObjectName(Unknown)=


Session: ANT@SBLDWP.CCCI.ORG

Timestamp: 10:16:56.497

Select TABLE_NAME, CONSTRAINT_NAME

from SYS.DBA_CONSTRAINTS

where owner = :own

AND constraint_type in (‘V’, ‘O’)

AND generated = ‘USER NAME’

and TABLE_NAME = :OneObjectName

order by table_name

:own(VARCHAR[3],IN)=‘ANT’

:OneObjectName(VARCHAR[17],IN)=‘WV_ADDR_F_GEOCODE’


Session: ANT@SBLDWP.CCCI.ORG

Timestamp: 10:16:56.509

Select t.*

from SYS.DBA_TRIGGERS t

where t.table_owner = :own

and t.table_name = :OneObjectName

order by t.trigger_name

:own(VARCHAR[3],IN)=‘ANT’

:OneObjectName(VARCHAR[17],IN)=‘WV_ADDR_F_GEOCODE’


Session: ANT@SBLDWP.CCCI.ORG

Timestamp: 10:16:56.521

Select c.TABLE_NAME, NULL COLUMN_NAME, c.COMMENTS

FROM SYS.DBA_TAB_COMMENTS c

WHERE c.OWNER = :own

AND c.COMMENTS IS NOT NULL

and C.TABLE_NAME = :OneObjectName

order by c.table_name

:own(VARCHAR[3],IN)=‘ANT’

:OneObjectName(VARCHAR[17],IN)=‘WV_ADDR_F_GEOCODE’


Session: ANT@SBLDWP.CCCI.ORG

Timestamp: 10:16:56.527

Select c.TABLE_NAME, c.COLUMN_NAME, c.COMMENTS

FROM SYS.DBA_COL_COMMENTS c

WHERE c.OWNER = :own

AND c.COMMENTS IS NOT NULL

and C.TABLE_NAME = :OneObjectName

order by c.table_name

:own(VARCHAR[3],IN)=‘ANT’

:OneObjectName(VARCHAR[17],IN)=‘WV_ADDR_F_GEOCODE’


Session: ANT@SBLDWP.CCCI.ORG

Timestamp: 10:16:56.532

Select constraint_type, count (*) cnt

from SYS.DBA_CONSTRAINTS

where owner = :own

and not ((length(CONSTRAINT_NAME) = 30) and

(substr(CONSTRAINT_NAME, 1, 4) = ‘BIN$’) and

(substr(CONSTRAINT_NAME, 27, 4) = ‘==$0’))

group by constraint_type

:own(VARCHAR[3],IN)=‘ANT’


Session: ANT@SBLDWP.CCCI.ORG

Timestamp: 10:16:56.626

Select C.CONSTRAINT_NAME, CC.COLUMN_NAME, C.SEARCH_CONDITION, C.GENERATED, C.TABLE_NAME

from SYS.DBA_CONS_COLUMNS cc , SYS.DBA_CONSTRAINTS c

where c.OWNER = cc.OWNER

and c.CONSTRAINT_NAME = cc.CONSTRAINT_NAME

and c.TABLE_NAME = cc.TABLE_NAME

and c.OWNER = :own

and not ((length(C.CONSTRAINT_NAME) = 30) and

(substr(C.CONSTRAINT_NAME, 1, 4) = ‘BIN$’) and

(substr(C.CONSTRAINT_NAME, 27, 4) = ‘==$0’))

AND C.CONSTRAINT_TYPE in (‘C’, ‘?’, ‘F’)

order by table_name, constraint_name, column_name

:own(VARCHAR[3],IN)=‘ANT’


Session: ANT@SBLDWP.CCCI.ORG

Timestamp: 10:16:56.671

Select CONSTRAINT_NAME, OWNER, TABLE_NAME, CONSTRAINT_TYPE, SUBSTRB(STATUS, 1, 1) STATUS

, SEARCH_CONDITION

, SUBSTRB(DEFERRABLE, 1, 1) deferrable, SUBSTRB(DEFERRED, 1, 1) deferred, SUBSTRB(GENERATED, 1, 1) generated

, VALIDATED

, RELY

, VIEW_RELATED

FROM SYS.user_constraints

where 1=1

AND CONSTRAINT_TYPE in (:ct, ‘?’, ‘F’)

AND Constraint_name in (‘TI_NUM_INSERT_TABLES_CHK’,‘TI_REQ_FIELDS_CHK’,‘VDEF_NO_FAIL_CHK’,‘VRES_RESULT_CHK’)

and not ((length(CONSTRAINT_NAME) = 30) and

(substr(CONSTRAINT_NAME, 1, 4) = ‘BIN$’) and

(substr(CONSTRAINT_NAME, 27, 4) = ‘==$0’))

order by table_name, constraint_name

:ct(VARCHAR[1],IN/OUT)=‘C’


Session: ANT@SBLDWP.CCCI.ORG

Timestamp: 10:16:56.792

Select CONSTRAINT_NAME, OWNER, TABLE_NAME, CONSTRAINT_TYPE, SUBSTRB(STATUS, 1, 1) STATUS

, SUBSTRB(DEFERRABLE, 1, 1) deferrable, SUBSTRB(DEFERRED, 1, 1) deferred, SUBSTRB(GENERATED, 1, 1) generated

, VALIDATED

, RELY

, VIEW_RELATED

, INDEX_NAME, INDEX_OWNER

FROM SYS.user_constraints

where 1=1

AND CONSTRAINT_TYPE = :ct

and TABLE_NAME = :OneObjectName

and not ((length(CONSTRAINT_NAME) = 30) and

(substr(CONSTRAINT_NAME, 1, 4) = ‘BIN$’) and

(substr(CONSTRAINT_NAME, 27, 4) = ‘==$0’))

order by table_name, constraint_name

:ct(VARCHAR[1],IN/OUT)=‘P’

:OneObjectName(VARCHAR[17],IN)=‘WV_ADDR_F_GEOCODE’


Session: ANT@SBLDWP.CCCI.ORG

Timestamp: 10:16:56.799

Select CONSTRAINT_NAME, OWNER, TABLE_NAME, CONSTRAINT_TYPE, SUBSTRB(STATUS, 1, 1) STATUS

, R_OWNER, R_CONSTRAINT_NAME, SUBSTRB(DELETE_RULE, 1, 1) delete_rule

, SUBSTRB(DEFERRABLE, 1, 1) deferrable, SUBSTRB(DEFERRED, 1, 1) deferred, SUBSTRB(GENERATED, 1, 1) generated

, VALIDATED

, RELY

, VIEW_RELATED

FROM SYS.user_constraints

where 1=1

AND CONSTRAINT_TYPE = :ct

and TABLE_NAME = :OneObjectName

and not ((length(CONSTRAINT_NAME) = 30) and

(substr(CONSTRAINT_NAME, 1, 4) = ‘BIN$’) and

(substr(CONSTRAINT_NAME, 27, 4) = ‘==$0’))

order by table_name, constraint_name

:ct(VARCHAR[1],IN/OUT)=‘R’

:OneObjectName(VARCHAR[17],IN)=‘WV_ADDR_F_GEOCODE’


Session: ANT@SBLDWP.CCCI.ORG

Timestamp: 10:16:56.804

Select CONSTRAINT_NAME, TABLE_NAME, COLUMN_NAME

FROM SYS.USER_CONS_COLUMNS A

WHERE 1=1

and TABLE_NAME = :OneObjectName

ORDER BY TABLE_NAME, CONSTRAINT_NAME, POSITION, COLUMN_NAME

:OneObjectName(VARCHAR[17],IN)=‘WV_ADDR_F_GEOCODE’


Session: ANT@SBLDWP.CCCI.ORG

Timestamp: 10:16:56.821

select ue.name GRANTEE, u.name OWNER, o.name TABLE_NAME, ur.name GRANTOR, tpm.name PRIVILEGE,

decode(mod(oa.option$,2), 1, ‘YES’, ‘NO’) GRANTABLE,

decode(bitand(oa.option$,2), 2, ‘YES’, ‘NO’) HIERARCHY,

decode(o.TYPE#, 2, ‘TABLE’, 4, ‘VIEW’,

6, ‘SEQUENCE’, 7, ‘PROCEDURE’,

8, ‘FUNCTION’, 9, ‘PACKAGE’,

13, ‘TYPE’, 22, ‘LIBRARY’,

23, ‘DIRECTORY’, 24, ‘QUEUE’,

66, ‘JOB’, 67, ‘PROGRAM’, 74, ‘SCHEDULE’,

28, ‘JAVA SOURCE’, 29, ‘JAVA CLASS’, 30, ‘JAVA RESOURCE’,

32, ‘INDEXTYPE’, 33, ‘OPERATOR’,

42, ‘MATERIALIZED VIEW’, 90, ‘CREDENTIAL’, ‘UNDEFINED’) object_type

from sys.objauth$ oa, SYS."_CURRENT_EDITION_OBJ" o, sys.user$ u, sys.user$ ur, sys.user$ ue,

sys.table_privilege_map tpm

where oa.obj# = o.obj#

and oa.grantor# = ur.user#

and oa.grantee# = ue.user#

and oa.col# is null

and oa.privilege# = tpm.privilege

and u.user# = o.owner#

and o.TYPE# in (4)

and u.name = :own

and o.name = :OneObjectName

and bitand(o.flags, 128) <> 128

ORDER BY 1, 2, 3, 5

:own(VARCHAR[3],IN)=‘ANT’

:OneObjectName(VARCHAR[17],IN)=‘WV_ADDR_F_GEOCODE’


Session: ANT@SBLDWP.CCCI.ORG

Timestamp: 10:16:56.828

Select * from SYS.DBA_COL_PRIVS where OWNER = :own

and not ((length(TABLE_NAME) = 30) and

(substr(TABLE_NAME, 1, 4) = ‘BIN$’) and

(substr(TABLE_NAME, 27, 4) = ‘==$0’))

ORDER BY grantee, OWNER, table_name, column_name, privilege

:own(VARCHAR[3],IN)=‘ANT’


Phyllis Helton

Data Magician
Digital Strategies, Cru | Data Sciences & Analytics
Office :phone: 407-515-4452

phyllis.helton@cru.org


#2

Thanks Phyllis. It’s fixed for next beta


#3

Thank you, John! I’m glad I happened to look.

On Tue, Feb 17, 2015 at 10:47 AM, John Dorlon bounce-jdorlon@toadworld.com wrote:

RE: F4 Script Tab displaying code not related to current view

Reply by John Dorlon
Thanks Phyllis. It’s fixed for next beta

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.


Phyllis Helton

Data Magician
Digital Strategies, Cru | Data Sciences & Analytics
Office :phone: 407-515-4452

phyllis.helton@cru.org