SQL & PL/SQL :: Function That Accepts 2 Dates - Not A Valid Month Error
Apr 15, 2011
I have function that accepts 2 dates namely sDate and eDate i.e start and end dates. See the declaration part of the function:
startDate eab.eod_date%type;
endDate eab.eod_date%type;
begin
startDate := to_date(sDate,'DD-MON-YY');
endDate := to_date(eDate,'DD-MON-YY');
end
When I called the function it gives me the following error: ORA-01843: not a valid month..And I tried to simulate the process, the below code also show the same error:
select TO_DATE(to_date('13-DEC-09', 'dd-MON-yy', 'nls_date_language=english'),'DD-moN-YY') from dual;
how I can check that error. I need to pass dates to my function.
View 8 Replies
ADVERTISEMENT
Aug 16, 2010
why this query is not working
SQL> select xid,start_time from v$transaction;
XID START_TIME
---------------- --------------------
070006009A010000 08/16/10 13:24:43
SQL> select xid,start_time from v$transaction where start_time>to_timestamp('08/16/2010 12:15:00','mm/dd/yyyy hh24:mi:ss');
select xid,start_time from v$transaction where start_time>to_timestamp('08/16/2010 12:15:00','mm/dd/yyyy hh24:mi:ss')
*
ERROR at line 1: ORA-01843: not a valid month
View 17 Replies
View Related
May 17, 2012
I am trying to load below data using sql loader.
05/17/12,07:45:39,resn-j35-ctc113,"USR:ESM.CQueryTypeDlg::RunQuery().wft",0.125,P,schapptbook,22712,25704,705355992045,RBA4010200,10.146.218.154,0, "",0,"","",""
table structure
CREATE TABLE TEMP_CERNER_RESP_TIME_LND
(
INSTALLATION_ID VARCHAR2(50 BYTE) NULL,
TRANSACTION_ID VARCHAR2(50 BYTE) NULL,
SERVER_ID VARCHAR2(50 BYTE) NULL,
[code]...
Below function has been used to transfor data and callled in sql loader control file
CREATE OR REPLACE function return_domain( domain_name varchar2)
return varchar2
as
v_dmn varchar2(100)
[code]...
sql loader control file is as below:
load data
BADFILE '/backup/temp/rajesh/CERNER/BadFiles/FILENAME'
append into table TEMP_CERNER_RESP_TIME_LND
WHEN CLINICAL_TRANSACTION_ID = 'USR:ERM PMSEARCH ENCOUNTER RESULTS DISPLAY'
TRAILING NULLCOLS
[code]...
function takes the parameter as 'DOMAIN50_LPAR5002_slainterval051712_rj35cmi102_08_45_00.csv '
FILENAME in control file will be replace by DOMAIN50_LPAR5002_slainterval051712_rj35cmi102_08_45_00.csv when i run the the the loader i get the below error.
Record 1: Rejected - Error on table TEMP_CERNER_RESP_TIME_LND.
ORA-00604: error occurred at recursive SQL level 1
ORA-01843: not a valid month
View 4 Replies
View Related
Jul 25, 2011
I have a date field with varchar datatype. My data look like :
ID Amend_DATE
1 4/24/1974
1 4/18/2001
1 8/2/1971
2 8/4/1965
3 11/9/2001
4 8/15/2001
I need to find the minimum amend_date for each ID.When i calculate min(amend_date), it gives me error:
ORA-01843 : not a valid month
Query i am using is :
select min(to_date(amend_date ,'mm/dd/yyyy') ) from t group by id;
My data on application appears as 24 April 1974.
View 2 Replies
View Related
Sep 14, 2012
I have 2 cursors. Cursor 1 selects a number and 2 dates (values in the Table are in the format mm/dd/yyyy for both the columns).
I pass these values to Cursor 2.
v_promotion_id promotion.promotion_id%TYPE;
v_promotion_start_date promotion.start_date%TYPE;
v_promotion_end_date promotion.end_date%TYPE;
CURSOR c_promotion_list IS
(SELECT DISTINCT p.promotion_id,
p.start_date - 5,
p.end_date + 5
FROM promotion p,
[code]....
I am getting this error: ORA-01843: not a valid month
Initially I was passing just the variables without To_date. But I got the same error.
View 2 Replies
View Related
Nov 1, 2013
our web application has a field DATETO which has no validation (end date). So user can put garbage there. In this field should be SYSDATE + max. 2 years. But also SYSDATE + 1 month OK.
I need to make a script that sets for example "31.12.9999" to null. Because so long end date is non sense.
SQL> SELECT EXTRACT (YEAR FROM (TO_DATE (DATETO, 'DD.MM.YYYY')))
FROM XXX_USER_JC_ORDERID
WHERE DATETO IS NOT NULL;
ERROR:
ORA-01839: date not valid for month specified
no rows selected
Littlefoot writes about ORA-01839 "It usually happens when invalid dates are used, such as 30th of February"[URL]..
I don't see invalid date???
SQL> SELECT DATETO
FROM XXX_USER_JC_ORDERID
WHERE DATETO IS NOT NULL;
DATETO
--------------------------------------------------------------------------------
23.09.2013
[code]...
DATETO
--------------------------------------------------------------------------------
31.12.2014
31.12.2016
31.12.2013
31.08.2014
[code]...
View 7 Replies
View Related
Apr 29, 2013
I am using the cloud version of APEX for a college course. The database script I am trying to load has dates formatted in "DD-MON-RR" and I receive 'not a valid month' and 'a non-numeric character was found where a numeric was expected'. Is there a workaround in APEX for this? I'm trying to avoid changing thousands of lines of data.
View 4 Replies
View Related
Apr 28, 2010
I have a table with starting date and ending date.
I want to fetch monthwise days for given two dates
IE starting 25/12/2009 ending 25/03/2010
Result should like below.
Dec-09 7
Jan-10 31
Feb-10 28
Mar-10 25
I have tried this but it is not giving me the result which want..
select to_char(thedate,'mon-yy') mnth,count(to_char(thedate,'mon-yy')) days from
(SELECT TRUNC(to_date('25/12/2009','dd/mm/yyyy'),'Y')+ROWNUM-1 THEDATE
FROM ( SELECT 1 just_a_column
FROM dual
CONNECT BY LEVEL <= 366
[code].....
View 3 Replies
View Related
Aug 14, 2012
Is there anyway to show calendar month dates in horizontal line?
First line will have dates and then second line will have day i.e Sat, Sun, Mon.......
1 2 3 4 5 6 7 8 9 ........31
View 12 Replies
View Related
Sep 26, 2012
I need query to find the missing dates between two columns in given month.
CREATE TABLE emp_shift (
empno NUMBER(4),
fr_date DATE,
TO_DATE DATE,
shift VARCHAR2(1));
CREATE TABLE emp (
empno NUMBER(4)
);
[code].....
Required output is
MISSING_DATES EMPNO
---------------------- ----------
09-SEP-12 TO 11-SEP-12 7499
23-SEP-12 TO 26-SEP-12 7499
01-sep-12 TO 30-SEP-12 7521
01-sep-12 TO 30-SEP-12 7788
View 8 Replies
View Related
Jun 2, 2011
I am writing a REGEXP_LIKE function to validate the email address.. below function works for most of the valid email address, but not working for below condition... not sure how it can be tweaked to work for below condition as well...
Character . (dot, period, full stop) provided that it is not the first or last character, and provided also that it does not appear two or more times consecutively (e.g. John..Doe@example.com).
Select 'X' from dual
WHERE NOT REGEXP_LIKE('John.Doe@example.com','^(([a-zA-Z0-9_-])([a-zA-Z0-9_.''-]*)@([((25[0-5]|2[0-4][0-9]|1[0-9][0-9]|[1-9][0-9]|[0-9]).){3}|((([a-zA-Z0-9-]+).)+))([a-zA-Z]{2,}|(25[0-5]|2[0-4][0-9]|1[0-9][0-9]|[1-9][0-9]|[0-9])]))$')
View 4 Replies
View Related
Sep 17, 2013
find the last nth month name [Single month].
View 5 Replies
View Related
Sep 26, 2010
I was just wondering that do we have any function available in oracle which returns the last Friday of the month.
In our company we close our monthly books on last Friday of the month and there are few activities that we have to do on the following Monday as a part of month-end activity. Now the following Monday can fall on the same month or at times it falls on the following month.
I have to schedule a report to be sent to a user on the following Monday after the month-end. I need to schedule it using the cron job.
We are using Oracle 9i on Linux platform.
View 2 Replies
View Related
Sep 22, 2011
create or replace function getDate(p_joing_date Date,p_sysdate)
Return Date;
IS
v_compltd_mnths;
BEGIN
SELECT into v_compltd_mnths MONTHS_BETWEEN(TO_DATE('sysdate','MM-DD-YYYY'), TO_DATE('joing_date','MM-DD-YYYY') ) "Months"FROM DUAL;
return v_compltd_mnths;
END;
that i have worte..
View 3 Replies
View Related
Mar 17, 2011
I have a query that uses a function to find the business days between two dates.It sums the total number of days between two dates per employee to find the total days for the past 30, 90, or 365 days.
The problem is that the query takes 21 second to return the last 30 days.Over 70 second to return the last 90 days and over 140 second to return the last 365 days.Do you know how I could tune the query to return faster? Below is the query for the last 30 days:
select dwt_emp_id, SUM((SELECT GET_BDAYS(DWT_DATE,DWT_CREATE_DATE) FROM DUAL))
from dwt_dvt_work_time where dwt_create_date > sysdate - 30
and dwt_hours > 4 and dwt_usr_uid_created_by <> -1 group by dwt_emp_id order by dwt_emp_id
Here's the function:
CREATE FUNCTION get_bdays (d1 IN DATE, d2 IN DATE)
RETURN NUMBER
IS total_days NUMBER(11,2);
holiday_days NUMBER(11,2);
[code]....
View 1 Replies
View Related
Oct 12, 2012
I have to write a stored procedure that accepts the post code in which the customer resides as the input parameter. The procedure should then use an explicit cursor to display comprehensive details about each customer, the number of bookings that customer has made and the total cost of all completed rentals for that customer.
The table structure is
custmr - which contains details about the customer
booking - contains detail abut the car booking and its details
View 4 Replies
View Related
Oct 24, 2012
I have Installed Oracle database 11g, By mistake I logged as :
mm/mm@orcl as sysdba
The Database acctepted the login with a full DBA privillages, although this user is not created within the database users.
View 9 Replies
View Related
Feb 17, 2010
I want Oracle stored function/procedure to calculate number of working days between two dates. We need to exclude Firdays and Saturdays as there are weekend holidays and also exclude official holidasy that lie between two dates.
View 7 Replies
View Related
Feb 4, 2013
I have the following C code:
class Factorial {
public:
int getVal (int a);
};
[code]....
/When I am trying to execute this function always get the ORA-06521. I changed the data types - but nothing changed.
Just in case, listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = db)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
[code]....
View 6 Replies
View Related
Oct 21, 2013
I am having a problem when calculating dates in a package usiong Toad. I have my function :
FUNCTION PORTFOLIO_MEMO_RPT_Sql( schema_name IN VARCHAR2 , select_business_date IN DATE ) RETURN VARCHAR2.....SELECT DISTINCT Case when NVL(TO_DATE(''09/24/2013'',''MM/DD/YYYY'') - DECODE(mn.Active, 1, mn.DueDate, sc.DueDate ),0) = 0 then ''0Days'' when NVL(''' || select_business_date || ''' - DECODE(mn.Active, 1, mn.DueDate, sc.DueDate ),0) = 0
then ..... when I give it my variable from outside , it is not working......but when I use a date inside, it works. I call my function from outside like :
SELECT * FROM TABLE ( FI_rpt.Fi_Rpt_Pkg_loan.PORTFOLIO_MEMO_RPT (sys_context('userenv','session_user'),TO_DATE('09/24/2013','MM/DD/YYYY')) )
View 17 Replies
View Related
Aug 3, 2013
i'm trying to get the difference between two dates so i have two date itemsthen i did daynamic action (set Value) and choose pl/sql function then added the following codeDecalrestart_date date;end_date datebeginstart_date:=to_date(:p20_start_date,'dd/mm/rrrr');select to_date (sysdate,' dd/ mm /rrrr')into end_datefrom dual;return end_date-start_date;end; but when i run the page i got error ora-01861
View 1 Replies
View Related
Jun 4, 2013
I have a requirement to list the data month wise dynamically where month data is also in the same table, hopefully the below posts should bring more clarity to my requirements.
1. Table creation:
Create table T1 (account_no varchar2(15), area_code varchar2(2), bill_month date, consumption number);
2. List table content:
select * from T1;
account_no area_code bill_month consumption
Q00001Q31-Jan-12125
Q00002Q31-Jan-1265
Q00003Q28-Feb-12219
Q00004Q28-Feb-12805
Q00005Q28-Feb-1254
Q00001Q31-Mar-12234
Q00002Q31-Mar-12454
Q00003Q31-Mar-12232
Q00004Q30-Apr-1221
Q00005Q30-Apr-12218
Q00001Q30-Apr-1254
Q00002Q31-May-1219
Q00003Q31-May-1287
Q00004Q30-Jun-12187
Q00005Q30-Jun-1278
so on......so on......so on......so on......
3. Expected output:
account_no area_code Jan-12 Feb-12 Mar-12 Apr-12 May-12Jun-12Jul-12Aug-12Sep-12Oct-12Nov-12Dec-12
Q00001 Q 125 548 2345487423154821518738721512
Q00002 Q 65 127 45487819357831585683152878
Q00003 Q 545 219 2328738735188745897313
Q00004 Q 78 805 1221218187885718387389787138
Q00005 Q 541 54 2621878778386538698182
With the conventional query I hope this is impossible,
View 2 Replies
View Related
Nov 15, 2010
Oracle version - 10.1.0.4 OS - HP-UX
In my alert log I am getting the following error quite repeatedly; My application hangs, and have to restart the instance.
CODEORA-00604: error occurred at recursive SQL level 1
ORA-06521: PL/SQL: Error mapping function
ORA-06512: at "SYS.OLAPIHISTORYRETENTION", line 1
ORA-06512: at line 6
View 4 Replies
View Related
Jan 26, 2011
I want to find the dates which have a date plus with in 2 days after this date. I mean group by 3 days each even the date i missing between two days. Actualy I want to find the start date where the employ was missing on job.
Basic concept is employes have allowed to use 10 personal leaves of a year. Each leave can be use for maximum 3 days.
If employ did not come on the job for one day or two days or three days, it shoul be count as ONE personal leave. And If employ is missing at job for four or five days, it should be count as 2 personal leaves.
seq date
------------------------------
101.01.10
205.01.10
306.01.10
410.01.10
512.01.10
613.01.10
714.01.10
815.01.10
916.01.10
1018.01.10
1119.01.10
1220.01.10
1321.01.10
1423.01.10
1526.01.10
1627.01.10
1729.01.10
1831.01.10
The result should be (Don't use Pl/Sql)
seq date
------------------------------
101.01.10
205.01.10
310.01.10
413.01.10
516.01.10
619.01.10
723.01.10
826.01.10
929.01.10
After finding these days I want to select the starting date of 5th personal leave. (which is 16.01.10).
I am not a expert of using SQL, but I think it could be possible with using partitioning a table on the givin reslult and further partition the reslut on rownum() as rn and the using case statement where rn = 5.
View 2 Replies
View Related
Mar 10, 2011
Split a date into new dates according to black out dates!
Here is my tables:
CREATE TABLE travel
(
start_date,
end_date
)
AS
SELECT DATE '0000-01-01', DATE '9999-12-31' FROM DUAL;
[code]....
I have lets say a "travel date" and black out dates. I will split the travel date into pieces according to the black out dates.
Note: Travel Date can be between 0000-01-01 - 9999 12 31
Sample:
Travel Date:
Travel | START DATE | END DATE
T | 2011 01 04 | 2011 12 11
Black Out Dates:
BO | START DATE | END DATE
A | 2010 11 01 | 2011 02 11
B | 2011 01 20 | 2011 02 15
C | 2011 03 13 | 2011 04 10
D | 2011 03 20 | 2011 06 29
Excepted Result:
New Travel | START DATE | END DATE
X1 | 2011 02 16 | 2011 03 12
X2 | 2011 06 30 | 2011 12 11
Visually:
Travel Date : -----[--------------------------]--
A : --[------]-------------------------
B : ------[---]------------------------
C : --------------[---]----------------
D : ----------------[------]-----------
Result :
X1 : -----------[--]--------------------
X2 : -----------------------[--------]--
Sample 2:
Travel Date : -[--------------------------------]--
BO Date A : ----[------]-------------------------
BO Date B : -------------------------[---]-------
BO Date C : ----------------[---]----------------
BO Date D : ------------------[------]-----------
Result X1 : -[-]-------------------------------
Result X2 : -----------[--]--------------------
Result X3 : -----------------------------[--]--
How can I do it using PL SQL ?
View 5 Replies
View Related
May 16, 2013
Using 11gR2, windows 7 client machine. I need to update the table missing_volume (below), where I need to calculate the estimated_missing column. The calculation of estimated_missing column for current month needs previous month numbers (as commented inside the code below). I want the output like the first table. Notice the records start from January, hence estimated_missing for January can't be calculated, but for the the rest of the months it can be done by simply changing 'yr' and 'mnth' (commented inside the code towards the end).
yr mnth location volume actual_missing expected_missing estimated_missing
---------------------------------------------------------------------------------------------------------------------------------
2013 January loc1 48037 24 57
2013 February loc1 47960 3660 53 24
2013 March loc1 55007 78 57 28
2013 April loc1 54345 72 58 77The code:
UPDATE missing_volume g
[Code]....
The code does calculate correct number for 'estimated_missing' as I run the code for each month, but the problem is while updating the current month it also erases the record for previous month. E.g. as can be seen below, after I updated April the column only has the record for April, previous month record is gone, similarly updating March removed February, etc. I can't understand why it's happening!! Here is the output I get:
yr mnth location volume actual_missing expected_missing estimated_missing
---------------------------------------------------------------------------------------------------------------------------------
2013 January loc1 48037 24 57
2013 February loc1 47960 3660 53
2013 March loc1 55007 78 57
2013 April loc1 54345 72 58 77
why it's happening (I mean where is the flaw in the code) and how to get the desired output (first table).
View 5 Replies
View Related
May 11, 2011
I'm receiving these errors when I try to run my function. The object of my function is to "This function will format the employee's name in the following format:
if the employee has a middle initial: John P. Smith
If the employee does not have a middle initial: John Smith"
LINE/COL ERROR
-------- -----------------------------------------------------------------
3/1 PLS-00103: Encountered the symbol "P_INITIAL" when expecting one
of the following:
:= ) , default character
The symbol "," was substituted for "P_INITIAL" to continue.
4/1 PLS-00103: Encountered the symbol "P_LNAME" when expecting one of
the following:
:= ) , default character
The symbol "," was substituted for "P_LNAME" to continue.
[code]....
And that is the syntax I currently have.
View 6 Replies
View Related
Apr 16, 2013
I'm using the following code for a function which is working fine in Oracle 9i, but throwing error like ORA-06512 (Numeric value error) in Oracle 11g.
CREATE OR REPLACE FUNCTION Decrypt(toconvert VARCHAR2) RETURN VARCHAR2 IS
str_original VARCHAR2(30);
str_new VARCHAR2(30);
sub_str VARCHAR2(1);
j NUMBER;
[code]......
View 17 Replies
View Related
Nov 25, 2010
I need for each date sum the values from the begin of the year to present date. In January I will have the value of this month, on February I must sum the value of this month and the value of the month before, and so on, at the end of the year.
Date input
SELECT ID_CLIENT, DT_REG, VAL
FROM (
SELECT 1 as ID_CLIENT, TO_DATE('20100101', 'YYYYMMDD') as DT_REG, 200 as VAL FROM DUAL UNION
SELECT 1 as ID_CLIENT, TO_DATE('20100201', 'YYYYMMDD') as DT_REG, 100 as VAL FROM DUAL UNION
SELECT 1 as ID_CLIENT, TO_DATE('20100301', 'YYYYMMDD') as DT_REG, 200 as VAL FROM DUAL UNION
SELECT 1 as ID_CLIENT, TO_DATE('20100401', 'YYYYMMDD') as DT_REG, 150 as VAL FROM DUAL UNION
SELECT 1 as ID_CLIENT, TO_DATE('20100501', 'YYYYMMDD') as DT_REG, 100 as VAL FROM DUAL UNION
SELECT 2 as ID_CLIENT, TO_DATE('20100101', 'YYYYMMDD') as DT_REG, 100 as VAL FROM DUAL UNION
SELECT 2 as ID_CLIENT, TO_DATE('20100301', 'YYYYMMDD') as DT_REG, 220 as VAL FROM DUAL UNION
SELECT 2 as ID_CLIENT, TO_DATE('20100501', 'YYYYMMDD') as DT_REG, 500 as VAL FROM DUAL UNION
SELECT 3 as ID_CLIENT, TO_DATE('20100201', 'YYYYMMDD') as DT_REG, 150 as VAL FROM DUAL UNION
SELECT 3 as ID_CLIENT, TO_DATE('20100501', 'YYYYMMDD') as DT_REG, 100 as VAL FROM DUAL);
Result
ID_CLIENTDT_REGVAL
101/01/2010200
101/02/2010300
101/03/2010500
101/04/2010650
101/05/2010750
201/01/2010100
201/03/2010320
201/05/2010820
301/02/2010150
301/05/2010250
View 17 Replies
View Related
Apr 7, 2011
I tried to use external C procedure from the database and i did all required steps as below.
1. create a C program
2. compile and link the C program
3. copy it to the Oracle_home/bin directory
4. configure listener.ora and tnsnames.ora files
listener.ora
=======
callout_listener =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP) (HOST = ip_address)(PORT = 1521)
[code]...
View 4 Replies
View Related