SQL & PL/SQL :: Get Exact Value From Table

Aug 16, 2010

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...

View 3 Replies


ADVERTISEMENT

How To Validate A Sum Of Some Records In A Table To Be Exact Value

Apr 9, 2008

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...)

View 3 Replies View Related

SQL & PL/SQL :: Possible To Get Exact Name Of Constraint From Any System Table

Sep 1, 2010

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 Related

How To Create Exact Copy Of A Table

Nov 24, 2012

I 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.

View 11 Replies View Related

Exact Space Occupied By A Table With LOB Column

Oct 18, 2013

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.

View 4 Replies View Related

SQL & PL/SQL :: How To Calculate Exact Age

Oct 21, 2011

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 Related

Get Exact Value Without Changing Datatype?

Nov 23, 2011

I 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 Related

SQL & PL/SQL :: Get Exact Matching String

Dec 11, 2012

When 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

View 5 Replies View Related

SQL & PL/SQL :: Exact Count Of Characters

Jan 7, 2013

How can I find out that exact count of '~'?

SELECT NVL(LENGTH('~~~~~~~~~~~~~~~~~')-LENGTH(REPLACE('~~~~~~~~~~~~~~~~~','~','')),0) result
FROM dual;

View 11 Replies View Related

How To See The Exact Special Character

Mar 18, 2013

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.

View 1 Replies View Related

How To Know Exact Time Of Row Modified And Deleted

Jul 16, 2010

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 ?

View 4 Replies View Related

SQL & PL/SQL :: Linking 2 Tables Without Exact Matches

Mar 4, 2011

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'.

View 8 Replies View Related

How To Set Exact Page Size For Query Output Format

Oct 2, 2012

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 ....

View 6 Replies View Related

How To Know Data Is Coming From Exact Table from Union All View

Feb 28, 2013

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

View 3 Replies View Related

JDeveloper, Java & XML :: Regular Expression For Exact Word And Occurrence

Mar 15, 2012

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.

View 2 Replies View Related

Forms :: Oracle 6i For Windows 7 64 Bit / Can't Find Exact Location From Google

Sep 20, 2012

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 Related

Backup & Recovery :: Exact Fetch Returns More Than Requested Number Of Row

Aug 24, 2011

I 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?

View 5 Replies View Related

Reports & Discoverer :: Continuous Pages Not Printing In Exact Pre-printed Area

Nov 29, 2011

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.

View 3 Replies View Related

Forms :: ORA-01422 / Exact Fetch Returns More Than Requested Number Of Rows

Aug 18, 2010

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].....

View 10 Replies View Related

Export/Import/SQL Loader :: Get Exact Path Where SQLLDR Got Installed In UNIX?

Nov 1, 2013

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 Related

How To Know Password (Exact Password Not Hascode) Of All Users As A Sysdba

May 28, 2008

How 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 Related

SQL & PL/SQL :: How To Find Whether Exact String Is Present / Not In Given String

Mar 14, 2013

I'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

View 3 Replies View Related

Server Administration :: Move Partitioned Table Between Table Spaces Of Different Block Size?

Apr 4, 2011

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?

View 14 Replies View Related

Server Utilities :: Geometric Data From Text To Table And Wrong CTL Upload Into Table

Jul 11, 2013

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]....

View 24 Replies View Related

SQL & PL/SQL :: Primary Constraint On Table Affecting Procedure To Insert Rest Of Rows In Table?

Jun 12, 2012

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]...

View 2 Replies View Related

SQL & PL/SQL :: Interface Table Compared With Normal Table - Result Dumped If Match Found

Aug 17, 2012

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]....

View 7 Replies View Related

Creating SQL Script That Can Update Info From One Table In Dbase1 To Another Table In Dbase2?

May 16, 2013

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 Related

SQL & PL/SQL :: Dynamic Function - Create Physical Table And Return Table Name In Out Variable

Aug 30, 2011

I 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]......

View 2 Replies View Related

SQL & PL/SQL :: Load 10 Million Rows In Table From Another Table Based On Multiple Joins

Sep 24, 2010

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 Related

SQL & PL/SQL :: Create Complete Hierarchical Table From Table With Only Two Columns - Parent And Child

Aug 13, 2012

We 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.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved