2

This is one of a major brain-ache problem I faced while doing programming in Oracle PLSQL to achieve one of my serious task.

I need to write a procedure/function/anonymous-block that will fetch all the records from an unknown table, let's say MyUnknownTable. This unknown table name could be any table from my existing database. The thing is, using some decision logic, I am finding a table name dynamically and then passing to the below code (means MyUnknownTable will be any table name which I will decide as per my logic in some other code), and then the below code should fetch all the records from that table in output.

SET serveroutput ON;
DECLARE
  rc_ SYS_REFCURSOR;
  c_         NUMBER;
  i_         NUMBER;
  col_count_ NUMBER;
  desc_tab_ DBMS_SQL.DESC_TAB;
  table_header VARCHAR2(2000);
  table_data   VARCHAR2(2000);

  l_rec **MyUnknownTable**%rowtype;

BEGIN
  OPEN rc_ FOR 'SELECT * FROM **MyUnknownTable**';

  LOOP
     FETCH rc_ INTO l_rec;
     EXIT WHEN l_rec%notfound;
     dbms_output.put_line(**<All Columns from l_rec>**);
  END LOOP;

  rc_.CLOSE();
END;

My problem with above code is l_rec. I know this way is wrong and senseless, but in the above code I am defining l_rec as **MyUnknownTable**%rowtype, so that l_rec will gain the same data structure as MyUknownTable and FETCH rc_ INTO l_rec will work. I haven't found the way how to declare l_rec dynamically so that this can be used with any unknown table which my code will pass to this code block.

I used my brain to its limit minus half, but couldn't get into the way 'HOW'.

I need help from the rest of the Universe with a big hope.

Even this question is unanswered in Oracle Support Community https://community.oracle.com/thread/1036107

Below is the original code for reference where I need to make the code to fetch the data:

SET serveroutput ON;
DECLARE
  v_column_name  VARCHAR2(100) := UPPER('CASE_ID');
  v_column_value VARCHAR2(100) := '324735';
  CURSOR table_names_cur
  IS
    SELECT DISTINCT table_name
    FROM ALL_TAB_COLS
    WHERE UPPER(column_name)=UPPER(v_column_name)
    AND table_name NOT LIKE '%$%'
    AND owner LIKE 'ARGUS_APP';

  rc_ SYS_REFCURSOR;
  c_         NUMBER;
  i_         NUMBER;
  col_count_ NUMBER;
  desc_tab_ DBMS_SQL.DESC_TAB;
  table_header VARCHAR2(2000);
  table_data   VARCHAR2(2000);
BEGIN
  dbms_output.put_line ('Script Execution Started ..');
  FOR c_table_name IN table_names_cur
  LOOP
    OPEN rc_ FOR 'SELECT * FROM ' || c_table_name.table_name || ' WHERE ' || v_column_name || ' = ' || v_column_value || '';
    c_ := DBMS_SQL.to_cursor_number(rc_);
    DBMS_SQL.DESCRIBE_COLUMNS(c_, col_count_, desc_tab_);
    table_header:= 'XYZ';
    FOR i_      IN 1..col_count_
    LOOP
      IF table_header = 'XYZ' THEN
        table_header := desc_tab_(i_).col_name;
      ELSE
        table_header := table_header || '|' || desc_tab_(i_).col_name;
      END IF;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE(table_header);
    /*
    **Need to write code here to fetch data from rc_**
    */
    DBMS_SQL.CLOSE_CURSOR(c_);
  END LOOP;
  dbms_output.put_line ('Script Execution Completed ..' );
END;

~ Ajit Singh Sachan

MeisterAjit
  • 108
  • 9
  • 1
    I don't think you can declare a record that way. You will need [`dbms_sql.describe_columns`](https://docs.oracle.com/database/121/ARPLS/d_sql.htm#ARPLS68257) to get the columns of the result and then use that information –  Apr 05 '16 at 13:53
  • 4
    What do you really need to do with the data from this unknown table? That is going to drive how complex the code needs to be. In this example, it looks like you already know that the table has a column named `CASE_ID` and that that is the only column you are interested in. If that is the case in reality, then the problem is much easier than if you are trying to make it fully dynamic. – Justin Cave Apr 05 '16 at 14:03
  • This kind of dynamic sql will not perform well for a CRUD application. Were you thinking of using this in an application for every day use? – kevinskio Apr 05 '16 at 14:04
  • @justin - I need to print all the columns of the table in output. #corrected above question. – MeisterAjit Apr 05 '16 at 14:17
  • @a_horse_with_no_name - I will try using describe_columns and let you know if it works for me. Thanks for the suggestion. – MeisterAjit Apr 05 '16 at 14:19
  • 1
    Are you sure you want to use `dbms_output`, which relies on the session have that enabled, and can't be consumed by anything else? Maybe you want to return/display the ref cursor? [This](http://stackoverflow.com/a/11233929/266304) has a simple `describe_columns` approach to dump an existing ref cursor, so is that doing the sort of thing you need? – Alex Poole Apr 05 '16 at 14:20
  • @kevinsky - requirement is to fetch all the data from the set of unknown number of tables where data is present for particular entered customer. I give customer number as input and it should search in whole database where this entered customer number is present in customer_number column in all the table. Here table names are fetched dynamically as per my predefined logic of comparing customer numbers. I need to print that customers data from all these dynamically found tables. – MeisterAjit Apr 05 '16 at 14:26
  • If you **only** want to print the column names, a `select column_name from user_tables where table_name = 'MYUNKNOWNTABLE'` will do –  Apr 05 '16 at 14:28
  • @Alex Poole - I used DBMS_OUTPUT just for this example testing. In my original code, I could use some other output stream like into a plain file or smthing. Yes I need the ref cur fetched records in my hand as output. – MeisterAjit Apr 05 '16 at 14:38
  • @a_horse_with_no_name - I need both Header with Data Rows. I coded successfully for Header, but the headache is with Data Rows. – MeisterAjit Apr 05 '16 at 14:44
  • 2
    You could do all this with package `DBMS_SQL`. However, tell us what you actually want to achieve (not what you assume to work) then you will get better answers. Maybe put a complete example for one real table. – Wernfried Domscheit Apr 05 '16 at 14:46
  • @Wernfried - Sure, I will provide you full code after sometime. At present, I am on the way to my home from office. Apologies for the delay that will cause. – MeisterAjit Apr 05 '16 at 14:53
  • Hi Guys - I have added the original code in the question. There I am able to get the column names but unable to fetch the data records from cursor rc_. Any suggestion ? – MeisterAjit Apr 06 '16 at 07:38

