Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now
Course Of The Month
8 days, 2 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Open & Fetch cursor for inserting into temp table in oracle
Posted on 2015-01-09
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 !!
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 ]
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
5
4-
4
- +3
16 Comments
Active today
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;
Well usually you either do not need a temp table orthe cursor. Why is this need of this combined need?
Active today
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:
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;
/
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');
Active 5 days ago
On running this query, I get 45 records.
When i use Create Temp Table - I get 720 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'
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');
Active today
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 *
...
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 *
...
Well usually you either do not need a temp table orthe cursor. Why is this need of this combined need?
Active today
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.
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.
Active 5 days ago
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
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
Active today
If I understand what you are trying to do, you don't need all those steps.
Temporary table to hold backup of information:
Then do the update of the original data from 85 to 0:
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.
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';
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);
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" ...
Active today
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.
Active today
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.
If you describe the business problem you are trying to solve, we can help you with the best way to the task in Oracle.
Well usually you either do not need a temp table orthe cursor. Why is this need of this combined need?
Featured Post
PeopleSoft Adoption Made Smooth & Simple!
On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool. Claim Your Free WalkMe Account Now
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
This article shows the steps required to install WordPress on Azure.
Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan.
WordPress is no exception and requires an App Service Plan and Database to install
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
- Query Syntax
- SQL
- Databases
- MySQL Server
- MongoDB
- NoSQL Databases, Percona
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses
Course of the Month8 days, 2 hours left to enroll
728 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.