Search results for query: *

Your select is getting error messages because of the final where clause. the MSF620 alias is not recognized because it is inside an in-line view. You are going to have to rework this query. WHERE MSF620.Equip_No = WO_Orig.Equip_No AND MSF620.Work_Order < WO_Orig.Work_Order AND...

I did the 8i certification. I took the following exams: SQL & PL/SQL Architecture & Administration Backup & Recover Performance Tuning Network Administration For 9i you have to take 4 (2 for OCA, 2 for OCP) exams. All exams are taken on-line at authorized testing centers. OCA exams...

Check out this link at oracle and get the info straight from the horses mouth: http://www.oracle.com/education/certification/index.html?content.html In general, you are required to take one instructor led class. The preparation for the rest of the OCP exams can be self-study. That is what I...

Did you create the service for your new instance with oradim? Also try deleting the password file and then create it again, giving the full path to the file, instead of relying on the default location. Aryeh Keefe

You have function calls in the not exists sub-query this will usually result in full table scans. You might consider changing the "and sysdate between srts.eff_start_dt and nvl(srts.eff_end_dt, sysdate) " condition in the sub-query to "and sysdate between srts.eff_start_dt and srts.eff_end_dt...

Is your password file in the default location? oracle_home/ora81/database Aryeh Keefe

try this update table_to_be_updated a set (last_amt, deposit_amt) = ( select LAST_AMT, DEPOSIT_AMT from source_table b where a.acc_no = b.acc_no ) where exists ( select LAST_AMT, DEPOSIT_AMT from source_table c where a.acc_no = c.acc_no and ( a.last_amt <> c.last_amt or...

Do you mean that the LAST_AMT and DEPOSIT_AMT fields have to match the values in another table and that you only want to update those fields when they are different from the fields that you are checking against? Aryeh Keefe

You might check the inserts and update statements. If there are things like union, distinct, order by in them, they are causing sorting. The problem might be one of tuning, not how much space you have in the temp tablespace. What I've done in similar situations is to split up the updates into...

Are you getting an error message? It would be helpful if you would post it. When you say that it isn't working, could you give us some more specifics? By the way, in general I load everything into a holding table and then do data manipulations, such as type conversions, data validation, and...

I've gotten this error message when sql*loader encountered nulls in the data. Try using nullif in the control file <column_name> nullif <column_name>=blanks Aryeh Keefe

...You are correct about needing to recompile. I'm just saying that in this situation it would be wise to do it explicitly. When doing select * from two different tables, if someone adds a column to table 1, but not to table2, then the procedure won't recompile successfully. If the columns are...

It's not just a question of which is quicker. What happens when you change on of the tables by adding, dropping, or modifying a column. If the select .. union is just an one-time deal, no problem. If it's going to become a part of a stored procedure, it's not a good practice. Aryeh Keefe

When the index is local, then it exists for each partition. When you drop a partition, the index remains for each of the remaining partitions. Aryeh Keefe

It sounds like you aren't using bind variables, so that every iteration of the loop is a new cursor. If you use bind variables then it is possible to have many iterations but only one cursor. Here is an example without bind variables in pseudo code: for i from 1..100 loop insert into sam...

...I can then do any data manipulations that I want; constraint checking, generate log files of bad records, prior to the final clean insert into the target table. I only like to do direct loads with sql*loader, and clean inserts into the target table. This is my personal preference. Aryeh Keefe

Just remove the product.prodprice from the select and group by sections of your query. That is what is causing multiple rows, where you want only one.

Have you had any large data loads between the time the database was running well and now? Aryeh Keefe

If you have the resources available, then you shouldn't let a 6gb rollback segment intimidate you. However, it is possible to do things your way simply by looping rhrough the cursor and issuing commit statements every time the counter reaches a multiple of 1000, 10,000, or whatever. This cuts...

Try this. Aryeh Keefe select orion_tank, OrionCrude, prop_modified_value, DayInserted, SEQ5_CRDINV.nextval, sysdate, user from ( select a.orion_tank, b.OrionCrude, a.prop_modified_value, a.DayInserted, sysdate, user from ps_staging_data a, ps_crude_map b where a.Orion_tank = b.OrionTank and...