Oracle Version : Oracle Database 11g Express Edition Release 11.2.0.2.0 - ProductionI have following sample data:
Sample DataWITH DATA AS( SELECT '100' GRP, '01-JAN-2012' EFFECTIVE_DATE, '30-JUN-2012' TERMINATION_DATE
from DUAL
UNION ALL
SELECT '100' GRP, '01-JUL-2012' EFFECTIVE_DATE, '31-JUL-2012'
FROM DUAL
UNION ALL SELECT '100' .
[Code]....
Business Rules: I have to get all invalid records according to the below business rules.
- row no 1 and 2 are valid records because row no 2 effective date start after the termination date of row no 1
- row no 3 is invalid record because effective date start with termination date of row no 2
- row no 4 is valid record because effective date is after or greater than previous valid record termination date that is row no 2 (31-jul-2012).
- row no 5 is invalid record effective date is smaller than previous termination date
- row no 6 is valid record because effective date is greater than previous valid termination date that is row no 4 .
- row no 7 is invalid record because same data already exist in row no 6
- row no 8 is valid because effective date is greater than previous valid record that is row no 6 termination date.
WITH DATA AS ( SELECT '100' GRP, '01-JAN-2012' EFFECTIVE_DATE, '30-JUN-2012' TERMINATION_DATE from DUAL UNION ALL SELECT '100' GRP, '01-JUL-2012' EFFECTIVE_DATE, '31-JUL-2012' FROM DUAL union all [code]......
The above mentioned output is produced by using the following business rules:
-- row no 1 = Valid records -- row no 2 = effective date is 1 day after termination date of row no 1. Means valid records -- row no 3 = effective date is equal to the row no 2 termination date -- Means invalid record -- row no 4 = effective date is 1 day after termination date of row no 2 Means valid records -- row no 5 = The gap between row no 4 termination date and row no 5 effective date is more than 1 day. Means record is invalid.
Query always compare effective date with previous valid record termination date by using the above mentioned condition and return result accordingly.
Summary is that there should be 1 day gap between termination date and next effective date as well as effective date is always less than termination date in same rows. In addition, if record is invalid then next record check with previous valid record termination date.
I am trying to update records in the target table based on the records coming in from source. For instance, if the incoming record is present in the target table I would update them in the target else I would simply insert. I have over one million records in my source while my target has 46 million records. The target table is partitioned based on calendar key. I implement this whole logic using Informatica. Looking at the informatica session log I find that the informatica code is perfectly fine but its in the update part it takes long time (more than 5 days to update one million records). find the TARGET TABLE query and the UPDATE query as below.
TARGET TABLE: CREATE TABLE OPERATIONS.DENIAL_REGRET_FACT ( CALENDAR_KEY INTEGER NOT NULL, DAY_TIME_KEY INTEGER NOT NULL, SITE_KEY NUMBER NOT NULL, RESERVATION_AGENT_KEY INTEGER NOT NULL, LOSS_CODE VARCHAR2(30) NOT NULL, PROP_ID VARCHAR2(5) NOT NULL, [code].....
I have written the following PL/SQL procedure to delete the records and count the number of records has been deleted.
CREATE OR REPLACE PROCEDURE Del_emp IS del_records NUMBER:=0; BEGIN DELETE FROM candidate c WHERE empid in (select c.empid from employee e, candidate c where e.empid = c.empid and e.emp_stat = 'TERMINATED' ); [code]....
I am running a query in our Clarity PPM database to return a list of all Support projects. This returns a simple list of project code and project name:
The query has the project resource tables associated with it, so I am able to list all resources allocated to the project. But for now i am only selecting a DISTINCT list of projects.
I have a separate query which returns a list of support resources.
select res.full_name, res.unique_name , dep.description from niku.srm_resources res, niku.pac_mnt_resources pac, niku.departments dep where res.unique_name = pac.resource_code and pac.departcode = dep.departcode and res.is_active = 1 and description like 'IMS%' and UPPER(dep.description) like '%SUP%'
What I need to be able to do in the first query, is return only projects that do NOT have a resource that appears in the resource list in the second query.
(the res.unique_name field in the second query can be linked to the same in the first query)
Logically, the process would be: 1. Identify Support Project 2. Identify Resources allocated to the project team 3. Compare with List of Support Resources 4. If any Resources in that list do NOT appear on the project, then return project.
I am trying to do an update with an anonymous PL/SQL block because it has been absolutely impossible to get by the infamous "Single-row subquery returns multiple rows" Whatever.So this code below work... Obviously, I am updating it to a constant.. OK, duh.. Of course it works, it's easy.... Now, I really need to update it to a value in another table that is in my cursor. I believe that I probably need to declare a secondary cursor.
I'm just getting back in the DBA saddle, so I'm a little rusty..
set serveroutput on; DECLARE numrows NUMBER := 0; total NUMBER := 0; CURSOR upd_record_cur IS SELECT m.rowid, m.swcm_cycle, t.newcycle, p.aprem_no from toad.fswcmas m,
[code]...
Not so good here
ORA-06550: line 20, column 53: PL/SQL: ORA-00904: "TOAD"."TTP43425_LOAD"."NEWCYCLE": invalid identifier ORA-06550: line 20, column 10: PL/SQL: SQL Statement ignored
Doesn't work... BEGIN FOR upd_rec IN upd_record_cur LOOP update toad.fswcmas sw set sw.swcm_cycle = toad.ttp43425_load.newcycle WHERE rowid = upd_rec.rowid; total := total + 1;
There are 4 packages got invalid 2 days back. when I analyze the database I came to know that there are 5 tables got truncated and 2 tables got altered during the issue period through the code. Those truncated tables have the indirect relationship with these 4 packages.but there is no any relation between these packages and altered table.
Also during that time I got the below error in my alert log.I am sure the cause this error is the invalid packages.
I know if any alteration happens in a table, the refrence package will be getting as invalid. Apart from this, is there anyother cause to bring the package into invalid status? How to proceed further to find the root cause of thses invalid package?
procedure vieworderstatus(in_CALL_ID IN NUMBER , in_DAYS_OF_HISTORY in number , out_SUPLY_REORDR_STATUS_LST OUT supplyreordrlist , out_ERR_CODE OUT NUMBER, out_ERR_MESSAGE out varchar2);
This procedure takes retrievs cust_id for given input call_id. The procedure then retrieves order information for that customer and returns using the out ref cursor. I need to test the scenario where the user passes invalid call_id.
The following query returns no data for call_id = 12345 ( invalid call_id) select fk_cust_id into v_cust_id from xcom_call where call_id = in_CALL_ID;
in such cases; I just need to return null ref cursor and log the error but right now when I pass invalid call_id ; I am getting "invalid cursor" error.How do I handle this ?
COMP_IDSTATUS APSOPTION OFF SDOOPTION OFF ODMOPTION OFF AMDOPTION OFF XOQOPTION OFF OWMVALID CATALOGVALID CATPROCVALID JAVAVMVALID XMLVALID CATJAVAVALID RULVALID EXFVALID CONTEXTVALID XDBVALID EMVALID ORDIMVALID
I restored a date in my database (the backup of that date was taked by a snap on Windows Server 2003), now I try to startup the database it gives me the following error
ORA-02778: Name given for the log directory is invalid
Tue May 06 09:42:15 2008 Starting background process EMN0 EMN0 started with pid=53, OS id=13704 Tue May 06 09:42:16 2008 Shutting down instance: further logons disabled Tue May 06 09:42:18 2008 Stopping background process QMNC Tue May 06 09:42:19 2008
I get the ORA-00911: invalid character. I tried using quotation marks but does not work. The error is on line 25, if you change it the error just change to statement ended incorrectly.
i want create view to select all invalid objects in database.So i create this one:
CREATE OR REPLACE FORCE VIEW INVALID_OBJECTS_DETAILS ( DETAILS ) AS SELECT DISTINCT a.owner || ', ' || a.object_name FROM dba_objects a, dba_source b WHERE a.owner = b.owner AND a.object_name = b.name AND a.object_type = b.TYPE AND a.status != 'VALID' AND b.text NOT LIKE '%@%';
But I want only select invalid objects without a database link .
i want to update a table with the .xsl file i wrote the below code for that but its sohwing me the ORA-911 invalid charecter error at the symbol "{" in code. UPDATE LR.LR_LED_DOC_XSL_MASTER SET XSL_DATA ={blobfile='D:BackupLog detailsCANCUSTOMSMEMO.xsl'} where rtrim(XSL_CODE) ='CANCUSTOMSMEMO';
I am using APEX 4.0.2.00.06 on a Linux server running Oracle 11g R2 and have the following problem:
I have a page with two forms for creation of new rows in either the sites table or the site-projects table. Site-projects is defined as follows with four numbers and two character fields:
The entry form uses select lists for many of the fields, but it is the assigned_flag that seems to be causing problems. It�s LOV definition is static2:UNASSIGNED;N,ASSIGNED;Y When I tap the create button, I get :
ORA-20507: Invalid numeric value N for column ASSIGNED_FLAG Error Unable to process row of table SITES.
This suggests that something is going on at the sites table even though I use the create button associated with the site-projects table. Am I invoking the row processing for the sites table as well? Why does it run into invalid number when the field is varchar2? Actually, I get the exact same error if I use the create button on the sites portion of the page.
I am Modifying a table structure, so dependent objects(triggers,packages etc) are getting invalid. So i thought of compiling those invalid objects which are related with the modified table. I used below query to get the invalid objects,
select obj.object_name,obj.object_type from user_objects obj,all_dependencies dep where referenced_name='DEPT' and obj.object_name=dep.name and dep.owner='SCOTT' and obj.status='INVALID'
Q1)What is wrong with this query, sometimes it works sometime it doesn't.? Q2)All_dependencies : does this view show all dependent object on a table even the Invalid one's? Q3) Is there any alternative to find the dependent invalid objects or even dependent object on a table ?
I am going through this link: URL.....Under the heading "Creating FORCE VIEWS",it states,
For example, if a view refers to a non-existent table or an invalid column of an existing table, or if the owner of the view does not have the required privileges,. I did not understand what is meant be Invalid column and the situations which make a column Invalid.
I can get a list of invalid objects, know how to recompile them but I was wondering if there was a way to query the date/time of when an object went invalid.
I'm working on a 10.2.0.4.0 (Enterprise Edition) database running on SLES10.
while select RA_CUSTOMERS orderby customer_id innerJoin AR_PAYMENT_SCHEDULES_ALL where RA_CUSTOMERS.customer_id = AR_PAYMENT_SCHEDULES_ALL.customer_id AND AR_PAYMENT_SCHEDULES_ALL.DUE_DATE > 90 (select CUSTOMER_ID FROM AR_CASH_RECEIPTS_ALL where AR_CASH_RECEIPTS_ALL.customer_id = RA_CUSTOMERS.customer_id);
Quote:by default the job runs once every 24 hours.
When you see LAST_ANALYZED being days, weeks, or months in the past do not be alarmed.
If/when the data in a table does not change, then the statistics do not need to change.
Oracle collects new statistics when enough of the data (about 10%) has changed.
Like above statement for DBMS_STATS job, is there a automatic job that runs every day to compile invalid objects for schema or we need to compile it manually ?
when I execute this I get an output of $123.50...I want to use other speacial characters like @,* instead of $...But I am getting invalid identifier error if I use any special character other than $.
