Home » SQL & PL/SQL » SQL & PL/SQL » create view dynamically
create view dynamically [message #192452] Tue, 12 September 2006 08:07 Go to next message
Messages: 18
Registered: November 2005
Junior Member
Hello, I have done very little PL/SQL. I am on a tight deadline and trying to figure out the best way to do something.

I need to create a PL/SQL package that will (among many other things) read through the list of columns in a table in another instance and only add certain columns to the create view statement. To better explain, let me give a simplified example.

Let's say I have a table named 'testTable' on instance 'testInst'.

testTable appears as follows:


When I call a procedure in my package I want it to search for all columns with type not = SDO_TOPO_GEOMETRY and add it to my create view.

A simple SQL statement that will perform this is:

SELECT column_name, data_type from all_tab_cols
WHERE owner = 'testInst' AND table_name = 'testTable' AND data_type != 'SDO_TOPO_GEOMETRY';

I want to take the values from that query and add them to my create view statement. In this case, I would like my create view statement to appear as follows:

CREATE OR REPLACE VIEW testView AS SELECT id, name FROM testInst.testTable; (note the geom column is omitted)

How can I construct this functionality?

Re: create view dynamically [message #192511 is a reply to message #192452] Tue, 12 September 2006 12:32 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
It seems you are confusing 'schema' and 'instance' - it appears you really mean schema. Two very different things.

Here's a template - the owner of this procedure will need the 'create view' privilege granted directly (not through a role):

create or replace procedure p_view
   p_owner      in all_tab_cols.owner%type,
   p_table_name in all_tab_cols.table_name%type
  v_column_list varchar2(32767);
  for r in (select column_name
              from all_tab_cols
             where owner = p_owner
               and table_name = p_table_name
               and data_type != 'SDO_TOPO_GEOMETRY') loop

    v_column_list := v_column_list || ',' || r.column_name;           

  end loop;

  if v_column_list is not null then
    execute immediate 'create or replace view testview as ' ||
                      'select ' || ltrim(v_column_list, ',') ||
                      ' from ' || p_owner || '.' || p_table_name;
  end if;                    
Re: create view dynamically [message #192521 is a reply to message #192511] Tue, 12 September 2006 13:12 Go to previous message
Messages: 18
Registered: November 2005
Junior Member
Thanks! Yes, I was confusing schema and instance (I do it all the time Embarassed )

Anyway, I got it working, but as I look through your code I see yours is a lot simpler. I will probably try to use some of your practices so thank you very much!
Previous Topic: ORA-01847 day of the month must be between 1 and last day of month
Next Topic: Inserting the values in Internal BLOB
Goto Forum:

Current Time: Tue Jan 24 05:41:20 CST 2017

Total time taken to generate the page: 0.22245 seconds