Open & Fetch cursor for inserting into temp table in oracle
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 tcomgrpwhere sls_grp_cd ='IS'and sales_vew_cd ='S1709'and dat_cx_tm_prd_nr = '85'minusselect svc_pvr_pyr_nr from tcomgrpwhere 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_0ASSELECT * FROM TCOMGRPWHERE 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 ]
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; /
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');
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Well usually you either do not need a temp table orthe cursor. Why is this need of this combined need?
0
Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.
Thank you , Thanks a lot !! Getting into oracle gradually - Thanks !!
0
chokkaAuthor Commented:
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'
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');
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.
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';
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.
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.
Here is your code with a cursor:
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:Open in new window