I want to writa a query to fetch the INCOME TAX RATE from the given table, my calculated gross salary amount is 594000
FromAMT ToAMT Rate
------- ------ ----
300001 350000 0.75
350001 400000 1.50
400001 450000 2.5
450001 550000 3.5
5500001 650000 4.50
and so on...
How to validate a sum of some records in a table to be exact value?
I want to guarantee that sum(val_column)=100 at database level. Check constraints out of question. Trigger before update statement disable any update to that column when incrementing one row and decrement another... (unless with for all...)
There was one constraint define on the column of table that the value of column should be in range of 100 to 200. Some one has deleted the constraint. Is it possible to get the exact name of constraint from any system table.
View 7 Replies View RelatedI must admit my "google skills" have failed me and it is possible that I might be missing something obvious here but allow me to explain.
If I have a table that is populated/accessed by a typical OLTP application such that over time the data in the table may become "fragmented".
I know that is a very controversial term I used but that is not the question here. A simple way to describe the state of the table might be that the table has always been populated with lots of small INSERTs, modified with lots of small UPDATEs and data has been deleted in small DELETEs.
This has meant that the data is neither closely stored in data blocks nor is in any particular order (so there are some empty blocks under HWM).
Now my question is how do I create exact copy of this table and its indexes AS THEY EXIST AT THE MOMENT that includes
1) its data
2) its constraints/indexes etc.
3) its storage parameters
4) data stored in EXACTLY same manner as in original table.
To avoid complications, the table in question is just a normal heap table without any partitioning involved. While
CREATE TABLE AS SELECT (or CREATE TABLE followed by INSERT) will take care of points (1) to (3) above, it will not achieve point (4) above.
I need to check the exact amount of space used (in bytes or MB) by a table which is having a BLOB column.I tried the following query but it is not giving the proper usage.
select segment_name , sum(bytes)from dba_extentswhere segment_type='TABLE'and segment_name in ('TEST_CLOB','TEST_BLOB','TEST_CLOB_ADV','TEST_BLOB_ADV') group by segment_name; I even tried the following stored procedure create or replace procedure sp_get_table_size (p_table_name varchar2)as l_segment_name varchar2(30); l_segment_size_blocks number; l_segment_size_bytes number; l_used_blocks number; l_used_bytes number; l_expired_blocks number; l_expired_bytes number; l_unexpired_blocks number; l_unexpired_bytes number; begin select
[code].......
But it is giving the error
Error starting at line 298 in command:exec sp_get_table_size ('TEST_CLOB_ADV')Error report:ORA-03213: Invalid Lob Segment Name for DBMS_SPACE packageORA-06512: at "SYS.DBMS_SPACE", line 210ORA-06512: at "SYS.SP_GET_TABLE_SIZE", line 20ORA-06512: at line 103213. 00000 - "Invalid Lob Segment Name for DBMS_SPACE package"*Cause: The Lob Segment specified in the DBMS_SPACE operation does not exist.
*Action: Fix the Segment Specification Although the LOB section is specified in create table syntax.
How to calculate exact age for example my date of birth is 10-04-1972 and today current date is 21-10-2011 so i want to calculate age how many years, how many months and how many days.
View 27 Replies View RelatedI created a table of Number(20,4) column. I inserted an amount value 999999999999999.5555 but this value is rounded off to 1000000000000000.0000 automatically in Oracle. How to avoid this? I tried for less number of digits and I am getting the exact value. Is there any way to get the exact value without changing the datatype?
View 1 Replies View RelatedWhen I pass the input as 'micky', then i should get the count of records as 4. I tried to use REGEXP_LIKE, but could not get the result.
Test Case:
DROP TABLE test1;
CREATE TABLE test1( pattern_series VARCHAR2(30));
INSERT INTO test1 VALUES ('qa_micky1');
INSERT INTO test1 VALUES ('qa_micky2');
[code].....
-- I should get the output as 4 not 8
How can I find out that exact count of '~'?
SELECT NVL(LENGTH('~~~~~~~~~~~~~~~~~')-LENGTH(REPLACE('~~~~~~~~~~~~~~~~~','~','')),0) result
FROM dual;
I am using Release 10.2.0.1.0 version of oracle. I am getting a special character, but i suspect it as any other language character.
Its appearing in my 'TOAD editor/Sql prompt editor' as 'A?'. but when i am selecting it from the table using below query, its giving 'No rows Returned'.
select id from tab1 where id like 'A%';
How can i be able to see the exact character or which editor will enable me to see the character?
I think sql developer GUI might be able to show the same but i dont have sql developer with me.
1)How can i know that in a table when i modified a row or deleted a row and which row i inserted when i want to know the particular time
can it is possiable if possiable then tell me how.
2)Is there any difference between 9i merge and 10g merge command ?
I have two tables as per attachement - TABLE_A has Vehicle details while TABLE_B has Address details:
I am trying to build a query in order to link the Vehicle Details with their Address Details.
I would require an SQL that links records in TABLE_A with records in TABLE_B when group_number are the same; however links with group_number '999' (for the same customer_number) if there are no exact matches.
SELECT A.*,B.*
FROM TABKE_A A, TABLE_B B
WHERE ((A.CUSTOMER_NUMBER = B.CUSTOMER_NUMBER AND A.GROUP_NUMBER = B.GROUP_NUMBER)
OR (A.CUSTOMER_NUMBER = B.CUSTOMER_NUMBER AND A.GROUP_NUMBER = '999'))
The only problem with such query is that record in TABLE_A with group_number '456' will return 2 times. One with address having group_number '456' and one with group_number '999'.
I have following shell script :-
In our testing DB local Server , we are using following script ... We are connecting Via putty ...
CODEexport ORACLE_SID=testdb
sqlplus /nolog <<eof
conn sys/sys as sysdba
[Code].....
QUOTE 1. How can i set pagesize to get good format Our problem is output format is not good .. we set set pagesize from 0 to 120 .. no improvement .. " Again Re-Installed VMware tools also ... no improvbement ....
2. We are planning to set alert message if "Archive destination crossed 60 % Script automatically will generate alert message via mobile or mail.. JUst we will configure crontab ....
I am using view AB_EV_VIEW created with UNION ALL of 10 tables (ex. AB_EV_1, AB_EV_2.... )
I want to know the data comming in select * from AB_EV_VIEW is from which table ?
Suppose AB_EV_1 is having column ID, NAME
sql> select * from AB_EV_VIEW
100, A1
200, A2
300, A3
400, A4
500, A5
I need to search a specific pattern from a source code. In word, I need to check whether "getCode" has been called or not, for all the string inside double-quote("). Following are sample code lines -
1.->if(val==23){ month_desc = "a sample data"; }
2.->if(val==23){ month_desc = getCode("a sample data"); }
3.->if(val==23){ month_desc = "a " + getCode("sample data"); }
4.->if(val==23){ month_desc = getCode("sample data"); var2="sample data2";}
Now, expression should be such that it will return true during check for 1, 3 and 4, although, for 3 & 4 getCode has been called for part of the String.
i want to download form 6i and report 6i for window 7 64bit. from where i can download it. i can't find exact location from google.
View 6 Replies View RelatedI have perform a failover and encounters the following problem.
ORA-01422: exact fetch returns more than requested number of rows
initially primary db: chicago
physical standby db: boston
after failover
primary db:boston
physical standby: NA
after recreating physical standby
primary db:boston
physical standby: chicago
over at chicago (new standby)
log_archive_dest_3=E:chicago_arc
over at boston (new primary)
log_archive_dest_3=E:oston_arc
over at boston when I connect to both boston as target db and recovery catalog
RMAN> list archivelog like 'E:CHICAGO_ARC\%';
List of Archived Log Copies
Key Thrd Seq S Low Time Name
------- ---- ------- - --------- ----
252690 1 10 A 22-AUG-11 E:CHICAGO_ARC10_759842424_1.ARC
over at chicago when I connect to both chicago as target db and recovery catalog
RMAN> list archivelog like 'E:CHICAGO_ARC\%';
List of Archived Log Copies
Key Thrd Seq S Low Time Name
------- ---- ------- - --------- ----
252690 1 10 A 22-AUG-11 E:CHICAGO_ARC10_759842424_1.ARC
however at E:chicago_arc when I do a os check
C:Documents and SettingsAdministrator>dir /w E:chicago_arc
Volume in drive E is New Volume
Volume Serial Number is C874-DD6D
Directory of E:chicago_arc
[.] [..] 100_759410562_1.ARC
101_759410562_1.ARC 102_759410562_1.ARC 103_759410562_1.ARC
[Code]....
26 File(s) 9,077,989 bytes
2 Dir(s) 2,650,337,280 bytes free
there's more files than indicated by rman
also at both chicago and boston
I have the following error:
RMAN> debug on;
RMAN-03036: Debugging set to level=9, types=ALL
RMAN> report obsolete;
RMAN-06524: RMAN retention policy will be applied to the command
RMAN-06511: RMAN retention policy is set to redundancy 1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of report command at 08/24/2011 23:12:15
RMAN-06004: ORACLE error from recovery catalog database: ORA-01422: exact fetch returns more than requested number of rows
RMAN> debug off;
Debugging turned off
here's the trace files I logged
DBGRCVMAN: EXITING listBackup with exception: ORA-01422: exact fetch returns more than requested number of rows
DBGMISC: krmicomp: error 6004 signalled during compilation [23:12:15.358]
DBGMISC: ENTERED krmkmrsr [23:12:15.358]
[Code]...
How should I resolve this issue?
I have a Invoice Report, i will give the parameters.
From_Inv_No:
To_Inv_No:
From_Inv_Date:
To_Inv_Date:
so i will give 10 invoices with dates in the above parameters to generate the report, actually the report prints in a pre-printed sheet, well it prints the first page correctly but the second page and continuous pages not printing in the exact pre-printed area. example see below;
(Page2)
506176
28/11/2011
(Preprinted text)INVOICE NO :
INVOICE DATE:
how to give spaces between pages actually every pages it should print 3 to 4 inches gap between the first page but it prints just one inch above the preprinted area. how to make it print without gaps. Additional Information: Preprinted sheet Height 17 inches.
Paper Layout Width 8.5
Height 12
Character Mode
Report Width 80
Report Height 72
for header section, main and trailer sections above readings only given.
ORA-01422: exact fetch returns more than requested number of rows.
I receive this error because i tried to introduce the below coding in a post query of the block.
begin
select supplier_cd into :fin_ex_rev_head.vendor_code
from fin_ex_rev_receipts
where receipt_date
between :keyblock.receipt_date
[code].....
How to get the exact path where SQLLDR got installed in UNIX. I am getting a path related issue while executing SQLDR unix command in Oracle
View 3 Replies View RelatedHow to know the password(Exact password not hascode) of all users as a sysdba in Oracle 9i or 10g.How to convert hashcode password to Actual password.
View 1 Replies View RelatedI'm facing some problem even after using INSTR function in Oracle.The problem is I have written the logic in the PL/SQL block which appends all the values fetched in a loop on the basis of whether the string is present or not.
For ex:
The first value fetched from the select query first is ABCDEFG which gets appended to a variable
The next value fetched is AB even this has to be appended to the variable since this exactly doesn't match with ABCDEFG.
The next value fetched is BCDEF even this has to be appended to the variable since this exactly doesn't match with ABCDEFG.
The third Value fetched is ABCDEFG this will not get appended presently according to the logic which is correct.
writing that piece of code to append the value fetched which doesn't exactly match with the existing string
I was about to move some tables from one table space to another but it seems it is not possible to move partitioned tables between table spaces of different block sizes.
So far the only option I have is to export and then import back the data.
know if there is any way to move a partitioned table between table spaces of different block size?
I have a requirement to import text files which are generated from 3d modelling software xsteel where it records all geometric information and i want to import this information into oracle table.
CREATE TABLE dstv_head ( wo_no VARCHAR2(12),struct VARCHAR2(12),rev_no NUMBER,
mark VARCHAR2(12),pos VARCHAR2(12),grade VARCHAR2(12),qty NUMBER,PROFILE VARCHAR2(24),TYPE VARCHAR2(12),
len NUMBER,width_web NUMBER,width_bottom NUMBER,flange_thk NUMBER,web_thk NUMBER,radius NUMBER,kgm NUMBER,
kgm1 NUMBER,kgm2 NUMBER,bevel_plus NUMBER,bevel_minus NUMBER,holes_yn VARCHAR2(1),holes_v_yn VARCHAR2(1),
hole_x_dim NUMBER,hole_y_dim NUMBER,hole_dia NUMBER,no_of_holes NUMBER)
-- All the data which has to go under specific field for example **9005.nc1 will go into wo_no field, 1239401A will go under struct.
ST
** 9005.nc1 --WO_NO
1239401A - STRUCT
1 -REV_NO
9005 -MARK
9005 --POS
S275JR --GRADE
2 --QTY
[code]....
primary key constraint on transaction_dtl_bk is affecting the insertion of next correct rows.
CREATE OR REPLACE PROCEDURE NP_DB.san_po_nt_wnpg_1 (
dt DATE
)
IS
v_sql_error VARCHAR2 (100); -- added by sanjiv
v_sqlcode VARCHAR2 (100); ---- added by sanjiv added by sanjiv
[code]...
Oracle 10g, Windows XP
There is an interface table and there is an normal transcational table..interface table is being compared with normal table and if match found the result is dumped into another normal table.
I am using two cursors one is to query the interface table and in a for loop pass the results to the second cursor..The interface table is having 5000 + rows and the transcation table is having more than 3.7 millions ..and the program is taking lots of time to execute..took almost 35-45 minutes..
create table x_interface /* INterface table */ -- 5000 + rows
( name varchar2(80), addr_line1 varchar2(35), addr_line2 varchar2(35), addr_line3 varchar2(35),
addr_line4 varchar2(35), addr_line5 varchar2(35), addr_line6 varchar2(35), suffix varchar2(35),
city varchar2(15), state varchar2(10), zcode varchar2(10))
[code]....
creating an sql script that can update info from one table in dbase1 to another table in dbase2 that has the same columns and if possible insert date and time in one column when the synchronized is done?
View 3 Replies View RelatedI am trying to execute dynamic SQL in Stored Function and I don't know how to do this.
Explanation:
In the function I am calling pr_createtab is procedure which will create a physical table and return the table name in the out variable v_tbl_nm.
I need to query on this dynamic table and return the result as return result. But i am not able to do it.
Here T_web_loylty_report_table is a type.
CREATE OR REPLACE function CDW_DSS.f_ReturnTable(i_mrkt_id in number, i_cmpgn_year in number)
return T_web_loylty_report_table is
v_tbl_nm varchar2(50);
i_cntry_cd varchar2(20);
v_sql_str varchar2(32567);
[code]......
We have to load 10 million rows in a table from another table based on the multiple joins. How much tablespace size we allocate to the table and for performance point of view how much should be the SGA size.
View 11 Replies View RelatedWe have a table in the client database that has two columns - column parent and column child. The whole hierarchy of DB table dependencies is held in this table.If Report 1 is dependent on Table A and Table A in turn is dependent on two tables Table M and Table N. Table N is dependent on table Z it will appear in the db table as,
Hierarchy Table
Parent Child
Report1Table A
Table ATable M
Table ATable N
Table NTable Z
Requirement :
From the above structure, we need to build a table which will hold the complete hierarchy by breaking it into multiple columns.The o/p should look like this
-ParentChild 1Child 2 Child 3
-Report1Table ATable M
-Report1Table ATable N Table Z
Child 1, Child 2, Child 3 ....and so on are columns.The number of tables and the no of hierarchical relationships are dynamic.
SQL Statements to create hierarchy table:
create table hierarchy (parent varchar2(20), child varchar2(20));
insert into hierarchy values ('Report1','Table A');
insert into hierarchy values ('Report1','Table B');
insert into hierarchy values ('Table A','Table M');
insert into hierarchy values ('Table B','Table N');
insert into hierarchy values ('Report2','Table P');
insert into hierarchy values ('Table M','Table X');
insert into hierarchy values ('Table N','Table Y');
insert into hierarchy values ('Report X','Table Z');
Approached already tried :
1) Using indentation : select lpad(' ',20*(level-1)) || to_char(child) P from hierarchy connect_by start with parent='Report1' connect by prior child=parent;
2)Using connect by path function :
select *
from (select parent,child,level,connect_by_isleaf as leaf, sys_connect_by_path(child,'/') as path
from hierarchy start with parent='Report1'
connect by prior child =parent) a where Leaf not in (0);
Both the approaches give the information but the hierarchy data appears in a single column.Ideally we would like data at each level to appear in a different column.