What is the string concatenation operator in Oracle?
It is ||, for example: select ‘Mr ‘ || ename from emp; The only “interesting” feature I can think of is that ‘x’ || null returns ‘x’, not null as you might perhaps expect.
oracle
How to add ‘ON DELETE CASCADE’ in ALTER TABLE statement
You can not add ON DELETE CASCADE to an already existing constraint. You will have to drop and re-create the constraint. The documentation shows that the MODIFY CONSTRAINT clause can only modify the state of a constraint (i-e: ENABLED/DISABLED…).
Oracle SELECT TOP 10 records [duplicate]
You’ll need to put your current query in subquery as below : SELECT * FROM ( SELECT DISTINCT APP_ID, NAME, STORAGE_GB, HISTORY_CREATED, TO_CHAR(HISTORY_DATE, ‘DD.MM.YYYY’) AS HISTORY_DATE FROM HISTORY WHERE STORAGE_GB IS NOT NULL AND APP_ID NOT IN (SELECT APP_ID FROM HISTORY WHERE TO_CHAR(HISTORY_DATE, ‘DD.MM.YYYY’) =’06.02.2009′) ORDER BY STORAGE_GB DESC ) WHERE ROWNUM <= 10 Oracle … Read more
How to retrieve the current value of an oracle sequence without increment it?
SELECT last_number FROM all_sequences WHERE sequence_owner=”<sequence owner>” AND sequence_name=”<sequence_name>”; You can get a variety of sequence metadata from user_sequences, all_sequences and dba_sequences. These views work across sessions. EDIT: If the sequence is in your default schema then: SELECT last_number FROM user_sequences WHERE sequence_name=”<sequence_name>”; If you want all the metadata then: SELECT * FROM user_sequences WHERE … Read more
error: ORA-65096: invalid common user or role name in oracle
99.9% of the time the error ORA-65096: invalid common user or role name means you are logged into the CDB when you should be logged into a PDB. For example, if you used the default 19c installation settings, you should login to ORCLPDB (the PDB) instead of ORCL (the CDB). DANGER – If you insist … Read more
Oracle “(+)” Operator
That’s Oracle specific notation for an OUTER JOIN, because the ANSI-89 format (using a comma in the FROM clause to separate table references) didn’t standardize OUTER joins. The query would be re-written in ANSI-92 syntax as: SELECT … FROM a LEFT JOIN b ON b.id = a.id This link is pretty good at explaining the … Read more
Removing duplicate rows from table in Oracle
Use the rowid pseudocolumn. DELETE FROM your_table WHERE rowid not in (SELECT MIN(rowid) FROM your_table GROUP BY column1, column2, column3); Where column1, column2, and column3 make up the identifying key for each record. You might list all your columns.
Oracle find a constraint
select * from all_constraints where owner=”<NAME>” and constraint_name=”SYS_C00381400″ / Like all data dictionary views, this a USER_CONSTRAINTS view if you just want to check your current schema and a DBA_CONSTRAINTS view for administration users. The construction of the constraint name indicates a system generated constraint name. For instance, if we specify NOT NULL in a … Read more
How do I turn off Oracle password expiration?
To alter the password expiry policy for a certain user profile in Oracle first check which profile the user is using: select profile from DBA_USERS where username=”<username>”; Then you can change the limit to never expire using: alter profile <profile_name> limit password_life_time UNLIMITED; If you want to previously check the limit you may use: select … Read more
How do I reset a sequence in Oracle?
Here is a good procedure for resetting any sequence to 0 from Oracle guru Tom Kyte. Great discussion on the pros and cons in the links below too. tkyte@TKYTE901.US.ORACLE.COM> create or replace procedure reset_seq( p_seq_name in varchar2 ) is l_val number; begin execute immediate ‘select ‘ || p_seq_name || ‘.nextval from dual’ INTO l_val; execute … Read more