How to see who is currently connected?
SELECT username, program FROM v$session WHERE username IS NOT NULL;
How to see your database version?
SELECT banner FROM v$version
Hidden Columns
Interestingly, dbms_stats will collect statistics on hidden columns, eg, from a function based index, but it doesn't appear that you can actually retrieve them
SQL> create table T ( x number ) ;
Table created.
SQL> create index TX on T ( x+10 );
Index created.
SQL> select column_name from dba_tab_cols
2 where table_name = 'T'
3 and owner = user;
COLUMN_NAME
------------------------------
SYS_NC00002$
X
SQL> declare
2 srec dbms_stats.statrec;
3 DISTCNT number;
4 DENSITY number;
5 NULLCNT number;
6 AVGCLEN number;
7 begin
8 dbms_stats.GET_COLUMN_STATS
9 (OWNNAME=>user
10 ,TABNAME=>'T'
11 ,COLNAME=>'SYS_NC00002$'
12 ,DISTCNT=>distcnt
13 ,DENSITY=>density
14 ,NULLCNT=>nullcnt
15 ,SREC=>srec
16 ,AVGCLEN=>avgclen);
17 end;
18 /
declare
*
ERROR at line 1:
ORA-20000: Unable to get values for column SYS_NC00002$
ORA-06512: at "SYS.DBMS_STATS", line 3976
ORA-06512: at "SYS.DBMS_STATS", line 3991
ORA-06512: at line 8
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade=>true);
PL/SQL procedure successfully completed.
SQL> select column_name , num_distinct
2 from dba_tab_cols
3 where table_name = 'T'
4 and owner = user;
COLUMN_NAME NUM_DISTINCT
------------------------------ ------------
SYS_NC00002$ 0
X 0
Bulk collect on records
Start with a working example of bulk collect into records.
SQL> create table T ( c1 number, c2 number );Table created.
SQL> declare
2 type r is record (
3 x number,
4 y number );
5
6 type rt is table of r;
7
8 d rt;
9
10 begin
11 select rownum, rownum
12 bulk collect into d
13 from all_Objects
14 where rownum <= 20;
15
16 forall i in 1 .. 20
17 insert into T values d(i);
18
19 end;
20 /
PL/SQL procedure successfully completed.
But what if table T has three columns, and we wanted to add the constant value "10" when we insert. Then we have problems because it would look like this:
SQL> declare2 type r is record (
3 x number,
4 y number );
5
6 type rt is table of r;
7
8 d rt;
9
10 begin
11 select rownum, rownum
12 bulk collect into d
13 from all_Objects
14 where rownum <= 20;
15
16 forall i in 1 .. 20
17 insert into T values ( d(i).x, d(i).y, 10);
18
19 end;
20 /
insert into T values ( d(i).x, d(i).y, 10);
*
ERROR at line 17:
ORA-06550: line 17, column 28:
PLS-00436: implementation restriction: cannot reference .... etc
However, what we CAN do is use objects and then apply SQL to them. All we need is some objects to mimic our PLSQL types
SQL> create or replace type r is object ( x number, y number );2 /
Type created.
SQL> create or replace type rt is table of r;
2 /
Type created.
SQL> declare
2 d rt; -- this is now pointing to a database definition not a plsql definition
3
4 begin
5 select r(rownum, rownum)
6 bulk collect into d
7 from all_Objects
8 where rownum <= 20;
9
10 insert into T
11 select x,y,10
12 from table(d);
13
14 end;
15 /
PL/SQL procedure successfully completed.
Apps2Fusion Articles 