Open & Fetch cursor for inserting into temp table in oracle

Well usually you either do not need a temp table orthe cursor. Why is this need of this combined need?

Thank you , Thanks a lot !! Getting into oracle gradually - Thanks !!

On running this query, I get 45 records.

SELECT svc_pvr_pyr_nr 
                                FROM   tcomgrp 
                                WHERE  sls_grp_cd = 'IS' 
                                       AND sales_vew_cd = 'S1709' 
                                       AND dat_cx_tm_prd_nr = '85' 
                                MINUS 
                                SELECT svc_pvr_pyr_nr 
                                FROM   tcomgrp 
                                WHERE  sls_grp_cd = 'IS' 
                                       AND sales_vew_cd = 'S1709' 
                                       AND dat_cx_tm_prd_nr = '0'

Select allOpen in new window

When i use Create Temp Table - I get 720 records.

CREATE TABLE temptcompgrp_context_0 AS 
  SELECT * 
  FROM   tcomgrp 
  WHERE  sls_grp_cd = 'IS' 
         AND sales_vew_cd = 'S1709' 
         AND dat_cx_tm_prd_nr = '85' 
         AND svc_pvr_pyr_nr IN (SELECT svc_pvr_pyr_nr 
                                FROM   tcomgrp 
                                WHERE  sls_grp_cd = 'IS' 
                                       AND sales_vew_cd = 'S1709' 
                                       AND dat_cx_tm_prd_nr = '85' 
                                MINUS 
                                SELECT svc_pvr_pyr_nr 
                                FROM   tcomgrp 
                                WHERE  sls_grp_cd = 'IS' 
                                       AND sales_vew_cd = 'S1709' 
                                       AND dat_cx_tm_prd_nr = '0'); 

Select allOpen in new window

Well usually you either do not need a temp table orthe cursor. Why is this need of this combined need?

As for the difference in the number of rows between the 2 queries, a query with MINUS is going to remove duplicates.

We don't know your data, only you do.  Those 2 queries are different and I would expect that they return a different number of rows.

I need to get the missing records to a temp zone like temp table and update the dat_cx_tm_prd_nr  from 85 to 0.

Thats my goal.

So i requested for three steps

1) Minus query

2) Insert into Temp Table

3) Then update the dat_cx_tm_prd_nr  to 0

4) Push back to the original table : tcomgrp

5) If something goes wrong, i have to roll back the data in the tcomgrp

If I understand what you are trying to do, you don't need all those steps.

Temporary table to hold backup of information:

CREATE TABLE temptcompgrp_context_0 AS 
  SELECT orig_rowid, 
         tcomgrp.* 
  FROM   tcomgrp 
  WHERE  sls_grp_cd = 'IS' 
         AND sales_vew_cd = 'S1709' 
         AND dat_cx_tm_prd_nr = '85'; 

Select allOpen in new window

Then do the update of the original data from 85 to 0:

UPDATE tcomgrp 
SET    dat_cx_tm_prd_nr = 0 
WHERE  ROWID IN (SELECT orig_rowid 
                 FROM   temptcompgrp_context_0); 

Select allOpen in new window

The rowid from the original record is stored in the temporary table, that way it is easily found in the original table.  Also, that way you know that you only updated the records that you backed up.

If something goes wrong, then you can just rollback the update statement and everything is back the way it was.  You would also have a list of all the records you updated in the temporary table in case you need to refer back to it.

the requirement looks like you can do all of this in 1 single MERGE or UPDATE statement, with a inline "subquery" ...

I would say you could do it in a single statement, but it seems to me that they want to create a backup of the original rows.  I don't think you could do that in a single statement.  If you can, I would love to see it.  This is a common task that we do, so if it can be done in one step, that would be great.

Your question indicates that you may have some SQL Server experience, and you tell us that you are: "Getting into Oracle gradually".  If you do have SQL Server experience, do not assume that the way things are done in SQL Server is the best or only way to get things done in Oracle!   These are very different systems!  Some programming techniques that work well in SQL Server (like creating "temp" tables dynamically) are certainly *NOT* the best approach in Oracle.

If you describe the business problem you are trying to solve, we can help you with the best way to the task in Oracle.

Thanks

Well usually you either do not need a temp table orthe cursor. Why is this need of this combined need?

Well usually you either do not need a temp table orthe cursor. Why is this need of this combined need?