SQL & PL/SQL :: Finding Value Of Bits From Hex Number?
Jan 12, 2012
Version : Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
I have a source which is of datatype RAW(1). Basic requirement is to convert it into binary, and then lookup the value of the 4th and 6th bit. Is this possible using simple SQL ?
Eg.
DataBinary
----- -------
EF1110 1111
From this binary output, i need to find the value of 4th and 5th bit.
So if the 4th bit is 1, I need to perform some operations if 0 then some other.
Create table test_hex_to_bin (col1 raw(1));
INSERT into test_hex_to_bin values ('99');
INSERT into test_hex_to_bin values ('EF');
INSERT into test_hex_to_bin values ('AB');
The code which I am working on consists of an incoming dynamic string which be in the form of binary digits. The max size of the string will be 12 digits. For example, the string can be '111011000001', '000000000000', '111111011111', etc.
I need to find the number of occurences of '111' in the incoming string. Say in the 1st example, result will be 1, in the 2nd example result will be 0, and in the third example, the result will be 3.
I have been trying to capture the string length and replacing the variables '111' to find the number of occurences, but it isn't giving me the result that I want. This is what I have tried
SQL> conn hr/hr Connected. SQL> show user USER is "HR"
[Code]....
I searched the forum and found a similar topic, and following that guideline, I even tried dividing the string with the length of the pattern. It works in some scenarios (the first and second examples mentioned below), while it fails in some scenarios (third example mentioned below)
SQL> select (length('11101110111') - length(replace('11101110111','111','')))/length('111') as occurences from dual;
OCCURENCES ---------- 3
SQL> select (length('110111110111') - length(replace('110111110111','111','')))/length('111') as occurences from dual;
OCCURENCES ---------- 2
SQL> select (length('111111111111') - length(replace('111111111111','111','')))/length('111') as occurences from dual;
if there is any inbuilt function or way to find a row in the table that is having value for maximum number of columns.
For example, the table A has 5 columns (c1,c2,c3,c4,c5) and it has 3 records(r1,r2,r3) and r1 has values only for c1,c2 r2 has values only for c1,c2,c3,c4 r3 has values only for c1
so I should get the result as "r3 has values for 4 columns & it is not having value for column c5".
I have been working in the installation of oracle 11g r2 in my fedora 17 (32 bits) system for a few days and still have not gotten.Now I found that I need the OPtatch for enable the db console and even being a member of My Oracle Support I can not request the patch for a technical question.
1) Note to the Approver: I am that one because I am an end user (freelance)
I'm not really sure why oracle is not finding my Foreing Key, I'm creating an easy set of table for a company and I'm declaring all Primary keys and foreing keys as necessary and this is my
mission_id, mission_type_id, security_level and code_name.
What i have to do is get the 10 most recent missions and change their security level to the highest one in their mission_type_id but ONLY if code_name length is >7
So far I have this, the problem is that the oracle moans about the order and wants me to close the bracket before the order
sql
UPDATE missions m SET m.security_level = ( SELECT max(m2.security_level) FROM missions m2 WHERE m2.mission_type_id = m.mission_type_id AND length(m2.code_name) > 7 ) WHERE m.mission_ID IN ( Select m3.mission_id From missions m3 ORDER BY m3.mission_id desc)
How can i come to know that which current alert log file is being used for database? Is their a command at database level to find out the current alert log file to which database is using ?
I have read and used the AWR script (mentioned in the page Finding unused index for finding unused customised (Z) indexes in our SAP system using oracle 10.2.0.2 as the SAP database.
But this returns no rows. Is there any precondition? I want to know how much / many times the indexes are used...We are smelling that lot of unused index are there in the database.
i have table with name, count, flag with dublicate records
example
with swayam name , counts are 3, 4 with ramana name, counts are 5,5 with reddy name, counts are 1,2,3
i want to update the flag
if count are same then update one of record (flag='A') and other should be flag='R' if count are different then update the max count (flag='A') and other should be reject remaing (flag='R'). use below quires
CREATE TABLE TEST_DUB ( NAME VARCHAR2(99), V_COUNT NUMBER, FLAG VARCHAR2(1));
Insert into TEST_DUB (NAME, V_COUNT) Values ('SWAYAM', 3); Insert into TEST_DUB (NAME, V_COUNT) Values
I am reading Section 4-8 (page 42/216) in the Oracle Database 2-day Developer Guide from here:
[Code]....
It reads:
Suppose that you want to select every employee whose last name has a double vowel(two adjacent occurrences of the same vowel).
Example 4.9 shows how you can do this.
The regular expression ([AEIOU]) represents any vowel. The metacharacter 1 represents the first (and in this case, only) regular expression. The third function parameter, 'i', specifies that the match is case-insensitive.
Example 4.9 Selecting All Employees Whose Last Names Have Double Vowels
SELECT FIRST_NAME, LAST_NAME FROM EMPLOYEES WHERE REGEXP_LIKE(LAST_NAME, '([AEIOU])1', 'i');
Result is similar to:
FIRST_NAME LAST_NAME -------------------- ------------------------- Harrison Bloom Lex De Haan Kevin Feeney Ki Gee Nancy Greenberg Danielle Greene Alexander Khoo David Lee
8 rows selected.
I don't understand how [AEIOU])1 would find a double vowel(two adjacent occurrences of the same vowel).
I am using oracle 10g, i want to know the cost of query. In the explain plan and by auto trace am able to get some query cost, but i dont know how to calculate the query cost.
How i will know the query is costly and take more time..? But i know about the hash table and nested tables in the explain plan. Other than this how to calculate the query cost..?
I have inserted through frontend which is ORACLE ADF and if i want to see those inserted data's in a table then how to check it when i dont know the table names and if there are large number of tables available.. Its I need a solution in this way as i know the values of fields so by giving values alone I want to fetch the table and column names as wherever this values are present...I am already aware about user_tab_columns and user_tables or all_tables .. So i want to try through fetching of tables or column names though values or data's as reference ...
code = CHAR(5) as primary key name = VARCHAR2(bla blal ba)
There is one-block-form based on this table. Block's datasource type = table and datatarget type = pl/sql procedure
Table API, Module API - all generated, all valid and seems correct. Now.. I try to update "name" from form. As soon as I type in something in the field, I get message "Row no longer exists". When digging deeper it seems that problem raises from lock_prodecure:
DECLARE bk_data CGC$CL_1_9_DKD.cgc$rec_tab; BEGIN PLSQL_TABLE.POPULATE_TABLE(bk_data, 'DKD', PLSQL_TABLE.LOCK_RECORDS); CGC$CL_1_9_DKD.lck(bk_data); END;
It seems, it has some problems on populating the "code" attribute in bk_data.
how to solve this problem without massive re-coding??.
Now the ultimate aim is to find unique combinations (even if the same combination is present in different rows, we have to consider it as one combination), along with its frequency, result will be like below:
BISCUIT [occurence in 2 transactions and total 4 transactions] = 2/4 = .5 BREAD [occurence in 2 transactions and total 4 transactions] = 2/4 = .5 COKE [occurence in 1 transactions and total 4 transactions] = 2/4 = .25 MILK = 3/4 = .75
[Code]....
writing query to find unique combination like above? What I have tried is as below:
create table test (row_no number, col_no number, item varchar2(50)) /
insert into test values (1,1,'BREAD'); insert into test values (1,2,'BISCUIT'); insert into test values (2,1,'BREAD');
[Code]....
But I am not able to form the exact query. let me know if this can't be done through a single query.
I'm sure one of the databases we keep has a column called UNIT_PRICE, is there a way for me to get Oracle SQL Developer find all databases with columns named that?
I want to use the aggregate CORR_S function which take two numerical columns and output the correlation between them. Assuming I have the table:
Employee(name, gender, salary) where I want to create two derived columns, how would I do it?
Like, ------------------- SELECT CORR_S( SELECT salary FROM employees WHERE salary < 10000, SELECT salary FROM employees WHERE salary > 10000 ) as correlation FROM employees ---------------------
It will find the correlation between salary's less than $10,000 in one column and salary's more than $10,000 in another column. Of course, I get an error when running the query. It's no problem if I use CORR_S(salary, salary) but I want to use derived columns, is it possible? Or, will I need to create a new table view with two new columns CORR_S(lowSalary, highSalary)?
