To convert from milliseconds from epoch (assume epoch is Jan 1st 1970): select to_date(‘19700101’, ‘YYYYMMDD’) + ( 1 / 24 / 60 / 60 / 1000) * 1322629200000 from dual; 11/30/2011 5:00:00 AM To convert that date back to milliseconds: select (to_date(’11/30/2011 05:00:00′, ‘MM/DD/YYYY HH24:MI:SS’) – to_date(‘19700101’, ‘YYYYMMDD’)) * 24 * 60 * 60 * … Read more
You need to turn on dbms_output. In Oracle SQL Developer: Show the DBMS Output window (View->DBMS Output). Press the “+” button at the top of the Dbms Output window and then select an open database connection in the dialog that opens. In SQL*Plus: SET SERVEROUTPUT ON
According to Tom Kyte: “Unless and until you add “order by” to a query, you cannot say ANYTHING about the order of the rows returned. Well, short of ‘you cannot rely on the order of the rows being returned’.” See this question at asktom.com. As for ROWNUM, it doesn’t physically exist, so it can’t be … Read more
The referenced primary key is described in the columns r_owner and r_constraint_name of the table ALL_CONSTRAINTS. This will give you the info you want: SELECT a.table_name, a.column_name, a.constraint_name, c.owner, — referenced pk c.r_owner, c_pk.table_name r_table_name, c_pk.constraint_name r_pk FROM all_cons_columns a JOIN all_constraints c ON a.owner = c.owner AND a.constraint_name = c.constraint_name JOIN all_constraints c_pk ON … Read more
No – you either get all fields (*) OR specify the fields you want.
According Oracle these locations are searched for tnsnames.ora, resp. sqlnet.ora and ldap.ora: Oracle Net files in present working directory (PWD/CWD) TNS_ADMIN defined sessionally or by user-defined script TNS_ADMIN defined as a global environment variable TNS_ADMIN defined in the registry Oracle Net files in %ORACLE_HOME/network|net80\admin (Oracle default location) However, I am not sure whether each application/driver … Read more
This can be done with a single statement and some XML magic: select table_name, to_number(extractvalue(xmltype(dbms_xmlgen.getxml(‘select count(*) c from ‘||owner||’.’||table_name)),’/ROWSET/ROW/C’)) as count from all_tables where owner=”FOOBAR”
This is usually caused by duplicates in the query specified in USING clause. This probably means that TABLE_A is a parent table and the same ROWID is returned several times. You could quickly solve the problem by using a DISTINCT in your query (in fact, if ‘Y’ is a constant value you don’t even need … Read more
You can change this in preferences: From Oracle SQL Developer’s menu go to: Tools > Preferences. From the Preferences dialog, select Database > NLS from the left panel. From the list of NLS parameters, enter DD-MON-RR HH24:MI:SS into the Date Format field. Save and close the dialog, done! Here is a screenshot:
Put the values in a temporary table and then do a select where id in (select id from temptable)