This is called a correlated update UPDATE table1 t1 SET (name, desc) = (SELECT t2.name, t2.desc FROM table2 t2 WHERE t1.id = t2.id) WHERE EXISTS ( SELECT 1 FROM table2 t2 WHERE t1.id = t2.id ) Assuming the join results in a key-preserved view, you could also UPDATE (SELECT t1.id, t1.name name1, t1.desc desc1, t2.name … Read more
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
Whilst you can use the old “select 1 from dual” trick, the downside with this is that it issues an extra query each and every time you borrow a connection from the pool. For high volumes, this is wasteful. JBoss provides a special connection validator which should be used for Oracle: <valid-connection-checker-class-name> org.jboss.resource.adapter.jdbc.vendor.OracleValidConnectionChecker </valid-connection-checker-class-name> This … Read more
You’re probably looking forwith (updlock, holdlock). This will make a select grab an exclusive lock, which is required for updates, instead of a shared lock. The holdlock hint tells SQL Server to keep the lock until the transaction ends. FROM TABLE_ITEM with (updlock, holdlock)
I think this will do the trick: SELECT REGEXP_REPLACE(COLUMN, ‘[^[:print:]]’, ”)
Expanding a bit on the answers from @Guru and @Ronnis, you can hide the sequence and make it look more like an auto-increment using a trigger, and have a procedure that does the insert for you and returns the generated ID as an out parameter. create table batch(batchid number, batchname varchar2(30), batchtype char(1), source char(1), … Read more
You’re missing quotes around the first value, it should be INSERT INTO LOCATION VALUES(‘PQ95VM’, ‘HAPPY_STREET’, ‘FRANCE’); Incidentally, you’d be well-advised to specify the column names explicitly in the INSERT, for reasons of readability, maintainability and robustness, i.e. INSERT INTO LOCATION (POSTCODE, STREET_NAME, CITY) VALUES (‘PQ95VM’, ‘HAPPY_STREET’, ‘FRANCE’);
Alex Keh from Oracle in aug 2013 says: Managed ODP.NET is released. It is currently part of the Oracle DB 12c client. To use managed ODP.NET, you have to download and install the DB client. From there, you can extract just the managed ODP.NET assembly and setup files. These files are less than 10 MB … Read more
You can do this: select * from user_source where upper(text) like upper(‘%SOMETEXT%’); Alternatively, SQL Developer has a built-in report to do this under: View > Reports > Data Dictionary Reports > PLSQL > Search Source Code The 11G docs for USER_SOURCE are here
I think you should not rely on the implicit conversion. It is a bad practice. Instead you should try like this: datenum >= to_date(’11/26/2013′,’mm/dd/yyyy’) or like datenum >= date ‘2013-09-01’