PL/SQL :: Update Entire Table

Nov 23, 2012

If i have a table, my_table, with 20 columns, and i have something like:

declare
...
begin
  for r in (select * from my_table) loop
    insert into my_table (...) values (...)
  end loop;
end;

So, I want to insert a row into that table, with values for all columns being those returned by the for loop, but without a column (i'm giving the value for that column).For example, the first 19 rows should contain the values from the currently record in 'for.. loop', and the last column to contain a static value.Is it possible to do this, without specifying in the 'values' clause, something like (r.col1, r.col2, .... r.col19, 'G')? meaning other insert method.

View 7 Replies


ADVERTISEMENT

SQL & PL/SQL :: Entire Insert Script Row Will Be Inserted Into Cbxrdlog Table Sqltext Column

Aug 24, 2011

I have 2 tables. Cbxrd and Cbxrdlog. If the Cbxrd table having creation date column. when it inserts the row. if the column is null value. then the entire insert script row will be inserted into Cbxrdlog table sqltext column.

i have attached the trigger script. when i execute the table but i shows error like

"Ora-0756 Quoted String not properly terminated".
"Ora-04098: trigger os_wm_sit_owner.cbx trigger is invalid and failed revalidation.

View 5 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

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

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 :: 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

Recognized Way Of Replacing Entire DB With Impdp

Nov 15, 2012

I did an expdp on the prod DB and have been doing a straight impdp as a test, just to have data to work with, but it spews 214 errors. Mostly these:

ORA-31684 lots
ORA-39083 some
ORA-39151 lots
ORA-39082 lots
ORA-39111 lots
ORA-39112 lots

I can see that I can use REUSE_DATAFILES & TABLE_EXISTS_ACTION to overwrite tables by default, but is there a recognised way of replacing the entire DB with the impdp? Do I just create the instance, (with the init file) and not build/init the tables, or what? I'll experiment, but I'm just interested if there is a DBA best practice for this sort of thing.

View 3 Replies View Related

Backup Entire Database Without Archived Logs?

May 13, 2011

Backup your entire database, without archived logs, while the database is open for user activity. This backup should be the base for an incremental backup strategy

View 1 Replies View Related

SQL & PL/SQL :: Select French Characters Generic Entire Schema

Nov 17, 2010

I need to select all names that can possibly have ether accents or french characters what is the easiest way?

View 1 Replies View Related

JDeveloper, Java & XML :: Select Entire Message From CLOB - ORA-22806

Jun 20, 2013

I have a "simple" query that needs to extract all of the XML messages stored in a CLOB column in a table for a data deidentification project. This query

select c.cc_data_area.getClobVal() as cc_xml
from ci_cc c;

This query returns an ORA-22806: not an object or REF

cc_data_area is the clob column, and contains messages longer than 4000 characters.

View 1 Replies View Related

Application Express :: How To Highlight Entire Classic Report Column To Red

Sep 20, 2012

We have a requirement, to highlight an entire column of a classic report based on a query to red. So if the search of the report returns column1, column2 as the output. Column 2 (text) should be highlighted in red across all results rows.

In some forum responses, I see using some id format, one can try to highlight a single column in a row. Not sure how can this be done across the whole column of multiple rows.

View 9 Replies View Related

Forms :: Changing Current Visual Attribute Property For Entire Application?

Feb 18, 2013

I want to change the current visual attribute color of my form at the application level, reason being i don't want to generate all the forms again from scratch.I know that we can change this using visual attributes but is there a way to change this through some form setting?(Oracle 10g)

View 3 Replies View Related

SQL & PL/SQL :: Date Format Picture Ends Before Converting Entire Input String

Jun 29, 2011

I am trying to insert a row in a table and getting the below error.

SQL> insert into tbl_force_charging(ANI, date_time, durations,src, circleid)
2 values ('9569333585','29-JUN-11 03.19.41.000000000 PM','1027','51010','BIR'
)
3 ;
values ('9569333585','29-JUN-11 03.19.41.000000000 PM','1027','51010','BIR')
*
ERROR at line 2: ORA-01830: date format picture ends before converting entire input string

Table Structure is

Name Null? Type
----------------------------------------- -------- ---------------------------
ANI VARCHAR2(10)
DATE_TIME DATE
DURATIONS VARCHAR2(10)
SRC VARCHAR2(10)
CIRCLEID VARCHAR2(10)
SQL>

View 11 Replies View Related

Real Application Clusters :: How To Completely Shutdown The Entire Crs Stack In All Nodes

Nov 11, 2013

Grid version: 11.2.0.4Platform : OEL 6.4 

To shutdown the entire crs stack, I ran crsctl stop cluster -all from one node. After the command execution,  the below mentioned processes were still running on all nodes.   

[root@intsmdp01 ~]# ps -ef | grep d.binroot     38562 33228  0 19:12 pts/2    00:00:00 grep d.binroot     80820     1  0 Nov09 ?        00:20:56 /crs/product/11.2.0/bin/ohasd.bin rebootgrid     81448     1  0 Nov09 ?        00:01:22 /crs/product/11.2.0/bin/mdnsd.bingrid     81459     1  0 Nov09 ?        00:01:55 /crs/product/11.2.0/bin/gpnpd.bingrid     81473     1  0 Nov09 ?        00:17:26 /crs/product/11.2.0/bin/gipcd.binroot     81484     1 20 Nov09 ?        09:10:27 /crs/product/11.2.0/bin/osysmond.bin  

So , I had to manually run crsctl stop crs on all nodes. 

[root@intsmdp01 ~]# /crs/product/11.2.0/bin/crsctl stop crsCRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'intsmdp01'CRS-2673: Attempting to stop 'ora.crf' on 'intsmdp01'CRS-2673: Attempting to stop 'ora.drivers.acfs' on 'intsmdp01'CRS-2673: Attempting to stop 'ora.mdnsd' on 'intsmdp01'CRS-2677: Stop of 'ora.crf' on 'intsmdp01' succeededCRS-2673: Attempting to stop 'ora.gipcd' on 'intsmdp01'CRS-2677: Stop of 'ora.mdnsd' on 'intsmdp01' succeededCRS-2677: Stop of 'ora.drivers.acfs' on 'intsmdp01' succeededCRS-2677: Stop of 'ora.gipcd' on 'intsmdp01' succeededCRS-2673: Attempting to stop 'ora.gpnpd' on 'intsmdp01'CRS-2677: Stop of 'ora.gpnpd' on 'intsmdp01' succeededCRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'intsmdp01' has completedCRS-4133: Oracle High Availability Services has been stopped.[root@intsmdp01 ~]#  

Is there a way to bring down the entire crs stack in all the nodes in the cluster from one node ?

View 3 Replies View Related

SQL & PL/SQL :: ORA-01830 / Date Format Picture Ends Before Converting Entire Input String

Feb 26, 2013

when i run this query i am facing date format error.

select sbrueregister.UEIMSI,sbrueregister.fapid,sbrfapslid.slid,sbrfapslid.ACTIVATION_TS,sbrfapslid.DEACTIVATION_TS from SBRFAPSLID INNER JOIN sbrueregister ON sbrfapslid.fapid=sbrueregister.fapid where sbrfapslid.slid='1234567890' and sbrueregister.registeredat between TO_DATE('2013-02-1.12.0. 10. 123000000','YYYY-MM-DD HH.MI.SS.SSSSSS')and TO_DATE('2013-02-1.12.9.10.123000000', 'YYYY-MM-DD HH.MI.SS.SSSSSS');

ORA-01830: date format picture ends before converting entire input string

View 1 Replies View Related

SQL & PL/SQL :: JSP Limitations - Date Format Picture Ends Before Converting Entire Input String

Feb 21, 2010

I am using

SELECT TO_CHAR(TO_DATE((:BILL_VALUE),'J'),'Jsp') FROM sys.dual;

its givinig right result for

1023411->One Million Twenty-Three Thousand Four Hundred Eleven

but if i will change it to 10234111 then this query giving an error

ORA-01830: date format picture ends before converting entire input string.

Is there any limitation or is there any other built in function to get the number to a word.

View 2 Replies View Related

Creating SQL Script That Can Update Info From One Table In Dbase1 To Another Table In Dbase2?

May 16, 2013

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?

View 3 Replies View Related

Application Express :: Insert Into History Table When Update Action Is Performed On Tabular Form View Or Table

Aug 24, 2013

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

View 2 Replies View Related

Performance Tuning :: Update Columns Of One Table Using Another Table

Feb 6, 2011

I am trying to update columns of Table A with the columns of Table B. Both these tables have 60,000 rows each. I tried this operation using following 2 queries:

Query 1

Update TableA A
set
(A.col1,A.col2,A.col3)=(select B.col1,B.col2,B.col3
from TableB
where A.CODE=B.CODE)

Query 2
Update TableA A
set
(A.col1,A.col2,A.col3)=(select B.col1,B.col2,B.col3
from TableB
where A.CODE=B.CODE)
where exists
A.code = (select B.code
from TableB B
where A.code=B.code)

When i execute these two above queries, it keeps executing indefinitely.

View 4 Replies View Related

Oracle 10g - Take Snapshot Of Table Before Insert Or Update Happens To That Table

Dec 28, 2010

I need to take a snapshot of a table before insert or update happens to that table.... in oracle 10g. I am reading the MV docs from oracle and below link..

[URL].......

how MV should be written for this and how to schedule it in dbms_jobs for auto refresh?

assuming that t1 is the table where DML operation are goin to happen so before any insert or update, snapshot has to be taken, and I am assuming that to do this it would look something like this?

create materialized view my_view refresh fast as select * from t1;

create materialized view log on my_view;

and then schedule in a dbms_jobs?

View 1 Replies View Related

SQL & PL/SQL :: Update A Table Using Another Schema Table Of Same Style?

Aug 14, 2011

Can we update a table using another schema table of same style?

Example:

Update employees
set employee_name=hr.employees.employees_name
where employee_id=100;

View 8 Replies View Related

SQL & PL/SQL :: Update Table From Other Table Over Database Link

Aug 14, 2010

i have two databases and created the link between them. I can easily query the data but when i need to update my local records from the remote its showing an error

SQL> update laptop set name =
2 (select name from laptop@ora_link1 where id between 2 and 4)
3 where id between 2 and 4;

(select name from laptop@ora_link1 where id between 2 and 4)
*
ERROR at line 2: ORA-01427: single-row subquery returns more than one row

select multiple rows from the remote db and update them in the local db.

View 2 Replies View Related

Backup & Recovery :: Backing Up / Restoring Oracle Database Via Entire Backup Of Server

Aug 7, 2012

I've read a lot about the different types of backup available with Oracle (hot and cold backup). However, I was thinking of a different way of performing this task. I'm currently using Windows Server 2008 R2 and Oracle 11g Standard Edition. I'd like to schedule an entire backup of my server via the utility "Windows Server Backup" (available for free).That way, I could recover my entire server with all the programs and files in case the latter crashes.I'm wondering if this solution could be used as a way of backing up (and recovering) the Oracle database. Should I still set up a regular hot backup with the Archivelog mode enabled in case some operations/transactions were being done at the time of the crash (for the data integrity)?

View 2 Replies View Related

Backup & Recovery :: Backup Entire Database / Without Archived Logs

May 13, 2011

Backup entire database, without archived logs, while the database is open for user activity and also This backup should be the base for an incremental backup strategy.

View 10 Replies View Related

Using Trigger On One Table To Update Another Table

Aug 28, 2011

My problem is the following:

At the time when P_delivery_date changes in P_ORDERS I want to transfer P_delivery_date to S_delivery_date in S_ORDERS for corresponding records.

Tables:

purchase orders table P_ORDERS:
P_order_number,
P_poz_number,
S_order_number,
S_poz_number,
P_delivery_date

sales orders table S_ORDERS:
S_order_number,
S_poz_number,
S_delivery_date

My question is:

Is it possible and what would be the solution using the TRIGGER on table P_ORDERS to update S_delivery_date with P_delivery_date in S_ORDERS?

View 2 Replies View Related

SQL & PL/SQL :: Update Table After Insert To Another Table

Aug 8, 2012

how to adjust a total (counter) after a record is inserted into a table.

the dilemma i am facing is we are using third party software for our fundraising operations so I have no control over what gets done in the background as users process their daily batches into the system. below is the scenario:

create table paytable(
idnumber number(12),
appealcode varchar2(20),
batchno number(8),
trantype varchar2(3),
transnum number(8),
split_transnum number(8));

[Code]..

IDNUMBER APPEALCODE BATCHNO TRA TRANSNUM SPLIT_TRANSNUM
---------- -------------------- ---------- --- ---------- --------------
16084 DVFG1206 20120808 PP 23853576 1133821
16084 DVFG1206 20120808 PPO 23853577 1133821
1234 DVFG1206 20120808 PP 23853578 1133822

create table appealtable
(appealcode varchar2(20),
total# number(9),
total$ number(13,2));

[Code]...

APPEALCODE TOTAL# TOTAL$
-------------------- ---------- ----------
DVFG1206 100 2500

during batch posting records are inserted into the paytable, on some pledge donations donors will send overpayments when fulfilling a PLEDGE(as is the case with donor 16084) therefore the system will split the payment during the process and will assign a trantype of 'PP' to the exact pledge amount and a 'PPO'(pledge payment overage) towards the balance. additionally as records get inserted into paytable there is counter of those paytable records going into the appealtable for that particular appealcode so in the case above when batchno 20120808 is completed appealtable.total# will show 103 and total$ will show $2532($10,$12,$10,,,I did not include payment$ since that is not the focus of this issue and will not change).

mgt wants the counter into the appealtable to be 2 instead of 3 records since the two records that were split(same split_transnum) should be recorded as one response not two.

I have tried writing an after insert trigger(dreaded mutating table error) and can't seem to figure out how to update the counter to the appealtable after records are inserted into paytable. below is some code I've been working with but it's not working.

CREATE OR REPLACE TRIGGER PPO_Payment
AFTER INSERT ON paytable
FOR EACH ROW

DECLARE
p_cnt NUMBER :=0;

[Code]...

show errors

View 1 Replies View Related

PL/SQL :: Update Table Based On The Value Of Another Table

Apr 21, 2013

Using Oracle 11g SOE R2...

I used to have two tables to store details of PROPERTIES e.g UNIT_COMMERCIAL , UNIT_RESIDENTIAL. I need to combine both of them into one table called UNIT. I moved the data to the new table, but now I am stuck how to update the values of the child table which called MARKETING.

---

Now, I have two tables:

MARKETING (ID   NUMBER  PK , OLD_UNIT_ID NUMBER FK ...)
UNIT (NEW_ID  NUMBER  PK , OLD_UNIT_ID  NUMBER  ... )

I need to update the value of OLD_UNIT_ID in Marketing table to be equal to NEW_ID in the table of UNIT. As you can see the values of OLD_UNIT_ID in both tables are the same.

I used this statement

update ( SELECT  distinct  M.UNIT_ID , U.OLD_ID , U.ID FROM MARKETING M INNER JOIN UNIT U ON (M.UNIT_ID = U.OLD_ID))  set unit_id = idBut I got this error:

ORA-01732: data manipulation operation not legal on this view??

View 8 Replies View Related

PL/SQL :: Update Table With Values From Another Table

Dec 19, 2012

I've tried to Update table with values from another table, but I couldn't.Is there a single way to do that with just one query?Below goes an example:

CREATE TABLE TEST1 (
     COD NUMBER,
     DCR VARCHAR2(10) );

CREATE TABLE TEST2 (
     COD NUMBER,
     DCR VARCHAR2(10),
     DCR2 VARCHAR2(10) );
[code]....

I want to UPDATE the field DCR of the table TEST1 with the VALUE of the field DCR2 of the table TEST2.At the end, after the update, the table TEST1 would be like that:

SELECT * FROM TEST1

COD --- DCR
------------------
1 'TESTE3'

View 6 Replies View Related

PL/SQL :: Update Row In Table And Insert Same Row Into Another Table?

Sep 6, 2013

I want to update a row in a table say Table A and the updated row should be inserted into another table say Table B. I need to do it in a single SQL query and i don't want to do it in PL/SQL with triggers. And i tried with MERGE statement but its working with this scenario.

(Note: I'm using Oracle Database 10g Enterprise Edition Release 10.2.0.1.0).

View 8 Replies View Related

SQL & PL/SQL :: Update From Other Table Value?

Oct 26, 2010

I am having a requirement to update a table column from min value of column from other table..

Since i cannot format the code well here..I attaching one pdf where we have details and requirements..

View 7 Replies View Related







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