Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2005
    Posts
    41

    Unhappy Unanswered: Passing table name into a Cursor - PL/SQL

    I am trying to pass a table name from one cursor to another but the code doesn't compile. I cannot hardcode this as I'll have several tables in cursor A1 (this is just an example). Cursor B1 gets distinct dates for each table.

    SET SERVEROUT ON SIZE 200000 TRIMS ON
    DECLARE

    DT1 NUMBER := 0;

    CURSOR A1 IS SELECT DISTINCT TABLE_NAME FROM ALL_TAB_COLS
    WHERE OWNER = 'EDW_STAR_SIT'
    AND TABLE_NAME = 'CU_WS_DOCSTATUS_FACT_SNAP'
    AND COLUMN_NAME = 'DT_SK';

    CURSOR B1 (A VARCHAR2) IS SELECT DISTINCT DT_SK FROM A ORDER BY DT_SK;

    BEGIN
    FOR G1 IN A1
    LOOP
    DBMS_OUTPUT.PUT_LINE('TABLE_NAME..........'||G1.TA BLE_NAME);

    FOR H1 IN B1(G1.TABLE_NAME)
    LOOP
    DBMS_OUTPUT.PUT_LINE('DATE..........'||H1.DT_SK);

    END LOOP;
    END LOOP;
    END;

  2. #2
    Join Date
    Feb 2005
    Posts
    57
    Have a look at Cursor Variables and Dynamic SQL

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    and execute immediate.
    you cannot pass variables for table-names or column names into a cursor
    (like you would with standard variable values) without doing an execute
    immediate or ref cursor or the like which will dynamically build your cursor/query.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    Apr 2010
    Posts
    4

    Example of passing table name to a ref cursor

    I would oh so appreciate an example of passing a table name to a ref cursor!

    I am trying to build what is effectively a parameterized view, where the parameterized part is in forming the table names from which to query. I am implementing this as a pipelined table function in which I intend to dynamically build a Select statement that feeds a cursor. I would then fetch records from this cursor one at a time and pipe them out (after some minor manipulation).

    Any help you can offer would be wonderful. My forehead is pretty swolen from banging it against the Oracle wall.

    Thanks,
    Neil

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I am trying to build what is effectively a parameterized view
    If it is your intention to issue CREATE VIEW, please realize that views do not take/accept parameter(s).
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Aug 2009
    Posts
    262
    perhaps some thing like this ?


    Code:
    create or replace
        function foo
        ( p_tname in varchar2,
          p_nrows in number,
          p_cols  in sys.odcivarchar2list default null ) return sys_refcursor
        as
            l_sql    long := 'select ';
            l_cursor sys_refcursor;
        begin
           if ( p_cols is not null )
           then
               for i in 1 .. p_cols.count
               loop
                   l_sql := l_sql || dbms_assert.simple_sql_name( p_cols(i) ) || ', ';
               end loop;
           else
               l_sql := l_sql || 't.*, ';
           end if;
     
           l_sql := l_sql || 'rownum r from ' || dbms_assert.sql_object_name( p_tname ) || ' t where 
    rownum <= :x';
     
           open l_cursor for l_sql using p_nrows;
           return l_cursor;
       end;
       /
    Function created.
    
    ORAEDT>
    ORAEDT> variable x refcursor
    ORAEDT> exec :x := foo( 'scott.emp', 4 )
    
    PL/SQL procedure successfully completed.
    
    ORAEDT> print x
    
         EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
    ---------- ---------- --------- ---------- --------- ---------- ----------
        DEPTNO          R
    ---------- ----------
          7369 SMITH      CLERK           7902 17-DEC-80        800
            20          1
    
          7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300
            30          2
    
          7521 WARD       SALESMAN        7698 22-FEB-81       1250        500
            30          3
    
          7566 JONES      MANAGER         7839 02-APR-81       2975
            20          4

  7. #7
    Join Date
    Apr 2010
    Posts
    4
    Thanks for the information. I wasn't aware of the dbms_assert fucntions that you mention above. I may end up going that route if I can't get mine to work. I finally got mine to compile, but now I cannot get it to run. Here's the code:

    From my Reports package spec:
    -- Type used in MFG data collection tables (e.g. PLATE_EDM, PLATE_HEO)
    type MFG_DATA_REC is record(
    job_no number,
    build_no number,
    device_id varchar2(20),
    mode_id varchar2(20),
    operation_no number,
    seq_no number,
    last_seq number,
    test_type varchar2(20),
    station_id varchar2(20),
    location_id varchar2(20),
    result_code number,
    test_date date,
    cycle_time number,
    worked_on number,
    accepted number
    );

    -- Table of MFG_DATA_REC records
    Type MFG_DATA_TBL is table of MFG_DATA_REC;

    -- Records returned from fRpt_MFG_TEST_FL_Results()
    Type MFG_TEST_FL_REC is record(
    bom_id varchar2(20),
    handler_id varchar2(20),
    job_no number,
    build_no number,
    test_date date,
    device_id varchar2(20),
    mode_id varchar2(20),
    seq_no number,
    result_code number,
    cycle_time number,
    f01_flow_rate_mass number,
    f05_flow_rate_ratio number,
    prior_device varchar2(20),
    prior_op_date date,
    is_return varchar2(3),
    fail_code number,
    fail_parameters varchar2(400),
    result_code_fields varchar2(960));

    -- Table of MFG_TEST_FL_REC records as returned by fRpt_MFG_TEST_FL_Results()
    Type MFG_TEST_FL_TBL is table of MFG_TEST_FL_REC;


    FUNCTION fRpt_MFG_TEST_FL_Results (
    strProdClass in varchar2,
    strAssemType in varchar2)
    return MFG_TEST_FL_TBL pipelined;

    And the Body:
    FUNCTION fRpt_MFG_TEST_FL_Results (
    strProdClass in varchar2,
    strAssemType in varchar2)
    return MFG_TEST_FL_TBL pipelined
    IS

    -- Local variables...
    strSchema varchar2(32);
    strBuildTable varchar2(64);
    strTestTable varchar2(64);
    strEDMTable varchar2(64);
    strHEOTable varchar2(64);
    strGBBTable varchar2(64);
    strReworkTable varchar2(64);
    strCustomTable varchar2(64);
    strSQL varchar2(2000);
    curTestFLResults sys_refcursor;
    recTestFLResults MFG_TEST_FL_REC;

    BEGIN

    strSchema:=strProdClass||'_MAIN';
    strBuildTable:=strSchema||'.'||strAssemType||'_BUI LD';
    strTestTable:=strSchema||'.'||strAssemType||'_TEST _FL';
    strEDMTable:=strSchema||'.'||strAssemType||'_EDM';
    strHEOTable:=strSchema||'.'||strAssemType||'_HEO';
    strGBBTable:=strSchema||'.'||strAssemType||'_GBB';
    strReworkTable:=strSchema||'.'||strAssemType||'_RE WORK';
    strCustomTable:=strSchema||'.'||strAssemType||'_CU STOM';

    open curTestFLResults for
    'SELECT a.bom_id, a.handler_id, a.job_no, a.build_no, a.test_date, a.device_id,
    a.mode_id, a.seq_no, a.result_code, a.cycle_time, a.f01_flow_rate_mass, a.f05_flow_rate_ratio,
    a.prior_device, a.prior_op_date,
    (case
    when (a.seq_no<=a.last_valid_seq_no) then ''''
    else ''RET''
    end) as "Is_Return",
    a.fail_code, a.fail_parameters,
    decode_result_code('||strProdClass||','||strAssemT ype||',''TEST_FL'',a.result_code) as "FAIL_FIELDS"
    from
    --All flow test records, with bom_id, etc added in for reporting
    (
    select jb.handler_id, jb.job_no, jb.build_no, aa.test_date, aa.station_id, aa.device_id, jb.bom_id,
    aa.mode_id, aa.seq_no, aa.result_code, aa.cycle_time, aa.f01_flow_rate_mass, aa.f05_flow_rate_ratio,
    NVL(tc.value,0) as "LAST_VALID_SEQ_NO", aa.prior_station, aa.prior_device, aa.prior_op_date,
    jb.fail_code, jb.fail_parameters
    from
    (
    Select j.job_no, b.build_no, product_id, assem_type, handler_id, pd.bom_id, rw.fail_code, rw.fail_parameters
    from arm_main.job j
    inner join '||strBuildTable||' b on b.job_no=j.job_no
    inner join arm_main.product_detail pd on pd.product_id = j.product_id and pd.assem_type = j.assem_type
    left outer join '||strReworkTable||' rw on rw.job_no=b.job_no and rw.build_no=b.build_no
    where j.line_id=''MFG'' and j.module_id=''MFG''
    ) jb
    inner join
    (
    select tfl.job_no, tfl.build_no, tfl.test_date, tfl.station_id, tfl.device_id, tfl.mode_id, tfl.seq_no,
    tfl.result_code, tfl.cycle_time, tfl.f01_flow_rate_mass, tfl.f05_flow_rate_ratio,
    ps.station_id as prior_station, ps.device_id as prior_device, ps.test_date as prior_op_date
    from '||strTestTable||' tfl
    left join
    ( --All possible prior station records
    select job_no, build_no, test_date, station_id, device_id, mode_id from '||strEDMTable||'
    where (job_no, build_no, mode_id, test_date) in
    (select job_no, build_no, mode_id, max(test_date) from '||strEDMTable||'
    group by job_no, build_no, mode_id)
    union
    select job_no, build_no, test_date, station_id, device_id, mode_id from '||strHEOTable||'
    where (job_no, build_no, mode_id, test_date) in
    (select job_no, build_no, mode_id, max(test_date) from '||strHEOTable||'
    group by job_no, build_no, mode_id)
    --union
    --select job_no, build_no, test_date, station_id, device_id, mode_id from '||strGBBTable||'
    -- where (job_no, build_no, mode_id, test_date) in
    -- (select job_no, build_no, mode_id, max(test_date) from '||strGBBTable||'
    -- group by job_no, build_no, mode_id)
    ) ps
    on ps.job_no=tfl.job_no and ps.build_no=tfl.build_no and ps.mode_id=tfl.mode_id
    ) aa
    on aa.job_no = jb.job_no and aa.build_no = jb.build_no
    left join '||strCustomTable||' tc
    on tc.job_no = jb.job_no and tc.build_no = jb.build_no and tc.name = ''LAST_SEQNO_''||aa.mode_id
    ) a
    order by job_no desc, build_no desc, test_date desc'
    ;

    loop
    fetch curTestFLResults into recTestFLResults;
    exit when curTestFLResults&#37;NOTFOUND;

    pipe row(recTestFLResults);

    END LOOP;
    close curTestFLResults;
    RETURN;

    END fRpt_MFG_TEST_FL_Results;

    This all compiles now, but I cannot figure out the syntax to make it run. The idea was to use a pipelined table function so that it could be run by the user in a simple Select statement. Unfortunately, none of my attempts have worked. My attempts at execution:
    declare
    strProdClass varchar2(20):='CR600';
    strAssemType varchar2(20):='PLATE';

    begin
    --select * from table(cast(reports.fRpt_MFG_TEST_FL_Results(strPro dClass, strAssemType) as reports.MFG_TEST_FL_TBL));
    --select * from table(cast(reports.fRpt_MFG_TEST_FL_Results(strPro dClass, strAssemType) as MFG_TEST_FL_TBL));
    --select * from table(cast(reports.fRpt_MFG_TEST_FL_Results(strPro dClass, strAssemType) as reports.MFG_TEST_FL_REC));
    --select * from table(cast(reports.fRpt_MFG_TEST_FL_Results(strPro dClass, strAssemType) as MFG_TEST_FL_REC));
    --select * from table(reports.fRpt_MFG_TEST_FL_Results(strProdClas s, strAssemType));

    end;

    All attempts at using CAST result in ORA-00902: invalid datatype.
    Attempting without CAST results in PLC-00428: an INTO clause is expected in this SELECT statement.

    Can someone tell me what I am doing wrong?

    Thanks,
    Neil

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I have a simple design & implementation policy.
    First make it work, then make it fancy.
    If you can not make something work in a simple mock environment, trying to make it work in Production environment is a recipe for disaster.

    You should be able to prototype the desired language constructs in 1 - 2 dozen lines of code.
    If you can't make it work in a simple scenario, you will fail when trying to integrate into the monster of the previous post.

    HTH
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  9. #9
    Join Date
    Apr 2010
    Posts
    4

    Talking It works!

    Using the simple form:
    select * from table(reports.fRpt_MFG_TEST_FL_Results('CR600', 'PLATE'));
    works like a champ. Note that in the 'monster post' above, I did have to fix one small section. I replaced

    decode_result_code('||strProdClass||','||strAssemT ype||',''TEST_FL'',a.result_code)

    with

    decode_result_code('''||strProdClass||''','''||str AssemType||''',''TEST_FL'',a.result_code)

    For both my understanding, and for any other noob's that might be following this, additional input on any of the following would be really appreciated:
    1. Not sure why I could not get the Select from my table function to work in any of the forms listed in my previous post, where it was being done inside of a Begin...End. If anyone can enlighten me, I would appreciate it.
    2. In what I did above, I gathered the output of the query into a ref cursor, then fetched and piped each individual record from the ref cursor one at a time in a Loop construct. Seems like a long way around. Is there a simpler way to get the same effect?

    PS: I do appologize for the size of that post. It didn't look that big in Navigator!

    Thanks,
    Neil

  10. #10
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by das_bultz View Post
    1. Not sure why I could not get the Select from my table function to work in any of the forms listed in my previous post, where it was being done inside of a Begin...End. If anyone can enlighten me, I would appreciate it.
    Just read the error message:
    Quote Originally Posted by Oracle error message
    PLC-00428: an INTO clause is expected in this SELECT statement.
    Inside a PL/SQL program any value retrieved must be stored into a variable

Posting Permissions

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