SQL & PL/SQL :: Update Job-id Of Employees Table Of HR Schema?
Jan 8, 2012
I am trying to update of job_id column of employees table for employee number 205 two times one after another. First time job_id column of employees table for employee number 205 is updated with new job_id. But second time job_id column of employees for employee number 205 table can not be updated. Oracle returns the following errors
HR:orcl > update employees set JOB_ID='AC_MGR' where employee_id=205;
update employees set JOB_ID='AC_MGR' where employee_id=205
*
ERROR at line 1:
ORA-00001: unique constraint (HR.JHIST_EMP_ID_ST_DATE_PK) violated
ORA-06512: at "HR.ADD_JOB_HISTORY", line 10
ORA-06512: at "HR.UPDATE_JOB_HISTORY", line 2
ORA-04088: error during execution of trigger 'HR.UPDATE_JOB_HISTORY'
As there is composite primary key using employee_id and start_date column.how to update same employee job_id twice.AS we can see from job_history table, the record for employee 200 is as follow
So how can i do the same for employee 205 without changing hire_date after first job_id update. Since for every update of job_id fires trigger. To insert row in job_history table employee id and start_date must be unique each time. HERE hire_date of employees table is used as start_date of job_id table. how it was possible for employee id 200 to change job_id twice?
I want to update the names of employees by concatenating with A for DEPT 20. For that I have written the following PL/SQL block. But instead of one A the ename are concatenating with five AAAAA s.
DECLARE TYPE lt_emp_arr IS TABLE OF t_emp.deptno%TYPE; l_emp_arr lt_emp_arr; BEGIN SELECT deptno BULK COLLECT INTO l_emp_arr [code].......
I want to update the names of employees by concatnating with A for DEPT 20. For that I have writteh the following PL/SQL block. But instead of one A the ename are concatinating with five AAAAA s.
DECLARE TYPE lt_emp_arr IS TABLE OF t_emp.deptno%TYPE; l_emp_arr lt_emp_arr; BEGIN SELECT deptno
I used Scott schema.I take 2 blocks ,one block having Deptno,Job and also 1st block is a non database block and Another Block Empno,ename,job,sal,deptno, is a database block,my question is when i enter Deptno,Job then Display Employees Details and also Department name
I would like to create a table in another schema(CBF) as already exist in my schema(TLC) without data but related indexes,synonyms and grants should be include.
How could I do this without using export import. I am using TOAD 9.0.1.
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?
i want to create a trigger that will update a table when there is an insert or update.i can't across this error that i don't even know what it means "table %s.%s is mutating, trigger/function may not see it".
*Cause: A trigger (or a user defined plsql function that is referenced in this statement) attempted to look at (or modify) a table that was in the middle of being modified by the statement which fired it.
*Action: Rewrite the trigger (or function) so it does not read that table.
CREATE OR REPLACE TRIGGER set_date_end BEFORE INSERT OR UPDATE OF issued ON shares_amount FOR EACH ROW DECLARE BEGIN INSERT INTO shares_amount(date_end) VALUES(SYSDATE); END set_date_end; /
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Master table "MVANMANNEKES"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded Starting "MVANMANNEKES"."SYS_IMPORT_SCHEMA_01": mvanmannekes/******** schemas=cmsstagingb remap_tablespace=cmsliveb_data:cmslivea_data
I have a column "empno" in EMP table and "deptno" in DEPT table . I want to update both the columns with single UPDATE statement. With out a creation of stored procedure or view(updating it through view).
I am creating a table from another existing table in another schema. The existing table contains data. When I am using the query- create table m _voucher as select * from ipm.m_voucher,I am getting the whole data of m_voucher but I want empty m_voucher table, so what will be the query to get the empty m_voucher table?
EMPLOYEES __________________________________________ EMPID NAME MANAGERID ------------------------------------------ 34 Amy 17 Ben 34 5 Chris 34 10 Don 5 ...
How can we use SQL to get all employees under one manager , either direct or indirect
I only can think the following SQL to get the first direct employee.
select e1.empid from employees e1, employees e2 where e1.managerid = e2.empid and e2.empid = '34';
Perhaps we could put it in PL/SQL procedure to do recursive call?
I borrowed some code fragments from other posts and put together a sql query. I think there is a better method in other posts but I couldn't get them working.
I'm trying to get the count of hired employee(s) using the EMP table. I want from the first hire to the last and ALL in between. If there were no hires in that window, I want 0.
WITH minmax AS (SELECT MIN(last_day(hiredate)) fmonth, MAX(last_day(hiredate)) lmonth FROM emp), cal AS (SELECT add_months(fmonth, LEVEL - 1) mnth FROM minmax CONNECT BY LEVEL <= months_between(lmonth, fmonth) + 1), vals AS (SELECT extract(YEAR FROM mnth) YEAR, extract(MONTH FROM mnth) MONTH FROM cal), data AS (SELECT extract(YEAR FROM hiredate) YEAR, extract(MONTH FROM hiredate) MONTH, COUNT(*) hired_cnt [code]...
Assign employees to their jobs in consideration the maximum number of employees to each jobs is 5 employee plus each job has own the maximum number of employees
we need the maximum number of employees for each job 5 to be variable when need to change this maximum for certain job , change this number from database (form the from of job ) not form code )
tables emp emp_no name manager hiredate salary
job job_no job
you can add tables or attributes to tables to complete you business.
I want TO find out FIRST two employee joined IN A particular department WITH department information.THE relation IS basically FROM THE scott SCHEMA.I tried LIKE AS follows. IS there ANY other way FOR best PERFORMANCE.
SELECT deptno,dname,loc, Max(Decode(rn, 1, hiredate))hiredate1, Max(Decode(rn, 1, ename)) employee1, Max(Decode(rn, 2, hiredate))hiredate2, Max(Decode(rn, 2, ename)) employee2 FROM (SELECT d.deptno,dname,loc,hiredate,ename,Row_number() over(PARTITION BY e.deptno ORDER BY hiredate) rn FROM dept d, emp e WHERE d.deptno = e.deptno(+)) GROUP BY deptno,dname,loc;
creating an sql script that can update info from one table in dbase1 to another table in dbase2 that has the same columns and if possible insert date and time in one column when the synchronized is done?
I'd like to know which SQL command (if there is any) I can use to display the current schema of a table. I'm particular interested in knowing which columns are indexes.My privileges to the DB are quite basic (I think that I'nm only allowed to run SELECT statements
This is the Oracle version that I'm using:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production PL/SQL Release 9.2.0.6.0 - Production CORE 9.2.0.6.0 Production TNS for HPUX: Version 9.2.0.6.0 - Production NLSRTL Version 9.2.0.6.0 - Production
My scenario is I need to insert into History table when a record is been updated into a tabular form(insert the updated record along with the additional columns Action_by,Action_type(Like Update or delete) and Action Date Into History table i.e History table contains all the records as the main table which is been visible in tabular form along with these additional columns ...Action_by,action_type and action_date.
So now i dont want to create a befor/after update trigger on base table rather i would like to create a generic procedure which will insert the updated record into history table taking the page alias and pade ID as the parameters(GENERIC procedure is nothing but whcih applies to all the tabular forms(Tables) contained int he application ).
I have to display count of employees that belongs to different categories.
is the situatio There is a category table CATEGORY with three columns (PK,NAME,TREEPOSITION) and we have categories A, B, C these three categories can further have sub-categories so the treeposition for the sub categories will be followed by their root category with _ 'symbol'
Now I have table for the employees with 3 columns (pk,name,category_id), where employees.category_id=category.pk So I want to calculate the number of employees in each category or sub-category.
since the number of categories will be large and each will be having different names so going through names will be bad option left is grouping through the treepostion the problem is I cant use like using IN for the TREEPOSITION. .
