SQL & PL/SQL :: Cursors To Update Data And Store It Back To Same Table

Sep 4, 2010

I'm using few cursors to update my data and store it back to the same table. But for some reason the cursor seems to be picking obsolete data.

cursor c1
is
select distinct roles
from table1
where flag = '1';

cursor c2
is
select distinct roles
from table1
where flag = '1';
begin
for i in c1
loop

here im updating the roles im picking to to a suffix and role.

update table1
set role = suffix_role
where 'some condition';
end loop;
commit; -- committing so changes are visible for my next cursor.

for j in c2
loop

here im deleting all the roles that are not part of my comparing table.

delete from table1
where role = 'something';

But in my debugging table i see that its deleting roles present as input for first cursor, whereas it should actually pick data with suffix_roles.

View 12 Replies


ADVERTISEMENT

SQL & PL/SQL :: Query To Store Results - Update Values In Another Table?

Feb 22, 2010

I have a query like this -

SELECT
FIELD_A,
FN_FUNCTION(CARVE_ID, 1) FIELD_B,
FN_FUNCTION(CARVE_ID, 2) FIELD_C,
FN_FUNCTION(CARVE_ID, 3) FIELD_D,
FN_FUNCTION(CARVE_ID, 4) FIELD_E,
FN_FUNCTION(CARVE_ID, 5) FIELD_F,
FN_FUNCTION(CARVE_ID, 6) FIELD_G
FROM TB_CARVE;

When I execute the query, it returns the data (approx - 40,000 rows) in 1 min.But when I try to insert this data into another table (or create a table of this data) it takes me about 2 hours.

Tried using Materialized view, its again the same the refresh takes 2 hours.Basically here, what I am trying to do is the data from the above query is used to update the values in another table.What ever the procedure I am trying it takes 2 hours.

View 6 Replies View Related

SQL & PL/SQL :: Cursors To Load Data Into A Table?

Oct 3, 2011

DB version: Oracle DB 10g Enterprise Edition 10.2.0.4.0

I have the following four tables:

tab_main- which lists main projects
tab_sub_main - which lists sub projects
tab_budget - amounts per projects/subprojects
tab_total - I want to load the data here

The table script with data is attached.

I want to load data into tab_total fields for prj_type= 'J' as follows:

1. accn_no from tab_main table.

2. fy from tab_budget table

3. fy_total_amt which is the sum(amt) from tab_budget table by accn_no and fy

4. all_FY_amt which is the sum(amt) from tab_budget table by accn_no

5. all the audit fields- date/user inserted/updated will come tab_budget table

how to create this procedure with cursors.

CREATE OR REPLACE PROCEDURE LOAD_DATA_INTO_TAB_TOTAL_PROC
IS
CURSOR C IS
select distinct m.accn_no, a.control_no,m.prj_type,
b.fy, b.amt, b.user_created, b.date_created, b.user_last_mod, b.date_last_mod
from tab_main m,
tab_sub_main a,

[code]....

CREATE TABLE tab_main
(
ACCN_NO NUMBER(7) NOT NULL,
PRJ_TYPE VARCHAR2(1 BYTE) NOT NULL
)
/
Insert into TAB_MAIN
(ACCN_NO, PRJ_TYPE)

[code]....

View 34 Replies View Related

SQL & PL/SQL :: Data Should Be Updated Back To Table

Aug 16, 2011

The requirement is, the combination of col1,col2,col3 and col4 should always be unique, and wherever the col1, col2,col3 are same then col4 should be the sequence, starting from 1. Likewise the data should be updated back to the table.I'm able to do this using PL/SQL. Can I do the same using a single update statement?

create table tab1 (col1 number(5), col2 number(5), col3 number(5), col4 number(5));

Existing Data:

insert into tab1 values (101,521,3,1);
insert into tab1 values (101,521,3,1);
insert into tab1 values (101,522,3,2);
insert into tab1 values (101,522,3,2);
insert into tab1 values (101,523,3,1);
insert into tab1 values (101,523,3,2);
[code]....

View 3 Replies View Related

Store Procedure Insert / Update?

Dec 7, 2011

Store procedure code, I want to insert data in a database in this fashion,I want to check first if the record exist, if not Insert or else Update.

View 2 Replies View Related

Update Store Procedure (List Of Input)

Jan 10, 2012

The current update store procedure that I have updates a list of input provided, but it there are fields that are left blank, they are being updated as null in the database.

I'm having a trouble creating a store procedure that will just update the provided fields only.

View 1 Replies View Related

PL/SQL :: Update Multiple Records Using Store Procedure?

