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
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
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
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.
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.
I just upgraded my oracle DB version from 9 to 11.2g with the assistance of a DBA. The upgrade went fine, and all the data transfered as it should.
The only problem that I got is that almost every query that I try to run via my PL/SQL developer throws the ORA-01843 error. I checked the NLS-lang property from the previus DB and it is the same as the new one. The DBA says that the problem is in the PLSQL properties (althogh I didn't change them).
i am facing problem with date column.when i am trying to do execute a query this works fine
select null as link,'S'||to_char(DATE_OF_JOIN,'YY'),COUNT(STUDENT) "Blue Acres" from STUDENT_RECORD where PROCESSOR=1 and DATE_OF_JOIN >= to_date('0620' || to_char(sysdate, 'YYYY'), 'MMDDYYYY') and DATE_OF_JOIN < to_date('0920' || to_char(sysdate, 'YYYY'), 'MMDDYYYY') group by to_char(DATE_OF_JOIN,'YY')
and when i am trying the below query its showing the only this year dates its not referring the previous years
select null as link,'S'||to_char(DATE_OF_JOIN,'MMDDYY'),COUNT(STUDENT) "Blue Acres" from STUDENT_RECORD where PROCESSOR=1 and DATE_OF_JOIN >= to_date('1220' || to_char(sysdate, 'YYYY')-1, 'MMDDYYYY') and DATE_OF_JOIN < to_date('0320' || to_char(sysdate, 'YYYY'), 'MMDDYYYY') group by to_char(DATE_OF_JOIN,'MMDDYY')
when i am trying below query its showng the error ORA-01843: not a valid month
select null as link,'Fall'||to_char(to_char(DATE_OF_JOIN,'YY')-1),COUNT(STUDENT) "Blue Acres" from STUDENT_RECORD where PROCESSOR=1 and DATE_OF_JOIN >= to_date('0620' || to_char(sysdate, 'YYYY')-1, 'MMDDYYYY') and DATE_OF_JOIN < to_date('0920' || to_char(sysdate, 'YYYY')-1, 'MMDDYYYY') group by to_char(to_char(DATE_OF_JOIN,'YY')-1)
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.
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).
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);
I have a table and no related backup available.If some block is broken, then , how can I get the data with the blocks that are not broken?
Test environment:
SQL> create tablespace test datafile '/u01/app/oracle/oradata/test/test.dbf' size 1m;
Tablespace created.
SQL> create table scott.test_bad_block (id int, name varchar2(20)) tablespace test;
Table created.
SQL> r 1 begin 2 for i in 1..1000000 loop 3 insert into scott.test_bad_block values (i, 'test' || i); 4 commit; 5 end loop; 6* end; begin * ERROR at line 1: ORA-01653: unable to extend table SCOTT.TEST_BAD_BLOCK by 8 in tablespace TEST ORA-06512: at line 3 SQL> select count(*) from scott.test_bad_block;
COUNT(*) ---------- 40984
Now I know, I have 40984 rows in the table.In order to simulating the block corrupt, I shutdown the database and I used the bvi (a binary file editor to modify a line of record) , then start the database, when I am issuing the same statement, the problem shows:
SQL> select count(*) from scott.test_bad_block; select count(*) from scott.test_bad_block * ERROR at line 1: ORA-01578: ORACLE data block corrupted[b] (file # 7, block # 72)[/b] ORA-01110: data file 7: '/u01/app/oracle/oradata/test/test.dbf'
It says that the block is corrupted.Now, here is what I can do to get the data right now:
#get the object id SQL> r 1* select OBJECT_NAME, DATA_OBJECT_ID from dba_objects where owner='SCOTT' and object_name='TEST_BAD_BLOCK'
BEGIN dbms_network_acl_admin.add_privilege (acl=> 'mails_senden.xml', principal=> 'USER2', is_grant=> true, privilege=> 'connect');
[code]....
USER1 sent a mail via UTL_SMTP successful whereas USER2 got the errors:
DECLARE * ERROR at line 1: ORA-24247: network access denied by access control list (ACL) ORA-06512: at "SYS.UTL_TCP", line 17 ORA-06512: at "SYS.UTL_TCP", line 267
[code]....
There is still the following fact:
USER1 has the role DBA, USER2 got the EXECUTE privilege to packages UTL_TCP, UTL_SMTP (GRANT from SYS).
IF the role DBA was granted to USER2 too then he couldt send emails just as well as USER1.
I'm having problem with my database, which contains more than 1 rows with a same value on a field that has uniqueness contraint.
Here is the log from sqlplus. When I select on RI field, it shows 2 rows. But when I select on SCNUM field, it shows only 1 row. This SCNUM has an unique index on it.
And it is still in VALID state
SQL> set autotrace on SQL> select ri, scnum from scratch1_p where ri in (536964983, 536955574); select ri from scratch1_p where scnum='444393975'; RI SCNUM ---------- ---------- 536955574 444393975
I'm trying to add a column in an existing form. I added a text item and defined it as a database item. When I tried to rename the item the message (frm-15500 valid and unique object name must be entered)appears, and therefore not able to exit the property Platte.
Is there a way I can exit the Platte and should I've first add the column to the data block and then rename in my form ?
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])]))$')
I have a requirement like to validate the data in PL/SQL script dynamically.
I have 4 tables
TEST_TBL: Data available in this table TEST_VALID_TBL: Contains field names of TEST_TBL and condition VALID: Need to insert valid records INVALID: Need to insert invalid records
I have to insert data into valid table when validation are full filled otherwise it should be insert invalid table .
Validation are based on TEST_VALID_TBL
While checking the data validations FIELD_NAME should be passed dynamically,because i have four columns in TEST_TBL but at present I am validating only 3 columns in feature it may be add more columns to validate.
I have a requirement to create a packaged proc which lists down a set of database objects and its statuses whenever the status of objects is changed as valid/invalid in user_objects. Also, those valid objects need to be compiled while running the packaged proc.
After validating the SQL statemnet during creating a Custom folder in OracleBi Admin following Error displays. While the same query execute in toad successfully.
The Custom SQL entered is not a valid SQL statement - ORA-01031:Insufficeint Privileges
i'm writing a job to export data on a weekly basis and archive those data in-case needed to be re-imported in future, its important that we able to import successfully if ever needed.
can impdp perform checks to see if dumps are valid ?how about old imp?if not, will it be safe enough to rely on the export log file? in another word is it safe to assume its all safe if there is no error or warning in the exp log?
restored from tape a backup then issued rman command
RMAN> restore database;
Starting restore at 21-MAY-13 Finished restore at 21-MAY-13
RMAN> alter database open;
RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of alter db command at 05/21/2013 15:36:15 ORA-01190: control file or data file 1 is from before the last RESETLOGS ORA-01110: data file 1: '/u02/oradata/system01.dbf' [code]....
SQL> alter database mount; Database altered. SQL> alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-01139: RESETLOGS option only valid after an incomplete database recovery
