We have a form which contain the multiple record.
I require to disable the record during the query, if the certain flag (Y) is activiate. And their as allow to display the next record without the flag (N) is editable.
how to done it.
What is the trigger which should be used to check certain field value after posting the query.
Example:
I have executed the query and the records are fetched. There is one field I want to check if it is null then it should be enabled, else, keep it disabled.
I want to put one check box to check all the check boxes.how can i do this?
View 5 Replies View RelatedI have this query
SELECT wmsys.wm_concat (gp.prog_acronym)
FROM inf_program gp, ea_audit_program ap
WHERE ap.sys_prog_id = gp.sys_prog_id
AND ap.sys_audit_id =484
is there any way I can check the datatype of the result of the above query ? ,my dba added some patch to oracle , after the patch this query is returning a clob in java , it should return string and it used to return string before patch and in other databases it returns string, I can check the return type only from java side , is there any way oracle can say me the datatype ?
I have partitioned an oracle table and created Local Index for the Partitioned table .
Now i want to make sure that the Local index is being used when i perform select query on the Table partition.
How do i confirm that ? can i check the explain plan generted for the select query to confirm local index is being used ?
Need to check the elapse time for particular query ran from last 24 hours , it was ran multiple times and need to know for each execution what is elapse time .
which view we can use ?
What is the query to check disk space usage for oracle 10g ? What is the query to check percent of disk space usage for oracle 10g ?
View 1 Replies View RelatedCan we query on database to check if data pump was run on scheduled time incase we dont have access to operating system to see the log file.
View 1 Replies View RelatedIs there any sql select query to check and find for special and non printable characters in a table column.
View 4 Replies View Relatedprovide me the query to check our database is running using spfile or running using pfile.
View 2 Replies View RelatedI need to display the parameter and status of DB for listener and Read Only.
I know those value could be get from command line , but could we get the values of Listener and Read only by SQL/PlSQL? So I can get it through the query of DB.
How can i check if paging happening while running the query. As i have 4gb of PGA target but the query is taking long time in parallel and has hash join.
how to check paging in 11gr2
I have a base table (Table A) block with multiple records displayed. I need to track audits to this underlying table in the following way:
If user updates a field in the block I want the pre-changed record's audit fields to be set and I need to create a copy of the record with the changed values. Basically any changes will result in the record being logically deleted, and a copy record created with the newly changed values.
Tried to implement in the block's pre-update trigger which will call a package to directly update Table A then Insert into Table A, then requery the block. Is there a clean and efficient way to do this?
How to update single table column using join query
Example:
Update table1 t1,table2 t2
set t1.column2 = 'Y'
where t1.column1 = t2.column1
I have 2 tables with 3 common columns (Col1, col2,Primary). One common column name is primary (oracle key word).Around 2 lakhs rows to be updated and No indexes are used on these tables. I need to write an update query as shown below.
Emp table
Col1col2primay
100101y
103104n
201105y
100101y
Dept table
Col1col2primay
100101null
103104null
000656null
Update query Result
Col1col2primary
100101y
103104n
I have this SQL select to give me all the nodes of a tree starting at a particular node:
SELECT tree.node_id, LEVEL depth_level
FROM tree_of_nodes nodes
START WITH nodes.node_id = 1000
CONNECT BY PRIOR nodes.node_id = nodes.parent_id
I need to update a column called dept_level, which is essentially the same as Oracle's LEVEL. Something like this statement is what I need, but obviously this statement doesn't work:
update tree_of_nodes
set depth_level = LEVEL
START WITH nodes.node_id = 1000
CONNECT BY PRIOR nodes.node_id = nodes.parent_id
I've tried inline views and other approaches and have not found a method that works.
I am manually committing the form by writing commit statement in key - commit trigger for some reason.
Everything is working fine. I want to display a message if some necessary fields are not filled properly, like if a user filled half table and mistakenly he press the commit button. Here I want to show him a message that you can't saved the data because the table is not completely filled.
I have two field date from and date to... i want to enter an employee in date range that range should not be enter again.
View 8 Replies View RelatedI have a leave form where I need to put a check which prevents the user from entering 'sick leave' if the employee hasn't completed 3 months in the company.This is the code I've written:
SELECT ABS(MONTHS_BETWEEN(t.start_date,sysdate)) INTO l_date
FROM hrp_per_all_people_f t
WHERE t.company_id = :parameter.p_company_id
AND t.employee_number = :parameter.p_employee_number
[code]...
The absence_type_id = 21 is for 'sick leave'. I can't figure out why this is not working. When I do enter 'sick leave' for a new employee I do not get any checks and neither am I able to save the changes.
I was working with forms and found a problem related to accessing of status of checkbox. In a block with multi record to be checked, if we want to check all the checkbox status which is already clicked, Then what should i do.
Now i am using the function Checkbox_check as:
if Checkbox_Checked('FVA.STATUS')
But the problem with this function, it is giving status of last clicked checkbox only.
I've just started with the Oracle SQL and come from a heavy MS SQL background and I understand that here are some natural differences in the syntax but I'm stumped as to why the following sql represents a problem:
update MASTERMICODES t1
set t1.TEMPTA = ( select t2.TAFCODE
from TA_FEATURES t2
where t2.FCODE = t1.FCODE
)
It returns Error report:
SQL Error: ORA-01427: single-row subquery returns more than one row
01427. 00000 - "single-row subquery returns more than one row"
*Cause:
*Action:
I want it to return more than one row...in fact I want it to make on all rows that have the same fcode between tables.
I am looking to build a query to update a current value in a DBA view to a new one.i.e. updating directories based on the current value:
CREATE OR REPLACE DIRECTORY 'DIRECTORY_NAME' AS 'DIRECTORY_PATH'(substr(directory_path, 1,5) + '/&dbname' {i.e. this is different for every database name }+ 'DIRECTORY_PATH'(string after /xyz/)
WHERE DIRECTORY_NAME in
( select DIRECTORY_NAME
from DBA_DIRECTORIES
WHERE DIRECTORY_PATH
like '/xyz/%'
)
i.e. resulting output should be:
CREATE OR REPLACE DIRECTORY 'ABC' AS '/xyz/DBNAME/abc/def/';
(when the directory previously was 'xyz/abc/def/') i.e. basically inserting the db name into the directory.where DBNAME is a variable more directories are added frequently so therefore this needs to be a dynamic procedure to change the directories in the db.
The requirement is I have a table (TAB1), wherein I have 3 columns, ID, LID and STATUS.
The value in ID column = ID_SEQ.NEXTVAL,and LID will be either 0 or 1 and the possible values for STATUS are 'ED','CP', NULL. The ID column is not suppose to have duplicate values, but there is no check on the table for the same.
Someone has updated the existing data and ID column is containing duplicate values. Wherever LID = 0 and STATUS = NULL and if only if ID is duplicated then the ID_SEQ.NEXTVAL has to be assigned to ID field, so that there are no more duplicate values.
CREATE TABLE tab1 (id NUMBER , lid NUMBER, status VARCHAR2(10));
Existing Data
------------------
INSERT INTO tab1 VALUES (1,0, 'ED');
INSERT INTO tab1 VALUES (1,0, 'CP');
INSERT INTO tab1 VALUES (1,0, NULL);
INSERT INTO tab1 VALUES (1,0, NULL);
INSERT INTO tab1 VALUES (1,0, NULL);
INSERT INTO tab1 VALUES (1,0, NULL);
[code]....
get the result using a single update statement.
How to update two tables in single set or single query ?
View 8 Replies View RelatedI am trying to use the below query
update t_emp set TTL_FLG =
CASE
WHEN EXISTS
(SELECT 1 from Schema1.T_STG_LW_EMP E
WHERE E.Employee = Schema2.T_emp.EMPLOYEE_NUMBER
AND E.JB_CODE like '%TP%' or E.JB_CODE like '%DGD%' or E.JB_CODE like '%PDD%'
or E.JB_CODE like '%YND%'
)
THEN 'Y'
ELSE 'N'
END;
I've seen this example numerous places, and tried to implement it, but I keep getting an "invalid identifier" error message, despite the fact that I've got the table and column specifically identified.For instance, my query reads like:
UPDATE tbl1
SET tbl1.EMPID =
(SELECT tbl2.EMPIDA FROM tbl2
WHERE LOWER(tbl1.EMAILCOL) = LOWER(tbl2.EMAILCOL2)
)
WHERE tbl2.EMPIDA IN ('Z1O435','S8M4722','M0D5156')
AND EXISTS
(SELECT tbl2.EMPIDA
FROM tbl2
WHERE tbl1.EMAILCOL= tbl2.EMAILCOL2 );
But I'll keep getting flagged at the tbl2.EMPIDA column reference. I have not tried this in SQL Plus, just in TOAD, but it seems to repeatedly fail.I have had to dump records to standalone Access tables and link back to perform the updates.
There is a table copper_terminal with columns
small_world_id,address_number,street_name.
I need to update small_world_id with address_number,street_name I am trying to write as
SQL> update COPPER_TERMINAL set smallworld_id=ADDRESS_NUMBER||_||STREET_NAME whe
re copper_terminal_id='45';
update COPPER_TERMINAL set smallworld_id=ADDRESS_NUMBER||,||STREET_NAME where co
pper_terminal_id='45'
*
ERROR at line 1: ORA-00911: invalid character
How to correct this??
is it possible to update a same column name in two tables.
I have two tables in same schema
(1)table name
pem.igp_parent
column name
igp_no.
igp_type
(2)table name
pem.igp_child
column name
igp_no.
igp_type
i want to update igp_no column in one query, how it would be possible.
I wonder if there is some tiny technique to trace a duplicity on a block label without committing the records ( Maybe on Validate or new record instance)
I have one procedure to check duplicate but what I remember that there is something very smart provided from oracle to do that.
I have a form in which i had 6 items in a block,like sno,task,responsible,totaldays,date,status .Except 'totaldays' item all items will be updated when i press button called 'Update'.am doing this in on - update by using if(:system.block_status='changed').
Now i want only if a specific item 'totaldays' is changed , the on-update should happen.
i have 6 items in a block
sno
task
responsible,
totaldays
date,
status
only if i change totaldays item the on - update should happen
how to do this?