i executed the below code and found that output should be 5 but it shows 7(always else part). it is not comparing string in case.
declare
check_in_date date;
var_day varchar2(11);
rate_id number;
[code]....
The Ename SMITH should appear as Smith.
View 1 Replies View RelatedMy DB version is
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
I'm getting this error while executing a package.But this is unpredictable because sometimes it's coming and sometimes it's not. Everytime I'm passing the value as 'ALERT' for the transaction name. Sometimes it's successful and sometimes it's throwing ORA-06592
CASE UPPER(IC_TRANSACTION_NAME)
WHEN 'ALERT' THEN
SELECT A.FACILITY_ID INTO VN_FACILITY_ID FROM ALERT A
WHERE A.ALERT_ID = IN_PARENT_NODE_ID;
INSERT INTO TRANSACTION_HISTORY (TXN_HISTORY_ID,
[code]....
I have a requirement to get the count of those records whose department is not changed since they have joined the organization.
Script:
CREATE TABLE ddumps(orgid NUMBER, orgdate DATE, orgdept VARCHAR2(10))
/
INSERT INTO ddumps VALUES(1,'01-JAN-1999','ORG1')
/
INSERT INTO ddumps VALUES(1,'01-JAN-2000','ORG2')
/
INSERT INTO ddumps VALUES(1,'01-JAN-2001','ORG2')
/
INSERT INTO ddumps VALUES(2,'01-JAN-1999','ORG1')
/
INSERT INTO ddumps VALUES(2,'01-JAN-2000','ORG1')
/
INSERT INTO ddumps VALUES(2,'01-JAN-2001','ORG1')
ORGID ORGDATE ORGDEPT
1 1/1/1999 ORG1
1 1/1/2000 ORG2
1 1/1/2001 ORG2
2 1/1/1999 ORG1
2 1/1/2000 ORG1
2 1/1/2001 ORG1
since the orgid 1 has changed the dept from org1 to org2 I do not want this to be appeared in the final count. Results should only include the orgid 2 since it didn't changed any dept.
I have 2 strings that I want to compare for example
string1 = 'ABC~AB/10/1234'
string2 = 'ABC~AB/10/1234~.....'
There could be anything after the 2nd ~ in string 2 is there a easy way of trimming string2 to the first 14 Characters? Or do I have to find the 2nd instance of ~ and then remove everything after (and including) that?
I have two databases.
one is "ora" it is a 8i version
2nd is "orcl" it is a 11g version
"Oracle" is the my local database. i wrote following program for comparing the row by row data in both the tables.
Q)Is it BEST practice? If not let me know the best practice to compare data in tables?
Q) If am not using the order by clause its giving me wrong output even though both the data tables has same data. WHY?
DECLARE
v_emptest1 EMP_TEST1%ROWTYPE;
v_emptest2 EMP_TEST1@ora%ROWTYPE;
v_suc_flg NUMBER := 0;
v_cnt1 NUMBER:=0;
[code].......
How to select the transactions out of the database that occurred within 70 seconds of each other. The toll_date field is a TIMESTAMP field.
Problem is, I seem to only get transactions that occurred within 70 minutes of each other. On the timestamp field I break the math down into the seconds in a day and I add 70. I then subtract that value and add that value to the timestamp and I should get anything between those values right?
SELECT Acct_ID, Ln, Tag_Rd, COUNT(*)
FROM (
SELECT T1.Account_ID Acct_ID, T1.Tag_Read Tag_Rd, T1.Revenue_Date Rev_Date, T1.Toll_Date, T1.Lane_ID, T1.Plaza, T1.Lane Ln, T2.Toll_Date, T2.Plaza, T2.Lane
FROM Toll T1
JOIN Toll T2
[code]......
Recently i have started working on PLSQL coding. I have a requirement. Either error or un-processed record count is 90% of to be processed records then the script has to fail. Currently I am having a situation where error count is 1 and total to be processed is also 1.
in the below
V_ERR is error count
V_UPS is un processed count
V_PROCESSED_COUNT is total to be processed.
I am expecting PASS result but it is giving FAIL.
DECLARE
V_ERR NUMBER:=0;
V_UPS NUMBER:=0;
V_PROCESSED_COUNT NUMBER:=0;
NIN NUMBER;
BEGIN
V_PROCESSED_COUNT:=1;
[Code] .......
I want to do a comparision for the missing rows between two diffrent tables
TBL1 and TBL2 both with the same structure but with diffrent data some data is identical. though my data is huge i wanted to make sure the technique i am using
As part of our project, we need to perform table comparisons in two different databases. I am currently looking for various options to accomplish this.
One of them is doing minus operation between these two tables. Also, i have looked at the data compare option in toad utility.
I am working in form 6i, database 9i. I have datablock on table t1.
table t1: name(varchar2), date(varchar2)
datablock: name(varchar2), date(varchar2)[i have insert date with time stamp]
for date column, i am inserting date with time stamp.While querying data, user just enters only date(no time stamp), i should be able to query data. I tried in data block where condition
WHERE: substr(date,1,11)=nvl(:block.date,substr(date,1,11)
i am not able to retrieve data through date, through name it's working fine.how to retrieve data by only entering date (without time stamp)
Managed to confuse myself significantly. I essentially want to write a query to determine when two counts are the same using a subquery.
Eg:
R(x,y)
Select count(x)
from R
group by x;
Then I want to run another query to determine which x's have the same count value and output these corresponding x's.
I have a SQL query which joins several large tables (so indexes matter here) from Oracle database. In the where condition I use IS NULL with one of the date field values. Query takes 40 sec to run and if I comment this one line...it takes 1 sec to run. This date field is an index on the table and I learn that --
1. IS NOT NULL in where clause uses an index
2. IS NULL in where clause does not use an index
Is there any work around to make the query faster...other than changing all the NULL date values in the table to some string. In other words can I force it to use the index.
SF at oracle.com/technetwork/issue-archive/o53plsql-083350.html states that you can compare two database tables (of the same structure) by defining a nested table type (using %ROWTYPE) and two NT variables of that type, and loading the contents of each table into its respective NT variable, before comparing them using the = operator. Having read the Oracle documentation which states that you can only compare NTs for equality if they don't contain record types, I was surprised to read this, but figured I would try it because I must be misunderstanding SF, but it didn't work.
SCOTT@ORCL> create table empcopy3 as select * from emp;
Table created.
declare
type emp_ntt is table of emp%rowtype;
emp_nt1 emp_ntt;
[Code]....
But SF goes on to say he timed the execution of his NT equality method, comparing it with a SQL-only equivalent, and so I must be missing something. My understanding is that using %ROWTYPE declares a record type.
I have to compare a string entered in a form with a series of English strings in back-end PL/SQL( using LIKE operator )
But it so happens, the string entered in the form is specific to the language used in the country. for ex. in Dutch, it is entered in Dutch language.
So on comparing, it fails as the PL/SQL compares it with English Strings.
i am fetching one record based on date but not able to fetch the data.
in my table pdate column is there with date datatype and value is in that is:15-OCT-12 so i am fetching empid based on dates.
select empid from masterprocessdailydata where pdate=to_date('15/OCT/12','dd/MM/yy'); but not getting the empid.
I am trying to compare 2 char columns using trim to avoid space padding
CREATE TABLE TRIAL_A
(ABC CHAR(6));
INSERT ALL
[Code]....
I have got a table having column a, b , c, d, e and some values are stored in it. e.g. a, c and e have 'Y' and b and d have 'N' value in it.
How can i count with one query , how many columns have 'Y' and how many have 'N'.
cursor is one solution.
I am having some issues in creating the following trigger. It gets created but with compilation errors.
create or replace trigger min_max_update_trg
after insert on tidlrrep
referencing new as n
for each row
declare
bltMaxSize number;
repMaxSize number;
[code].....
when i tried without the comparison if repMaxSize < bltMaxSize then it got created successfully.
the problem is: 2 tables - one with 2 million records, and the other with 8000 records.
i need to compare for each record in a table if there's a similar string on the other table.
i've created a procedure that does the following:
opens the first cursor (select col1,col2,col3,col4... from table 1)
loop
opens second cursor (select col1 from table 2)
loop
if utl_match(col1, table2.col1) > 80 then
insert col1,col2,col3,col4... into tableX
end if
close second cursor
close first cursor
the thing is that this procedure takes forever to end...about 8 days.
is it because im using the utl_match function? is there a way to speed this up?
I'm using oracle 10g.I have a table with 4 columns
main_group-----id--------start_date------------end_date
M1-------------1---------07FEB11---------------10FEB11
M1-------------2---------09FEB11---------------11FEB11
M1-------------3---------10FEB11---------------12FEB11
M1-------------4---------13FEB11---------------16FEB11
M2-------------5---------18FEB11---------------21FEB11
M2-------------6---------19FEB11---------------24FEB11
M2-------------7---------26FEB11---------------27FEB11
i need to group the id's which are having overlapping dates and the output should be
main_group-----id--------start_date------------end_date
M1-------------1---------07FEB11---------------10FEB11------G1
M1-------------2---------09FEB11---------------11FEB11------G1
M1-------------3---------10FEB11---------------12FEB11------G1
M1-------------4---------13FEB11---------------16FEB11------G2
M2-------------5---------18FEB11---------------21FEB11------G3
M2-------------6---------19FEB11---------------24FEB11------G3
M2-------------7---------26FEB11---------------27FEB11------G4
I can give you the logic first i'll sort the start_date(already sorted in given example), then i'll compare the 2'nd id start date with 1'st id end date if it is less than the 1'st id end date, which means overlapping is there, then i'll group those 2 id's in to same group if not group them into 2 different groups.
I am about to compare Indexes and PK's(Constraint) of 3 instances.
What DBA table/s should i use best? The comparison is done to a specific schema only.
PS:Which is better dba_ind_columns or dba_objects?
I am new to oracle, I have request to build a query,
we have table that generates data from 7am to 20pm for eavery hour it generates 4 rows and has 43 session values as 43 columns.
Now i want to find for every hour which is the hights session value at what time. in one hour it runs four times like 7, 7:15, 7:30 and 7:45 and each row has date, time and 43 session columns in table...
I'm looking to see if there's a solution to my problem that I can use within the context of my business application interface into an Oracle RDMS. I have access to write custom SQL statements and functions, but I am NOT able to create stored procedures using the interface I have.
The challenge I am having is comparing date ranges. I have a table containing two columns labelled START TS TIME and END TS TIME, both of type 'Date'. I have figured out how to query each row against a given Next Session Start and Next Session End and determine if each row overlaps that row.
I need a procedure that will be recursive: that is, set Next Session Start and Next Session End to START TS TIME and END TS TIME of the first row, compare all rows against it, then set Next Session Start and Next Session End to the next row, compare all rows, ... for all rows in the table. I want to know what the maximum number of matches is (i.e. the most time periods that overlap).
If I could use a stored procedure I could complete this query easily. Is there other techniques (i.e. functions) available to leverage in order compare each row of date ranges against ALL rows in the same table?
I have master-detail form I would like to update the emp1 table on the base of current value of dept table deptno and emp table ename .
update emp1
set ename = :emp.ename
where ename =
(select ename from emp e
where e.ename=:emp.ename
and e.deptno=:dept.deptno);
I try the above query but it did not able to update emp1 table.
we have a two different databases at different locations and on different servers, like one in our company with SID='A' and remote database with SID='B', We have recently implemented new module in database 'A' by creating lots of tables, functions, indexes, sequences, synonyms etc and now we need to install this on 'B' but the problem is we have not documented which tables we created, first we need to create a DB link between these two and then we need a tool to compare what are all the tables that we need to create in database 'B' , is there a tool for doing all this.
View 7 Replies View RelatedI have two schemas with 149 tables in each schema, what I need to do is to prove that the content(data) between the two schemas is identical. I know that all the table names between the two schemas are the same, just need to prove that there is no difference in data.
So the query needs to prove that Schema A content = Schema B content
I know I cant do a simple select from Schema A.tab1 minus select Schema B.tab1 but since there are 159 tables, I am not sure if this is an efficient way of doing it.
Oracle v11.1 Standard edition. I am trying to use TRIM function around the columns which are used for outer join and it's not working. However, if I use ANSI syntax, it works. Please check the SQL below.Is there any way, I can use old syntax and TRIM and get the outer join to work?I know, I can fix/trim the data in the tables, but for some reason (need to keep the data exactly same as we received it), that is not possible here.
SQL>SQL> desc tabaName Null?
Type----------------------------------------- -------- ----------------------------COL1
VARCHAR2(20)SQL> desc tabbName Null?
Type----------------------------------------- -------- ----------------------------COL1
VARCHAR2(20)SQL> select a.col1, b.col1 from taba a, tabb b where a.col1 = b.col1 ;
no rows selectedSQL> select a.col1, b.col1 from taba a, tabb b where trim(a.col1) = trim(b.col1) ;
COL1 COL1
-------------------- --------------------
A AC CD
DSQL> select a.col1, b.col1 from taba a, tabb b where trim(a.col1) (+)= trim(b.col1) ;
select a.col1, b.col1 from taba a, tabb b where trim(a.col1) (+)= trim(b.col1)
*ERROR at line 1:ORA-00920: invalid relational operator
SQL> select a.col1, b.col1 from taba a, tabb b where trim(a.col1) = trim(b.col1) (+);
select a.col1, b.col1 from taba a, tabb b where trim(a.col1) = trim(b.col1)
*ERROR at line 1:ORA-00933: SQL command not properly ended
SQL> select a.col1, b.col1 from taba a left outer join tabb b on trim(a.col1) = trim(b.col1) ;
COL1 COL1
-------------------- --------------------
A AC CD DB
SQL> select a.col1, b.col1 from taba a right outer join tabb b on trim(a.col1) = trim(b.col1) ;
COL1 COL1
-------------------- --------------------
A AC CD D E
I have a SP for comparing 80 diff column values in 8 table pairs and it is taking a huge lot of time to process as I have to process around 10k records.
View 5 Replies View RelatedI need to compare data of .csv file with the SELECT query result or with any table data.
Is there any possibilities in ORACLE to fulfill this requirement?