1 Answers1

0

Finally here is the code that works fine for me:

SET serveroutput ON;
DECLARE
  v_column_name  VARCHAR2(100) := UPPER('CASE_ID');
  v_column_value VARCHAR2(100) := '324735';
  CURSOR table_names_cur
  IS
    SELECT DISTINCT table_name
    FROM ALL_TAB_COLS
    WHERE UPPER(column_name)=UPPER(v_column_name)
    AND table_name NOT LIKE '%$%'
    AND owner LIKE 'ARGUS_APP';
  --AND table_name IN ('CASE_MASTER');
  rc_ SYS_REFCURSOR;
  c_         NUMBER;
  i_         NUMBER;
  col_count_ NUMBER;
  desc_tab_ DBMS_SQL.DESC_TAB;
  table_header VARCHAR2(2000);
  table_data   VARCHAR2(2000);
  header_done  NUMBER;
  L_VARCHAR    VARCHAR2(4000);
  --filehandle1 UTL_FILE.FILE_TYPE;
  -- l_rec case_master%rowtype;
BEGIN
  --filehandle1 := UTL_FILE.FOPEN ('C:\Users\M513705\Desktop\Ajit\','test.txt','W');
  -- UTL_FILE.PUT (filehandle1,'Works');
  dbms_output.put_line ('Script Execution Started ..');
  FOR c_table_name IN table_names_cur
  LOOP

    header_done:=0;

    OPEN rc_ FOR 'SELECT * FROM ' || c_table_name.table_name || ' WHERE ' || v_column_name || ' = ' || v_column_value || '';
    c_ := DBMS_SQL.to_cursor_number(rc_);
    DBMS_SQL.DESCRIBE_COLUMNS(c_, col_count_, desc_tab_);

    FOR i       IN 1..col_count_
    LOOP
      DBMS_SQL.DEFINE_COLUMN(c_, i, L_VARCHAR, 4000);
    END LOOP;

    table_header:= 'XYZ';

    LOOP
      EXIT WHEN DBMS_SQL.FETCH_ROWS (c_) = 0;

      IF header_done              = 0 THEN
        header_done              := 1;
        FOR i_                   IN 1..col_count_
        LOOP
          IF table_header = 'XYZ' THEN
            table_header := desc_tab_(i_).col_name;
          ELSE
            table_header := table_header || '|' || desc_tab_(i_).col_name;
          END IF;
        END LOOP;
        DBMS_OUTPUT.PUT_LINE('Table: ' || c_table_name.table_name);
        DBMS_OUTPUT.PUT_LINE(table_header);
      END IF;

      table_data:='XYZ';

      FOR i     IN 1..col_count_
      LOOP
        DBMS_SQL.COLUMN_VALUE(c_, i, L_VARCHAR);
        -- DBMS_OUTPUT.PUT_LINE('Row ' || DBMS_SQL.LAST_ROW_COUNT || ': ' || desc_tab_(i).col_name || ' = ' || L_VARCHAR);
        IF table_data = 'XYZ' THEN
          table_data := L_VARCHAR;
        ELSE
          table_data := table_data || '|' || L_VARCHAR;
        END IF;
      END LOOP;

      DBMS_OUTPUT.PUT_LINE(table_data);

    END LOOP;

    DBMS_SQL.CLOSE_CURSOR(c_);

  END LOOP;

  dbms_output.put_line ('Script Execution Completed ..' );
END;
/

Special thanks to a_horse_with_no_name, Justin Cave, kevinsky, Alex Poole and Wernfried Domscheit for the support :)

MeisterAjit
  • 108
  • 9