INSERT From A CURSOr

All posts relating to Oracle database administration.

Moderator: Tim...

INSERT From A CURSOr

Postby kaushik.nandy » Tue Jun 25, 2013 11:50 am

I have an anonymous block to insert data into a table from a cursor query ---
Here is the block---
-----------------------------------------------------
Code: Select all
   BEGIN   
           FOR CUR_GENLMATTER IN( SELECT DISTINCT
          cases.casekey as casekey,
          NROrigIPSite.NameRoleKey AS OriginalIpSiteKey,
          NRAssistant.NameRoleKey AS AssistantKey,
          NRAttorney.NameRoleKey AS AttorneyKey,
          NRBusinessLine.NameRoleKey AS BusinessLineKey,
          NRBusinessUnit.NameRoleKey AS BusinessUnitKey,
          FIRST_VALUE(TABLEPROGRAMPROJECT.PROGRAMPROJECTKEY)  OVER  ( PARTITION BY CASES.CASEKEY ORDER BY TABLEPROGRAMPROJECT.PROGRAMPROJECTKEY) AS PROGRAMPROJECTKEY,
          FIRST_VALUE(TableRank.RankKey)  OVER  ( PARTITION BY Cases.CaseKey ORDER BY TableRank.RankKey) AS RankKey,
          FIRST_VALUE(TableTechnicalField.TechnicalFieldKey)  OVER  ( PARTITION BY Cases.CaseKey ORDER BY TableTechnicalField.TechnicalFieldKey) AS TechnicalFieldKey,
          NRIpSite.NameRoleKey AS IpSiteKey,
          GAOriginalReference.ValueString AS OriginalReference,
          GMComments.ShortValue AS Comments,
          NROutsideAttorney.NameRoleKey AS OutsideAttorneyKey
          From Cases
         LEFT JOIN CaseName CNOrigIPSite ON CNOrigIPSite.CaseKey=Cases.CaseKey AND CNOrigIPSite.NameTypeKey=14
         LEFT JOIN NameRole NROrigIPSite ON NROrigIPSite.NameKey=CNOrigIPSite.NameKey AND NROrigIPSite.NameTypeKey=13
         LEFT JOIN CaseName CNAssistant ON CNAssistant.CaseKey=Cases.CaseKey AND CNAssistant.NameTypeKey=105
         LEFT JOIN Namerole Nrassistant ON Nrassistant.Namekey=Cnassistant.Namekey AND Nrassistant.Nametypekey=1
         LEFT JOIN CaseName CNAttorney ON CNAttorney.CaseKey=Cases.CaseKey AND CNAttorney.NameTypeKey=106
         LEFT JOIN NameRole NRAttorney ON NRAttorney.NameKey=CNAttorney.NameKey AND NRAttorney.NameTypeKey=1
         LEFT JOIN CaseName CNBusinessLine ON CNBusinessLine.CaseKey=Cases.Casekey AND CNBusinessLine.NameTypeKey=504
         LEFT JOIN NameRole NRBusinessLine ON NRBusinessLine.NameKey=CNBusinessLine.NameKey AND NRBusinessLine.NameTypeKey=504
         LEFT JOIN CaseName CNBusinessUnit ON CNBusinessUnit.CaseKey=Cases.Casekey AND CNBusinessUnit.NameTypeKey=503
         LEFT JOIN NameRole NRBusinessUnit ON CNBusinessUnit.NameKey=NRBusinessUnit.NameKey AND NRBusinessUnit.NameTypeKey=503
         LEFT JOIN CaseDescriptor CDProgramProject ON CDProgramProject.CaseKey=Cases.CaseKey                                               
         LEFT JOIN TableDescriptorValue TDVProgramProject ON TDVProgramProject.DescriptorValueKey=CDProgramProject.DescriptorValueKey
                                                AND TDVProgramProject.DescriptorKey=4
         LEFT JOIN TableProgramProject ON TableProgramProject.ProgramProjectCode=TDVProgramProject.DescriptorValueCode 
         LEFT JOIN CaseDescriptor CDRank ON CDRank.CaseKey=Cases.CaseKey
         LEFT JOIN TableDescriptorValue TDVRank ON TDVRank.DescriptorValueKey=CDRank.DescriptorValueKey AND TDVRank.DescriptorKey=32
         LEFT JOIN TableRank ON TableRank.RankCode=TDVRank.DescriptorValueCode
         LEFT JOIN CaseDescriptor CDTechnicalField ON CDTechnicalField.CaseKey=Cases.CaseKey
         LEFT JOIN TableDescriptorValue TDVTechnicalField ON TDVTechnicalField.DescriptorValueKey=CDTechnicalField.DescriptorValueKey AND TDVTechnicalField.DescriptorKey=3
         LEFT JOIN TableTechnicalField ON TableTechnicalField.TechnicalFieldCode=TDVTechnicalField.DescriptorValueCode
         LEFT JOIN CaseName CNIpSite ON CNIpSite.CaseKey=Cases.CaseKey AND CNIpSite.NameTypeKey=13
         LEFT JOIN NameRole NRIpSite ON NRIpSite.NameKey=CNIpSite.NameKey AND NRIpSite.NameTypeKey=13
         LEFT JOIN CaseName CNOutsideAttorney ON CNOutsideAttorney.CaseKey=Cases.CaseKey AND CNOutsideAttorney.NameTypeKey=111
         LEFT JOIN NameRole NROutsideAttorney ON NROutsideAttorney.NameKey=CNOutsideAttorney.NameKey AND NROutsideAttorney.NameTypeKey=200
         LEFT JOIN GlobalAttribute GAOriginalReference ON GAOriginalReference.ParentKey=Cases.CaseKey AND GAOriginalReference.AttributeKey=113  AND
         gaoriginalreference.parenttablename='CASES'
         LEFT JOIN Globalmedia Gmcomments ON Gmcomments.Parentkey=Cases.Casekey AND Gmcomments.Mediatypekey=200  AND Gmcomments.Parenttablename='CASES'
         WHERE Cases.CaseTypeKey =9 and cases.casekey = v_icasekey )
       LOOP
         
          Insert Into Casegeneralmatter (Casekey,Originalipsiterolekey,Attorneyrolekey, Assistantrolekey, Businessunitrolekey,Businesslinerolekey,Technicalfieldkey,
          Rankkey,Programprojectkey,Ipsiterolekey,Outsideattorneyrolekey,Comments,Originalreference)
          Values (Cur_Genlmatter.Casekey, Cur_Genlmatter.Originalipsitekey, Cur_Genlmatter.Attorneykey, Cur_Genlmatter.Assistantkey, Cur_Genlmatter.Businessunitkey,
           cur_GenlMatter.Businesslinekey,cur_genlmatter.technicalfieldkey,cur_GenlMatter.Rankkey,cur_GenlMatter.Programprojectkey,cur_GenlMatter.Ipsitekey,cur_GenlMatter.Outsideattorneykey,
          cur_GenlMatter.Comments,cur_GenlMatter.Originalreference);
         END LOOP;
       EXCEPTION
        WHEN OTHERS THEN
        INSERT INTO DEBUG_K VALUES ('Error',null);
        end;
-----------------------------------------------------------------------
Now the Cursor query gives the result I want , (LIKE I get VALUES FOR rankkey, assistantkey )
but apart from the Casegeneralmatter.casekey all the fields INSERTED are NULL,
I cannot figure out why the data inserted into the table null. The cursor query is generating only one row .
No error message is generated in this block.

any suggestions will be great help.
Thanks
kaushik.nandy
Member
 
Posts: 43
Joined: Sun Oct 31, 2010 8:12 am

Re: INSERT From A CURSOr

Postby Tim... » Tue Jun 25, 2013 12:56 pm

Hi.

I would suggest you use DBMS_OUTPUT to print every variable value before running the cursor for loop, and every value coming out of the for loop before performing the insert to check what is being returned by the query. People have this habit of running queries in SQL*Plus with literal values, then running them in PL/SQL using variables and *assuming* all the settings were the same. :)

You are using outer joins, which will replace values with NULL to prevent the row from being returned. It sounds like these join tables don't have values in for the rows you are querying.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17936
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle Database Administration

Who is online

Users browsing this forum: No registered users and 5 guests

cron