DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: "from" clause question

  1. #1
    Join Date
    Apr 2003
    Location
    Rosmalen, Netherlands.
    Posts
    73

    "from" clause question

    Hi,

    How can I make the "FROM" clause variable using a cursor ??


    Cursor c_one is
    select tables
    from all_tables
    where ..... ;

    r_c_one c_one%rowtype;

    Cursor c_two is
    select count(*)
    from r_c_one.tables;

    r_c_two c_two%rowtype;


    Or... do I have to use dynamic SQL for this??

    Please give me some examples on dynamic SQL ( its been ages ago I last used it ).

    mvg GKramer

  2. #2
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    Hi.

    If you have a finite number of options you could do something like:


    SET SERVEROUTPUT ON SIZE 1000000
    DECLARE
    c_objects SYS_REFCURSOR;
    TYPE t_object IS RECORD (
    object_name user_objects.object_name%TYPE,
    object_type user_objects.object_type%TYPE
    );
    l_object t_object;
    BEGIN
    IF FALSE THEN
    OPEN c_objects FOR
    SELECT object_name, object_type FROM user_objects;
    ELSE
    OPEN c_objects FOR
    SELECT object_name, object_type FROM all_objects WHERE owner = USER;
    END IF;

    LOOP
    FETCH c_objects
    INTO l_object;
    EXIT WHEN c_objects%NOTFOUND;

    DBMS_OUTPUT.put_line('Object Name:' || l_object.object_name || ' - ' || l_object.object_type);
    END LOOP;
    CLOSE c_objects;
    END;
    /


    Otherwise use Dynamic SQL.

    Cheers
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: oracle-base.com
    My blog: oracle-base.com/blog

  3. #3
    Join Date
    Apr 2003
    Location
    Rosmalen, Netherlands.
    Posts
    73
    TIM,

    This didn't work out You defined two tables in te select part.

    My chalange is to retrieve data from various tables fetched by a previous cursor ( like the example I gave )

    /*********************************************************/

    -- Retrieval of tablenames from all_tables
    -- based on the user and made earlier in another script

    Cursor c_one is
    select tables
    from all_tables
    where ..... ;

    r_c_one c_one%rowtype;


    /*********************************************************/

    -- I want to make a selection on these newmade tables
    -- fetched in cursor c_one DYNAMICLY

    Cursor c_two is
    select data
    from r_c_one.tables;

    r_c_two c_two%rowtype;

    /*********************************************************/

    If this can not be done in a cursor please give me some examples of dynamic SQL on this.

    Regards,
    GKramer

  4. #4
    Join Date
    Apr 2003
    Location
    Rosmalen, Netherlands.
    Posts
    73
    Hi to all,

    I found some doumetation in the DYNAMIC SQL and assembled a script on this information and it WORKS !!! ( hurray ).

    If you have some comment on it please let me know.

    Regards,

    GKramer

    ******************************************************************
    create or replace procedure XXXXXXX
    as
    --
    STRING_V VARCHAR2(2000);
    --
    CURSOR C_EEN IS
    SELECT OWNER,
    TABLE_NAME
    FROM ALL_TABLES
    WHERE OWNER ='Q1000_SPSS'
    AND (TABLE_NAME LIKE ('U0%')
    OR TABLE_NAME LIKE ('M0%') )
    and TABLE_NAME LIKE ('%MODULE')
    AND TABLE_NAME NOT LIKE ('%SESSION%');
    --
    R_C_EEN C_EEN%ROWTYPE;
    --
    begin
    --
    open c_een;
    fetch c_een into r_c_een;
    while c_een%found loop
    --
    --DBMS_OUTPUT.PUT_LINE(r_c_een.table_name);
    STRING_V := 'select count(*) from Q1000_spss.'||r_c_een.table_name;
    --
    DECLARE
    LOGCUR INTEGER;
    REQ_COLUMN VARCHAR2(2000);
    LOGCUR_EXEC INTEGER;
    STRING_DAT VARCHAR2(3000);
    LAST_ROW EXCEPTION;
    PRAGMA EXCEPTION_INIT(LAST_ROW,-01002);
    BEGIN
    LOGCUR:=DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(LOGCUR,STRING_V,DBMS_SQL.NATIVE);
    dbms_sql.define_column(LOGCUR, 1, REQ_COLUMN,2000);
    LOGCUR_EXEC := DBMS_SQL.EXECUTE(LOGCUR);
    --
    loop
    IF DBMS_SQL.FETCH_ROWS(LOGCUR) = 1 THEN
    DBMS_SQL.COLUMN_VALUE(LOGCUR, 1, REQ_COLUMN);
    --
    --DBMS_OUTPUT.PUT_LINE('r_c_een.table_name '||r_c_een.table_name);
    DBMS_OUTPUT.PUT_LINE(' Number of column present in '||r_c_een.table_name||' -- '||REQ_COLUMN);
    --
    STRING_DAT := 'INSERT INTO Q1000_spss.aap VALUES ( ''I did it !!'||r_c_een.table_name||' ** '||REQ_COLUMN||''')';
    DBMS_OUTPUT.PUT_LINE('INSERT INTO Q1000_spss.aap VALUES ( ''I did it !!'||r_c_een.table_name||' ** '||REQ_COLUMN||''')');
    execute immediate(STRING_DAT);
    ELSE
    RAISE LAST_ROW;
    END IF;
    end loop;
    --
    DBMS_SQL.CLOSE_CURSOR(LOGCUR);
    --
    EXCEPTION
    WHEN LAST_ROW THEN
    null;
    --DBMS_OUTPUT.PUT_LINE('--Last row--');
    WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('What went wrong ;'||SQLERRM);
    END;
    --
    commit;
    --
    fetch c_een into r_c_een;
    end loop;
    close c_een;
    end;

Posting Permissions

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


Click Here to Expand Forum to Full Width