Home » SQL & PL/SQL » SQL & PL/SQL » Using updated values and inserting into table (merged)
icon9.gif  Using updated values and inserting into table (merged) [message #345896] Fri, 05 September 2008 04:08 Go to next message
thisisnatesh
Messages: 92
Registered: March 2007
Member
Hi

I am using update command on a table
which updates 10 records. Now, i want to
store the effected records into a pl/sql table,
so that i can do a forall later
in the process.

Please suggest some ideas.


Thanks in Adv.
Natesh

icon9.gif  Using updated values and inserting into table [message #345898 is a reply to message #345896] Fri, 05 September 2008 04:10 Go to previous messageGo to next message
thisisnatesh
Messages: 92
Registered: March 2007
Member
Hi

I am using update command on a table
which updates 10 records. Now, i want to
store the effected records into a pl/sql table,
so that i can do a forall later
in the process.

Please suggest some ideas.


Thanks in Adv.
Natesh

Re: Using updated values and inserting into table [message #345901 is a reply to message #345896] Fri, 05 September 2008 04:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
select ... bulk collect into mytab from ... where ...

Regards
Michel
Re: Using updated values and inserting into table [message #345905 is a reply to message #345901] Fri, 05 September 2008 04:19 Go to previous messageGo to next message
thisisnatesh
Messages: 92
Registered: March 2007
Member
Hi Mic

Thanks for that quick reply.

Example purpose i gave it as 10 records.
But, the table is very large and update
is done based on checking 5 different conditions.

If i do it based on your reply,i have to
write a select beneath my update,
which has to fetch from 1000's of records
based on the same update condition.

This may sure effect my performance.

So, do we have other way to fulfill the
requirement.

Tx.
Natesh

Re: Using updated values and inserting into table [message #345908 is a reply to message #345898] Fri, 05 September 2008 04:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't multipost your question.

Regards
Michel
Re: Using updated values and inserting into table [message #345909 is a reply to message #345908] Fri, 05 September 2008 04:24 Go to previous messageGo to next message
thisisnatesh
Messages: 92
Registered: March 2007
Member

Am sorry for that Mic.
It was not done intentionally.
Re: Using updated values and inserting into table [message #345910 is a reply to message #345905] Fri, 05 September 2008 04:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to post more details.
Post a test case representativ of your problem.
Post what you've already done.
...

Regards
Michel
Re: Using updated values and inserting into table [message #345915 is a reply to message #345910] Fri, 05 September 2008 04:36 Go to previous messageGo to next message
thisisnatesh
Messages: 92
Registered: March 2007
Member
Hi

Please find the test case:

Quote:

CREATE TABLE TEMP
(PERIOD_N NUMBER,
DEPT VARCHAR2(100),
PROCID VARCHAR2(10),
VAL_n NUMBER);

Assume, Records are inserted into this table.


SQL> Update temp set procid='A1'
where period_n=10
and dept='ACCTS';



Now, While updating the records itself i want to store
modified dept,procid,val_n column values
into a plsql table.

if above command updates 100 records then
i want all 100 records into plsql table.

Like this i am having multiple update commands where
i am going to append into the same plsql table and
do forall at the end.

Tx in Adv.
Natesh

Re: Using updated values and inserting into table [message #345926 is a reply to message #345915] Fri, 05 September 2008 05:53 Go to previous messageGo to next message
thisisnatesh
Messages: 92
Registered: March 2007
Member
Hi Folks

I got the solution.

declare
upd1 varch......;
type t1 is table of ...;
t t1;
begin
upd1:='update command';
exexcute immediate up1 bulk collect into t;
end;

Cheers.
Natesh
Re: Using updated values and inserting into table [message #345933 is a reply to message #345926] Fri, 05 September 2008 06:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post an actual and tested solution to not mislead others; your solution is not correct.
Here's an example:
SQL> declare
  2    type mytab is table of emp.empno%type;
  3    emptab mytab;
  4  begin
  5    execute immediate 
  6      'update emp set sal=sal*2 where deptno=10 returning empno into :1'
  7      returning bulk collect into emptab;
  8    for i in 1..emptab.count loop
  9      dbms_output.put_line(emptab(i));
 10    end loop;
 11  end;
 12  /
7782
7839
7934

PL/SQL procedure successfully completed.

SQL> select empno from emp where deptno=10
  2  /
     EMPNO
----------
      7782
      7839
      7934

3 rows selected.

Regards
Michel
Re: Using updated values and inserting into table [message #345966 is a reply to message #345933] Fri, 05 September 2008 07:31 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Why is everyones first instint these days to use Dynamic SQL?
What's wrong with good old fashioned normal SQL?
create table test_0076  (col_1 number, col_2 number);

insert into test_0076 (select level,level from dual connect by level <= 10);

declare
   type ty_t_0076 is table of test_0076%rowtype index by binary_integer;
   
   t_0076 ty_t_0076;
   
begin
  update test_0076
  set    col_2 = col_2 + 1
  returning col_1,col_2 
  bulk collect into t_0076;
  
  dbms_output.put_line(t_0076.count);
end;
/
Re: Using updated values and inserting into table [message #345980 is a reply to message #345966] Fri, 05 September 2008 08:03 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, you are right ./fa/3518/0/ I should put the example in static SQL instead of keeping in dynamic way OP posted.

Regards
Michel
Previous Topic: AGGREGATE FUNCTIONS
Next Topic: DYNAMIC SQL - GLOBAL VARIABLES
Goto Forum:
  


Current Time: Sat Dec 03 22:07:26 CST 2016

Total time taken to generate the page: 0.15227 seconds