Solved

Open & Fetch cursor for inserting into temp table in oracle

Posted on 2015-01-09
16
1,818 Views
Last Modified: 2015-01-16
I have to fetch the values returned by the first set of query to an Open Cursor and Insert into a Temp Table.

I am  not familiar with Open - Fetch Cursor, Looking for syntax help !!


-- This query returns 101 row of 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';


-- I have to pass each row of record into the select query paramter and insert the Temp Table.x`x`

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 =   [ VALUE WHICH I GET FROM MINUS QUERY ]

Open in new window

0
Comment
Question by:chokka
  • 5
  • 4
  • 4
  • +3
16 Comments
 
LVL 10

Assisted Solution

by:HuaMinChen
HuaMinChen earned 167 total points
ID: 40541706
See this example
1  DECLARE
 2     l_total       INTEGER := 10000;
 3
 4     CURSOR employee_id_cur
 5     IS
 6          SELECT employee_id
 7            FROM plch_employees
 8        ORDER BY salary ASC;
 9
10     l_employee_id   employee_id_cur%ROWTYPE;
11  BEGIN
12     OPEN employee_id_cur;
13
14     LOOP
15        FETCH employee_id_cur INTO l_employee_id;
16        EXIT WHEN employee_id_cur%NOTFOUND;
17
18        assign_bonus (l_employee_id, l_total);
19        EXIT WHEN l_total <= 0;
20     END LOOP;
21
22     CLOSE employees_cur;
23  END; 

Open in new window

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40541840
Well usually you either do not need a temp table orthe cursor. Why is this need of this combined need?
0
 
LVL 34

Accepted Solution

by:
johnsone earned 167 total points
ID: 40541962
There actually isn't a need for all the code.  Oracle will do a lot of it for you if you just ask it to.

Here is your code with a cursor:
BEGIN 
    FOR c1rec 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') LOOP 
        BEGIN 
            INSERT INTO temptcompgrp_context_0 
            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 = c1rec.svc_pvr_pyr_nr; 
        END; 
    END LOOP; 

    COMMIT; 
END; 
/ 

Open in new window

But, there really isn't a need for the cursor at all, you can simply do it all in one statement:
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'); 

Open in new window

0
 

Author Comment

by:chokka
ID: 40542438
Thank you , Thanks a lot !! Getting into oracle gradually - Thanks !!
0
 

Author Comment

by:chokka
ID: 40542453
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'

Open 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'); 

Open in new window

0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 166 total points
ID: 40542481
Might I also suggest that you use a Global Temporary Table.

This is a special table in Oracle that you create once and just keep reusing.  Make makes them special is that only the session that inserts data into the table can see/use the data.

Once the session ends, the data automatically gets cleared.

http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_7002.htm#SQLRF54642

Create it once then insert into it:
Insert into  temptcompgrp_context_0
  SELECT *
...
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40542957
Well usually you either do not need a temp table orthe cursor. Why is this need of this combined need?
0
 
LVL 34

Expert Comment

by:johnsone
ID: 40543004
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.
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:chokka
ID: 40543142
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
0
 
LVL 34

Expert Comment

by:johnsone
ID: 40543153
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'; 

Open 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); 

Open 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.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40543175
the requirement looks like you can do all of this in 1 single MERGE or UPDATE statement, with a inline "subquery" ...
0
 
LVL 34

Expert Comment

by:johnsone
ID: 40543224
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.
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 40544347
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.
0
 

Author Closing Comment

by:chokka
ID: 40548176
Thanks
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40554876
Well usually you either do not need a temp table orthe cursor. Why is this need of this combined need?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40554879
Well usually you either do not need a temp table orthe cursor. Why is this need of this combined need?
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now