My task is to test a field in a certain database. We shall refer to the field as ship_to. There is an algorithm for which the field ship_to is populated and higher up in the algorithm, a variable we shall call X is created. The algorith states that I should assign the variable to ship_to unless X is = -1. If X is = -1, the algorithm continues with multiple joins and assignments. What would be the best way to go about coding this solution. There are 4 individual paths. I have only described the first path, but they are similiar in structure. I was thinking of using either Case's or decodes?The field ship_to is a number. I have already created a statement to test the sum of the Target, but now I need to test the entire algorithm to see if it too sums the Target.
select field1,field2,field3 from Table1 union select field1,field2,field3 from table2
In the query from table2 i am getting duplicate rows, HOW can i retrieve only distinct rows...Using distinct keyword did not work...if i have to post create and insert statements for this one...
I have had a google around and can't seem to find an answer as to how do do the following Select statement. i am wanting to Select the following fields from across multiple tables.
insert into TEST (ENT_SIG, EMP_ID, DATE_START, DATE_END) values ('014', '120', TO_DATE('20080101','YYYYMMDD'), TO_DATE('20080131','YYYYMMDD')); insert into TEST (ENT_SIG, EMP_ID, DATE_START, DATE_END) values ('014', '121', TO_DATE('20080201','YYYYMMDD'), TO_DATE('20080228','YYYYMMDD')); [code].....
I'm asking if there's a way to do this:
update test a set (date_Start, date_end) = (select date_start, date_end from test2 b where b.emp_id = a.emp_id [code].......
Without using the WHERE EXISTS. I don't want to make two accesses to table2, I would like instead to do something like "If subquery returns a row, use it, else keep what you have in destination table".
Is there a way to do it without entering test2 twice?
Note: The ... in the tables are just placements symbolizing that there are multiple irrelevant (to this thread) fields.
The problem is with the field highlighted in italics whilst the primary keys are in bold. I need to somehow retrieve that ID, but I can't create a many-to-many relationship.
Problem is I have to get all the path from 1st 'A''B''C''D' to 'A' 2nd 'A''B''C''D' to 'B' 3rd 'A''B''C''D' to 'C' 4th 'A''B''C''D' to 'D'
For 1st one all the possible paths will be:
ABCD --> ABC --> AB --> A ABCD --> ABC --> AC --> A ABCD --> ABD --> AB --> A ABCD --> ABD --> AD --> A ABCD --> ACD --> AC --> A ABCD --> ACD --> AD --> A ... same approach for second path and so on...
The inputs may vary in number, like they may be 'A','B','C','D','E'... so on.
Note: This may not seems to be a Oracle problem, but I have to do it in Oracle as my further steps will depend on this.
We are about to undertake a storage migration of our RAC environment from EMC to XIV storage.
The migration method we are using means that we will be presented with identical disks post the migration, however the UNIX paths to the disk devices will be changing. Using our migration method it is not possible to present the old and new devices at the same time.
Therefore once the migration is complete i need to update the device configuration to reflect the new paths. I believe this is done using the following techniques but I am unsure as some of the documentation is ambiguous and I have not done this before
We need to do load testing on our DB before going live. Mainly with 5 queries which are mainly used in our web interface to pull data from the database. For example. I need to send morethan 400-500 sessions to our database using this 5 queries (with different values) and to note down the time taken with every 50 number of users..
Oracle 10.2.0.4 I belive the following query is incorrect in my opinion. (there is an index on col but NULLS are ignored?
SELECT COUNT(*) from <TABLE>
where col in (null,'a','b','c') this works (no errors) and returns pretty quick. However I think the correct query would be SELECT COUNT(*) from <TABLE> where col IS NULL OR col in ('a','b','c') This one takes a long time. As I see it it does a table scan for NULL part and uses the index for the rest as the index cannot be used for NULL values. explanation on this, especially why Oracle accepts the first query "where col in (null,'a','b','c')" without any issue.
I work as a Sys. Admin. for several RHEL 3.8 servers, most of them are clusters of 2 machines. All these servers are running Oracle 9.2.0.7 database. They are running fine on a separate filesystem. So everytime the system has to be formatted for some particular reason, there is no need to re-install the database.
I am trying to make some tests by running the same filesystem containing the oracle database in a fresh Red Hat Enterprise Linux 4.8 X86_64 Install. This task has become impossible, and I'm not quite sure why.
I installed all the compat- packages required for a fresh oracle 9 install in a RHEL4 machine, but at the time of stating the STARTUP sentence, it gives me the next error:
CMCLI ERROR: OpenCommPort: connect failed with error 2. CMCLI ERROR: OpenCommPort: connect failed with error 2. CMCLI ERROR: OpenCommPort: connect failed with error 2. CMCLI ERROR: OpenCommPort: connect failed with error 2. CMCLI ERROR: OpenCommPort: connect failed with error 2.
After this error (repeated) it says something like: Cannot start an already running database.... But if i stat a shutdown sentence, it says that the instance has not been initialized....
I don't know whether i have to re-install all the oracle software in order to make a clean install in the new kernel version or not, i tried to apply a patch, and the oracle installer recognized the installation i had.
I think it might be because the original system is configured to work as a cluster, and i'm running it on only a virtual machine.
I am trying to add a not null constraint on a collection for testing. It not allowed null values as below
pointers@ORCL> declare 2 type test_type is table of number not null; 3 t_test test_type; 4 begin 5 t_test := test_type(2,4,null); --null is being added 6 dbms_output.put_line('Type variable is initialized and the first element value is '||t_test(1));
[code]....
But what I observed is, i can add, null elements by using 'EXTEND' routine though 'not null' constraint is used for that type.
The below is the example.
pointers@ORCL> ed Wrote file afiedt.buf 1 declare 2 type test_type is table of number not null; 3 t_test test_type; 4 begin 5 t_test := test_type(2,4);
I have problems in opening the database of the physical standby in read- write mode/ read only mode. I have a primary server which is running on 2 node RAC and the standby on a seperate single server being used as DR. I recently got this server and my aim was to isolate the standby server from primary server and perform few test. As it has never been tested even once.
Primary Database spec: (2 Node Rac on ASM) Oracle Version : 10.2.0.3.0 O/s : HP-UX B.11.23
alter database recover managed standby database cancel;
Database altered.
SQL> alter database open 2 ; alter database open * ERROR at line 1: ORA-16004: backup database requires recovery ORA-01152: file 1 was not restored from a sufficiently old backup ORA-01110: data file 1: '+DATA/dprod/datafile/system01.dbf'
Steps tried so far: Changed log_archive_dest_2 = DEFER on both the primary nodes
Standby :
startup nomount alter database mount standby database; alter database recover managed standby database disconnect; alter database recover managed standby database cancel; alter database open/readonly (tried both) Same error.
On Primary: SQL> select max(sequence#) from v$log_history;
Additional Information : There is a delay of 20 minutes before the logs get applied. which has been intentional set by team. Dataguard broker is not configured as well.
I receive the output from only one of the nested blocks:
bad PL/SQL procedure successfully completed.
SQL>
I understand that I don't need nested blocks for the example above, but this was just a condensed version of what I'm trying to do. I think nesting blocks will be easier to read and maintain, instead of having a huge CASE statement.
How can I execute only the nested block for which the condition is true and ignore the nested blocks that follow?
Are nested blocks not the correct answer here? Should I be looking at invoking procedures/functions instead?
I'm using Oracle Database 11g R2 for study purposes.Currently I'm learning about the DBCA clonic templates.I have an Oracle DB 11g R2 X86 running properly in Ms Windows XP Professional SP 3 X86, using DBCA I created the seed template file .dbc and the .CTL and .DBF files, later I copied those files to a server running Oracle DB 11g R2 X86 in Ms Windows 7 Ultimate X86. Again, using DBCA I successfully created the source database through the seed template file, everything was ok.
Now, I formatted my testing server and I installed Ms Windows 7 Ultimate X64 and Oracle Database 11g R2 X64. I copied the seed template file .dbc and the .CTL and .DBF files to "assistantsdbca emplates" directory. Well, I started DBCA to try create the source database and when the DBCA is creating and starting the Oracle instance it shows the errors:
i am testing a proc after tuning it but the problem is, it is taking a very very less time which it shouldn't. I know that it is because of the buffer cache and the shared pool. that why i need to clean the cache to retest it.
I cannot bounce the database as other schemas are part of it. so is there any way to clean the cache for that particular schema i.e bouncing any particular schema(i know that the term is not appropriate).
How to merge multiple rows into single row (but multiple columns) efficiently.
For example
IDVal IDDesc IdNum Id_Information_Type Attribute_1 Attribute_2 Attribute_3 Attribute_4 Attribute_5 23 asdc 1 Location USA NM ABQ Four Seasons 87106 23 asdc 1 Stats 2300 91.7 8.2 85432 23 asdc 1 Audit 1996 June 17 1200 65 affc 2 Location USA TX AUS Hilton 92305 65 affc 2 Stats 5510 42.7 46 9999 65 affc 2 Audit 1996 July 172 1100
where different attributes mean different thing for each Information_type. For example for Information_Type=Location
Attribute_1 means Country Attribute_2 means State and so on.
For example for Information_Type=Stats
Attribute_1 means Population Attribute_2 means American Ethnicity percentage and so on.
I want to create a view that shows like below:
IDVal IDDesc IDNum Country State City Hotel ZipCode Population American% Other% Area Audit Year AuditMonth Audit Type AuditTime 23 asdc 1 USA NM ABQ FourSeasons 87106 2300 91.7 46 85432 1996 June 17 1200 65 affc 2 USA TX AUS Hilton 92305 5510 42.7 46 9999 1996 July 172 1100
I am attempting to select back multiple values for a specific key on one row. See the example below. I have been able to use the sys_connect_by_path to combine the fields into one field but I am unable to assign them to fields of their own. See the example below
TABLE DETAILS: Policy id plan name 111 A Plan 111 B Plan 111 Z Plan 112 A Plan 112 Z Plan
My desired result is to be able to show the output as follows
Policy ID Plan_1 Plan_2 Plan_3 111 A Plan B Plan Z PLan 112 A Plan Z PLan
Once a year in the application we have a specific query that gets used a lot. It's an UPDATE that updates a single record in a single table with a few different datatypes, but the issue is happening with one of the VARCHAR2 fields. It updates one VARCHAR2(2000) and three VARCHAR2(4000) fields at the same time.
This year, 9 of the 95 times it was used resulted in one of the VARCHAR2(4000) fields as null in the database. The users would not want this field to be null and 5 of the 9 have told us they entered something (the form they're filling out is a research proposal and leaving this field empty would be pointless because it's part of the funding request, so they're not doing it). The application isn't doing it because it's not consistent. I've checked the application and these fields can't be nulled any other way.
We just found the issue so I looked back over the past years back to 2005. Last year it didn't happen at all. In 2010 it happened a handful of times. Some years there were even more times. It's not always the same field but it's always a VARCHAR2 of at least 2000 characters.
I have a lot more information but it's all just details (let me know if you need to know more). I'm wondering if there is a bug in 10g with these types of fields. I don't believe it's malicious behavior on an individual's part but I suppose that's always possible.
how to research something like this. I tried to get access to Oracle Support and the Knowledge Base I heard they have but it doesn't look like I can do that
1. I would like to know if any of the fields are empty I would like to eliminate the comma character from the string. 2. Can I replace the comma with a new line character and what character to be used in the syntax.
We are using Oracle 10g, on redhat linux (version not known to me).I'm running a Perl script that reads in data from Visual FoxPro tables, creates datafiles on the fly from these FP tables, then SQL-Loader runs and uploads all the data to matching tables in Oracle.
Problem:My upload runs, but I'm not getting the data in some of the fields that I should be. It is being uploaded to the wrong fields.
I had to add more columns to the Oracle tables in order to capture the data coming from FoxPro. These columns were/are in FoxPro. In the past, they were not needed in Oracle. I was using the FILLER command in the Control files to skip these. Now they are needed in Oracle. The new columns get added to the end of the table in Oracle, so they are not in the same exact order they are in FoxPro. It is these new columns that are not getting the correct data.
I've learned in the past that I need the fields/columns in the control files to match the order they are in the FoxPro tables from which the data comes. My Question is, Does the Oracle table column order have to match the Control file column order. Like so: FoxPro table column order = control file column order = Oracle table column order
The pipe separator needs to appear only when values found in addr1 or addr2 or addr3.
WITH address AS (SELECT 'Silver Arc Plaza,' addr1,'4th Floor, 20/1, New Palasia' addr2,'Indore' addr3 FROM dual UNION ALL SELECT 'Shop No. 1,Vishnu Priya Building,' addr1,'' addr2,'pune' addr3 FROM dual UNION ALL SELECT 'D/7, Siddhivinayak Nagari,' addr1,'Nr. Majura Gate, Ghod dod Road,' addr2,'' addr3 FROM dual UNION ALL SELECT '' addr1,'B 4, Gold Coin Complex,' addr2,'Ahmedabad' addr3 FROM dual UNION ALL SELECT '' addr1,'' addr2,'' addr3 FROM dual) select addr1||'|'||addr2||'|'||addr3 address from address;
