select profilename from PROFILE where user_data like '%DATE_RULE!115%';
Output will be "Test A".Now, this is just a single value from RULES table used to find the data of PROFILE table.I will have to run the query on multiple values of RULES tables to find records containing a string format of sort "DATE_RULE!<rule_no>". How to search on WILD CARDs like these?
I have a service that executes a pl/sql function (legacy app) to delete records from multiple tables. This function works fine in development, and has worked fine in production until about a week ago. I'm not a DB guy but the DB guys are trying to say this is an application issue. That may be, be the "insuff privileges" really leads me to believe otherwise.
What is causing this type of Oracle error? Permissions between dev and prod are the same, yet it works in dev but not prod.
ORA-29876: failed in the execution of the ODCIINDEXDELETE routine ORA-20000: Oracle Text error: DRG-50857: oracle error in textindexmethods.ODCIIndexDelete ORA-20000: Oracle Text error: DRG-10602: failed to queue DML change to column ABSTRACT for primary key AAAfBoAAEAABa62AAA DRG-50857: oracle error in drexrdml ORA-01031: insufficient privileges [code]....
I have two tables A with columns a.key, a.location_code, a.status and a.first_name and table B with cols b.key, b.location_code, b.status and b.first_name.
I want to find the missing records between the two tables and as well check whether each column is populated correctly. That is if u take a record with id 1 check if loc_code is same in both the tables and if they are different, insert the key and first record column and second record column into a new table. And similarly if there is no record wiht that particular id in the second table, insert the record.
For missing records in the sense for records which are present in A but not in B, am using
Select a.key_no, a.loc_code, b.loc_code from A,B where a.key_no=b.key_no(+) and b.key_no IS NULL
But the problem is I need to put some constraints on the B table like b.status='Married'and b.loc_code='CA'. When am using this condition in the above query, it's throwing me error saying cannot use outer join operator in and or or.And I could not figure out how to check for the columns being populated correctly between the two tables and at the same time check for missing ones
I'm trying to select id's in a table that have 2 certain values for another column. Example below explains:
idCoupon Type 123Amount 123Percent 456Amount 789Percent
I would like to write a sql statement that would select all rows where id=123, because id 123 has both coupon types "Amount" and "Percent". So the result set of the sql statement would look like:
I'm facing some problem even after using INSTR function in Oracle.The problem is I have written the logic in the PL/SQL block which appends all the values fetched in a loop on the basis of whether the string is present or not.
For ex:
The first value fetched from the select query first is ABCDEFG which gets appended to a variable The next value fetched is AB even this has to be appended to the variable since this exactly doesn't match with ABCDEFG. The next value fetched is BCDEF even this has to be appended to the variable since this exactly doesn't match with ABCDEFG. The third Value fetched is ABCDEFG this will not get appended presently according to the logic which is correct.
writing that piece of code to append the value fetched which doesn't exactly match with the existing string
I have a string in this format '2011/06/01 00:00:00'. How do I convert this to date format.I tried to_date,to_char and they give errors invalid number & literal does not math format string. I don't have much control on the string since that is the way is comes from the application.
INSERT INTO JOB_EXECUTION (RUN_DATE ,RUN_ORDER ,RUN_DIV ,RUN_TYPE ,RUN_FMT ,PICKUP_STATUS ,START_TIME ,END_TIME ,REMARKS) VALUES (sysdate-1,1,'AE','FILE-HFM','NY_PLAN-2014','FINISHED',TRUNC(SYSDATE),SYSDATE,'"Load Process is INITIATed: with [BATCHID:= FILE-HFM_RUN_ORDER#_5 ]: as of @ 26-Sep-2013 10:26:50. AFM-Load END PROCESS: as of @ 26-Sep-2013 10:26:52. AUTO-LOAD RUNNING :as of @ 26-Sep-2013 10:26:57. FILEs-LOAD PROCESS: as of @ 26-Sep-2013 10:26:59. ALLOCATIONs PROCESS: as of @ 26-Sep-2013 10:27:52.
[code]....
The select I tried.
WITH reporting_info AS ( SELECT b.run_date,b.run_div,b.run_type,b.run_fmt,1234 as Batch_id, start_time as BATCH_START_TIME, end_time AS BATCH_END_TIME, REPLACE(substr(substr(remarks,instr(remarks,'FILEs-LOAD PROCESS: as of @ ')), 0,instr(substr(remarks,instr(remarks,'FILEs-LOAD PROCESS: as of @ ')),chr(10))-2 ),'FILEs-LOAD PROCESS: as of @ ','') FILE_LOAD, REPLACE(substr(substr(remarks,instr(remarks,'ALLOCATIONs PROCESS: as of @ ')), 0,instr(substr(remarks,instr(remarks,'ALLOCATIONs PROCESS: as of @ ')),chr(10))-2 ),'ALLOCATIONs PROCESS: as of @ ','') ALLOCATION,
[code]....
Since the date format does not contain AM or PM in it ... while subtracting time values crossing midnight the value goes in negative .... How to subtract two date strings with out AM or PM values to get a positive value ?
WHERE TO_DATE(TO_CHAR(RR.PEROFOPFROM,'DD-MON-YYYY')||RR.AIRCRAFTSTD,'DD-MON-YYYY:HH24MI') > TO_DATE(TO_CHAR(RR.PEROFOPFROM,'DD-MON-YYYY')||RR.AIRCRAFTSTA,'DD-MON-YYYY:HH24MI')
I have data like this:
PEROFOPFROMAIRCRAFTSTD
29/03/20102150 NULL NULL NULL NULL 30/03/20102150
When I execute the query it always gives me the error "literal does not match format string".
I'm trying to do data mining on a web log which recorded one day web access information from a busy web server. I imported the data into Oracle Data miner, and created a table (WEBLOG). The idea is to create a new field, i.e. session, for the users so that each session could be thought as a representative of a user-intent (aka topic). Now based on this, data mining models would be used to cluster(group) the users based on their similarity. The first step is to prepare the data which involves using SQL queries. So first, all I did was to create a function for date and time. This is the following code I used,
create or replace function ssndate(p_date in varchar2 default '03-01-18', p_time in varchar2) return number $if dbms_db_version.ver_le_10 $then deterministic $elsif dbms_db_version.ver_le_11 $then result_cache $end as begin return trunc((to_date(p_date||' '||p_time, 'dd-mm-yy hh24:mi:ss') - to_date('01-01-90','dd-mm-yy')) * (86400/2400)); end ssndate; /
The function ssndate compiled successfully.The next step I took was to create a view through the following query,
create or replace view WEBLOG_VIEWS as select (select ssndate(LOG_DATE, LOG_TIME) from dual) as "SESSION_DT", C_IP, CS_USER_AGENT, (CS_URI_STEM||'?'||CS_URI_QUERY) as WEB_LINK from WEBLOG;
This was successful as well. The problem is in the next step where I try to do data grouping.
create table FINAL_WEBLOG as select SESSION_DT, C_IP, CS_USER_AGENT, listagg(WEB_LINK, ' ') within group(order by C_IP, CS_USER_AGENT) "WEB_LINKS" from WEBLOG_VIEWS group by C_IP, CS_USER_AGENT, SESSION_DT order by SESSION_DT [code]....
the to_date function should be fine. In the data that I possess, the date and time are in no format. Example: 30118 and 0:00:09 respectively.
We store the date in String Type format of "MON-YYYY", we require to run the report base on that string contain date column, During the Query execute the Error Display of (ora-01858: a non-numeric character was found where a numeric was expected)
The Query is :
select period_name,doc_sequence_value,doc_sequence_id,date_created,name,JE_SOURCE,XX from ( select period_name,doc_sequence_value,doc_sequence_id,date_created,name,JE_SOURCE,TO_DATE(XX,'DD/MM/RRRR') XX from ( select TO_CHAR(TO_DATE(('01-'||substr(gl.period_name,1,3)||'-'||substr(gl.period_name,5,2)),'DD/MM/RR'),'DD-MON-RRRR') xx,
insert into iostat_mac values(to_date('2012/03/28 08:00:00 AM'),'653.6','20.7','15392.0','451.8','0.0','5.5','0.0','8.2','0','64','/vol/sun_dc_u2');
Receive ora-01861 error when trying to insert.Suspected it had to do with the date format, I changed the nls parameter for my session to include the time format, but to no avail.
I am getting daily basis data from third party in excel format which i am converting into CSV format and then uploading into oracle tables using External tables.Now problem is that every time i getting the dates in diff format i.e. sometimes dd-mon-yyyy , dd/mm/yyyy etc.
Now every time i have to open my code and change it there ...to make it as oracle date format. IS there way i can find out format of date and based on format i can do operations with getting errors every time.
I am storing the TP(Excel date) date into varchar columns only and then varchar2 column value i m inserting/updating into date format using to_Date ().
UPDATE event_request SET REQ_EVENT_DATE = TO_CHAR(REQ_EVENT_DATE - INTERVAL '1' HOUR, 'yyyy-mm-dd hh24:mi') WHERE eventID=123
Oracle returns this error:
ERROR at line 4:ORA-01861: literal does not match format string
where line 4 is REQ_EVENT_DATE = TO_CHAR(REQ_EVENT_DATE - INTERVAL '1' HOUR, 'yyyy-mm-dd hh24:mi').The REQ_EVENT_DATE field was originally populated with this stripped down query:
We need some transformation rules on Source employee table which comes from SAP and want to load in Target table.Basic idea is we need to calculate time when position was not held by sub owner so . . . Etc
I am trying to insert a row in a table and getting the below error.
SQL> insert into tbl_force_charging(ANI, date_time, durations,src, circleid) 2 values ('9569333585','29-JUN-11 03.19.41.000000000 PM','1027','51010','BIR' ) 3 ; values ('9569333585','29-JUN-11 03.19.41.000000000 PM','1027','51010','BIR') * ERROR at line 2: ORA-01830: date format picture ends before converting entire input string
Table Structure is
Name Null? Type ----------------------------------------- -------- --------------------------- ANI VARCHAR2(10) DATE_TIME DATE DURATIONS VARCHAR2(10) SRC VARCHAR2(10) CIRCLEID VARCHAR2(10) SQL>
We were trying to refresh the QA database from the production export dump.After import we checked the dba_rules in QA database doesnot show any rule . This seems a reasons for many of the packages get invalidated after import.I checked the production database that have 16 rules defined under dba_rules tables.I am not sure abt the syntax for the creation of rules . Is there any way i can take the backup of rules from PROD or some creation of rule script so that i can create similar rules in QA.
when i run this query i am facing date format error.
select sbrueregister.UEIMSI,sbrueregister.fapid,sbrfapslid.slid,sbrfapslid.ACTIVATION_TS,sbrfapslid.DEACTIVATION_TS from SBRFAPSLID INNER JOIN sbrueregister ON sbrfapslid.fapid=sbrueregister.fapid where sbrfapslid.slid='1234567890' and sbrueregister.registeredat between TO_DATE('2013-02-1.12.0. 10. 123000000','YYYY-MM-DD HH.MI.SS.SSSSSS')and TO_DATE('2013-02-1.12.9.10.123000000', 'YYYY-MM-DD HH.MI.SS.SSSSSS');
ORA-01830: date format picture ends before converting entire input string
I am required to manipulate a string in this way.For example ABCDEF)* to $ABCDEF). So I should find * and delete it and instead put $ in the beginning of string.
How can i identify all the occurences of raise_application_error(-20XXX, '<the message>'); within all database objects, and replace them with other text?
