I am trying to run a query from sql developer and query has variables (:var). I am having problem with the date variables. I used all the possible combinations to format date using to_date() function….
Tag: oracle
Using SQL*Plus from command line
I have a small vbscript to load data and process it using sqlldr and sqlplus. I have 2 questions about sqlplus usage though: 1) Can I exec a stored procedure without using an .sql script file? e.g. sqlplus user/pass@server @exec proc_myname 2) Can I use .sql script files on a shared UNC path? e.g. sqlplus use…
Oracle Regular Expression (REGEXP_LIKE) Too Long Error – ORA-12733
I have to validate an IPv6 address in PL/SQL. I came up with the regular expression from here: Regular Expression (RegEx) for IPv6 Separate from IPv4 I am getting an ORA-12733: regular expression too long error. Is there any way around this? The limit is 512 (https://stackoverflow.com/a/2694119/3112803), I…
How to read sql query from a txt-file in Java
I am having some some class that is sending queries to data base. Everything was working fine when the query was in the code, but as it’s pretty big, I decided to put it in a file and to read it with buffered reader, but it’s not working, I always get this: java.sql.SQLException: ORA-00900: invali…
Product() aggregate function
While explaining some concepts over CTE, was asked with a cute question.. can we find the multiplication of rows , while we always concentrate on the SUM() from a fresher. That gave me a thought! Is it ever possible with just an SQL. I was also considering the maximum number precision we can even support, sin…
How to know if a user has a privilege on Object?
I would like to know if a user has a privilege on an object or not. I’m working on SQL Developer. When I query manually the table DBA_TAB_PRIVS, I get all the information needed. However, I need this information to be used in some triggers and functions. So, I’m writing PL/SQL function that will r…
Use string contains function in oracle SQL query
I’m using an Oracle database and I want to know how can I find rows in a varchar type column where the values of that column has a string which contains some character. I’m trying something like this (that’s a simple example of what I want), but it doesn’t work: I also want to know if …
Counting number of joined rows in left join
I’m trying to write an aggregate query in SQL which returns the count of all records joined to a given record in a table; If no records were joined to the given record, then the result for that record should be 0: Data My database looks like this (I’m not able to change the structure, unfortunatel…
Nth max salary in Oracle
To find out the Nth max sal in oracle i’m using below query But According to me by using the above query it will take more time to execute if table size is big. i’m trying to use the below query but not getting output.. any suggetions please .. Please share any link on how to optimise queries and …
Explanation of the query for getting the 3 maximum salaries
Can any one explain the below query for getting the 3 maximum salaries? Someone suuggested to me the use of the above query to get 3 max. salaries in a table. I didn’t understand what is happening in the below part of the query: Can anyone explain it? if there is any other way to get the same result,ple…