SQL & PL/SQL :: Update Is Not Working

Apr 27, 2012

with a as (
SELECT subscriber_agn,max(ixml_id) ixml_id, claim_id
FROM
(

[Code].....

i want to combine above two and need only one sql stmt.

View 2 Replies


ADVERTISEMENT

Update Statement Not Working - Invalid Identifier?

Apr 15, 2009

I am having trouble getting the following update statement to work.

update Team
set Names.Team_Name = "Not Yet Assigned"
WHERE
Team.ROE_PROCESS = 'G' AND

[code]...

The error message I am receiving is:
ORA-00904: "Department"."UI_WKLY_EARNS": invalid identifier

View 4 Replies View Related

ODP.NET :: Visual Studio 2012 Update 2 - Wizard Quit Working

Apr 22, 2013

I had installed ODAC 11.2 Release 5 (11.2.0.3.20) and Update Model from database wizard was working as expected...then after installing update 2 the wizard quit working....So Installed the latest managed drivers and beta..Data Connection connect and I can see the views and tables. but the Update Model from database wizard does not show the views and tables..

View 5 Replies View Related

ORA-00932 - Sample Code Working Fine In 10g And Not Working Now In 11g

Apr 1, 2013

Below is the sample code working fine in 10g and not working now in 11g.

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "PSTest" AS
import java.sql.SQLData;
import java.sql.SQLException;
import java.sql.SQLInput;
import java.sql.SQLOutput;
import java.util.List;
[code]....

we got the below error: ORA-00932: inconsistent datatypes: expected an IN argument at position 1 that is an instance of an Oracle type convertible to an instance of a user defined Java class got an Oracle type that could not be converted to a java class

Current Oracle version is Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit and the version we are upgrading is Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit

View 3 Replies View Related

PL/SQL :: To_char Not Working / (||) Is Working With Join Query

Mar 22, 2013

I have two tables : oa_membership_dtl(in this created_by field is varchar2(200 byte) ,oa_partner_usr_dtl(in this table partner_userid is number(8,0) i need to do join on above fields.

I am using following two queries:

select * from oa_membership_dtl membership
join oa_partner_usr_dtl partner_user
on to_char(partner_user.partner_userid,'9999')=membership.created_by
select * from oa_membership_dtl membership
join oa_partner_usr_dtl partner_user
on rtrim(ltrim(partner_user.partner_userid||' '))=rtrim(ltrim(membership.created_by))

by using first data is not fetched but 2nd is working fine , i am getting the matched records using 2nd query.

whats the diff between to_char and || symbol?

View 1 Replies View Related

Forms :: Insert And Update Directly Into Table From Pre Update Trigger Of Block?

May 14, 2010

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?

View 4 Replies View Related

Update Statement Versus Cursor Based Update

Sep 7, 2010

I have to update 20 and 60 million records of a table. The update statement are

1> 20 million recs
update mycustomer set update_time=add_months(sysdate,240) where seq_num = 1;

commit;

2> 60 million recs
update mycustomer set update_time=sysdate-seq_num where seq_num <> 1;

commit;

Q1> Is there any way to improve performance
Q2> Will parallel dml improve performance
Q2> Would a pl/sql cursor make any difference in speed.

View 1 Replies View Related

SQL & PL/SQL :: Update Statement With Join Only Update Matching Rows

Aug 17, 2010

This is my query:

UPDATE t_tt_hours a
SET a.sak_request = (
SELECT b.sak_request
FROM t_requests b, co c

[Code]...

The problem I am having is that it is updating all rows even when it is pulling back a null value for b.sak_request. I've tried adding b.sak_request is not null to the select statement like this:

UPDATE t_tt_hours a
SET a.sak_request = (
SELECT b.sak_request
FROM t_requests b, co c
WHERE b.nam_eds_tracking_id = c.id_dir_track_eds

[Code]...

but it doesn't seem to make a difference. The reason I need to do this is that the difference between where it matches with a valid (non-null) value is 396 rows vs. 12,484 rows which is too time consuming to run on my page.

View 9 Replies View Related

SQL & PL/SQL :: Create Trigger That Will Update Table When There Is Insert / Update

May 29, 2012

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;
/

View 3 Replies View Related

SQL & PL/SQL :: Update Previous Row Based On Next Row And Update Duplicate Record

Mar 6, 2013

create table test1

( ID NUMBER(11)
,MEMBER_NMBR NUMBER(10)
,CODE_NMBR NUMBER(7)
,ROW_EFCTV_DT DATE
,ROW_TRMNTN_DT DATE

[code]....

insert into test1 values (11007,7462,32,'30/sep/2012','31/dec/9999',3,'25/sep/1998','AUTUMN',1,0,344);
insert into test1 values (11007,7462,32,'30/oct/2012','31/dec/9999',3,'25/sep/1998','AUTUMN',1,0,344);
IDMEMBER_NMBRCODE_NMBRROW_EFCTV_DTROW_TRMNTN_DTFLAG_NMBRBRTH_DTNAMECLAIM_CDAMT1AMT2
1100774623209/30/2012 00:0012/31/9999 00:00309/25/1998 00:00AUTUMN10344
1100774623210/30/2012 00:0012/31/9999 00:00309/25/1998 00:00AUTUMN10344

I have to update the row_trmntn_dt of first row to row_efctv_dt of 2nd row which is 30th Oct 2012 - 1 day i.e. 29th Oct 2012

View 10 Replies View Related

PL/SQL :: Update Multiple Columns With Single Update Statement

May 30, 2013

i am reading the columns value from different table but i want to update it with single update statement. such as how to update multiple columns (50 columns) of table with single update statement .. is there any sql statement available i know it how to do with pl/sql.

View 5 Replies View Related

SQL & PL/SQL :: How To Update Multiple Rows With Different Values Using Update Statement

Mar 21, 2011

I have one doubt about update command in sql. How to update the multiple rows with different values using update statment.

Eg:-

SQL> set linesize 500;
SQL> set pagesize 500;
SQL> select * from emp;
SQL> select empno,ename,sal from emp;
SQL> select empno,ename,sal from emp;

EMPNO ENAME SAL
---------- ---------- ----------
7839 KING 5000
7698 BLAKE 2850
7782 CLARK 2450
7566 JONES 2975
7654 MARTIN 1250

[Code]....

The above table contains 14 records. Now i would like to update the salary column with different values like

EMPNO SAL
===========
7839 18000
7698 20000
7782 5000
...
...
...
7934 25000

How to update above values with single update query.

View 11 Replies View Related

SQL & PL/SQL :: Compound Trigger Update Firing Update

Jan 25, 2012

After many tests I can't make work and update of the same table inside the same table.

Trying to avoid Mutating Table Error now I have
ORA-00036: maximum number of recursive SQL levels (50) exceeded

Sample Data :

create table test_compound (USERID VARCHAR2(10),APP VARCHAR2(15),LAST_UPDATED_ON TIMESTAMP);

insert into test_compound values ('user1','1',systimestamp);
insert into test_compound values ('user2','2',systimestamp-4);
insert into test_compound values ('user3','3',systimestamp-6);

CREATE OR REPLACE TRIGGER trigger_test
FOR UPDATE ON test_compound
COMPOUND TRIGGER
TYPE t_tab IS TABLE OF VARCHAR2(50);
l_tab t_tab := t_tab();
[code].......

When I execute :

update test_compound
set last_updated_on=systimestamp
where userid='user1' and app='1';

The trigger should update the first row and all the data from test_compound table where userid='user1'. Maybe the problem is that updating the same table inside the trigger is firing in a recursive way the trigger.

View 13 Replies View Related

SQL & PL/SQL :: How To Update Multiple Table With Single UPDATE

Jul 19, 2011

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).

View 4 Replies View Related

SQL & PL/SQL :: Create Trigger To Update Table B After Update On Table A

Jul 21, 2011

I have table test1(id,name) and table test2(id,,name)

Now when I update name column of a row on test1 I want the same value to be updated for the same id in test2.

So I wrote this trigger but its not working

create trigger test_trigger after update on test1 for each row
begin
update test2 set name=new.name where test2.id=id
end
/

View 9 Replies View Related

Sqlplus UI - Not Working?

Apr 22, 2008

I'm having some minor trouble with my sqlplus in Windows. Don't know which feature I accidentally edited by cosole seems to be in debug mode. Everything is ok, except that it print the old and new values of my variables. How can't I restore it to it's original behavior.

View 3 Replies View Related

SQL & PL/SQL :: How To Calculate Working Day

Jan 1, 2013

I need a query to find out the working days. I have attached the sample script to create table and data. Here is the description of the tables.

Emp1 : To record the employe's information and weekly rest day.
Attendance :- To record daily attendance.
Leave_appovd : To record the approved leaves.
Holiday : To record the holidays.

W = Attendance
R = Weekly Rest
L = Leave
H = Holiday
A = Absent

Required output is

Emp No.In Date Out Date Type
736912/1/201212/1/2012W
736912/2/201212/2/2012R
736912/3/201212/3/2012W
736912/4/201212/4/2012W
736912/5/201212/5/2012W
736912/6/201212/6/2012L
736912/7/201212/7/2012H
736912/8/201212/8/2012H
736912/9/201212/9/2012R
736912/10/201212/10/2012A
736912/11/201212/11/2012W
736912/12/201212/12/2012W
736912/13/201212/13/2012W
736912/14/201212/14/2012W
736912/15/201212/15/2012L
736912/16/201212/16/2012L
736912/17/201212/17/2012L
736912/18/201212/18/2012W
736912/19/201212/19/2012W
736912/20/201212/20/2012W
736912/21/201212/21/2012W
736912/22/201212/22/2012W
736912/23/201212/23/2012R
736912/24/201212/24/2012A
736912/25/201212/25/2012A
736912/26/201212/26/2012A
736912/27/201212/27/2012W
736912/28/201212/28/2012W
736912/29/201212/29/2012W
736912/30/201212/30/2012R
736912/31/201212/31/2012W
778212/1/201212/1/2012W
778212/2/201212/2/2012W
778212/3/201212/3/2012W
778212/4/201212/4/2012W
778212/5/201212/5/2012W
778212/6/201212/6/2012W

Separate Query will be run for the following output.

Wroking Days
EmpnoAttend WeeklyRestsLeavesHolidays TotalAbsentsG. Total
7369 17 4 4 2 27 4 31
7782 6 0 0 0 6 25 31

If any body work on weekly rest or holiday, it will be considered as weekly rest and holiday. There working on these days will be treated separately.

View 10 Replies View Related

SQL & PL/SQL :: DDL Trigger Not Working?

May 14, 2013

I have a trigger to automate grants to a Role whenever a table is created in my schema.I have dba rights.

CREATE OR REPLACE PROCEDURE deepa.myddl
(p_ddl IN VARCHAR2) IS
BEGIN
EXECUTE IMMEDIATE p_ddl;

[Code].....

This works perfectly when i create a table as deepa user. But say when another User creates a table in my schema

like create table deepa.test1 ( a number) This trigger is not fired. Whenever a table is created by me or by any other user in my Schema, i need the trigger to be fired.

View 2 Replies View Related

SQL & PL/SQL :: In Clause Is Not Working?

Sep 18, 2013

SELECT sno,mid,mname
FROM manage_date
WHERE mname IN
('KIRAN-KUMAR',
'RAHUL-RAJ',
'KAUSHAL-SONI');

IF I use this query directly in DB it's working fine. But when this query is calling in .net using parameter as below it's not giving any records for more than one value. IN (:p_mname)

If I pass one name 'KIRAN-KUMAR' from .net It's working.

If I pass multiple names from .net query not returning any records.

View 4 Replies View Related

Oradim Is Not Working

Dec 13, 2010

I trying to create the new service for my physical standby database. It's on the same vista machine. But somehow it's not working.

C:\Oracle\product\10.2.0\mayuprd\database>oradim -new -sid -intpwd ananya -start
mode manual
DIM-00003: An argument is missing for the parameter.

View 5 Replies View Related

SQL & PL/SQL :: Recycle Bin Not Working

Jun 14, 2010

I went through the link given in orafaq and tried to work on my system but the things did not work.

SQL> select *
2 from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> ALTER SYSTEM SET recyclebin = ON;

System altered.

SQL> SELECT Value FROM V$parameter WHERE Name = 'recyclebin';

VALUE
--------------------------------------------------------------------------------
ON

SQL> CREATE TABLE TEST_RBIN(VAL NUMBER);

Table created.

SQL> INSERT INTO TEST_RBIN(VAL) VALUES(10);

1 row created.

SQL> COMMIT;

Commit complete.

SQL>
SQL>
SQL> DROP TABLE TEST_RBIN;

Table dropped.

SQL> SHOW RECYCLEBIN;
SQL> SELECT * FROM USER_RECYCLEBIN;

no rows selected

View 6 Replies View Related

SQL & PL/SQL :: Select INTO Is Not Working

Mar 8, 2013

why my "select into" not working.i also create the table testjobs but also not working

SQL> select *
2 into testjobs
3 from jobs;
into testjobs
*
ERROR at line 2:
ORA-00905: missing keyword

View 8 Replies View Related

Oracle 11g EM Not Working (Linux OS)

Jan 20, 2011

I am unable to configure EM(Oracle 11.2g) on Redhat Linux 5 Enterprise edition(32 bit). Environment variables are set to correct SID. If I try to run dbca the wizard does not display the Enterprise manager configuration screen. I have tried to create repository by using emca -config dbcontrol -db config.

But it throws exception that database instance is unavailable?

View 5 Replies View Related

Self Join No Longer Working?

May 20, 2010

I have a piece of code that joined the same table onto itself twice in order to get the previous, current and future year's into columns in the same output.

Up until recently this has been working fine but the most recent data has just been uploaded into the table and now it comes up with an error.

On the second (left outer) join it now says that the column is ambiguously defined (ORA-00918). It doesn't matter which order the joins are in it is always the second join that the error pops up on.

View 4 Replies View Related

Working With Date In Oracle?

Oct 31, 2011

I tried to insert date in date column from another table date column. Below is SP ans execute procedure.

CREATE OR REPLACE PROCEDURE test(
start_time Varchar
)
AS
BEGIN
DBMS_OUTPUT.ENABLE(1000000);
DBMS_OUTPUT.PUT_LINE(start_time);
Insert into test_ora (report_date) (select start_time from

[code]....

execute test ('27-10-2011 12:01:01');

It gives error as Invalid number.

View 2 Replies View Related

SQL & PL/SQL :: EXISTS Not Working In Oracle 10g

Mar 30, 2012

following is a query which i find difficult to understand why EXISTS is failing. There are two scenarios where if i block LINE 30 and unblock line 31 of the code then one record is returned.

SELECT A.ENTITY_CODE,
A.VRNO,
A.VRDATE,

[Code]....

View 4 Replies View Related

SQL & PL/SQL :: INSERT INTO Table Not Working?

Oct 26, 2010

oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
"CORE 11.1.0.6.0 Production"

I am doing a simple INSERT INTO from the sql developer window. it shows that 1 row inserted, but when i query, it doesnt give th values.

insert into events_main(event_id,event_number,last_update_Date, last_updated_by)
values(162,999999, sysdate, 'rkhatiwala');
COMMIT ;

this does not give any error, but when i do

select * from events_main
where event_id = 162

it does not return anything.

View 20 Replies View Related

SQL & PL/SQL :: LEFT JOIN And NVL Isn't Working

Nov 29, 2011

Using Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

Here's a simplified version of the query I am having problems with:

SELECT
assoc.association_id
FROM mentor_initiative mi
LEFT JOIN program assoc_prog ON assoc_prog.program_id = -1
LEFT JOIN mentor_association assoc ON assoc.mentor_initiative_id = mi.mentor_initiative_id AND
NVL(assoc_prog.program_id, -1) = NVL(assoc.program_id, -1)

Note that there is no program with program id -1. So the assoc_prog left join will come up with nothing. I was thinking that since assoc_prog.program_id will be null, the second assoc left join would pick the row where assoc.program_id is null. However, the second left join doesn't join to any row.

In this query, it does join to an assoc row (I changed assoc_prog.program_id to NULL)

SELECT
assoc.association_id
FROM mentor_initiative mi
LEFT JOIN program assoc_prog ON assoc_prog.program_id = -1
LEFT JOIN mentor_association assoc ON assoc.mentor_initiative_id = mi.mentor_initiative_id AND NVL(NULL, -1) = NVL(assoc.program_id, -1)

I was thinking it would join to an assoc row in the first query though. How can I change the first query to have the desired effect of left joining to a row where assoc.program_id is null if assoc_prog.program_id is null?

View 7 Replies View Related

Cursor In Trigger Not Working?

Dec 25, 2011

i have a problem with Cursor in trigger,

CODEcreate or replace trigger admin
                  after insert on admin
                  for each row
                  declare
                   bang varchar2(250):='ADMIN';  
                  
[code]...

i will try with select :new.i.column_name from dual, but not run (because i can't determination exactly column name thus i must use column_name variable)

View 6 Replies View Related

Enterprise Manager :: OEM Is Not Working

Sep 24, 2010

I tried to start my oem managing but got the following error.

[oracle@Eagle2 ~]$ emctl start dbconsole
OC4J Configuration issue. /home/oracle/app/product/11.1.0/db_1/oc4j/j2ee/OC4J_DBConsole_Eagle_boomiedb not found.

View 19 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved