Results 1 to 2 of 2
  1. #1
    Join Date
    May 2006
    Posts
    1

    Post Unanswered: PL/SQL Variables

    This may be easy but I'm new to PL/SQL and I'm not sure I can do what I'm trying to do. The line I'm unsure of is

    AND UPPER(sb.RECIPIENT_NM) LIKE '%'||v_cand_nm_last||'%';

    Will concatenating the '%' to the variable work just like any other LIKE statement.


    DECLARE
    v_shema VARCHAR2(10) :='DISCLOSURE';
    v_rowid ROWID;
    v_count NUMBER := 0;
    v_found NUMBER := 0;
    v_ErrorCode NUMBER; --Variable to hold the error number
    v_ErrorText VARCHAR2(200); --Variable to hold the error message text

    v_cand_nm_first disclosure.dim_cand_inf.cand_nm_first%TYPE;
    v_cand_nm_last disclosure.dim_cand_inf.cand_nm_last%TYPE;


    CURSOR c_dim_cand IS
    SELECT
    ROWID,
    cand_nm_first,
    cand_nm_last
    FROM disclosure.dim_cand_inf;

    BEGIN
    OPEN c_dim_cand;
    LOOP
    FETCH c_dim_cand INTO
    v_rowid,
    v_cand_nm_first,
    v_cand_nm_last;
    EXIT WHEN c_dim_cand%NOTFOUND;

    BEGIN

    SELECT COUNT(*) INTO v_count
    FROM disclosure.dim_cand_inf cand, disclosure.dim_cmte_ie_inf cmte, disclosure.f_campaign fc, disclosure.nml_form_3 f3, disclosure.nml_sched_b sb
    WHERE UPPER(cand.CAND_NM_FIRST) = UPPER(v_cand_nm_first)
    AND UPPER(cand.CAND_NM_LAST)= UPPER(v_cand_nm_last)
    AND cand.CAND_PK = fc.cand_pk
    AND fc.cmte_pk = cmte.cmte_pk
    AND cmte.cmte_id = f3.cmte_id
    AND f3.SUB_ID = sb.link_id
    AND f3.rpt_yr IN (2005, 2006)
    AND cmte.filed_cmte_tp IN ('H', 'S')
    AND cmte.filed_cmte_dsgn IN ('P', 'A')
    AND sb.DELETE_IND IS NULL
    AND (sb.line_num LIKE '%17%' OR sb.form_tp LIKE 'SB17%')
    AND UPPER(sb.RECIPIENT_NM) LIKE '%'||v_cand_nm_last||'%';

    IF v_count > 0 THEN

    dbms_output.put_line(v_cand_nm_first||' '||v_cand_nm_last||' '||v_count);
    END IF;



    EXCEPTION
    WHEN OTHERS THEN
    v_ErrorCode := SQLCODE;
    v_ErrorText := SUBSTR(SQLERRM, 1, 200);
    dbms_output.put_line(v_ErrorCode||' '||v_ErrorText||' '||v_count);

    END;



    END LOOP;

    CLOSE c_dim_cand;

    EXCEPTION
    WHEN OTHERS THEN
    v_ErrorCode := SQLCODE;
    v_ErrorText := SUBSTR(SQLERRM, 1, 200);
    dbms_output.put_line(v_ErrorCode||' '||v_ErrorText||' '||v_count);
    END;

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713
    YES it will.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

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