Feb 27, 2013

i am trying to update multiple records using store procedure but failed to achieve

for example my source is

emp_name sal
abhi 2000
arti 1500
priya 1700

i want to increase salary of emp whose salary is less than 2000 it means rest two salary should get update..using stored procedure only

i have tried following code

create or replace procedure upt_sal(p_sal out emp.sal%type, p_cursor out sys_refcursor)
is
begin
open p_cursor for
select sal into p_sal from emp;
if sal<2000 then
update emp set sal= sal+200;
end i;f
end;

and i have called the procedure using following codes

set serveroutput on
declare
p_sal emp.sal%type;
v_cursor sys_refcursor;
begin
upt_sal(p_sal,v_cursor);
fetch v_cursor into p_sal;
dbms_output.put_line(p_sal);
end;

the program is executing but i should get o/p like this after updating

1700
1900

but i am getting first row only

2000

and record is not updating...

View 15 Replies View Related

Cursors - Display Data Gathered From 3 Different Tables?

Apr 30, 2010

I am stuck somewhere in my project, situation is that we are trying to display data gathered from 3 different tables, we used cursors for this, here is the cursor declaration.

DECLARE
procedure DocInfoCur(myWhere IN varchar2) IS

init_dept_id number;
init_dept_name varchar2(40);

rcvg_dept_id number;
rcvg_dept_name varchar2(40);

CURSORCUR_DOCINFO IS
SELECT DISTINCT I.CTN, I.DIARYNUMBER, I.INITIATINGDATE, T.TYPENAME, I.INITIATINGDEPT,
I.DOCUMENTSUBJECT, I.DESTINATIONDEPT
FROM DOCUMENTINFO I, DOCUMENTTYPE T, DEPARTMENT D

[code]...

This displays all the records out there.

Now, I have a search form where I need to filter the same data with different parameters, I have compiled the entered data in one string, now i need to join that string at the end of where clause of my cursor, how can I do that, i have tried concatenating both but received compile error, is there a way out in the current scenario???

View 1 Replies View Related

SQL & PL/SQL :: How To Remove Odd Rows From A Table Without Using Cursors

Nov 16, 2011

I have a table table1 with 2 crore records.

select * from table1;
id code updateddatetime
------------------------------------
1 10001 2011-10-21 15:31:21.390
2 10001 2011-10-21 15:31:22.390
3 10001 2011-10-21 15:31:21.390
4 10001 2011-10-21 15:31:22.390
5 10002 2011-10-21 15:31:22.390

I want to delete records like id 2 which has odd updated time which is more than id 3 updated time.

is there any alternatives without using cursors as it taking so much time to process.

View 12 Replies View Related

SQL & PL/SQL :: Deleted All Records From Table - How To Get Back

Nov 8, 2010

I have deleted all the records from the table.And I have committed.Now I want to get all the records back.

View 16 Replies View Related

SQL & PL/SQL :: Convert Data Back To Special Character Values?

Nov 4, 2011

Enterprise Edition Release 10.2.0.5.0

SQL> SELECT * FROM NLS_DATABASE_PARAMETERS where parameter = 'NLS_CHARACTERSET';
PARAMETER VALUE
------------------------------ ----------------------------------
NLS_CHARACTERSET AL32UTF8
SQL>

There is table (VIN_TEMP) in my company database containing following records. It seems like this table should contain some greek language special chracter values instead of this weird data.

SQL> select * from vin_temp;
ATTR
--------------------------------------------------------------------------------
���9999
���9998
���9997
���9997

[code]....

Client are reporting these records as invalid and requesting us to fix. As i investigated i found out, this table was created and loaded few year back. Client sent us one time files which we loaded into this table. I was able to find the code which was actually used to load this table, but unfortunately i was not able to find the raw files where we load this data from...

It seems like Previous Developer specified character set "UTF8" statement, in his sql loader script, to load this data. It seem those file contain some Greek language special character data which was not support by "UTF8" charater set and result in creating those invalid data. My Job is to fix these invalid records and convert them back to its original values which were present in the raw file. I tried to contact client and see if i can find out the raw files but no luck. I tried to use convert function as mention to convert this data from "UTF8" to our current character set format but no luck.

