?
Solved

Cursor Question

Posted on 2006-05-08
9
Medium Priority
?
995 Views
Last Modified: 2013-12-11
Let me prefix this post with the fact that I am really new at this and this is my first shot at creating a Stored Proc.  I have the shell and I have tried to code this but I am having some issues with the cursor.  Any tips or tricks that you can give me would be greatly appreciated.

Below is what I am trying to accomplish

Looking for the best approach to work with a complex cursor.

I have 4 files that are going to dump into a temp table. This is a sample of the Temp Table  

CHAN_ADDR,BRA,SRC_ID,R_Flag,C_Flag,S_Flag,N_Flag,Expire_Date,Wireless_Flag
1111111111,R,1-a,,,,NDNC,7/7/2006,
2222222222,R,2-b,,,SDNC,NDNC,7/7/2006,WIR
3333333333,R,3-c,,,SDNC,NDNC,7/8/2006,
4444444444,R,4-d,y,,SDNC,NDNC,7/9/2006,WIR
5555555555,R,5-e,y,,SDNC,,7/10/2006,
6666666666,R,6-f,y,,,,,WIR
7777777777,R,7-g,,,,,,
8888888888,R,8-h,y,,,NDNC,7/7/2006,WIR

I need to take this data and dump it into another table that looks like the following:

ADDR      Per_ID      Method      Name      Expire Date      Flag
1111111111      1-a      Phone      Nat      7/7/2006      Y
2222222222      2-b      Mobile      State      7/7/2006      Y
2222222222      2-b      Mobile      Nat      7/7/2006      Y
4444444444      3-c      Mobile      R      7/9/2006      y
4444444444      3-c      Mobile      State      7/9/2006      y
4444444444      3-c      Mobile      Nat      7/9/2006      y

 know that I need to use a cursor using loop and fetch but I am kind of confused on how to make this work.  I am fairly new to writing PL/SQL so any tips and tricks would be greatly appreciated.

For each phone number there can be 1 to 3 records written based on the flags.  For each of those records I must store the phone number, the id, flag data, expire date (for only State or National) and Flag must always be checked.

I have put together a small shell of the program but what goes in the middle is where I am having some problems.
CREATE OR REPLACE PROCEDURE USP_EIM_CONTACT3_UPD
IS

CURSOR dnc_cursor IS
SELECT CHAN_ADDR,
         BRA,
         SRC_ID,
         R_FLAG,
         C_FLAG,
         S_FLAG,
         N_FLAG,
         EBR_EXPIRE_DATE,
         WIRELESS_FLAG
FROM eim_admin.RCCL_OPT_OUT_TMP;

           v_counter                         NUMBER := 0;
           v_insert                          NUMBER := 0;
         v_sysdate                         DATE:=SYSDATE;
         v_chan_addr                        eim_admin.RCCL_OPT_OUT_TMP.chan_addr%TYPE;

BEGIN
        DBMS_OUTPUT.PUT_LINE ('***Begining USP_EIM_CONTACT3_UPD, time is ' ||
                          TO_CHAR (v_sysdate, 'MON-DD-YYYY HH24:MI.SS'));
        DBMS_OUTPUT.NEW_LINE;
FOR rec IN dnc_cursor LOOP
      
      v_counter:=v_counter+1;

      BEGIN
      SELECT CHAN_ADDR
        INTO v_chan_addr
        FROM eim_admin.RCCL_OPT_OUT_TMP;
      EXCEPTION
    WHEN OTHERS THEN
             DBMS_OUTPUT.PUT_LINE('*** Could not lookup RCCL_OPT_OUT_TMP. ***');
             DBMS_OUTPUT.PUT_LINE('ORA-'||SQLCODE||' '||SQLERRM);
      END;
BEGIN
             INSERT INTO siebel.S_PER_COMM_ADDR
              (ADDR)
          VALUES
            (rec.CHAN_ADDR);            
              v_insert := v_insert + SQL%ROWCOUNT;
          EXCEPTION WHEN OTHERS THEN
             DBMS_OUTPUT.PUT_LINE('*** Failed to insert into siebel.S_PER_COMM_AADDR DDR ***');
             DBMS_OUTPUT.PUT_LINE('ORA-'||SQLCODE||' '||SQLERRM);
      END;
END LOOP;

