DATE and TIMESTAMP have the same size (7 bytes). Those bytes are used to store century, decade, year, month, day, hour, minute and seconds. But TIMESTAMP allows to store additional info such as fractional seconds (11 bytes) and fractional seconds with timezone (13 bytes).

TIMESTAMP was added as an ANSI compliant to Oracle. Before that, it had DATE only.

In general cases you should use DATE. But if precision in time is a requirement, use TIMESTAMP.


And about Java, the oracle.sql.DATE class from Oracle JDBC driver, provides conversions between the Oracle Date/Timestamp data type and Java classes java.sql.Date, java.sql.Time and java.sql.Timestamp.

Illustration of datetime data types in Oracle:
[![enter image description here][1]][1]


and supporting functions:
[![enter image description here][2]][2]


  [1]: https://i.stack.imgur.com/yOVlg.png
  [2]: https://i.stack.imgur.com/0XHtB.png

I&#39;ve installed node.js for Macintosh using the lastest installer for Macintosh.  I now want to check that /usr/local/bin is in my $PATH, as the installer instructed. I&#39;ve opened up the Terminal application and researched help on the net, but I&#39;m lost, as to how to accomplish the above, then start the node.js process. Can anyone clear this up?   Thank you for very much.

Making sure /usr/local/bin is in my $PATH on mac

I created a dump with mongodump on computer A (ubuntu 12.04 server). I moved it to computer B (ubuntu 12.04 server) and typed:

&gt; mongorestore -db db_name --drop db_dump_path

It failed and it reported:

&gt; connected to: 127.0.0.1&lt;br&gt;
&gt; terminate called after throwing an instance of &#39;std::runtime_error&#39;&lt;br&gt;
&gt; what():  locale::facet::_S_create_c_locale name not valid&lt;br&gt;
&gt; Aborted

I&#39;ve successfully accomplished this operation before and this strange behavior has never occurred. What do I need to do to fix this?

MongoDB mongorestore failure: locale::facet::_S_create_c_locale name not valid

What are the difference between Oracle DATE and TIMESTAMP type?
Both have date and time component?
Also what is corresponding type in Java for these date types?

Difference between oracle DATE and TIMESTAMP

<p>What are the difference between Oracle DATE and TIMESTAMP type?
Both have date and time component?
Also what is corresponding type in Java for these date types?</p>


I am hoping you can help with this question.  I am using Oracle SQL (SQL Developer for this view)...  

If I have a table with the following columns:

 - ColumnA (Number)
 - ColumnB (Number)
 - ColumnC (Number)

In my view I have 

    Select  
    ColumnA,
    ColumnB,
    ColumnA + ColumnB As calccolumn1

Now at this point, I want to use calccolumn1
but I cannot just say...

    Select  
    ColumnA,
    ColumnB,
    ColumnA + ColumnB As calccolumn1
    calccolumn1 / ColumnC as calccolumn2

I am assuming I need some type of subquery..but this is where I need your help...
How would I word the query so that I can use calccolumn1 in another calculation within the same query?  It might be an If then or a Case when, but bottomline it is some derived number.

How to use a calculated column to calculate another column in the same view

I had a very simple question: Does oracle allow multiple &quot;WITH AS&quot; in a single sql statement.

Example:

    WITH abc AS( select ......)
    
    WITH XYZ AS(select ....) /*This one uses &quot;abc&quot; multiple times*/
    
    Select ....   /*using XYZ multiple times*/


I can make the query work by repeating the same query multiple times, but do not want to do that, and leverage &quot;WITH AS&quot;.
It seems like a simple requirement but oracle does not allow me:

&gt; ORA-00928: missing SELECT keyword

Can we have multiple &quot;WITH AS&quot; in single sql - Oracle SQL

I&#39;m running Oracle 11GR2 on an Amazon RDS instance. occasionally I get an `IO Error: Got minus one from a read call` when making a call to `DriverManager.getConnection(getUrl())` and I&#39;m not sure why. Other applications work correctly. 

To further confuse things, the error will correct itself on occasion (following next iteration of the program). 

**How should I approach a &quot;Got minus one from a read call&quot; error?**

