0

How do I get the data(i.e rows) from the column_name I retrieved from SQL statement? (Completely new to PL/SQL). Here is my code:

create or replace procedure com_coll_cur
as
    type comColcur is ref cursor;
    com_col_cur comColcur;
    sql_stmt varchar2(4000);
    type newtab_field is table of comColcur%TYPE;
begin
    sql_stmt :=
        'select column_name from all_tab_cols where table_name in (''TAB1'', ''TAB2'') ' ||
        'group by column_name having count(*)>1';

    open com_col_cur for sql_stmt;
    loop
        fetch com_col_cur into newtab_field;
        exit when com_col_cur%NOTFOUND;
    end loop;

    close com_col_cur;
end;

What I'm trying to do here is first find the common columns between the two tables. This part only grabs column_name but I also want the data in that common columns. So I used cursor to "point" that common column_name and used loop(fetch) to get the data inside that common column_name. Finally, I want to create new table with this common columns only with their data.

I am new to everything here and any help will be appreciate it.

William Robertson
  • 15,273
  • 4
  • 38
  • 44
kys92
  • 73
  • 3
  • 8
  • What do you mean by "make" new table? Do you want to create a new table? You can do that only using dynamic SQL in your code; that is an advanced topic (and not recommended in general anyway); definitely not recommended if you are "completely new" to PL/SQL. Then: what do you mean by "containing this column name + its data inside"? Do you mean in the same column? The column name is VARCHAR2; what if the "data inside" is NUMBER, or DATE or TIMESTAMP? How do you want to "contain" a string and TIMESTAMP data together? –  Oct 11 '17 at 21:48
  • @mathguy That is the part where I'm also stuck at. I want to define a table's column data types based on cursor's data type. So if cursor grabs a common columns between the two table and data type happens to be date,varchar2,number, for col1,col2,col3 I want to store these common columns to a newly created table with date,varchar2,number for col1,col2,col3 data types respectively. – kys92 Oct 11 '17 at 22:03
  • As you've written it, the cursor doesn't need to be dynamic. You have a report that returns a list of column names. I don't know what the "pointing" thing is meant to do. – William Robertson Oct 11 '17 at 22:56

1 Answers1

0

You don't understand how works cursors and fetch. Fetch get the data from the cursor, so in your procedure example you get only column names, not the data in the columns. To get data you need another cursor - select from the target table or use the dynamic sql.

This is a code that do what you describe. It is not clear to me how you want to store data from two tables - subsequently or in another manner. Let's assume that we store them subsequently. Also this code suggests than columns with the same names have the same datatypes. Part of this code (to make datatype) I get from another stackoverflow post to save time to write it: How do I get column datatype in Oracle with PL-SQL with low privileges?

dbms_output.put_line - used to print sql statements that we create

declare
   cSql          varchar2(4000);
   cCols         varchar2(4000);
   cNewTableName varchar2(30) := 'AABBCC';
   cTb1          varchar2(30) := 'TAB1';
   cTb2          varchar2(30) := 'TAB2';
begin 
   for hc in (
           select T.column_name, T.typ
           from
           (
           select column_name,
                  data_type||
                  case when data_precision is not null and nvl(data_scale,0)>0 then '('||data_precision||','||data_scale||')'
                       when data_precision is not null and nvl(data_scale,0)=0 then '('||data_precision||')'
                       when data_precision is null and data_scale is not null then '(*,'||data_scale||')'
                       when char_length>0 then '('||char_length|| case char_used 
                                                                  when 'B' then ' Byte'
                                                                  when 'C' then ' Char'
                                                                  else null 
                                                                  end||')'
                  end||decode(nullable, 'N', ' NOT NULL')  typ
        from all_tab_cols 
       where table_name in (cTb1, cTb2) ) T
             group by T.column_name, T.typ having count(*) > 1)
   loop
      cSql := cSql || case when cSql is null then null else ',' end || hc.column_name || ' ' || hc.typ;
      cCols := cCols || case when cCols is null then null else ',' end || hc.column_name;
   end loop;

   if (cSql is not null) then
      -- First drop table if it exists
      for hc in (select * from all_objects where object_type = 'TABLE' and object_name = cNewTableName)
      loop
         execute immediate 'drop table ' || hc.object_name;
      end loop;

      -- create table
      cSql := 'create table ' || cNewTableName || '(' || cSql || ')';
      dbms_output.put_line(cSql);
      execute immediate cSql;

      -- insert data
      cSql := 'insert into ' || cNewTableName || '(' || cCols || ') select ' || cCols || ' from ' || cTb1;
      dbms_output.put_line(cSql);
      execute immediate cSql;

      cSql := 'insert into ' || cNewTableName || '(' || cCols || ') select ' || cCols || ' from ' || cTb2;
      dbms_output.put_line (cSql);
      execute immediate cSql;

   end if;
end;
Victor O.
  • 41
  • 3