--Output STATISTICS.
  DBMS_OUTPUT.PUT_LINE('**Number records read :'||v_counter);
  DBMS_OUTPUT.NEW_LINE;
  DBMS_OUTPUT.PUT_LINE ('***Completing USP_EIM_CONTACT3_UPD, time is ' ||
                          TO_CHAR (SYSDATE, 'MON-DD-YYYY HH24:MI.SS'));

EXCEPTION
      WHEN OTHERS THEN
           DBMS_OUTPUT.PUT_LINE(' ****Error in USP_EIM_CONTACT3_UPD ****'||SQLCODE||SQLERRM);                                    
END USP_EIM_CONTACT3_UPD;

I have already created the table.  What we are going to do is using sqlloader to load our text files into our temp table.  From there we are going to read the data from the temp table into a table that is already created.

Basically what I am trying to do is the following:

Cursor 1
select * from Temp where, State_Flag and National_Flag is not null
Insert into Siebel.S_PER_COMM_ADDR
set id = source var
set phone = chann_add var

If
wireless flag is not null
set method = 'Mobile"
else
set method = 'Home'
end if

If
Brand = 'r' and R_Flag is not null
set name flg1
set flag as 'T'
 
and so on.

I am getting confused as to where to call the variables:
set id = source var
set phone = chann_add var

I am also confused on how to do the sets and how to set the date for S_Flag and N_Flag.

One of the other issues is that I need to go and look at the base table to pick up anyone else that has the phone number and update or delete based on the data in the table.
0
Comment
Question by:gschoser
  • 4
  • 3
  • 2
9 Comments
 
LVL 48

Assisted Solution

by:schwertner
schwertner earned 1000 total points
ID: 16639650
If you are using Oracle 9i and higher you can use External tables to read Excel Sheets:

create directory med as 'c:\med' ; -- on main
GRANT READ  ON DIRECTORY med TO lsmedical;
GRANT WRITE ON DIRECTORY med TO lsmedical;



drop table lsmedical.USERMEDBASIC_EXT;
create table lsmedical.USERMEDBASIC_EXT (
                   PZN  VARCHAR2(33),
                   NAME VARCHAR2(111)   )
    organization external  
    (type oracle_loader  
     default directory med
     access parameters (records delimited by newline
     fields terminated by '|')
     location ('med-de-formatted.txt','med-de-insulin-formatted.txt',
               'med-us-formatted.txt','med-us-insulin-formatted.txt'));


After that you can read this table using SELECT statement as regular Oracle table.

The next question you have to answer is how you will load this values in the Siebel table.
You have to publish the structure of the Siebel table and to explain how to get the values of the
columns.
0
 
LVL 48

Expert Comment

by:schwertner
ID: 16639719
one idea to do this is to put into the procedure:

FOR i in (select CHAN_ADDR,BRA,SRC_ID,R_Flag,C_Flag,S_Flag,N_Flag,Expire_Date,Wireless_Flag from Name_Of_Ext_table) LOOP

INSERT INTO another_table(column1, column2,...) VALUES (i.CHAN_ADDR, i.BRA, ..... );


END LOOP:


You have to define how to get the values of the columns that will not be directly transfered from the input external table.
0
 

Author Comment

by:gschoser
ID: 16675855
Sorry I did not answer sooner I was in the hospital.  I just got out yesterday.

Actually we are using a control file to load the files into a table.  From the table we need to populate another table that will eventually load the data into Siebel.

I am confused as to how cursors work especially in this case.  I am very new to this and I need to get this figured out.

I need for the date to get loaded exactly how Ihave in my example above.

Thanks

0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
LVL 48

Expert Comment

by:schwertner
ID: 16680186
Hello,

Hope you are well! Wish you all the best!
I worked with Siebel and know for sure that it has special table structure and
a tool to insert data. Do not try to insert directly in Siebel. I am sure you are informed .....

Your cursor looks well.

But this

     SELECT CHAN_ADDR
       INTO v_chan_addr
       FROM eim_admin.RCCL_OPT_OUT_TMP;

runs by every iteration of the loop and I will suggest to put it before the loop.

Please share with us the structure table you have to insert records.
the easiest way to do this is:

SQL>descr my_table

After that define a variable for every column value.

Put values into these variables using the logic yo described.

After tha issue an insert statement like

INSERT INTO another_table(column1, column2,...) VALUES (i.CHAN_ADDR, i.BRA, ..... );


0
 

Author Comment

by:gschoser
ID: 16683181
The table that we are instering into is eim_contact3  From eim_contact3 we are going to use an .ifb file to update the other table.

