Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2004
    Posts
    4

    Unanswered: passing table name dynamically in a cursor

    i need to pass the table name directly for a cursor declaration

    function F1(p IN varchar2) return interger IS
    cursor c is select * from p where <condition>
    begin
    open c
    ...
    ....
    end

    here in cursor declaration the table name p is coming as input parameter to fn .. will this work ?

  2. #2
    Join Date
    May 2003
    Location
    France
    Posts
    112
    Use then package DBMS_SQL to open/parse/execute and fecth dynamics cursors

  3. #3
    Join Date
    Nov 2004
    Posts
    4
    can you pls explain the usage a little bit

    thanks

  4. #4
    Join Date
    May 2003
    Location
    France
    Posts
    112
    It will be something like that

    function F1(p IN varchar2) return interger IS


    v_cursor NUMBER;
    v_table_count number;
    v_sqlstring varchar2(4000);
    BEGIN

    v_cursor := DBMS_SQL.OPEN_CURSOR;
    v_sqlstring := 'select count(*) from '|| p;
    DBMS_SQL.PARSE(v_cursor, v_SQlString, DBMS_SQL.V7);
    -- bind output column

    DBMS_SQL.DEFINE_COLUMN(v_cursor, 1, v_table_count);
    v_returnValue := DBMS_SQL.EXECUTE(v_cursor);
    -- if multiple lines you can loop and do your process

    v_returnValue := DBMS_SQL.FETCH_ROWS(v_cursor);
    DBMS_SQL.COLUMN_VALUE(v_cursor,1, v_table_count);
    DBMS_SQL.CLOSE_CURSOR(v_cursor);
    END F1;

Posting Permissions

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