Since Oracle 12c we can use IDENTITY fields. Is there a way to retrieve the last inserted identity (i.e. select @@identity or select LAST_INSERTED_ID() and so on)? Answer Well. Oracle uses sequences and default values for IDENTITY functionality in 12c. Therefore you need to know about sequences for your quest…
Tag: oracle
Which is more space efficient, multiple colums or multple rows? [closed]
Suppose if i have a table A with 100 columns of same data type and 100 rows. Table B with 2 columns and 5000 rows of same data type of above table columns. Which table takes more disk space to store …
How do I group “or” in pl/sql
I have legacy sql query that selects bit masks (among other data), something like: How do I group this output like: That should be 3 Answer In order to do bit-wise logic you have to do a “bit” of math. (Bad puns are free around here :-). Oracle defines the BITAND function. To get a bitwise ‘…
How to replace all the dots before @ in an email with empty string in Oracle SQL?
I want to replace all the dots before @ in an email with empty string in oracle query like: Answer Instr – To identify the position(@) Substr – To extract data between start(1) and end(@) position Replace – To replace . with ” || – To concatenate two strings Try this Result: SqlF…
Running Oracle SQL query over several dates
Within Crystal Reports, I’m using the following query (against an Oracle database) to generate data for a single field in a report: This works fine and returns a single integer value, based on the {?HB_As_At_Date} supplied (The {?} syntax is Crystal’s way of embedding parameter values into SQL). T…
In SQL I want to display the names of all those cities which end with an vowel
I wrote the following query What is wrong with this? Answer If you want it to work with lower/upper letters, you could use UPPER(CITY), otherwise it’s all good.
Migrating from Oracle to MySQL. VARCHAR2 length defined using bytes. How to port?
I have variable in Oracle procedure declared like this: myMsg VARCHAR2(256 BYTE) How can I port it to mysql? Because this when I try to declare it in MySQL procedure: DECLARE myMsg VARCHAR(256 …
How to run .sql file in Oracle SQL developer tool to import database?
I have exported database from Oracle SQL developer tool into .sql file. Now I want to run this file which is of size 500+ MB. I read about running scripts here, but I didn’t understand the way. Is …
Can row_number() ignore null in oracle
I have data like this How can i write query to get row_number without counting null column. Answer Well, not specifically. But you can get what you want by using conditional logic: It is not clear to me what the order by is for the row_number() which is what the . . . means.
Order by case insensitive in oracle
I want to order a following text in following order but, after trying the following query it is not working. values to order is “A”, “B”, “Y”, “Z”, “a”, “b”, “y”, “z”. Expected result “ZzYyBbAa” …