SQL & PL/SQL :: Code To Update Table In 11g

Jul 17, 2013

I have a table extended_values_Test, with the following structure:

CREATE TABLE extended_values_Test
(
MATRL_NUM VARCHAR2(18 BYTE),
PRI_SHIP_LOC CHAR(4 BYTE) NOT NULL,
PLANT CHAR(4 BYTE) NOT NULL,
STD_COST NUMBER,
SNAPSHOT_DATE CHAR(8 BYTE)
)

Snapshot_date is last working date of that month, and is recorded only once in the table for a matrl_num & plant combination.

Inserting records in the table:
Refer to insert_extended_values.sql
Note: This is just a sample table , having records for only two matrl_num.

after inserting records , you can see the records are for two different matrl_num's. For each matrl_num , we can have multiple values of plant and for each plant we can have different value of std_Cost(eg:matrl_num='0023173556').

Moreover for each matrl_num , I have some records in each month , based on the number of plants(with std_cost). Std_cost for a plant is updated in the table whenever there is a change in it from the previous value.In case of no change in value of std_cost over previous month's value , we have 'Null' std_cost for that matrl_num, plant & month(next month).

I want to update these 'Null' values with latest(last month's 'not null' - std_cost) std_cost for that matrl_num and plant. Such change in std_cost can occur multiple times , so we need to update the 'Null' std_cost till the time we have encountered a change in std_cost and so on.

View 14 Replies


ADVERTISEMENT

SQL Code For Update Using Join Conditions?

Jan 8, 2011

novice to SQL (Oracle 10g)

Am trying to write code for sollowing scenario:

Have 3 tables
table1 (campaignid,promoflag)
table2 (campaignid,projectid,campaigndesc)
table3 (projectid,promoflag,projectstart,projectend)

I am to update table 1 promoflag with value from promoflag in table3

Update table1
set promoflag = table3.promoflag

I would like to make sure only appropriate record is updated therefore want to use where clause condition but the primary key for table1 and table3 are different, the only link can be found on table2.

I want to use condition where table1.campaignid=table2.campaignid and table2.projectid=table1.projectid

Have used the following without success:

Scenario 1

Update table1
SET promoflag = table3.promoflag
FROM table1
inner join table2 on table1.campaignid = table2.camapaignid
inner join table3 on table2.projectid = table3.projectID;

Error at line 1 ORA-00933: SQL command not properly ended

Scenario 2 with real table/column names

Update UA_CAMPAIGNEXTATTR
SET CFPROMOTABLE = LMUK_PROJECT_AUDIENCE_GRID.GRID_AUD_CFPROMOTABLE
FROM UA_CAMPAIGNEXTATTR,UA_CAMPAIGN,LMUK_PROJECT_AUDIENCE_GRID
WHERE
UA_CAMPAIGNEXTATTR.CAMPAIGNID = UA_CAMPAIGN.CAMPAIGNID
AND UA_CAMPAIGN.PROJECTID = LMUK_PROJECT_AUDIENCE_GRID.GRID_AUDIENCE_ID;
Error at line 2
ORA-00933: SQL command not properly ended

It appears to get block with the 'FROM' statement (was underlined in red)

View 1 Replies View Related

PL/SQL - Wrote Code Successfully But Cannot Bulk Update It?

Dec 15, 2008

Has 2 tables with the following columns

1.abc_y (notes,frmt)
sample data:
notes:
i live in texas and work in AIG.

2.abc_z(tags_name)
sample data:
tags_name:

live in work.We shoud look for tags_name in notes field of abc_y. If we encounter any tags_name in notes field they should be removed and inserted in frmt field Now the column format should be updated to 'i texas and AIG'.abc_y has 2 million rows and abc_z has 120 rows.wrote the code sucessfuly but cannot bulk update it, which is really needed for me.

I am havin some problm in FOR LOOP after FORALL in the folowing.

DECLARE
l_sql_strng VARCHAR2 (20000);
TYPE var_tab IS TABLE OF VARCHAR2 (20000)
INDEX BY BINARY_INTEGER;
l_text_arry var_tab;
CURSOR c1

[code]...

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

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

Reports & Discoverer :: Bar Code Reader Will Readout Code

Feb 12, 2009

I have no knowledge about Barcode. The problem is an issue of Loyalty Cards of a Hotel and Restaurant to various customers and then these cards will be presented by the customers time to time in the Hotel as well as Restaurant. The Owner of the Hotel and Restaurant wants to generate separate barcode for each card and when this card will be presented then the bar code reader will readout the code and the system will calculate the amount of discount/rebate. Because if the data entry operator enter the code of the card through key board the it will be a chance of leakage or misuse of that card.

View 8 Replies View Related

SQL & PL/SQL :: Compare SVN Source Code With Production Code In Database

May 30, 2012

I have to compare my SVN source code (packages, views etc) with the production code in the database like views etc (actually we are not sure that what we have in the svn is the final version of production code, we have objects created in the production database, but we don't have latest scripts for that. we have to deploy the svn code in the UNIX box).

So here the comparison is between the OS files and the database objects.

I thought I would get scripts of all the packages, views etc from the production database by using DBMS_METADATA or some utility and save the code in OS files then compare one svn file with OS file manually by using some comparison tools e.g toad provide one comparison tool.

View 5 Replies View Related

SQL & PL/SQL :: Code To Create Heirarchy Table

Aug 21, 2013

I am trying to load one parent-child hierarchy table. I have a table XXX, which contains the columns containing parent, child , level and many more columns in it. Now I need to use the table XXX to load my parent-child hierarchy table. How can I perform this using SQL.

For eg :

XXX
----
Child Parent col1 col2
aaa bbb 123 345
ccc bbb 3456 786
bbb ddd 123 2345

I tried using the DML script generated in OBIEE for Parent/child heirarchy, but not sure the script is taking huge amout of time.

Heirarchy table
---------------
Child Parent
aaa bbb
ccc bbb
bbb ddd

View 4 Replies View Related

PL/SQL :: How To Find Source Code Of The Table

Sep 4, 2013

i have a existing table called table_1 to did some changes to it, but i need to do a source search to find where allĀ  in the code that we reference this code to ensure that there is not a variable declaration that sets this to a specific number. how do i do a source search. i alter a existing column (overbook_max) to number(2) tonumber (3)

View 8 Replies View Related

SQL & PL/SQL :: Code To Insert Into BLOB Table

Jul 6, 2012

I've searched for code but I didn't found any code which insert into table BLOB without using a specific Directory . my table is

create table ASSETS
(
v_id NUMBER not null,
v_name VARCHAR2(100),
v_content BLOB
)

I want to insert a JPG file.

View 15 Replies View Related

SQL & PL/SQL :: Type In Code To Make Few Tables Then Run Code

Feb 13, 2012

I downloaded oracle sql developer, i type my code into a worksheet but if i use the run statement option, it asks me to make a connection. I dont want to make a connection, just test the data locally.However, even if I do try and make a connection, i get ora-12560 error (local connection).

I just want to type up some data to make some table and test to retrieve or manipulate the data. I'll use any program, command line or gui.

View 7 Replies View Related

SQL & PL/SQL :: Export Of Table Data To Excel Through Code?

Jan 20, 2012

code for:

Export table data to excel file in Oracle PL/SQL.

View 3 Replies View Related

SQL & PL/SQL :: Code To Load Data From Text To Table

Oct 14, 2012

What is the best way to load the data from text file to the table in PL/SQL .How can i write a program for that ?

-data is separated by ',' in text file for each columns in table

View 19 Replies View Related

How To Extract DDL Of Table / Package / Procedure Using SQL Code

Jan 5, 2007

how to extract the ddl of a table/package/procedure using SQL Code?

I found a method, but it's only supported from Oracle9i and obove, im using Oracle8i

View 3 Replies View Related

Forms :: Fetch Record From Table Using Item Code

Jun 7, 2013

i want to fetch the data in my form from table, by using item_code.

eg when i write the item_code its value get matched in same table and fetch the another

columns by item_code.

View 1 Replies View Related

SQL & PL/SQL :: HTML Source Code Inserted Into Table As CLOB

Sep 24, 2010

I got html source code inserted into the table as CLOB (or BLOB). And I would like to search a some word from that.When I find a some value I can write this one into the column.It would be easy if this code is xml but isnt.

View 12 Replies View Related

SQL & PL/SQL :: Hard Code Bad File Names For External Table

Apr 7, 2011

I have question on the following, that gets defined for the bad file and the log file

BADFILE 'bad_%a_%p.bad'
LOGFILE 'log_%a_%p.log'

What does the %a and % p indicate? Also if I wanted to get the value of %p and %a into a variable how would I do it? I want to be able to append %p and %a to the below variable, but unsure how to achieve it..

l_badfile := file_nm || '.bad' ;

View 3 Replies View Related

SQL & PL/SQL :: Write A Code Which Dynamically Reads The Table Name From User_tables?

Oct 7, 2013

Firstly the table structures :

Create table cd_patient (pat_mrn varchar2(100)) ;
Create table cd_encount (pat_mrn varchar2(100), encounter_id varchar2(1000));

I need to write a code which dynamically reads the table name from user_tables (starting with cd) & load the data from remote database which has the same table name & structure based on the load number..

Static PLSQL statement would be :

declare
BEGIN
INSERT INTO "cd_patient" C-- DYNAMICALLY GET THE TABLE NAME
SELECT C1.PAT_MRN -- DYNAMICALLY GET THE COLUMN NAME
FROM REMOTE_DB.CD_PATIENT@XXX C1
WHERE C1.LOAD_NUMBER > 2;

[code]....

View 11 Replies View Related

SQL & PL/SQL :: Using Bulk Collect For Insert Into Table - Internal Error Code

Sep 29, 2011

Using the Bulk collect for insert into table,it's raising the below error.

ORA-00600: internal error code, arguments: [25027], [130], [1], [], [], [], [], [], [], [], [], []

View 5 Replies View Related

JDeveloper, Java & XML :: Select Records From Table Where XML Code Complies With Requirement

Dec 7, 2010

I got table with column of XML codes like this:

select xmltype ('<parameters xmlns="http://datalan.sk/webreporting/params/v1_0">
<parameter name="result"><value>success</value></parameter>
<parameter name="showBirthday"><value>false</value></parameter>
<parameter name="_wrCommand"><value>clearCacheBefore</value></parameter>
</parameters>'
) val
from dual;

And I need to select records from table where xml code complies with requirement:

name="result" equals success.
name="result"><value>success</value>

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

SQL & PL/SQL :: Ad Hoc MINUS - Compare Values In Code To Values In Table

Oct 28, 2013

I am searching the simplest way for ad hoc MINUS.I do:

SELECT *
FROM uam_rss_user_XXXXXXX
WHERE host_name IN
('XXX0349',
'XXX0362',
'XXX0363',
'XXX0343',
'XXX0342',
'XXX0499',
[code]....

and look in the table which values are missing (values that are in host_name IN but not in actual table).is there a simpler way for doing an ad hoc MINUS? I know to insert values in temp. Table. How are experienced Oracle pros doing this task?

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







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