PL/SQL :: Finding Table_name Or Column_name
Jan 15, 2013
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 ...
View 21 Replies
ADVERTISEMENT
Jun 14, 2011
I want to create a block of code that would search in all tables in a schema for a column_name where its data_length is like 4000 let's say. This data_length is actually dedicated for a comment column. If found, all not null column with like 4000 data_length will be changed by string "Comment has been removed". I have the following script below but it seemed lacking.
begin
for rec in (select table_name, column_name from user_tab_columns where column_name like ?data_length? = 4000 order by 1,2) loop
begin
execute immediate 'update '||rec.table_name||' set '||rec.column_name||' = ''Comment has been removed'' where '||rec.column_name||' is not null';
commit;
[code]........
View 6 Replies
View Related
Apr 20, 2012
I have statement like SELECT * FROM DIVISIONS;
i have to run this statement for different table, and I would like to pass the table name as variable
ex:
create or replace precedure dynamic_execute
v_tbl varchar2(30);
begin
---- assign table name here
v_tbl := DIVISIONS;
EXECUTE IMMEDIATE('SELECT * FROM '||v_tbl);
end;
/
When I executed the procedure I got error, how to pass the table name as variable and execute the statement successfully.
View 5 Replies
View Related
Oct 31, 2012
I would like to know what happens when we fire:
grant all on any table to user_name;
and
grant all on table_name to user_name;
Actually I was performing "grant all on table_name to user_name;" to grant the privilege but 1 of my friends suggested "grant all on any table to user_name;"
View 5 Replies
View Related
Jun 19, 2013
I have the following query where i need to find the max value[code] with t as
( select 'L1' R_nm ,'Data' R_Data , 'Obj' R_Obj , 'Wd' r_prec , '2' val
from dual union all select 'L1' , 'Data', 'Obj' , 'No', '4'
from dualunion all select 'L2' , 'Data', 'Obj' , 'No', '4' from dual )
select t.*, max(val) over(partition by r_nm,r_data,r_obj) rk from t[/code]
My expected output should be
[code]
r_nm r_data r_obj r_prec val rkL1 Data Obj Wd 2 1L1 Data Obj No 4 2L2 Data Obj Yes 1 1
[/code]
View 1 Replies
View Related
Dec 4, 2011
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
CREATE TABLE Items
(ItemID NUMBER(5) NOT NULL,
Description VARCHAR2(30),
ListPrice NUMBER(9,2) NOT NULL,
QtyOnHand Number(5),
ReorderPoint NUMBER(3),
ReorderQty NUMBER(3),
OnBackOrder Number(3),
CONSTRAINT ItemsPK
[Code] ............
There's three sets of inserts the first two successfully inserts the data NO problem but when i try to insert the data on SupplierItems comes as:
ERROR at line 1:
ORA-02291: integrity constraint (jcarter.SUPPLIERITEMSFK2) violated - parent key not found
I looked on the forum and it appears this happens when someone is doing a Insert ALL but this is not my case..
View 2 Replies
View Related
Oct 31, 2012
Select * from one:
ID SALARY
----------------------
10 1000
20 2000
30 3200
Select * from two:
ID SALARY
----------------------
10 1000
20 2000
40 4000
10 3000
20 4000
30 3000
When i try to find ID,MAX(SALARY) from these two tables, i am getting this output:
Select id,max(salary)
from
(select * from one
union
select * from two)
group by id
order by max(salary) desc;
OUTPUT:
ID MAX(SALARY)
--------------------------------------
20 4000
40 4000
30 3200
10 3000
I want OUTPUT to be like this:
ID MAX(SALARY)
--------------------------------------
20 4000
40 4000
View 3 Replies
View Related
Jun 23, 2008
What i have is table with:
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)
View 3 Replies
View Related
Dec 23, 2011
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 ?
View 2 Replies
View Related
Oct 4, 2007
Whats the command to show the name of the primary key in a table using oracle sql plus?
View 1 Replies
View Related
Nov 3, 2008
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.
View 8 Replies
View Related
May 30, 2011
How to find the list of tables which has permission select only and tables which has both read and write access permission for a particular user.
I'm using ORACLE database.
View 1 Replies
View Related
Nov 22, 2011
finding the difference between IN and EXIST.
View 15 Replies
View Related
Feb 7, 2012
I want to track the session id that has been initiated by a particular job (I have job ids with me). Which dictionary views I can get this result.
View 1 Replies
View Related
Nov 1, 2011
@test.sql
accept 1 num prompt 'enter begin value '
accept 2 num prompt 'enter end value '
Need to display all values between the above given inputs.
No tables involved.
Need SQL level solution in version 8i.
View 4 Replies
View Related
May 20, 2010
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
[Code]....
View 4 Replies
View Related
Aug 1, 2012
I have a partitioned tables and local partitioned index on the same.
I want to know that a particular index partition belong to which table partition.
View 1 Replies
View Related
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');
Basically the output should read as
Bit_number Value
----------------
4 1
5 0
View 5 Replies
View Related
Sep 26, 2011
we can find the IP address in V$session table. Is it possible to find the mac address of user's computer?
View 13 Replies
View Related
Feb 7, 2013
1. I have 6 data bases, one process is running slowly, how do you find which data base that process belongs to?
2. I have 6 data bases, only one data base we have ASM instance, how do you find which data base has ASM?
View 1 Replies
View Related
Dec 14, 2010
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).
View 6 Replies
View Related
Sep 2, 2010
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..?
View 5 Replies
View Related
Mar 28, 2012
I tried this query .. but no luck / I know it has some errors
SQL> select invoice_num from ap_invoices_all where rownum < 10 and invoice_num in ('%1%','%2%');
no rows selected
View 9 Replies
View Related
Jan 26, 2010
I have simple table:
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??.
View 24 Replies
View Related
Oct 4, 2012
I have contents like below:
BREAD,BISCUIT
BREAD,MILK,BISCUIT
COKE,MILK
MILK,SUGAR
Now combination from each row will be like below (Just for understanding):
BREAD,BISCUIT --> [COMBINATION] {BISCUIT} {BREAD} {BISCUIT,BREAD}
BREAD,MILK,BISCUIT --> [COMBINATION] {BISCUIT} {BREAD} {MILK} {BISCUIT,BREAD} {BISCUIT,MILK} {BREAD,MILK} {BISCUIT,BREAD,MILK}
COKE,MILK --> [COMBINATION] {COKE} {MILK} {COKE,MILK}
MILK,SUGAR --> [COMBINATION] {MILK} {SUGAR} {MILK,SUGAR}
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.
View 20 Replies
View Related
Sep 17, 2012
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?
View 2 Replies
View Related
May 13, 2007
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)?
View 1 Replies
View Related
Sep 6, 2010
how to find the time between the last two redolog switches?
View 1 Replies
View Related
Mar 2, 2012
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]......
View 1 Replies
View Related
Dec 16, 2010
I am trying to write SQL which finds records which are duplicated in more than one column.
Requirement : When ever i have duplicates in Col2 and Col3 both i need that record...
My Source table:
COL1COL2COL3COL4
163kg87
263fh87
1ab23
2ab24
3cd98
4fg87
5xy77
6xy67
Desired Output
COL1COL2COL3COL4
1ab23
2ab24
5xy77
6xy67
For Table generation
CREATE TABLE TEMP_TEST
(
COL1 NUMBER,
COL2 VARCHAR2(10 BYTE),
COL3 VARCHAR2(10 BYTE),
COL4 NUMBER
)
Insert statements
Insert into TEMP_TEST
(COL1, COL2, COL3, COL4)
Values
(163, 'k', 'g', 87);
Insert into TEMP_TEST
(COL1, COL2, COL3, COL4)
Values
[code]....
View 4 Replies
View Related
