The description field in the item table has the single quote used as the symbol for feet. I have the same issue pulling from a last name field in other tables. (Like O'Connor)
select descrip into v_result from c_ship_hist where shipment_dtl_id = :SDID; exception when others then null;
The error I get is "Missing right quote". How do I code around this issue without having to change the data?
I have a clients who currently implementing an application that using Oracle 10g in 20 distributed location. Each location will have its own database server and locally managed. I plan to create a Disaster Recovery (DR) Centre for this client in centralised location. I plan to setup 20 Application Server but only one database server with 20 instances. My question, can the Dataguard manage the replication between 20 database (with single instances) and single database (with 20 instances? The reason we design such way is to reduce the license cost of Oracle.
We have to update a single column data in about 10 tables which has child/parent table relations, pk/fk constraints.. The column that we are updating is a part of primary key in half of the tables and part of foreign key in the other half tables.. I'm thinking of disabling all the foreign key constraints in the tables then update the column data then enable the foreign key constraints in these tables.
I am trying to build a data warehouse for Consumer Price Index and so I have downloaded data from the Bureau of Statistics.It is in excel format and since I am working with Oracle Warehouse Builder I have converted it to .csv file so that I can use it as a data source.
Question1: Is it practical to use single .csv file as a source of data for a data warehouse?
Question2: I have 3 dimensions tables and a fact table.The dimensions are one for the Region(as the date is organized in region,states etc),two is the consumer goods and services (as the data is organized in groups of goods and services, services/goods types) and finally time(year and month),
Now how am I going to do the mapping here?Is it possible to do a one to one mapping here as all data required by the dimensions is located in the .csv file.
I am migrating data from DB2 to Oracle. I used DB2 export to extract the data specifying lobsinfile clause. This created all the CLOB data in one file. So a typical record has a column with a reference to the CLOB data. "OUTFILE.001.lob.0.2880/". where OUTFILE.001.lob is the name specified in the export command and 0 is the starting position in the file and 2880 is the length of the first CLOB.
When I try to load this data using sqlldr I'm getting a file not found.Attached is a copy of the control file and output from testing
PS. I cant use the DB2 option LOBSINSEPFILES which creates a separate file for each CLOB column because the table has over 14 million rows....and creating 14 mil files causes OS inode problems...
Attached File(s) sqlldr.txt ( 2.05K ) Number of downloads: 3
There is a Single Instance production server Oracle 9iR2 [9.2.0.8] OS HP-UX PARISC 11i v1 to RAC.
Now we want to migration this server with minimum downtime to Oracle 11gR2 [11.2.0.2] in RAC environment OS HP-UX 11i v3 Itanium DOwn time must be minimum. what will be the procedure ?
I've an Oracle Table which has around 300 columns. I've a requirement to split this single table into two tables (150 columns each) by a foreign key.
Now I want to know how to maintain the data integrity while I insert the data into two tables. which means each table should have equal number of rows as we insert the 300 columns data into tables each at a time.
Brand new box, and I installed 11.1.0.6 (software only)
I have a brand new box set-up for a single-instance standby database, and I have installed 11.1.0.6 software only.
Do I need to patch 11.1.0.6 to 11.1.0.7 before I send a copy of the primary over to be set-up as a standby? If so, how do I patch 11.1.0.6 without having a local database in place as this server will only be used for a standby? Can patching the oracle_home only be done?
I am not able to find any document that shows steps to create a SINGLE INSTANCE LOGICAL STANDBY database for a 2 NODE ORACLE RAC database. I have found documents for RAC to Single Instance Physical standby database. But not for Logical Standby.
Details:
OS: Redhat 5 DB: 10.2.0.4(shared file system, not using ASM/Standby also file system) Status: Single instance physical standby database created, need to convert it to Logical standby.
I am using Oracle RAC 11.2.0.3 as primary database, we are going to start using Oracle data guard. So I am designing my infrastructure and planing to use Oracle 11.2.0.3 Single instance as my physical stand by database.
My question is it feasible to have my standby database as single instance while the primary is RAC? is it feasible to build my Oracle single instance standby database from the RMAN backup of the RAC primary database? Is there any restrictions (or any points to be taken into consideration) since my primary database is RAC while the physical standby is Oracle single instance?
in the below link
[URL].......
it was mentioned that primary can be RAC or single and same for standby, but my question is it feasible to have primary as RAC while standby as single instance? or it should be like each others?
The primary database can be either a single-instance Oracle database or an Oracle Real Application Clusters (RAC) database. Similar to a primary database, a standby database can be either a single-instance Oracle database or an Oracle RAC database.
Actually I have one query which contains two parameters from_Date and To_date.
if user pass the values for the parameter like 1-dec-2010 to 30-dec-2010 query will works fine and it fetches the values according to the query selected. but now I am going to add one more parameter, the value is 01-apr-2010 now my query should run for 01-apr-2010 to 30-dec-2010.
Here my question is how should I make this scenario with only one query and at the same time the query should work for to scenarios.
I'm trying to test moving a single instance 11202 database to single instance w/ grid infra.
Here is what I've done:
1. Install a database (11202), single instance and create a database 2. Install Grid Software only (user: grid) 3. start the cluster 4. "srvctl add database -d orclsidb -o $ORACLE_HOME" to register the database with grid. 4.1> I was able to start/stop the database with srvctl command here onwards 5. configure disks using asmlib and start the asm 6. "srvctl add asm" to register asm with grid. 7. PROBLEM ... when I try to "backup as copy database format '+ASM_DATA_DG';" from oracle user it errors out as below:
RMAN> backup as copy database format '+ASM_DATA_DG';
Starting backup at 24-AUG-12 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=134 device type=DISK channel ORA_DISK_1: starting datafile copy input datafile file number=00011 name=/fs0/oracle/oradata/orclsidb/sjc883p_indx_large_01.dbf RMAN-00571:
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 08/24/2012 08:53:29 ORA-19504: failed to create file "+ASM_DATA_DG" ORA-12547: TNS:lost contact ORA-15001: diskgroup "ASM_DATA_DG" does not exist or is not mounted ORA-15055: unable to connect to ASM instance ORA-12547: TNS:lost contactConsidering "TNS: lost contact" I tried to see if grid listener is aware of ASM instance:
[Code]....
All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=usracdb03.rwcats.com)(PORT=1522))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.2.0 - Production Start Date 22-AUG-2012 06:08:53 Uptime 2 days 2 hr. 50 min. 48 sec Trace Level off
Input data: Sec_SSC_ID Column_nameAs of dateOld valu New Value IBM Mat_dt 10/10/20101/1/2001 1/1/2002 IBM Bid Market 10/10/201075 85 IBM asset_nm 1/1/2011International IBM MSFT asset_nm 1/2/2011Microsoft Intel MSFT Bid Market price 1/1/201189 90
Business should be able to issue a job whereby the sub assemblies do not have a different job number, however the job itself maintains the indentation (BOM levels). What we are looking to do is to issue one job for a full build of a product, which would be built in one go anyway.
I have a table which lists previous jobs, whcih has monthto,yearto, monthfrom and year from fields. I am trying to find the most recent job (of which they might be multiple ending on the same month) Initially, i did the following:
ran a sub query to find the most recent (employee id, max (yearto * 12 + monthto), then another query where I got a list of the most recent jobs, and returned the first one using rownum = 1 (note this may be inconsistent between runs as thee rownum is calculcated before nay ordering clause. This worked, but i figured using the first function might be better as it gives an order
select employee_id from (select employee_id ,max(NVL(yearto,0) *12 + NVL(monthto,1)) KEEP (DENSE_RANK FIRST ORDER BY emloyer) as latest from employees group by employee_id;
but this seems to return more than one entrty:
table date below: drop table employee_list; drop table employee_historic; [code]...
What is the best way to return a single row, if the primary key is not available / applicable.
I am returning the ORA-01427 after running the query below. why I am returning the error and how to address it.
select b.value , b.name, p.value ...... (case when p.value <> 'G2' then null else (select c.oldvalue from ad_changelog c where c.record_id = b.c_bpartner_id and c.ad_table_id = 291 and c.ad_column_id = 4216 ) end) as oldtradeName from c_bpartner b, zz_receipt r, zz_recp_alloc a, m_product p, ad_user us where a.c_bpartner_id = b.c_bpartner_id and a.zz_receipt_id = r.zz_receipt_id and us.ad_user_id = r.createdby and p.m_product_id = a.m_product_id
Just trying to update a table in which the sales amount is inserted when the sales amount is null.
I have
UPDATE ph2_customer_temp SET sales_amount = ( SELECT sl.sales_amount FROM PH2_CUSTOMER_TEMP pct join sales_limit sl on substr(pct.site_code,1,2) = sl.state where pct.credit_limit is null )
I can't seem to wrap my head around this problem I'm having with a query. I need to update all rows in my ps_ntsr_gf_stufile tables with the concatenated values from the ps_classes_tbl table where a.CLASS_NBR = b.CLASS_NBR. I tried to limit it to emplid from the ps_stdnt_enrl table but no luck.
UPDATE ps_ntsr_gf_stufile a SET a.CLASS_NAME = (SELECT CONCAT('SUBJECT', 'CATALOG_NBR') FROM PS_CLASS_TBL b WHERE a.CLASS_NBR = b.CLASS_NBR AND a.STRM = '1118' AND a.INSTITUTION = 'NT752') WHERE a.EMPLID IN (SELECT distinct EMPLID FROM PS_STDNT_ENRL);
I have one Linux machine on which first I did with Oracle 11gR2 Enterprise edition (EE) , it was working fine.
now today I installed oracle11gR2 Standard edition in different partition which is having separate oracle_home from EE.
when I checked the listener services ,port and all , I came to know that the new installation(SE) it will automatically pickup different listener name(listener_SE SID name) and non-standard port(1523).
I did with oracle10gR2 SE and EE both on the same machine with different oracle_home but all together using only one listener (LISTENER) and one port - 1521.
I'm trying to get this down from two regexp to a single one, but I'm not sure it can be done and if so, its beyond me to work out how atm.
BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production PL/SQL Release 11.1.0.7.0 - Production CORE 11.1.0.7.0 Production TNS for Solaris: Version 11.1.0.7.0 - Production NLSRTL Version 11.1.0.7.0 - Production [code]...
The expression itself contains all the rules I'm aware of (nothing to say they won't move the goalposts again mind!) of and the desired output is as shown above. I should add that the actual data is more complex (many tags per record) so it rules out a regexp_replace
/* (SELECT A.TOTAL_ALLOCATED_AMT FROM CLS_ALLOCATION_HDR@LAMS_PROD A WHERE A.APPROVAL_DATE BETWEEN BCTH.TRAN_FROM_DATE AND BCTH.TRAN_TO_DATE AND A.CUSTOMER_ID=BCTH.CUSTOMER_ID AND ALLOCATION_ID IN (SELECT ALLOCATION_ID FROM CLS_ALLOCATION_DTL@LAMS_PROD B WHERE ENTITY_TYPE='REC' AND B.ALLOCATION_ID=A.ALLOCATION_ID AND ENTITY_ID IN (SELECT RECEIPT_NO FROM CLS_RECEIPT_DTL@LAMS_PROD C WHERE B.ENTITY_ID=C.RECEIPT_NO AND RECEIPT_STATUS='A'))AND ALLOCATION_STATUS='A')*/
when i am trying to run this query i am getting an error'single row subquery returns more than one row'.
