SQL*Plus: Release 10.2.0.4.0 - Production on Thu Aug 30 11:45:59 2012
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Enter user-name: sys as sysdba
Enter password:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select value from v$nls_parameters where parameter= 'NLS_DATE_FORMAT';
VALUE
----------------------------------------------------------------
DD-MON-RR
We are trying to execute a statement SELECT CURRENT_DATE FROM DUAL on Timesten 11.2.2 . It throws error unknown referenced column error. Command> select current_date from dual; 2211:
Referenced column CURRENT_DATE not foundThe command failed. But the following doc shows the support.
TimesTen PL/SQL Support: Reference Summary CURRENT_DATE function
Returns the current date in the session time zone. YIn TimesTen this returns the current date in UTC (universal time). TimesTen does not support local time zones.
But while inserting the output of the above query in my table it throws error like this... ORA-01830: date format picture ends before converting entire input string Is there any possibility to achieve this.
I'm trying to select some data from table base on SYSDATE. The below query does not return any data.
My query is:
select count(TICKET_ID) "ECEMEA" from QTMT_TICKETS where STATUS_ID=1 and TEAM_ID=3 and RECEIVED_DATE=sysdate
why this does not work? Date format in my application is: DD-MMM-YY (16-AUG-12). In the database the dates are stored in this format: MM/DD/YYYY (08/25/2012). Does it mean, that I have to play with the format?
i'v a problem regarding dual table,i logged to oracle as sys as sysdba and i add a column to dual table and then want to delete that added column but oracle gives "ORA-12988: cannot drop column from table owned by SYS"
i'm not able to drop any user oracle gives "ORA-00604: error occurred at recursive SQL level 1" "ORA-01031: insufficient privileges"
another error:: SP2-0575: Use of Oracle SQL feature not in SQL92 Entry Level oracle gives error when i want to switch user all problems starts when i add that column to dual table
What I'm trying to do is make it so that it returns 'MATCH' when I pass a date that matches a date every two weeks starting Jan 01. Like Jan 01, Jan 15, Jan 29, Feb 12, etc. would return as MATCH Jan 02, Jan 03, etc. would return as NO_MATCH
The part in bold is what I'm having trouble figuring out.
select nvl( (select 'MATCH' from dual where 'date' = '2 week intervals starting Jan 01' ), 'NO_MATCH') from dual
I would like to SELECT these 3 hardcoded titles from DUAL, and have a blank line under each, on the output in this order from the SQL. But the result does not end up that way
SQL> set heading off; 1 select '#ENCODING WINDOWS-1252' from dual 2 union 3 select ' ' from dual 4 union 5 select 'Language Section EN-US' from dual 6 union 7 select ' ' from dual 8 union 9* select 'Catalog Section Title Date Source' from dual SQL> /
#ENCODING WINDOWS-1252 Catalog Section Title Date Source Language Section EN-US
- - - - - - - - - - - - - - - - -
Desired Output: #ENCODING WINDOWS-1252 Language Section EN-US Catalog Section Title Date Source
I have ORACLE XE 11g installed on two machines and have been trying my luck to get my local db to query/insert/update a table from the remote db.
I have created the public database link and querying the dba_db_links shows that the links is successfully created (see the bottom of the post). I have also created synonyms for my remote table and queries like select name from jforum_forums succeed, with jforum_forums being a public synonym referring to user.jforum_forums@corona.magic.ubc.ca.
However when I try to insert a row into the remote table, there is a query to the remote DUAL to fetch the last generated ID for a given table similar to the following: SELECT jforum_forums_seq.currval FROM DUAL. Statements of such fail and they either throw an ORA-02019: connection description for remote database not found or ORA-01729: database link name expected. I tried including the database link with the name of the table making it like SELECT jforum_forums_seq.currval FROM DUAL@corona.magic.ubc.ca but that didn't work either.
PS, I should mention that global_names are set to true in both databases and that my database link has the same name as the global_name of the remote database.
OWNER ------------------------------ DB_LINK -------------------------------------------------------------------------------- USERNAME ------------------------------ HOST -------------------------------------------------------------------------------- CREATED
DECLARE v_seq_num NUMBER; BEGIN SELECT SEQ_ID.NEXTVAL INTO v_seq_num FROM DUAL; INSERT INTO TABLEA (COL1, COL2) VALUES (v_seq_num, 'test'); INSERT INTO TABLEB (COL3) VALUES (v_seq_num); END;
-- Option2 - Using sequence.NEXTVAL in INSERT USING RETURNING INTO clause
DECLARE v_seq_num NUMBER; BEGIN INSERT INTO TABLEA (COL1, COL2) VALUES (SEQ_ID.NEXTVAL, 'test') RETURNING COL1 INTO v_seq_num; INSERT INTO TABLEB (COL3) VALUES (v_seq_num); END;
I am not strong in SQL but can write easy SQL for data extraction but seem to not understand how to correctly use sysdate in a where clause.
Case: I have to create an alert that will email my IT dept once a person is terminated in Oracle HR. The alert will only run once a day with all terminations specified in the alert. Setting up the alert is not the issue, but rather the SQL code I want to use.
The alert will run everyday at CoB 17:30. Now, in my query, how do i specify that the results should be only for the current day? The problem however, is that I also retrieve Person Type which should show as Ex-Employee, but this is only shown the day after the actual termination has been done, because the employee is still active on the date of termination.
We have forms 11gr2 on win xp 32bit , weblogic linux server 64bit. We are converting 6i to 11g. Our system has many forms that span on 2 monitors. 1280x768 monitors, so some forms are 2560x768.
When alerts/messages pop up on the screen , they are positioned in the middle of both screens (guessing Oracle by default calculates width =2560/2, height =768/2).
Possible options:
1- is there a way to tell oracle to open alerts/messages a X Y coordinates? Maybe a config file on the server? Already file a Sr with Oracle tech support. 2- tried creating a form to do our messages. but this is flawed since when you call the new form to display the message, then the focus on current form is lost. when the message form is closed (then trigger when_window_activate fires)...this would be hell to try and control all our messages (we may have 20-30 on each big form. We have a total of 118 forms. Lots of messages with Y/N/Cancel options. 3- Maybe create a place holder for messages on all forms (bottom of screen)...thats the best idea we have so far. 4- Is there a way to do something with a java beans? We already have some knowledge with beans since we use image beans and pdf beans.
I have an employee table. I Have to get the data of all employees in such a way that. If today I run the Query,then i have to get the data of all employees working between december 1st of previous year(current year-1 i.e., december 1st 2010.) till today(april 21st). If the query run date is in the month of december(example december 15th) then the query should get the data from december 1st of current year(december 1st 2011) to December 15th. I wrote the if statement some how its not working. I want to make use of this If or Case Statement as the start date of the employee_timestamp. Is this possible here or not.
select * from employee where employee.employee_timestamp > (select to_date(to_char(concat('12-01-', extract(YEAR FROM sysdate)-1)),'MM/DD/YYYY') as Startdate From DUAL) and employee.employee_timestamp < (SELECT SYSDATE FROM DUAL).
What is the difference between the values of sysdate and current_date ? On querying the database I got the results as sysdate = the date and time for the database server location and current_date = my local system date
If however, i change my local system date - it still shows the correct date.Myunderstanding was that current_date uses the dbtimezone.But..
I have a table called transaction_dw and I need to select all records that have an account balance that has been below 0 in the past 6 months. initial query I tried was:
select account_balance, timestamp from transaction_dw where account_balance < 0 and timestamp between sysdate and sysdate - 6;
but this is only taking 6 days off the sysdate rather than months, how I can get it to take off 6 months?
I'm working on a project and I can't figure out the procedure I will need to use. I've got a sysdate field in my "Calls" table which generates a call date + time, however I need to insert a severity level of the call after a certain amount of time,
e.g. after 1 hour level turns from level 4 to level 3 and so forth until reaching level 1 after x amount of time. I know I'd need to put in an else if statement within this procedure I just can't work out how to do it with the sysdate field that has also been created.
A bit of info about the table, table name = calls, column 1 = date_time and column 2 is called severity_level
I have one issue My server is in france and it is in french timezone but when I query for sysdate it returns US time.
In '/etc/sysconfig/clock/' Zone= europe/paris UTC= true
echo $TZ variable is returning nothing.
sysdate = us time systimestamp= us time current_timestamp = french time current_date = french time dbtimezone= europe/warsove, sessiontimezone=+2.00( which is also europe timezone offset) tz_offset(dbtimezone)=+2.00, tz_offset(sessiontimezone)= +2.00 i.e europe os timezone= europe/paris.
This command "./emctl config agent getTZ" is also returning timezone as europe/paris
Also in "emd.properties" file "agentTZRegion" parameter is set to europe/paris
Oracle version= 11.2.0
Now I don't understand why this sysdate and systimestamp is returning "US time zone" while everything else is returning french time zone.
i wanted to compare a date in one of my tables to sysdate. I have a table reservation and a field in it is Date Reserved From, i wanted to compare this to sysdate and returned the results
