SQL & PL/SQL :: Exact Count Of Characters
Jan 7, 2013How can I find out that exact count of '~'?
SELECT NVL(LENGTH('~~~~~~~~~~~~~~~~~')-LENGTH(REPLACE('~~~~~~~~~~~~~~~~~','~','')),0) result
FROM dual;
ADVERTISEMENT
How To Count Distinct Characters From A String
Sep 26, 2005how to count different characters from a string ....
View 7 Replies View RelatedSQL & PL/SQL :: Count Repeated Characters In String
Mar 9, 2011Is 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
SQL & PL/SQL :: Removing Special Characters And Get Desired Characters From Column Values
Jul 23, 2013create 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')
SQL & PL/SQL :: Get Exact Value From Table
Aug 16, 2010I 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...
SQL & PL/SQL :: How To Calculate Exact Age
Oct 21, 2011How 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 RelatedGet Exact Value Without Changing Datatype?
Nov 23, 2011I 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 RelatedSQL & PL/SQL :: Get Exact Matching String
Dec 11, 2012When 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 To See The Exact Special Character
Mar 18, 2013I 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.
How To Know Exact Time Of Row Modified And Deleted
Jul 16, 20101)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 ?
How To Validate A Sum Of Some Records In A Table To Be Exact Value
Apr 9, 2008How 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...)
SQL & PL/SQL :: Linking 2 Tables Without Exact Matches
Mar 4, 2011I 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'.
SQL & PL/SQL :: Possible To Get Exact Name Of Constraint From Any System Table
Sep 1, 2010There 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 RelatedHow To Create Exact Copy Of A Table
Nov 24, 2012I 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.
Exact Space Occupied By A Table With LOB Column
Oct 18, 2013I 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 Set Exact Page Size For Query Output Format
Oct 2, 2012I 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 ....
How To Know Data Is Coming From Exact Table from Union All View
Feb 28, 2013I 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
JDeveloper, Java & XML :: Regular Expression For Exact Word And Occurrence
Mar 15, 2012I 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.
Forms :: Oracle 6i For Windows 7 64 Bit / Can't Find Exact Location From Google
Sep 20, 2012i 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 RelatedBackup & Recovery :: Exact Fetch Returns More Than Requested Number Of Row
Aug 24, 2011I 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?
Reports & Discoverer :: Continuous Pages Not Printing In Exact Pre-printed Area
Nov 29, 2011I 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.
Forms :: ORA-01422 / Exact Fetch Returns More Than Requested Number Of Rows
Aug 18, 2010ORA-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].....
Export/Import/SQL Loader :: Get Exact Path Where SQLLDR Got Installed In UNIX?
Nov 1, 2013How 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 RelatedSQL & PL/SQL :: Difference Between Count(*) And Count(1)?
Nov 16, 2009When 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?
SQL & PL/SQL :: Difference Between Count(1) And Count(*)
Nov 24, 2011difference 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]....
How To Know Password (Exact Password Not Hascode) Of All Users As A Sysdba
May 28, 2008How 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 RelatedSQL & PL/SQL :: How To Find Whether Exact String Is Present / Not In Given String
Mar 14, 2013I'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
SQL & PL/SQL :: Divide Count From One Query By Count From Another Query
May 24, 2010I 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?
SQL & PL/SQL :: Get Count On Group And Total Count For Each Group
Mar 23, 2013I'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]
SQL & PL/SQL :: Characters Format On The Web?
Jul 10, 2011We 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.