Read all tables from a schema and insert values.

[Thirumurugan Sivaji]

Hi,

I want to read all the tables from a schema, and read all columns for the particular table, based on the column datatype and its length i need to insert the values .

Ex:

SELECT TABLE_NAME  FROM USER_TABLES;

wil return all the tables, from the result i need to loop the resutset

select COLUMN_NAME,DATA_TYPE,DATA_LENGTH from USER_TAB_COLUMNS where table_name  = <eachtable from the above query>

create or replace
PROCEDURE CREATEINSERT
IS

cursor tableCursor
          is SELECT TABLE_NAME  FROM USER_TABLES UT; TABLE_NM USER_TABLES.TABLE_NAME%TYPE;
          
BEGIN
         open tableCursor; 
          loop
            fetch tableCursor into TABLE_NM;
             exit when tableCursor%NOTFOUND; 
         
           INSERTRECORDS(TABLE_NM);
              
          end loop;
          close tableCursor;
        
END CREATEINSERT;  

procedure for INSERTRECORDS:
------------------------------------

CREATE OR REPLACE
PROCEDURE INSERTRECORDS(tblname in varchar2) AS
col_name USER_TAB_COLUMNS.COLUMN_NAME%TYPE;
data_typ USER_TAB_COLUMNS.DATA_TYPE%TYPE;
data_len USER_TAB_COLUMNS.DATA_LENGTH%TYPE;

cursor colCursor

          is  SELECT COLUMN_NAME,DATA_TYPE,DATA_LENGTH from USER_TAB_COLUMNS WHERE TABLE_NAME = tblname;  
          
BEGIN
         open colCursor; 
          loop
            fetch colCursor into col_name,data_typ,data_len;
             exit when colCursor%NOTFOUND; 

               if(tblname='A3') then
                
                 insert into A3(eid) values ('1');
               end if;
              
                        
          end loop;
          close colCursor;
          
END INSERTRECORDS;

Problem:

From the 1st procedure im getting each table and pass the value to second procedure. From the second procedure with in the cursor i need to get all the columns
in a single step, so that i can insert values into table ..

[Fatih Keles]

You can create the sql as varchar2 variable then use execute immediate command to execute it using your variables.

Regards,

Fatih.