Tag Info

Hot answers tagged

3

You can identify most recently created users/schemas from dba_users view select * from dba_users order by created desc; When you know what schemas to look out for, you can list the most recently created tables with select * from dba_objects where owner in ('SCHEMA1','SCHEMA2') and object_type = 'TABLE' order by created desc; For a single table, you ...


3

May I suggest doing it in a couple of passes: SQL> select * from records; SUBJ_NO -------------------------------------------------------------------------------- CBS405/505 Fourth Week(Wtr Qtr) BR 404-504-604 Fifth Week(Fall Qtr) AP490-QB-09 Weekly Sessions(Spr Qtr) APR501 Fourth Week(Wtr Qtr) MP 300 Fourth Week(Wtr Qtr) ACX/501 Fifth Week(Sum Qtr) ACX ...


3

There are several ways to do this. Either remove the join and convert the subqueries to correlated: SELECT a.ID, a.FK_ID_ANEXA, a.PET1, a.PET2, a.ALT1, a.ALT2, (SELECT SUM(b.CANT) FROM ANEXA2A_TABEL2_ROWS b WHERE b.TIP = 'PET' AND b.FK_ID_ANEXA = a.FK_ID_ANEXA ) AS PET3, (SELECT SUM(b.CANT) FROM ANEXA2A_TABEL2_ROWS b WHERE b.TIP = ...


2

You have a missing comma after the alias "Total" in your first query. Extract will take single column as input, while you are giving two columns. You could do it like: SQL> SELECT COUNT(deptno) AS "total", 2 extract(YEAR FROM hiredate) AS "year" 3 FROM emp 4 GROUP BY extract(YEAR FROM hiredate); total year ---------- ...


1

Specify the owner in GET_DDL, or use ALL_MVIEWS instead of USER_MVIEWS: select dbms_metadata.get_ddl('MATERIALIZED_VIEW', 'MVIEW_NAME', 'OWNER') from dual; select query from all_mviews where mview_name = 'your materialized view';


1

You are getting that error because you have a row in patient_info where either charges or payment (or both) are NULL. To just make the error go away, you could replace ' ' || c.charges - c.payment || ' ' with ' ' || (c.charges - c.payment) || ' ' but I suppose you would want to handle the case where charges or payment are NULL in some meaningful way, ...


1

If you query the table you're inserting into inside a trigger on that same table, you are likely to get "ORA-04091: table name is mutating, trigger/function may not see it." error. Check constraints cannot contain subqueries. Therefore your best option is to encapsulate logic for inserting data into a stored procedure and use that procedure instead of direct ...


1

skip=N is what you're looking for. Skips the first N rows, so in your case you just want to skip the first. OPTIONS(skip=1) LOAD DATA ... rest of normal control file



Only top voted, non community-wiki answers of a minimum length are eligible