Full stack trace: 

    java.sql.SQLRecoverableException: IO Error: Got minus one from a read call
    	at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:489)
    	at oracle.jdbc.driver.PhysicalConnection.&lt;init&gt;(PhysicalConnection.java:553)
    	at oracle.jdbc.driver.T4CConnection.&lt;init&gt;(T4CConnection.java:254)
    	at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
    	at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:528)
    	at java.sql.DriverManager.getConnection(DriverManager.java:579)
    	at java.sql.DriverManager.getConnection(DriverManager.java:243)
    	at com.cwd.facile.db.Database.&lt;init&gt;(Database.java:44)
    	at com.cwd.facile.ns.NetSuiteRequestBased.&lt;init&gt;(NetSuiteRequestBased.java:29)
    	at com.cwd.facile.ns.CommonOperations.isInventoryItem(CommonOperations.java:205)
    	at com.cwd.facile.ns.CommonOperations.findItemIdByName(CommonOperations.java:188)
    	at com.cwd.facile.ns.CommonOperations.createSalesOrder(CommonOperations.java:970)
    	at com.cwd.facile.Main.main(Main.java:47)
    Caused by: oracle.net.ns.NetException: Got minus one from a read call
    	at oracle.net.ns.Packet.receive(Packet.java:311)
    	at oracle.net.ns.NSProtocol.connect(NSProtocol.java:300)
    	at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1140)
    	at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:340)
    	... 12 more

Database.java line 44: `setConn(DriverManager.getConnection(getUrl()));`

Other info: 

- I thought it was a bad JDBC url, but it does work, sometimes for days on end before failing. 
- Amazon RDS is a managed instance and configuration changes may not be possible
- I am using ojdbc6.jar for connectivity  

How to approach a &quot;Got minus one from a read call&quot; error when connecting to an Amazon RDS Oracle instance

I am trying to understand GROUP BY *(new to oracle dbms)* without aggregate function.  
How does it operate?  
Here is what i have tried.  

EMP table on which i will run my SQL.  
![EMP TABLE][1]  
  
  
    SELECT ename , sal
    FROM emp
    GROUP BY ename , sal
  
![Result][2]
  
    SELECT ename , sal  
    FROM emp  
    GROUP BY ename;  
  
&gt; ## Result ##
&gt; ORA-00979: not a GROUP BY expression  
&gt; 00979. 00000 -  &quot;not a GROUP BY expression&quot;  
&gt; *Cause:  
&gt; *Action:  
&gt; Error at Line: 397 Column: 16  


    SELECT ename , sal  
    FROM emp  
    GROUP BY sal;  
  
&gt; ## Result ##
&gt; ORA-00979: not a GROUP BY expression  
&gt; 00979. 00000 -  &quot;not a GROUP BY expression&quot;  
&gt; *Cause:  
&gt; *Action: Error at Line: 411 Column: 8  
  
  
    SELECT empno , ename , sal  
    FROM emp  
    GROUP BY sal , ename;  
  
&gt; ## Result ##
&gt; ORA-00979: not a GROUP BY expression  
&gt; 00979. 00000 -  &quot;not a GROUP BY expression&quot;  
&gt; *Cause:  
&gt; *Action: Error at Line: 425 Column: 8  
  
  
    SELECT empno , ename , sal  
    FROM emp  
    GROUP BY empno , ename , sal;  
  
  
![Result][3]  
  
  
So, basically the number of columns have to be equal to the number of columns in the GROUP BY clause, but i still do not understand why or what is going on.


  [1]: http://i.stack.imgur.com/37oYU.jpg  
  [2]: http://i.stack.imgur.com/3n3JM.jpg  
  [3]: http://i.stack.imgur.com/pP6BB.jpg  

GROUP BY without aggregate function

I&#39;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&#39;m not able to change the structure, unfortunately):

&lt;!-- language: lang-none --&gt;

    MESSAGE
    ----------------------------------------------
    MESSAGEID   SENDER        SUBJECT
    ----------------------------------------------
    1           Tim           Rabbit of Caerbannog
    2           Bridgekeeper  Bridge of Death

    MESSAGEPART
    ----------------------------------------------
    MESSAGEID   PARTNO        CONTENT
    ----------------------------------------------
    1           0             (BLOB)
    1           1             (BLOB)
    3           0             (BLOB)

