Results 1 to 8 of 8
  1. #1
    Join Date
    May 2006
    Posts
    3

    Angry Unanswered: Problems with writing cursors

    Let me prefix this post with the fact that I am very new to this so if my coding does not look correct it is not intentional. Thank you to anyone that can give me tips or hints.

    I have this PL/SQL program that I am writing that is reading from one table and then inserting data into another table. I have attached a word document of what I am trying to accomplish

    This program needs two cursors 1 for the intial inserting of data, the second if for deleting and updating data.

    I have sort of written the first curosor and yes I know it needs some work and yes I know that I am getting compile errors. Still working through those.

    Ok so now I am getting a little further. I was given an 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.

    Code:
    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

    Code:
    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;
    Attached Files Attached Files

  2. #2
    Join Date
    May 2006
    Posts
    3

    Before you tell me to go read a manual

    I have been out on Oracle's website and I have been going through my book. I have gotten this far without any prior PL/SQL knowledge.

  3. #3
    Join Date
    Jan 2004
    Posts
    492
    A couple things off the top of my head. First off you obviously have not tried compiling this because you have a good number of syntax errors.

    1) There is no such thing as set in pl/sql. To assign a value to a variable, it needs to be like so:
    Code:
    variable := 'SOME VALUE';
    2) Its really not a great idea to call your Primary Key ROW_ID. Oracle has a pseudo-column called ROWID, and thats way too confusing. Name it something else.

    3) Ok this is the most important, but often most overlooked aspect. PL/SQL allows you to easily do if/then/else stuff, but that adds a lot of overhead, and should be used sparingly.

    You really should do as much in the sql select as you can. Instead of this:
    Code:
    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
    You really should do it in the select with decode or case:
    Code:
    select decode(wireless_flag, null, 'HOME', 'MOBILE'),
             decode(royal_flag, null, 'RCI OPT OUT', 'SOMETHING ELSE')
    ......
    4) If you are just going to insert into another table, skip the loop and insert directly into the other table like:
    Code:
    insert into table2 (col, col2)
    select a, b
      from table1
    I have a feeling this is for a school class, but if not, heed 3 and 4 especially. Good luck
    Oracle OCPI (Certified Practicing Idiot)

  4. #4
    Join Date
    May 2006
    Posts
    3

    I wish this was for class

    This is actually for work. I have fixed the syntax errors but looking at some of the other stored procs that were created here.

    Thanks for the advice. I will post more later on tonight when I have gotten a little further.

    Thanks again.

    Also you said to use decode instead of If then Else statements like below:

    Code:
    select decode(wireless_flag, null, 'HOME', 'MOBILE'),
             decode(royal_flag, null, 'RCI OPT OUT', 'SOMETHING ELSE')
    ......
    Could I use the decode for each of my If Statments:

    Here are the conditions that I have to meet:
    Each row must have a phone number/chan_addr
    Each row must have a source id.
    These can either be different or the same based on what flags are present.

    You suggested not looping but I don't think that this will work since it is not a straight insert into a table.

    Each record can have 1 to 3 flags
    Either RCI or CCI
    State
    National

    This is why I think I need to loop

    This is what I have so far:

    Code:
    create or replace procedure USP_EIM_CONTACT3_UPD_NEW 
     -- declare variables for data that has to be derived from the base table
     IS
      v_counter           VARCHAR2(20):= 0;
      v_count             VARCHAR2;
      v_insert            VARCHAR2:= 0;
      v_sysdate           DATE:=SYSDATE;
      v_chan_addr         eim_admin.RCCL_OPT_OUT_TMP.chan_addr%TYPE;
      v_type              VARCHAR2;--maps to weather home or mobile number
      v_name              VARCHAR2;-- deterimines wether RCI, CCI , State or Nat DNC
      v_expiredate        DATE;
      v_created           Date:=SYSDATE;
      v_created_by        VARCHAR2(12) :='EIM ADMIN';
      v_org_name          VARCHAR2(20) := 'Default Organization';
    
    BEGIN
    -- declare cursor based on the select criteria
    Declare
    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; 
    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(*)
          into v_count
            from s_contact
           where Home_Phone = dnc_cursor.chan_addr;
        
           If v_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;
     --Begin Second Cursor Loop to Delete and Update Data
     [IF v_count <> 0}
     cursor base_table_cur (Name type, Name type, ...) is 
       select person_uid, home_ph_num from s_contact where home_ph_num=cur.home_ph_num;
      FOR rec IN base_table_cur LOOP
      
      If base_table_cur.person_uid = dnc_cursor.con_per_id
      
      Delete * from siebel.s_per_comm_addr where base_table_cur.person_uid = dnc_cursor.con_per_id
      
      commit
      
      Insert Into
     
        --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_NEW;
    /
    I know for a fact that my IF THEN statements are not correct since I am getting compile errors on them

  5. #5
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    Quote Originally Posted by gs24x7
    I have fixed the syntax errors
    Not quite, I'm afraid.

    Assignments in PL/SQL are like this, as ss659 mentioned:

    v_somevar := 'somevalue';

    IF conditions are like this:

    IF condition THEN do_something;
    ELSIF other_condition THEN do_something_different;
    ELSE do_something_else;
    END IF;

    Note that the semicolons are not optional.

    You suggested not looping but I don't think that this will work since it is not a straight insert into a table.

    Each record can have 1 to 3 flags
    Either RCI or CCI
    State
    National

    This is why I think I need to loop
    I didn't follow that explanation. You insert one row into EIM_CONTACT3 for each row in RCCL_OPT_OUT_TMP. Why can't that be a single INSERT...SELECT?

  6. #6
    Join Date
    Jan 2004
    Posts
    492
    I just looked at his document - what he really need to do is something like this:
    Code:
    ID    FLAG1    FLAG2    FLAG3    FLAG4
    -------------------------------------
    123          'SDNC'    'NDNC'
    456  'Y'     'SDNC'                'Y'
    So each id/flag combination needs to be a row - if there were 4 values, you would have 4 rows. Nulls do not create a row.

    He wants output like so:
    Code:
    ID     TYPE
    --------------------------------
    123   STATE
    123   NATIONAL
    456   RCI OPT OUT
    456   STATE
    456   RCI OPT IN
    I did not follow the naming convention exactly as he wants it, but you get the idea.

    Anyway here is a link that maybe will help the original poster as it looks like what he needs:
    http://forums.oracle.com/forums/thre...737655&#737655
    Last edited by ss659; 05-17-06 at 21:36.
    Oracle OCPI (Certified Practicing Idiot)

  7. #7
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    Well columns into rows is easy

    First, some test data:
    Code:
    CREATE TYPE varchar2_tt AS TABLE OF VARCHAR2(4000)
    /
    
    CREATE TABLE mytable
    ( id INT PRIMARY KEY
    , flag1 VARCHAR2(15)
    , flag2 VARCHAR2(15)
    , flag3 VARCHAR2(15)
    , flag4 VARCHAR2(15) );
    
    INSERT INTO mytable VALUES (123, 'Y', NULL,NULL,'Y');
    INSERT INTO mytable VALUES (456, NULL,'Y','Y',NULL);
    
    
    SELECT * FROM mytable ORDER BY id;
    
    ID   FLAG1 FLAG2 FLAG3 FLAG4
    ---- ----- ----- ----- -----
    123  Y                 Y
    456        Y     Y
    Now we can construct a collection using the type's constructor "VARCHAR2_TT(a,b,c,...)", and put that in a TABLE() expression in the FROM clause to make it queriable:

    Code:
    col flag format a12
    
    SELECT id
         , column_value AS flag
    FROM   mytable
         , TABLE(VARCHAR2_TT(flag1,flag2,flag3,flag4))
    ORDER BY id;
    
    ID   FLAG
    ---- ------------
    123  Y
    123  
    123  
    123  Y
    456  
    456  Y
    456  Y
    456
    Then it's just a case of refining it to get the results we want:

    Code:
    SELECT id
         , column_value AS flag
    FROM   mytable
         , TABLE
           ( VARCHAR2_TT
             ( DECODE(flag1,NULL,'Home','Mobile')
             , DECODE(flag2,NULL,NULL,'RCI Opt Out')
             , DECODE(flag3,NULL,NULL,'CCI Opt Out')
             , DECODE(flag4,NULL,NULL,'State DNC') ))
    WHERE  column_value IS NOT NULL
    ORDER BY id;
    
    ID   FLAG
    ---- -----------
    123  Mobile
    123  State DNC
    456  Home
    456  RCI Opt Out
    456  CCI Opt Out

  8. #8
    Join Date
    Jan 2004
    Posts
    492
    Good show - that is your solution on the link I posted above. Very nice!
    Oracle OCPI (Certified Practicing Idiot)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •