SELECT CONCAT(Name,”(“,SUBSTR(Occupation,1,1),”)”) FROM OCCUPATIONS; First, the double quotes ” are used to enclose identifiers. use single quote ‘ to wrap a string. Second, CONCAT accepts two params. You could nest bunch of concats, but it’s easier and cleaner to use concatenation operation ||: SELECT Name || ‘(‘ || SUBSTR(Occupation,1,1) || ‘)’ FROM OCCUPATIONS;
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’);
Recent versions of Oracle do not have a limit but most older versions of Oracle have a nesting limit of 1 level deep. This works on all versions: SELECT ( SELECT * FROM dual dn WHERE dn.dummy = do.dummy ) FROM dual do This query works in 12c and 18c but does not work in … Read more
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
Something like create or replace procedure my_proc( p_rc OUT SYS_REFCURSOR ) as begin open p_rc for select 1 col1 from dual; end; / variable rc refcursor; exec my_proc( :rc ); print rc; will work in SQL*Plus or SQL Developer. I don’t have any experience with Embarcardero Rapid XE2 so I have no idea whether it … Read more
select v.SQL_TEXT, v.PARSING_SCHEMA_NAME, v.FIRST_LOAD_TIME, v.DISK_READS, v.ROWS_PROCESSED, v.ELAPSED_TIME, v.service from v$sql v where to_date(v.FIRST_LOAD_TIME,’YYYY-MM-DD hh24:mi:ss’)>ADD_MONTHS(trunc(sysdate,’MM’),-2) where clause is optional. You can sort the results according to FIRST_LOAD_TIME and find the records up to 2 months ago.
select exp(sum(ln(col))) from table; edit: if col always > 0
PARTITION BY segregate sets, this enables you to be able to work(ROW_NUMBER(),COUNT(),SUM(),etc) on related set independently. In your query, the related set comprised of rows with similar cdt.country_code, cdt.account, cdt.currency. When you partition on those columns and you apply ROW_NUMBER on them. Those other columns on those combination/set will receive sequential number from ROW_NUMBER But … Read more
Without seeing how you’re calling the blob to embed the image within your report code… Use blob.getBinaryStream(). Convert the stream using javax.imageio.ImageIO.read( InputStream ). For example: javax.imageio.ImageIO.read( blob.getBinaryStream() ) This will return an instance of BufferedImage, which subclasses java.awt.Image, and should be a suitable object to embed in the report. The blob variable shown in … Read more
If you need a result set and a ref cursor won’t do with a datatype called sys.anydataset. i.e what you seem to want is a pipelined function, but of course with a regular pipelined function you need to define the output structure, which in your case isn’t static. Enter anydataset. this type allows us to … Read more