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??.
my above table consists of two columnc sl_no and Status,col1 indicates the process no and the status indicates it is implemented or cancelled or failed during implementation.
i need to find the percentage of the implemented+cancelled process over failed..
test case: lets consider, A->count(Sl_NO) B->Count(STATUS) where STATUS='Implemented'
[Code]..
i think i have satisfactorily given enough data.. make it out using sql query..
I wanted to make a script for applying the Archived log File in to DR by certain interval.
1) I will use the below view for finding the SEQUENCE so far applied.select sequence# from v$log_archive. 2) But how i can compare with the archive log files available in physical location with the above mentioned view.
eg:- the above view shows, the till the sequence 46789 is applied.And in the DR physical Location available sequences are 46795. which means 6 more archived log files are more, which are not applied into the DR so far.
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
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');
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 ...
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)?
i'm trying to write a pl/sql to find all missing archived logs that are need for streams replication.
There is already a oracle metalink note for this. But yet it would give only the archive log name that contains my dba_capture.start_scn and we need to check if the files exist in disk or not!
The problem here is, when using ASM, dba_registered_archived_log view is truncation the file name and it is really difficult to pin point the logs. So is it fine to join this view with V$archived_log? is deleted and status column would do the trick? I modified the plsql as below. Is this fine/accurate?
CODEdbms_output.put_line('Capture will restart from SCN ' || lScn ||' in the following file:'); for cr in (select decode (a.name, NULL, 'NOT FOUND', a.name) name, to_char(a.completion_time, 'hh42:mi:ss') completion_time from v$archived_log a,dba_registered_archived_log b where lscn between b.first_scn and b.next_scn and a.deleted = 'YES' and a.status != 'A') loop f_rec :=1; [code]......
