Examine the following command:
CREATE TABLE (prod_id number(4),
Prod_name varchar2 (20),
Category_id number(30),
Quantity_on_hand number (3) INVISIBLE);
Which three statements are true about using an invisible column in the PRODUCTS table?
A.
The %ROWTYPE attribute declarations in PL/SQL to access a row will not display the invisible
column in the output.
B.
The DESCRIBE commands in SQL *Plus will not display the invisible column in the output.
C.
Referential integrity constraint cannot be set on the invisible column.
D.
The invisible column cannot be made visible and can only be marked as unused.
E.
A primary key constraint can be added on the invisible column.
Explanation:
AB: You can make individual table columns invisible. Any generic access of a table
does not show the invisible columns in the table. For example, the following operations do not
display invisible columns in the output:
* SELECT * FROM statements in SQL
* DESCRIBE commands in SQL*Plus
* %ROWTYPE attribute declarations in PL/SQL
* Describes in Oracle Call Interface (OCI)
Incorrect:
Not D: You can make invisible columns visible.
You can make a column invisible during table creation or when you add a column to a table, and
you can later alter the table to make the same column visible.
Reference: Understand Invisible Columns
CREATE TABLE mytable (a INT, b INT INVISIBLE, c INT, CONSTRAINT cons PRIMARY KEY (b));
table created
Referential integrity constraint can be set,
ABE
Ledeboer, Jeroen: Primary Key is Primary Key Constraint not Referential constraint like in your example 😉
on the other hand … C is true…. too.
CREATE TABLE DEPARTMENT
(DEPARTMENT_ID NUMBER INVISIBLE PRIMARY KEY,
DEPARTMENT_NAME VARCHAR2(100),
LOCATION_ID NUMBER);
Table created.
CREATE TABLE EMPLOYEE
(EMPLOYEE_ID NUMBER PRIMARY KEY,
EMPLOYEE_NAME VARCHAR2(100),
DEPARTMENT_ID NUMBER INVISIBLE,
SALARY NUMBER,
JOB VARCHAR2(100),
HIRE_DATE DATE);
Table created.
alter table employee
add CONSTRAINT FK_DEPT FOREIGN KEY (department_id) REFERENCES DEPARTMENT(DEPARTMENT_ID);
Table altered.
sorry C is not true.
maybe B is wrong?
http://tkyte.blogspot.com.es/2013/07/12c-sql-plus-new-things.html
ops$tkyte%ORA12CR1> create table t
2 ( x int,
3 y int invisible
4 );
Table created.
ops$tkyte%ORA12CR1> desc t
Name Null? Type
—————————————- ——– —————————-
X NUMBER(38)
ops$tkyte%ORA12CR1> show colinvisible
colinvisible OFF
ops$tkyte%ORA12CR1> set colinvisible ON
ops$tkyte%ORA12CR1> desc t
Name Null? Type
—————————————- ——– —————————-
X NUMBER(38)
Y (INVISIBLE) NUMBER(38)
A,B,E
A primary key can be added on an invisible column. Hence A,B,E are ture
ABE
CREATE TABLE C##USER1.TABLE1
(
COLUMN1 VARCHAR2(20)
, COLUMN2 VARCHAR2(20) invisible
);
set serveroutput on
declare
aa C##USER1.TABLE1%ROWTYPE;
begin
aa.column2:=’22’;
DBMS_OUTPUT.PUT_LINE(‘test’|| aa.column2);
end;
/
Error starting at line : 13 in command –
declare
aa C##USER1.TABLE1%ROWTYPE;
begin
aa.column2:=’22’;
DBMS_OUTPUT.PUT_LINE(‘test’|| aa.column2);
end;
Error report –
ORA-06550: linia 4, kolumna 4:
PLS-00302: component ‘COLUMN2’ must be declared
Correct Answer: ABE
Explanation : http://www.oracle.com/technetwork/articles/database/invisible-columns-odb12c-2331522.html
https://docs.oracle.com/database/121/SQLRF/statements_7002.htm#SQLRF01402 ( Answer A )
Answer A : PL/SQL %ROWTYPE attributes do not show INVISIBLE columns.
Answer B : By Default, invisible columns do not show up when the DESCRIBE statement is issued against the table
Answer E : Check Constraints defined on invisible columns will continue to work just as in visible columns:
Not D : Once a column has been set to invisible, it can be reverted back to visible using the ALTER TABLE statement.
a b e
SQL> alter table q modify quantity_on_hand invisible ;
Table altered.
SQL> desc q
Name Null? Type
—————————————– ——– —————————-
PROD_ID NUMBER(4)
PROD_NAME VARCHAR2(20)
CATEGORY_ID NUMBER(30)
SQL> alter table q modify quantity_on_hand visible ;
Table altered.
SQL> desc q
Name Null? Type
—————————————– ——– —————————-
PROD_ID NUMBER(4)
PROD_NAME VARCHAR2(20)
CATEGORY_ID NUMBER(30)
QUANTITY_ON_HAND NOT NULL NUMBER(3)
repeat to and fro as often as you like. So D works fine, too
It’s amazing to pay a visit this website and reading the views of all colleagues about this article, while I am also keen of getting knowledge.|
I read this piece of writing completely concerning the resemblance of most recent and earlier technologies, it’s awesome article.|
ABE
ABE