by using some simple view in Oracle you can extract a lot of information about your DB schema, some explicit samples : (assuming that MyDBOwner is the owner of the DB and ACTIVITY is a table of a schema, this sample is succesfully tested on Oracle 10g)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
-- LIST OF USERS select OWNER,COUNT(DISTINCT TABLE_NAME) from ALL_TAB_COLUMNS GROUP BY OWNER -- LIST OF TABLES PER OWNER select table_name from ALL_TAB_COLUMNS where owner = 'MyDBOwner' group by table_name -- LIST OF FIELDS PER TABLE select COLUMN_NAME, DATA_TYPE, DATA_LENGTH, NULLABLE, CHAR_LENGTH from ALL_TAB_COLUMNS where owner = 'MyDBOwner' AND TABLE_NAME = 'ACTIVITY' -- FINDING PRIMARY KEYS select COLUMN_NAME, DATA_TYPE, DATA_LENGTH, NULLABLE, CHAR_LENGTH from ALL_TAB_COLUMNS where owner = 'MyDBOwner' AND TABLE_NAME = 'ACTIVITY' -- A single character that indicates the type of constraint: 'C' = NOT NULL, 'P' = PRIMARY KEY, 'U' = UNIQUE, and 'R' = FOREIGN KEY. SELECT UC.CONSTRAINT_NAME, UCC.* FROM USER_CONSTRAINTS UC, USER_CONS_COLUMNS UCC WHERE UC.OWNER='MyDBOwner' AND UC.TABLE_NAME = 'ACTIVITY' AND UC.CONSTRAINT_TYPE = 'P' AND UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME |
a great article about this is :…
Leggi tutto
I just starting on using Oracle 10g …. how to 1 : how retrive a guid from Oracle
1 |
select sys_guid() from dual; |
how to 2 : how retrive next value from a Sequence
1 |
select [your sequence name].nextval into from dual ; |
how to 3 : Oracle db Link , how to manage sql between multiple schema … With DB Link you can produce sql /…
Leggi tutto
PLSQL is a world …. and Oracle an incredible RDBMS, so if you want to produce XML from query or manage XML inside your PLSQL Trigger or Procedure you can in different ways …. One of the most simple way is using the Oracle function to do that, like :
1 2 3 4 5 |
SELECT sys_xmlgen(pk_id) from sellers; SELECT XMLELEMENT("MAH", XMLELEMENT("pk_id", e.pk_id ||' '|| e.cod_fiscale), XMLELEMENT ( "miamail", e.email)) AS "result" FROM sellers e; |
only with this piece…
Leggi tutto
Anche quest’anno si son chiuse le votazioni per i migliori software OpenSource sul più prestigioso dei repository : SourceForge. A questo indirizzo http://sourceforge.net/community/cca09/ si può consultare l’elenco dei vincitori per categoria, il mio superaffezzionato Firebird porta a casa una altro bel titolo, dopo essere già stato vincitore nel 2007 quest’anno si attesta il titoto di…
Leggi tutto