Or you could have done this: select trunc(sysdate) – to_date(‘2009-10-01’, ‘yyyy-mm-dd’) from dual This returns a NUMBER of whole days: SQL> create view v as 2 select trunc(sysdate) – to_date(‘2009-10-01’, ‘yyyy-mm-dd’) diff 3 from dual; View created. SQL> select * from v; DIFF ———- 29 SQL> desc v Name Null? Type ———————- ——– ———————— DIFF … Read more
Your query should be: insert into table1 (col1, col2) select t1.col1, t2.col2 from oldtable1 t1, oldtable2 t2 I.e. without the VALUES part.
One solution is to install both x86 (32-bit) and x64 Oracle Clients on your machine, then it does not matter on which architecture your application is running. Here an instruction to install x86 and x64 Oracle client on one machine: Assumptions: Oracle Home is called OraClient11g_home1, Client Version is 11gR2 Optionally remove any installed Oracle … Read more
CAST(timestamp_expression AS DATE) For example, The query is : SELECT CAST(SYSTIMESTAMP AS DATE) FROM dual;
I found my java.exe that worked for SQL Developer at C:\ORACLE11G\product\11.2.0\client_1\jdk\bin where C:\ORACLE11G is my Oracle Home. The client that I have installed is 11.2.0. Hope this helps.
SELECT created FROM dba_objects WHERE object_name = <<your table name>> AND owner = <<owner of the table>> AND object_type=”TABLE” will tell you when a table was created (if you don’t have access to DBA_OBJECTS, you could use ALL_OBJECTS instead assuming you have SELECT privileges on the table). The general answer to getting timestamps from a … Read more
You need only to write: GRANT DBA TO NewDBA; Because this already makes the user a DB Administrator
I don’t have an 11g instance available today but could you not use: SELECT group_id, LISTAGG(name, ‘,’) WITHIN GROUP (ORDER BY name) AS names FROM ( SELECT UNIQUE group_id, name FROM demotable ) GROUP BY group_id
If you set the server output in ON mode before the entire code, it works, otherwise put_line() will not work. Try it! The code is, set serveroutput on; CREATE OR REPLACE PROCEDURE PROC1(invoicenr IN NUMBER, amnt OUT NUMBER) AS BEGIN SELECT AMOUNT INTO amnt FROM INVOICE WHERE INVOICE_NR = invoicenr; END; And then call the … Read more
No, you can’t. There’s no reason why you would need to. This is a one-time operation and so takes only an additional second or two to actually type and execute. If you’re adding columns in your web application this is more indicative of a flaw in your data-model as you shouldn’t need to be doing … Read more