(`MESSAGEPART` has a composite `PRIMARY KEY(&quot;MESSAGEID&quot;, &quot;PARTNO&quot;)`)

Desired output
----------------

Given the data above I should get something like this:

&lt;!-- language: lang-none --&gt;

    MESSAGEID   COUNT(*)
    -----------------------------------------------
    1           2
    2           0

It seems obvious that I need to do a left join on the `MESSAGE` table, but how do I return a count of `0` for rows where the joined columns from `MESSAGEPART` are `NULL`? I&#39;ve tried the following:

Logic
-------------------
I&#39;ve tried

    SELECT m.MESSAGEID, COUNT(*) FROM MESSAGE m
    LEFT JOIN MESSAGEPART mp ON mp.MESSAGEID = m.MESSAGEID
    GROUP BY m.MESSAGEID;

However, this returns

&lt;!-- language: lang-none --&gt;

    MESSAGEID   COUNT(*)
    -----------------------------------------------
    1           2
    2           1

I&#39;ve also tried

    SELECT mp.MESSAGEID, COUNT(*) FROM MESSAGE m
    LEFT JOIN MESSAGEPART mp ON mp.MESSAGEID = m.MESSAGEID
    GROUP BY mp.MESSAGEID;

but this returns

&lt;!-- language: lang-none --&gt;

    MESSAGEID   COUNT(*)
    -----------------------------------------------
    1           2
                1

What am I doing wrong here?

Counting number of joined rows in left join

I am trying to insert a photo into a BLOB column of a MySQL table, and I get an exception:

    Data too long for column &#39;logo&#39; at row 1. 

