oracle11g - w3toppers.com

Add days Oracle SQL

If you want to add N days to your days. You can use the plus operator as follows – SELECT ( SYSDATE + N ) FROM DUAL;

I’m going to assume that the PRIORITY column is always 1 when there’s a “main product” and never 1 any other time. From your data it also looks like each customer has only one “main” product. I’m going to assume that this is true. If it’s not then you should have another column to distinguish … Read more

How to use parameters in a ‘where value in…’ clause?

Using dynamic SQL is the simplest approach from a coding standpoint. The problem with dynamic SQL, though, is that you have to hard parse every distinct version of the query which not only has the potential of taxing your CPU but has the potential to flood your shared pool with lots of non-sharable SQL statements, … Read more

The network adapter could not establish the connection – Oracle 11g

I had the similar issue. its resolved for me with a simple command. lsnrctl start The Network Adapter exception is caused because: The database host name or port number is wrong (OR) The database TNSListener has not been started. The TNSListener may be started with the lsnrctl utility. Try to start the listener using the … Read more

passing in table name as plsql parameter

You can use dynamic SQL: create or replace function get_table_count (table_name IN varchar2) return number is table_count number; begin execute immediate ‘select count(*) from ‘ || table_name into table_count; dbms_output.put_line(table_count); return table_count; end; There is also an indirect way to get number of rows (using system views): create or replace function get_table_count (table_name IN varchar2) … Read more

Why does the wm_concat not work here?

You must avoid wm_concat function because it is undocumented and discovered as workaround at Oracle 8i times. Since times of old method with custom aggregate function as discovered by Tom Kyte here there are some new workarounds, showed at examples below. All of them reproduced in this SQL Fiddle. Workaround 1 – LISTAGG function, works … Read more

How to tweak LISTAGG to support more than 4000 character in select query?

You should add GetClobVal and also need to rtrim as it will return delimiter in the end of the results. SELECT RTRIM(XMLAGG(XMLELEMENT(E,colname,’,’).EXTRACT(‘//text()’) ORDER BY colname).GetClobVal(),’,’) from tablename;

HikariCP: What database level timeouts should be considered to set maxLifetime for Oracle 11g

Short answer: none (by default). For the record (to include details here in case the link changes), we’re talking about property maxLifetime of HikariCP: This property controls the maximum lifetime of a connection in the pool. An in-use connection will never be retired, only when it is closed will it then be removed. We strongly … Read more

Get the number of days between two dates in Oracle, inclusive of the dates

In Oracle substracting two dates returns the number of days between two dates. A minus operator works in the same way as for numbers: 20 – 20 = 0 ===> 2013-05-20 – 2013-05-20 = 0 25 – 20 = 5 ===> 2013-05-25 – 2013-05-20 = 5 If you want to include last number or last … Read more

How to use a dynamic parameter in a IN clause of a JPA named query?

JPA support the use of a collection as a list literal parameter only in JPQL queries, not in native queries. Some JPA providers support it as a proprietary feature, but it’s not part of the JPA specification (see https://stackoverflow.com/a/3145275/1285097). Named parameters in native queries also aren’t part of the JPA specification. Their behavior depends on … Read more