Here is the structure of the table and the columns that we need to fill in:
ROW_ID      VARCHAR2(15 CHAR)      NOT NULL,
CREATED      DATE      DEFAULT sysdate,
CREATED_BY      VARCHAR2(15 CHAR),      
LAST_UPD      DATE      DEFAULT sysdate,
LAST_UPD_BY      VARCHAR2(15 CHAR),      
MODIFICATION_NUM      NUMBER(10)      DEFAULT 0,
CONFLICT_ID      VARCHAR2(15 CHAR)      DEFAULT '0',
IF_ROW_BATCH_NUM      NUMBER(15)      DEFAULT 0 NOT NULL,
IF_ROW_STAT      VARCHAR2(30 CHAR)      NOT NULL,
PARTY_TYPE_CD      VARCHAR2(30 CHAR)      NOT NULL,
PARTY_UID      VARCHAR2(100 CHAR)      NOT NULL,
IF_ROW_STAT_NUM      NUMBER(10)      DEFAULT 0,
ADDR_PR_ALT_PH_NUM      CHAR(1 CHAR),      
ADDR_ADDR      VARCHAR2(100 CHAR),      
ADDR_COMMMEDIUM_CD      VARCHAR2(30 CHAR),      
ADDR_NAME      VARCHAR2(50 CHAR),      
CON_PERSON_UID      VARCHAR2(100 CHAR),      
PERSON_UID      VARCHAR2(100 CHAR),      
YEARLY_END_DATE      DATE      
OPT_OUT_FLG      VARCHAR2(1 CHAR)      

I know that Chan_Addr and Src Id need to get inserted into every record.

I am still working on this and I hope to have some more to show later on today.

Thanks
0
 
LVL 27

Accepted Solution

by:
sujith80 earned 1000 total points
ID: 16697494
Hi,
Your procedure should have a structure similar to the following pseudo code.

CREATE OR REPLACE PROCEDURE USP_EIM_CONTACT3_UPD
IS
 -- declare cursor based on the select criteria
 
 -- declare variables for data that has to be derived from the base table
 -- eg: l_name_flag varchar2(1);

BEGIN

 FOR rec in cursor LOOP
   -- rec has details of the current record being processed.
   -- i.e. rec.CHAN_ADDR has the CHAN_ADDR of the current record

   -- Assign values to variables to be derived.
   -- I.e. use if conditions like what you have mentioned.
   -- for eg: If (rec.Bra = 'r' and rec.R_Flag is not null ) then
                l_Name_Flag := 'T';
              end if ;

   -- Do insert
   -- insert into target table values(record variables, declared variables);
   -- something like insert into table(col1, col2) values(rec.CHAN_ADDR, l_Name_Flag);

 END LOOP;

END USP_EIM_CONTACT3_UPD;
/

Put more details on the issues that you are having with the cursor. What is the actual problem it is giving?
What do you mean by calling and setting variables.

rgds,
Sujith
0
 

Author Comment

by:gschoser
ID: 16699665
Ok so now I am getting a little further.  Thank you Sujith...for the outline of how the program should look.  I used it and incorportated my data:  See below.  I still have some things that I need to fix, like now I need to add another cursor

If Count  <> 0
Create Another Cursor
Select per id, phone from s_contact where home phone = cur.home phone#
Begin Loop

If the first record of the second cursor is = to the first record I need to delete the data from the table.
I then need to insert the data from cursor one into the the eim_contact3 table.

Please be patient with me as I am almost done with this and I have been reading books on how to program in pl/sql

CREATE OR REPLACE PROCEDURE USP_EIM_CONTACT3_UPD
IS
 -- declare cursor based on the select criteria
CURSOR dnc_cursor IS
    SELECT CHAN_ADDR,
           BRA,
           SRC_ID,
           ROYAL_FLAG,
           CELEBRITY_FLAG,
           STATE_FLAG,
           NATIONAL_FLAG,
           EBR_EXPIRE_DATE,
           WIRELESS_FLAG
      FROM eim_admin.RCCL_OPT_OUT_TMP;
 
 -- declare variables for data that has to be derived from the base table
  v_counter           VARCHAR2(20):= 0;
  v_insert            VARCHAR2:= 0;
  v_sysdate           DATE:=SYSDATE;
  v_chan_addr         eim_admin.RCCL_OPT_OUT_TMP.chan_addr%TYPE;
  v_comm_medium_cd    = '';
  v_type              --maps to weather home or mobile number
  v_name              -- deterimines wether RCI, CCI , State or Nat DNC
  v_expiredate        DATE;

