Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2003
    Posts
    7

    Unanswered: Dynamically substitute table name in Cursor Defn.

    hi,
    I am looking for a soln,where in I can substiute the name of the table
    dynamically with a paramter passed through the procedure in the Cursor
    Defn
    as shown below.

    PROCEDURE FLAT_TCN
    (p_table_name IN varchar2(50),
    p_error_msg IN OUT varchar2)

    IS

    error_msg VARCHAR2(300) := SQLERRM;

    TCN_record VARCHAR2(1000);

    CURSOR TCN_cur IS

    SELECT
    py.SRV_CAT,
    py.NET_FT,
    py.JUR_CODE,
    py.LATA_CODE,
    py.CALL_TYPE,
    py.CUST_SEG,
    py.HIST_CTR,
    py.EFF_DATE,
    py.EXP_DATE,
    py.TRAN_IND,
    FROM
    p_table_name py;

    here note the p_table_name used for cursor declaration(is passed as a
    parameter to the proc)....
    Any soln or any other way of implemention this is appreciated.

    Rgds
    Vivian

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Dynamically substitute table name in Cursor Defn.

    You need to use dynamic SQL:

    PROCEDURE FLAT_TCN
    (p_table_name IN varchar2(50),
    p_error_msg IN OUT varchar2)

    IS

    error_msg VARCHAR2(300) := SQLERRM;

    TCN_record VARCHAR2(1000);

    TYPE refcur IS REF CURSOR;
    tcn_cur refcur;

    BEGIN

    OPEN tcn_cur FOR
    'SELECT
    py.SRV_CAT,
    py.NET_FT,
    py.JUR_CODE,
    py.LATA_CODE,
    py.CALL_TYPE,
    py.CUST_SEG,
    py.HIST_CTR,
    py.EFF_DATE,
    py.EXP_DATE,
    py.TRAN_IND,
    FROM '||p_table_name||' py';
    ...

    By the way, I'm not sure this does what you intend:

    error_msg VARCHAR2(300) := SQLERRM;

    The value of SQLERRM will be assigned at the time the variable is declared - but there hasn't been any error yet, so the value will always be "ORA-0000: normal, successful completion". Or maybe that's the default value you want if no error occurs?

  3. #3
    Join Date
    Feb 2003
    Posts
    7
    Hi,
    Thks for ur reply.

    I want this cursor fo looping record by record.How do I do that. eg


    CREATE OR REPLACE procedure test (tab_id in integer)

    TYPE refcur IS REF CURSOR;
    c_Data refcur;


    begin

    select table_name into stg_table from table_source where table_type='ST' and table_id=tab_id and src_id=2;

    open c_Data for 'Select * from '||stg_table||'order by record_id;';
    loop
    fetch c__data into rec_id ;
    EXIT WHEN c_gib_data%NOTFOUND;
    -----------
    ----------- /* Some actions
    end Loop;



    When I complie,I get a error at the cursor where the sql statment is created

    BEGIN test_dym(1); END;

    *
    ERROR at line 1:
    ORA-00933: SQL command not properly ended
    ORA-06512: at "USR.TEST", line 46
    ORA-06512: at line 1

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    1) You need a space before 'order'
    2) You should not put a semi-colon in the SQL string

    open c_Data for 'Select * from '||stg_table||' order by record_id';

  5. #5
    Join Date
    Feb 2003
    Posts
    7
    Thnks Andrew.I t worked.

  6. #6
    Join Date
    Feb 2003
    Posts
    7
    Hi,
    Suppose if I have to do the follwoing,how do I implment using REF cursor.Actually I need to pass a IN paramter to the cursor. Can this be implemeted in REF cursor


    CURSOR c_Data( P_Serial_Number IN VARCHAR2 ) IS
    SELECT
    record_id,record_history_status ,updation_ico_count
    FROM
    prod_data
    WHERE
    record_id = ( SELECT max(record_id)
    FROM
    prod_data
    WHERE
    Serial_number LIKE P_Serial_Number);

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You don't pass a parameter to the ref cursor, instead you use a BIND VARIABLE and the USING clause. Here is a simple example:

    DECLARE
    TYPE refcur IS REF CURSOR;
    c refcur;
    r dept%ROWTYPE;
    BEGIN
    OPEN c FOR 'SELECT * FROM dept WHERE deptno > :mindeptno' USING 10;
    LOOP
    FETCH c INTO r;
    EXIT WHEN c%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(r.deptno);
    END LOOP;
    CLOSE c;
    END;
    /

    In this example, the value 10 is used for the bind variable :mindeptno in the query. In your example this would be used like this:

    OPEN c_data FOR 'SELECT ... WHERE Serial_number LIKE :x' USING P_Serial_Number;

  8. #8
    Join Date
    Feb 2003
    Posts
    7
    Hi,
    I have defined the cursor as ref cursor as follows

    TYPE c_Data_refcur IS REF CURSOR; --Cursor with dynamic table defn
    c_Data c_data_refcur;

    TYPE c_View_Data_refcur IS REF CURSOR; --Cursor with Dynamic
    table defn and IN Para
    c_View_Data c_View_Data_refcur;


    Begin

    --The value for stg_table is there with me ----
    open c_Data for'Select * from '||stg_table||' order by record_id';
    loop
    fetch c_Data into rec_id ; (Q1 : rec id should be deifned as what %rowtype as the table name is generated dynamically)

    EXIT WHEN c_data%NOTFOUND;

    IF v_transformation_boolean = FALSE
    THEN
    SELECT Transfer_ID_Seq.NEXTVAL INTO v_TransferSeq FROM dual;
    v_start_record_id := stg_table.record_id; (Q2: How do I specify the fetch cursor value here?)
    v_transformation_boolean := TRUE;
    END IF;

    v_end_record_id := stg_table.record_id; (Q3 How do I specify the fetch cursor value here?)


    Q4 : Here I have to inialize the field values as null for the second cursor which has a In paramter.Here again the table name is dynamically populated for the cursor defn.)
    c_GibView_Data.updation_ico_count := NULL;
    c_GibView_Data.record_history_status := NULL;
    c_GibView_Data.ico_status:= NULL;


    --2 cursor having IN Parameter---
    OPEN c_View_Data for 'SELECT record_id, record_history_status ,updation_count, status FROM'||prd_table||'WHERE record_id = ( SELECT max(record_id) FROM '||prd_table||'WHERE Serial_number = :P_Serial_Number)' using c_Data.serial_number;(This defn is not allowed)
    FETCH c_View_Data INTO {c_GibView_Data_Rec};(Q5: how do i defin this as %row type as the table is dynamically populated)
    CLOSE c_View_Data;

  9. #9
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Q1) You cannot declare a variable dynamically using NDS, you would need to know the structure of the record in advance. You need to use DBMS_SQL to handle this situation.
    Q2) If you had a record declared like v_rec mytable%ROWTYPE into which you had fetched a row, then you would say v_rec.record_id
    Q3) Same as Q2
    Q4) Don't understand the question.

Posting Permissions

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