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


ADVERTISEMENT

How To Count Distinct Characters From A String

Sep 26, 2005

how to count different characters from a string ....

View 7 Replies View Related

SQL & PL/SQL :: Count Repeated Characters In String

Mar 9, 2011

Is there any function to count number of specific character inside string. For example :

my string is 'ABCEF AYZA'

I want to calculate how many 'A' exist in my string. result should be = 3

View 17 Replies View Related

SQL & PL/SQL :: Removing Special Characters And Get Desired Characters From Column Values

Jul 23, 2013

create table test
(
name varchar2(50),
descd varchar2(50)
)
insert into test values ('kethlin','da,dad!tyerx');
insert into test values ('tauwatson','#$dfegr');
insert into test values ('jennybrown','fsa!!trtw$ fda');
insert into test values ('tauwatson','#$dfegr ,try');

how do I get the first three characters and last three characters from name field and remove all the junk characters from descd field?

so my o/p be like;

Quote:('ketlin','dadadtyerx')
('tauson','dfegr')
('jenown','fsatrtw fda')
('tauson','dfegr try')

View 6 Replies View Related

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

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

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

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

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

SQL & PL/SQL :: Difference Between Count(*) And Count(1)?

Nov 16, 2009

When we execute select count(*) from table_name it returns the number of rows.

What does count(1) do? What does 1 signifies over here? Is this same as count(*) as it gives the same result on execution?

View 13 Replies View Related

SQL & PL/SQL :: Difference Between Count(1) And Count(*)

Nov 24, 2011

difference between count(1) and count(*). As i know count(*) will give number of rows irrespective of null and count(1) will not count the null.My Oracle version is 10 g.

SQL> select * from t1;

A B C
---------- -------------------- --------------------
1 2 3
2
5

SQL> select rownum,a.* from t1 a;

ROWNUM A B C
---------- ---------- -------------------- --------------------
1 1 2 3
2 2
3 5
4
[code]....

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

SQL & PL/SQL :: Divide Count From One Query By Count From Another Query

May 24, 2010

I have the folloiwng two queries:

Query_1: select count(*) yy from table1;
Query_2: select count(*) zz from table2;

I need to compute the following:

var:=(yy/zz)*100

How can I achieve this in a single query?

View 3 Replies View Related

SQL & PL/SQL :: Get Count On Group And Total Count For Each Group

Mar 23, 2013

I'm using this code, and it performs fine, but I'm wondering if there is a more elegant way to do it--maybe with "ROLLBACK". Basically (as you can see) I need to get a normal count for each group but also for each group take a percentage of the total count (so all groups pct adds up to 100 (oh yeah, don't test for zero below, but just a test... )

select
c.Event,
c.code,
count(1) as calls,
total.total_count,
count(1) / total.total_count * 100 as pct_of_total
from
table1 c

[Code]....

[Edit MC: add code tags, do it yourself next time]

View 4 Replies View Related

SQL & PL/SQL :: Characters Format On The Web?

Jul 10, 2011

We have a production database that have : NLS_LANGUAGE=FRENCH_FRANCE.WE8ISO8859P1.

We use (INSERT, UPDATE) arabic and french languages, and it works properly.

When I issue SQL statments to retrieve arabic data (with SQL*PLUS), it works and it returns correct arabic format.

When I use PHP, with the same small query, the arabic format is not correct.

I've tried changing the encoding characters on my browsers (IE and FF) and it's still incorrect.

View 4 Replies View Related







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