BEGIN
 DBMS_OUTPUT.PUT_LINE('***Begining USP_EIM_CONTACT3_UPD, time is ' ||TO_CHAR(v_sysdate, 'MON-DD-YYYY HH24:MI.SS'));
  DBMS_OUTPUT.NEW_LINE;

 FOR rec IN dnc_cursor LOOP
 v_counter := v_counter + 1;
   -- rec has details of the current record being processed.
   -- i.e. rec.CHAN_ADDR has the CHAN_ADDR of the current record
   

   -- Assign values to variables to be derived.
   -- I.e. use if conditions like what you have mentioned.
   -- for eg: If (rec.Bra = 'r' and rec.R_Flag is not null ) then l_Name_Flag := 'T'; end if ;
BEGIN
      select count(*)
        from s_contact
       where Home_Phone = dnc_cursor.chan_addr;
   
       If count = 0
     
       If chan_addr is not null THEN set v_addr
     
       If wireless_flag is not null THEN set v_type = 'Mobile' Else set = v_type = 'Home' End If
     
       If Royal_Flag is not null THEN set = v_name = 'RCI Opt Out' End If
     
       If Celebrity_Flag is not null THEN set = v_name = 'CCI Opt Out' End If
     
       If State_Flag is not null THEN set = v_name = 'State DNC' End If
     
       If National_Flag is not null THEN set = v_name = 'National DNC' End if
       
       If Ebr_Expire_date is not null THEN set =v_expiredate End If

   -- Do insert
   -- insert into target table values(record variables, declared variables);
   -- something like insert into table(col1, col2) values(rec.CHAN_ADDR, l_Name_Flag);
   
       INSERT INTO Siebel.Eim_Contact3(ROW_ID,
                             CREATED,
                             CREATED_BY,
                             LAST_UPD,
                             LAST_UPD_BY,
                             MODIFICATION_NUM,
                             CONFLICT_ID,
                             IF_ROW_BATCH_NUM,
                             IF_ROW_STAT,
                             CON_PRIV_FLG,
                             REL_END_DT,
                             ADDR_ADDR,
                             ADDR_COMMEDIUM_CD,
                             ADDR_NAME,
                             CON_BU,
                             CON_PERSON_UID)
                             VALUES
                             (
                             row_id_seq.NEXTVAL,
                             v_created,
                             v_created_by,
                             v_created,
                             v_created_by,
                             0,
                             '0',
                             in_batch_id,
                             'FOR_IMPORT',
                             'N',
                             v_expiredate,
                             chan_addr,
                             v_type,
                             v_name,
                             v_org_name,
                             src_id
                             )

 END LOOP;
 
    --Output STATISTICS.
    DBMS_OUTPUT.PUT_LINE('**Number records read :' || v_counter);
    DBMS_OUTPUT.NEW_LINE;
    DBMS_OUTPUT.PUT_LINE('***Completing USP_EIM_CONTACT3_UPD, time is ' ||
                         TO_CHAR(SYSDATE, 'MON-DD-YYYY HH24:MI.SS'));
 
  EXCEPTION
WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(' ****Error in USP_EIM_CONTACT3_UPD ****' || SQLCODE || SQLERRM);
END USP_EIM_CONTACT3_UPD;

0
 

Author Comment

by:gschoser
ID: 16700142
Even thought I have awarded points on this I still need some help.

Thanks
0
 
LVL 27

Expert Comment

by:sujith80
ID: 16706721
Hi,
From your description I understand that
you need to check the existance of a record in s_contact for Home_Phone = rec.chan_addr while looping through the cursor dnc_cursor. And if you find an entry in s_contact, you need to delete all the records from <i dont know which table. its not clear ,  I assume that it is eim_contact3 >

Here you dont have to open another cursor to check for the existance of a record in s_contact.

You can do something like

 select count(*) into l_count
 from s_contact
 where home_phone = dnc_cursor.chan_addr;

 if l_count > 0 then
   -- Do your delete.
   -- Assign values to variables.
   -- Do your insert.
 end if ;

Rgds.
Sujith.
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This article will show a step by step guide on how to mask column values in Oracle 12c using DBMS_REDACT full redaction option. This option is available on licensed Oracle Enterprise edition as part of Oracle's Advanced Security.
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.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

589 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