Here is the JDBC:

        int idRestaurant = 42;
        String restoname=  &quot;test&quot;;
        String restostatus=  &quot;test&quot;;
        InputStream fileContent = getUploadedFile();
        int fileSize = getUploadedFileSize();
                    
        Class.forName(&quot;com.mysql.jdbc.Driver&quot;);
        try (Connection conn = DriverManager.getConnection(&quot;jdbc:mysql://localhost:3306/resto&quot; , &quot;root&quot; , &quot;&quot; )) {
            PreparedStatement ps = conn.prepareStatement(&quot;insert into restaurants (idRestaurant, restaurantName, status, logo) values(?,?,?,?)&quot;);
            ps.setInt(1, idRestaurant);
            ps.setString(2, restoname);
            ps.setString(3, restostatus);
            ps.setBinaryStream(4, fileContent, fileSize);
            ps.executeUpdate();
            conn.commit();
        }

How do I solve this problem?


Data truncation: Data too long for column &#39;logo&#39; at row 1

I have this Java program: `MySQLConnectExample.java`

    import java.sql.*;
    import java.util.Properties;
     
    public class MySQLConnectExample {
        public static void main(String[] args) {
            Connection conn1 = null;
            Connection conn2 = null;
            Connection conn3 = null;
     
            try {
                String url1 = &quot;jdbc:mysql://localhost:3306/aavikme&quot;;
                String user = &quot;root&quot;;
                String password = &quot;aa&quot;;
     
                conn1 = DriverManager.getConnection(url1, user, password);
                if (conn1 != null)
                    System.out.println(&quot;Connected to the database test1&quot;);
                
                String url2 = &quot;jdbc:mysql://localhost:3306/aavikme?user=root&amp;password=aa&quot;;
                conn2 = DriverManager.getConnection(url2);
                if (conn2 != null) {
                    System.out.println(&quot;Connected to the database test2&quot;);
                }

                String url3 = &quot;jdbc:mysql://localhost:3306/aavikme&quot;;
                Properties info = new Properties();
                info.put(&quot;user&quot;, &quot;root&quot;);
                info.put(&quot;password&quot;, &quot;aa&quot;);
     
                conn3 = DriverManager.getConnection(url3, info);
                if (conn3 != null) {
                    System.out.println(&quot;Connected to the database test3&quot;);
                }
            } catch (SQLException ex) {
                System.out.println(&quot;An error occurred. Maybe user/password is invalid&quot;);
                ex.printStackTrace();
            }
        }
    }

**I compile it like this:**

    E:\java mysql code driver&gt;javac MySQLConnectExample.java

    E:\java mysql code driver&gt;java -cp mysql-connector-java-3.0.11-stable-bin.jar;.
    MySQLConnectExample

**I get this error:**

    An error occurred. Maybe user/password is invalid
    java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost:3306/
    aavikme
            at java.sql.DriverManager.getConnection(DriverManager.java:596)
            at java.sql.DriverManager.getConnection(DriverManager.java:215)
            at MySQLConnectExample.main(MySQLConnectExample.java:20)
    
What am I doing wrong?

java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost:3306/dbname

I am retrieving columns names from a SQL database through Java. I know I can retrieve columns names from `ResultSet` too. So I have this sql query 

     select column_name from information_schema.columns where table_name=&#39;suppliers&#39;

The problem is I don&#39;t know how can I get columns names from `ResultSet` and my code is

    public void getAllColumnNames() throws Exception{
    
    String sql = &quot;SELECT column_name from information_schema.columns where table_name=&#39;suppliers&#39;&quot;;
    
    PreparedStatement ps = connection.prepareStatement(sql);
    ResultSet rs = ps.executeQuery(sql);
     // extract values from rs
    
    
    }

Print the data in ResultSet along with column names

I installed and tried to use jasper report studio. The first brick wall you hit when you try to create a datasource for your reports is 

    java.lang.ClassNotFoundException: com.mysql.jdbc.Driver

The forums say I need to install a jar on the classpath. I have no idea how to do this, so the first hurdle is how to get the jar. The only place I can find is this:  

http://dev.mysql.com/downloads/connector/j/ 

but this unfortunately gives you an msi installer, not a jar. I don&#39;t want to install stuff, just get the jar.

I have mysql DB installed, had have trawled through the install dir in program files, but can&#39;t find the jar.

Anyone know the official (not malware site) way to get hold of the mysql jar?  It seems bizarre that its so hard to find.

I have windows 8 64 bit and mysql 5.6.


Where can I download mysql jdbc jar from?

Spring-Boot is a pretty awesome tool, but the documentation is a bit sparse when it comes to more advanced configuration. How can I set properties like the maximum size for my database connection pool?

Spring-Boot supports `tomcat-jdbc`, `HikariCP` and `Commons DBCP` natively are they all configured the same way?

Spring-Boot: How do I set JDBC pool properties like maximum number of connections?

How to find out the users list, which is all created in the `oracle 11g` database. Is there any `command` to find out the users list which we can execute from the Command line interface! 

How to find the users list in oracle 11g db?

can anybody explain what is the main difference between functions and procedures in Oracle? Why must I use procedures if I can do everything with functions? 

 
 1. If I cannot call procedure in sql statement, ok, I&#39;ll write a function to do the same work.
 2. Procedures don&#39;t return values, ok, I&#39;ll return only sql%rowcount or 1(success), 0(exception) after any dml operation
 3. Both procedures and functions can pass variables to calling environment via OUT/IN OUT parameters

I heard that the main difference is in performance, &#39;procedures are faster than functions&#39;. But without any detail. 

Thanks in advance.

Functions vs procedures in Oracle

I am getting the error :

     ORA-28000: the account is locked 

**Is this a DB Issue?** When I unlock user account using the command `ALTER USER username ACCOUNT UNLOCK` temporarily it will be OK. Then after some time the same account lock happens again.

The database using is oracle `XE`

**Does anybody else have the same issue?** 





ORA-28000: the account is locked error getting frequently

    SELECT ORDER_NUM, CUSTOMER_NUM, CUSTOMER_NAME, ADD_DAYS (ORDER_DATE, 20)
    FROM CUSTOMER, ORDERS; 

Oracle Express says ADD_DAYS invalid? Any ideas what Am I doing wrong?



Content Type	Original Author	Original Content on Stackoverflow
Question	supernova	View Question on Stackoverflow
Solution 1 - Oracle	Guillermo Luque	View Answer on Stackoverflow
Solution 2 - Oracle	Marcin Badtke	View Answer on Stackoverflow

Difference between oracle DATE and TIMESTAMP

Oracle Problem Overview

Oracle Solutions

Solution 1 - Oracle

Solution 2 - Oracle

MongoDB mongorestore failure: locale::facet::_S_create_c_locale name not valid

Making sure /usr/local/bin is in my $PATH on mac

Attributions