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
I've been assigned a task at work that consists of creating a trigger on a table. This table is used to store temporary query results. I'm trying to make a trigger to clean the table so old results don't accumulate.
There is a column named DATE_UPDATED that stores an Oracle Date for when the row was inserted/updated. If the row is not updated in 12 hours, I want to delete it.
Here is what I have so far.
CREATE OR REPLACE TRIGGER clean_tableName AFTER INSERT OR UPDATE ON tableName
BEGIN
END clean_tableName;
As you can see, I don't have an actual function body yet. What would be the best way to accomplish this? Should I declare a variable to store the current Date+12 hours and then just compare that in a delete statement?
I'm thinking something like:
DECLARE laterDate DATE := SYSDATE+12; // Not sure how to add 12 hours to a date. BEGIN DELETE FROM tableName WHERE DATE_UPDATED > laterDate; END clean_tableName;
But I'm not sure how to add 12 hours to the current date. Is there a way I can do this without defining a variable?
I am comparing the dates in forms 6i.I need to compare the start and end dates and if the date are getting than an year then i need to get an error message.
when i open my oracle form on my live application server to display sysdate its does not show me.but when i open this same form on my test application server its shows my current sysdate time.
oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production PL/SQL Release 11.1.0.6.0 - Production "CORE 11.1.0.6.0 Production"
I have a .xls file, which has few columns with Korean data. I have problem converting it into .csv. I converted it to UNICODE TEXT, which was TAB separated. I replaced tabs with commas. but still saved it as .txt file. Now I am using utl_file package to open and read.
I need to prepare a bilingual system (English and Arabic).
So i need to store data in English as well as in Arabic
Is there any possibility to convert the data from English to Arabic and Arabic to English through oracle forms
basically what i am thinking is let us suppose user selected the English version of the application and he entered all the data in English during saving the records i need to convert all the English data into Arabic and store it in another table for Arabic data reference.
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 have an requirement like below and would like to have SQL for that.
Source Table:
EMP_NO EMP_CODE 1 'A' 1 'D' 1 'E' 1 'F'
2 'S' 2 'A' 2 'W' 2 'Q'
3 'A' 3 'T' 3 'D' 3 'E'
4 'D' 4 'A'
I want to load only data which has EMP_CODE as A and doesn't have subsequent 'E' or 'F' in it. In the above source you can see EMP_NO 2 and 4 satisfy the condition and rest wont. So i want the output data like below.
I want to check whether language is already there in database or not.
i have written the below query
select * from emp where language_known =nesttype('english','hindi');
i am getting the below error
SQL Error: ORA-22901: cannot compare nested table or VARRAY or LOB attributes of an object type 22901. 00000 - "cannot compare nested table or VARRAY or LOB attributes of an object type" *Cause: Comparison of nested table or VARRAY or LOB attributes of an object type was attempted in the absence of a MAP or ORDER method. *Action: define a MAP or ORDER method for the object type.
I have a table with two columns called startsem and gradsem they are both CHAR(3). Within those colums are rows that are listed as SemesterYear. For example, F09 is Fall 2009, S09 Spring 2009, and M09 is Summer 2009. I would like to create a constraint that says GradSem must be greater than StartSem b/c no one can travel back in time to graduate. However, as you know you can compare S09 > F09 because it will treat it as a string. I thought I could use a substring and compare the last two digits as a year and that would work but how do I compare the semesters as a time frame? Because in my schema F > S because Spring 2011 comes before Fall 2011 but in reality F < S because to Oracle it is a string and the ASCII value of F is less than S. I cannot chage the coding of the database so editing the rows so they are more date friendly is not an option.
So how can I modify this database to acruately compare StartSem and GradSem.
I have an PL/SQL query which gives the data between the date interval submitted by the user.The problem is that i want all the date irrespective of it has data or not for eg: Let say date parameter is from 1-Jan-2012 to 5-Jan-2012
Now, in the database the available dates are:
1-Jan-2012 2-Jan-2012 5-Jan-2012
So as you can see here that dates 3-Jan-2012 and 4-Jan-2012 is not resulted out by the query. I want all the dates.
I have an athletics participation table that only has the relevant emplid and effective date field. There is no term field on the table. I'm trying to only select those emplid's where the max( effdt) for the emplid is between the begin and end date of the current term. I only want to select current athletes. I would much rather it be for the current academic year but it seems impossible. Why can't I use max(effdt) here?
sql Code
AND h.emplid IN(SELECT b.emplid FROM PS_ATHL_PART_STAT b where max(b.effdt) between (SELECT term_begin_date AND term_end_date from PS_TERM_TBL
