Oracle stores only the fractions up to second in a DATE field. Use TIMESTAMP instead: SELECT TO_TIMESTAMP(‘2004-09-30 23:53:48,140000000’, ‘YYYY-MM-DD HH24:MI:SS,FF9’) FROM dual , possibly casting it to a DATE then: SELECT CAST(TO_TIMESTAMP(‘2004-09-30 23:53:48,140000000’, ‘YYYY-MM-DD HH24:MI:SS,FF9’) AS DATE) FROM dual
COUNT(expr) will count the number of rows where expr is not null, thus you can count the number of nulls with expressions like these: SELECT count(a) nb_a_not_null, count(b) nb_b_not_null, count(*) – count(a) nb_a_null, count(*) – count(b) nb_b_null, count(case when a is not null and b is not null then 1 end)nb_a_b_not_null count(case when a is … Read more
If you want to list last 3 chars, simplest way is select substr(‘123456’,-3) from dual;
The CONTINUE statement is a new feature in 11g. Here is a related question: ‘CONTINUE’ keyword in Oracle 10g PL/SQL
you can rewrite that by pushing the sub query so that its not outer joined: select Count(C.setid) from mycustomer C left outer join (select * from mycustoption co where co.effdt <= (select Max(COI.effdt) from mycustoption COI where COI.setid = co.setid and COI.cust_id = co.cust_id and COI.effdt <= sysdate)) co on ( C.setid = CO.setid and … Read more
If you want that format try this: select regexp_replace(rawtohex(sys_guid()) , ‘([A-F0-9]{8})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{12})’ , ‘\1-\2-\3-\4-\5’) as FORMATTED_GUID from dual Example Results: FORMATTED_GUID ———————————— F680233E-0FDD-00C4-E043-0A4059C654C9
You cannot issue DDL as static SQL in a PL/SQL block. If you want to put those commands in a PL/SQL block, you’d need to use dynamic SQL, i.e. BEGIN EXECUTE IMMEDIATE ‘alter table table1 enable row movement’; EXECUTE IMMEDIATE ‘alter table table1 shrink space cascade’; END; / It may be easier, however, to just … Read more
record: Cannot be stored in the database. Cannot be recursively referenced. Cannot have logic defined as part of their definition. object: Can be stored as a database table column or as an entire row. Can be recursively referenced using the SELF parameter. Can have logic defined as part of their definition using member methods.
To locate broken index use: select index_name,index_type,status,domidx_status,domidx_opstatus from user_indexes where index_type like ‘%DOMAIN%’ and (domidx_status <> ‘VALID’ or domidx_opstatus <> ‘VALID’); To rebuild the index use: alter index INDEX_NAME rebuild;
A user in an Oracle database only has the privileges you grant. So you can create a read-only user by simply not granting any other privileges. When you create a user CREATE USER ro_user IDENTIFIED BY ro_user DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp; the user doesn’t even have permission to log in to the database. … Read more