SQL> SELECT attr, dump(attr), convert(attr,'UTF8', 'AL32UTF8') from vin_temp;
ATTR DUMP(ATTR) CONVERT(ATTR,'UTF8','AL32UTF8'
------------------ ---------------------------------------------------------------- -----------------------------
���9999 Typ=1 Len=13: 239,191,189,239,191,189,239,191,189,57,57,57,57 ���9999
���9998 Typ=1 Len=13: 239,191,189,239,191,189,239,191,189,57,57,57,56 ���9998
���9997 Typ=1 Len=13: 239,191,189,239,191,189,239,191,189,57,57,57,55 ���9997
���9997 Typ=1 Len=13: 239,191,189,239,191,189,239,191,189,57,57,57,55 ���9997

[code]....

Here is the script to create table with those type of invalid records.

create table VIN_TEMP
(
attr VARCHAR2(50 CHAR)
);
insert into VIN_TEMP (attr) values ('���9999');
insert into VIN_TEMP (attr) values ('���9998');

[code]....

View 1 Replies View Related

SQL & PL/SQL :: Update Using Data From Another Table

Jan 19, 2012

table1: url_data

page_url category
learn.web/AU/eng/AWARD/efg null
learn.web/CN/eng/AWARD/efg null
learn.web/US/eng/AWARD/efg null
learn.web/UK/eng/AWARD/efg null
learn.web/JP/eng/AWARD/efg null
learn.web/CA/eng/AWARD/efg null

[Code]..

table2: country

country_code
AU
CN
US
UK
JP

[Code]...

Now I have to update category by using /CC/eng/ where CC is the country code from country

I can use where page_url LIKE '%/__/eng/' but here the problem is the last row. I have to update by looking up from country table.

View 6 Replies View Related

Store Data In CLOD Data Type - How To Create A Unique Index

May 20, 2013

We have been recommended to store data in CLOD data type.

Sample data: 1:2:2000000:20000:4455:000099:444:099999:....etc it will grow to a large number.

We want to create a Unique index, for functional reason. Is it advised to create a unique index on a CLOB datatype?

View 2 Replies View Related

SQL & PL/SQL :: How To Update Data From Backup Table

Nov 4, 2013

I have a table named employee with 10 records. Empid is the primary key and empsal contains salary of the employees. I created backup of this table and name of backup table is empbackup.

Now empsal was updated to empsal*10 in employee table and 10 more records were also inserted in employee table. Now I want to restore the empsal column for 10 employees from empbackup(as initially there were only 10 employees in employee table when empbackup was created) table how can I do that in PL/SQL or Oracle?

View 16 Replies View Related

SQL & PL/SQL :: Update Nested Table Data

Mar 12, 2012

SCOTT@orcl_11gR2> create or replace type nesttype as table of clob;
2 /

Type created.

SCOTT@orcl_11gR2> create table emp
2 (empnonumber,
3 enamevarchar2(1000),
4 hobbynesttype)
5 nested table hobby store as hobby_nt
6 /

Table created.

SCOTT@orcl_11gR2> insert into emp values
2 (1, 'name1', nesttype ('dancing', 'cricket','listening music','dancing'))
3 /

1 row created.

SCOTT@orcl_11gR2> insert into emp values
2 (2, 'name2', nesttype ('cooking', 'dancing','cooking'))
3 /

1 row created.

I want to update nested table record based on index suppose i want to update 3rd number of hobby for name2 employee

i have written the below query

SCOTT@orcl_11gR2>update Table(select hobby from emp
2 where empno= 2) e
3 set value(e)='new_value' where to_char(value(e)) = (select 4 to_char(tab1_element)
5 from (select rownum rn,

[Code]...

2 rows updated.

but the above query is updating 2 records but my requirement is it should update only third record

How can we update nested table based on index number

View 10 Replies View Related

Forms :: Update Table Data Via Oracle 10g

Jul 7, 2011

I am trying to develop a form consisting of a key block and a single data block. The problem is that the driving table is not the table that needs to be updated.

user wants the following layout:student Advisor
ID# name degree major Concentration ID# name
The driving table (TABLE A) will supply the first 5 fields. The advisor ID comes from (TABLE B).

The user needs to update the adviser ID# field associated with the student ID# field. The form is to be tabular listing all students. I've seen some info on using procedures to insert, delete, update, query and lock tables, but i'm just not sure if that's what is needed.

View 6 Replies View Related

Forms :: Insert Data And Then Update Table

Aug 5, 2010

i have a tabular form select * from emp and i want to create table and store there data in goup select empono,sal,com group by dept i want to insert in another table.

how i insert the data in table by forms front end and then update also when again click the button or any change occur in form insert into a select empono,sal,com group by dept

View 2 Replies View Related

PL/SQL :: Update Column Based On Sum Of Data From Another Table

Apr 17, 2013

We had two tables.

Table 1: matusetrans

ITEMNUM Location Quantity transdate
AM1324 AM1 2 12-4-12
AM1324 AM1 2 15-5-12
AM1324 AM1 3 10-6-12
AM1324 AM1 4 5-1-13

[Code]....

Table 2: Inventory

ITEMNUM STORELOC lastyear currentyear
AM1324 AM1 need sum(quantity) here need sum(quantity)
AM1324 AM2 need sum(quantity) here need sum(quantity)

We have to update the last year and current year columns with sum of quantities for each item from matusetrans table based on date at different location in Inventory table.

we had nearly 13,000 records(itemnum's with different location) in inventory table in DB we have to update entire records.

How to write an sql queries to update lastyear and currentyear columns with sum of quantities based on itemnum and location in Inventory table

Edit/Delete Message

View 6 Replies View Related

Update AGENTS Table With Data Based On Queries?

Feb 3, 2009

I was trying to update the AGENTS table with the data based on queries. But all the records updates with the same data. I can't understand why.

Write a PL script to populate these columns as follows:

TRAVEL_STATUS is one of three values:
GLOBETROTTER: agent has visited five or more different locations in the course of his missions
ROVER: agent has visited between one and four locations
SLOB: agent has been on no missions.

CONTACTS is the number of targets that share the agent's home location.

some of the tables and columns are
agents table: agent_id, location_id
targets table: target_id, location_id
missions_agents table: agent_id, mission_id
missions_targets: target_id, mission_id
missions table: mission_id, location_id

My code is

DECLARE
status AGENTS.TRAVEL_STATUS%TYPE;
contact AGENTS.CONTACTS%TYPE;
CURSOR loc_cur
IS
SELECT
a.AGENT_ID id,

[code].....

View 7 Replies View Related

SQL & PL/SQL :: Nested Table - Update Activity For Data Correction?

Mar 17, 2012

I've to do a update activity for data correction. There are about 1 crore records involved in this. I want to keep a log of the activity at least Sales No. so that I can validate my data correction. Main cursor is involved for this. The problem is if I insert each record for sales number in the loop then process will take about double time. So i decided to create a nested table and insert after each session finishes.

I'm talking about session here is. The table I'm updation is partition table. So I can have many sessions and each session updating different table partition. This way there is no lock, better performance. Server has 35 processors so I can at least have 25 sessiions.

I could have used varray for this. But I want to learn nested table also. Problem here is not to take different way to complete this activity but to learn why I'm facing this issue.

CREATE OR REPLACE TYPE OBJ_TXN_LOG AS OBJECT
(
SALES_NO VARCHAR2(24)
,sALES_SEQ NUMBER(4)
) ;

[code]...

View 18 Replies View Related

SQL & PL/SQL :: Script Store Data?

Feb 10, 2013

My requirement is to get primary key column name and there values of a table and store in a stage table

create table easy(id integer, event_rowid integer,txn_rowid integer,txn_name varchar2(200));

begin
for i in 1..5 loop
insert into easy values(i,'777'||i,i||89,'Ris'||i||i);

[code]...

I was able to fetch primary keys and there values for a table and store them in stage table but only 1 record , I am stuck when there are more than 1 record Here is what i tried

SQL> SET serveroutput ON
SQL> DECLARE
2 V_prim_key VARCHAR2(2000);
3 l_tab DBMS_UTILITY.uncl_array;

[code]...

If you look at the above code in the execute immediate where condition i have used id=1, which is 1 records only but if i change id <3 then there will be 2 records and i will get below error ORA-01422: exact fetch returns more than requested number of rows

Other problem if there are 3 primary keys as in above case i am fetching there values 1 by 1 , is there any way if i can fetch those values once in for all data to be stored in stage table in below format

TAB_NAME V_PEIM_KEY V_PRIM_DATA DT
------- --------- ------- ------
EASY TXN_NAME,EVENT_ROWID,ID Ris11,7771,1 10-Feb-13
EASY TXN_NAME,EVENT_ROWID,ID Ris22,7772,2 10-Feb-13

View 5 Replies View Related

PL/SQL :: Store XML Data In Tables

Jul 21, 2012

I Have to write a procedure which takes XML data and inserts into some tables.

If the XML format is fixed then i can use extract function for parsing and can insert into the tables.

But the problem is there is not fixed format for the xml.

Are there any built in packages which takes the xml data for parsing..

View 3 Replies View Related

To Store Data In Materialized View

Apr 14, 2011

I have a PL/SQL procedure which gathers data from multiple places as well as calculates some data. I want to store all this in a materialized view.

So, I created an object type (I've shortened the definitions):

CREATE OR REPLACE TYPE mf_record_type AS OBJECT
(identifier VARCHAR2(6),
name VARCHAR2(100));

Then created the table type of the object:

CREATE OR REPLACE TYPE mf_table_type IS TABLE OF mf_record_type;

Then in the stored procedure defined a variable of the table type:

v_mf_record mf_table_type := mf_table_type();

Then I loop and populate the record type:

v_mf_record.EXTEND(1);
v_mf_record(x) := mf_record_type(v_rec.identifier, v_mf_detail.name);

When all that is done I try and create the materialized view:

EXECUTE IMMEDIATE ('CREATE MATERIALIZED VIEW mf_snapshot_mv AS
SELECT * FROM TABLE (CAST (v_mf_record AS mf_table_type))');

ORA-00904: "V_MF_RECORD": invalid identifier

Am I doing something wrong here? Can't I create the materialized view based on something other than a physical table?

View 4 Replies View Related

SQL & PL/SQL :: Store Large XML Data In Blob?

May 8, 2010

Since XML-files only contain character data, we could/should store it in a CLOB, rather than a BLOB.

But, One of my friend having a table where a column is defined as bloband came to know that XML data are being stored. I searched for some article with keyword 'How to insert large XML data in BLOB' But did not work.How to store the large xml content in a Blob and How to extract it?

View 2 Replies View Related

SQL & PL/SQL :: Splitting Data And Store Into Columns?

Feb 28, 2013

I have string like 'PRASAD,ALLEN,STEWART,SMITH'.

LIKE
COL1 COL2 COL3 COL4
-------------------------------
PRASAD ALLEN STEWART SMITH

I want to store the data into columns using SELECT statement only

View 7 Replies View Related

PL/SQL :: Column Wise Data Store

Feb 18, 2013

how to tune qurey for coulumn wise data saved.because we have to join same table n number of times.for reference go through the following scnarios.
     
Suppose one table T1 is there and it has two column KEY and VALUE.if we are writing qurey for retriving desire result in row manner we have to join samae table no of times.

KEY Value
     agreesWith true
id 1
     assessment False
     basisForDateOfProgression 1
     bestOverallResponse 2
     bestOverallUnknownComments data is ok

Qurey:

select * from (
select t.agreesWith from t1 t
)a

[Code]...

In this manner we can join upto bestOverallUnknownComments .so which method we follow to reduce the execution time and performance should be good.

View 1 Replies View Related

SQL & PL/SQL :: Store Procedure To Truncate And Copy Data

Jan 27, 2012

I am very new to oracle and SQL.I am trying to create a store proc that will copy 14 day of data into a table and then truncate the original table. When i compile following code....

CREATE OR REPLACE PROCEDURE STOPROC_TRUNCATE
( dateNum IN NUMBER )
IS
BEGIN
create table AUDIT_14Days as select * from AUDIT where TIMESTAMP >= (SYSDATE - dateNum);
truncate table AUDIT drop storage;
[code]....

View 8 Replies View Related

Forms :: Read Data From Excel And Store It In Blob?

Aug 10, 2010

I am getting the file using CLIENT_GET_FILE_NAME. I need to read the data from the .xsl file and convert it into blob. The file should not be stored in DB.

View 8 Replies View Related

SQL & PL/SQL :: Data Types To Store Large Integer Values?

Aug 15, 2012

what could be effective data type to store large integer values like, 50,000; 10,000,000 etc.?

View 3 Replies View Related

SQL & PL/SQL :: Store Data From Multiple Places In Materialized View?

Apr 14, 2011

I have a PL/SQL procedure which gathers data from multiple places as well as calculates some data. I want to store all this in a materialized view.So, I created an object type (I've shortened the definitions):

CREATE OR REPLACE TYPE mf_record_type AS OBJECT
(identifier VARCHAR2(6),
name VARCHAR2(100));

Then created the table type of the object:

CREATE OR REPLACE TYPE mf_table_type IS TABLE OF mf_record_type;

Then in the stored procedure defined a variable of the table type:

v_mf_record mf_table_type := mf_table_type();

Then I loop and populate the record type:

v_mf_record.EXTEND(1);
v_mf_record(x) := mf_record_type(v_rec.identifier, v_mf_detail.name);

When all that is done I try and create the materialized view:

EXECUTE IMMEDIATE ('CREATE MATERIALIZED VIEW mf_snapshot_mv AS
SELECT * FROM TABLE (CAST (v_mf_record AS mf_table_type))');

ORA-00904: "V_MF_RECORD": invalid identifier

Am I doing something wrong here? Can't I create the materialized view based on something other than a physical table?

View 1 Replies View Related







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