Thanks for the question, Denise.

Asked: February 01, 2011 - 1:37 pm UTC

Last updated: February 04, 2011 - 10:08 am UTC

Version: 9.2.0

Viewed 10K+ times! This question is

You Asked

First off, I am reletively new to SQL-PL/SQL.

<bold>What I am ultimately trying to do:</bold>
I created a BULK REF CURSOR to hold all the names of our active studies.
I want to create a loop where for each study (e.g. studyXYZ) in the cursor, get the data for that study (e.g. "studyXYZ$current.dm" is the table/view name where the data is in) and put it in a new view (e.g. myView). So at the end myView contains all the data for all active studies. Currently the data for each study is in its own table/view.

For now, I am taking baby steps...

<bold>What the code below is suppose to do</bold>
The code below is suppose to create a view based on the execute immediate statment using a bind variable, where the bind variable is part of the table/view name.

--Before complicating things with a FOR LOOP, I am just trying to make it work for one study

 DECLARE 
 StudyName VARCHAR(10) := 'studyXYZ'; --bind variable to hold name of study

 CREATE VIEW v_myView AS (
 EXECUTE IMMEDIATE 'SELECT *'|| ' FROM '||StudyName||'$current.dm');

 GRANT SELECT ON v_myView TO PUBLIC;

--now that i have "copied" the data into myView, I want to see it.
 select * from v_myView;

<bold>Problem:</bold>
It doesn't work :)
I get a PLS: 00103 - Encountered the symbol CREATE when expecting one of the following:...

Any pointers?

and Tom said...

Well, this is a case of "thank goodness it did not work"

I'm not sure you understand what a view is - you do not put data into a view, a view is simply a stored query. At the end - myView would contain just the view text from the last create view statement you executed.

If you wanted to have a view that contained a sql query that could retrieve all of the data from a series of tables - you would do something like this:

create or replace view myView
as
select 'studyXYZ', t.* from studyXYZ$current.dm t
UNION ALL
select 'studyABC', t.* from studyABC$current.dm t
....
/

Now, if you wanted - you could write a plsql block to query the dictionary to find all of the 'study' tables and construct this query and then you would execute the statement via:

begin
    ....
    execute immediate the_variable_that_contains_the_create_view;
end;
/

Rating

(3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Denise, February 02, 2011 - 3:46 pm UTC

Thank you for your quick response. Currently, the other SQL programmers here use a similar approach to the first suggestion you made...

create or replace view myView
as
select 'studyXYZ', t.* from studyXYZ$current.dm t
UNION ALL
select 'studyABC', t.* from studyABC$current.dm t
....

...in the sense that they have a seperate query statment per study, and then union the data into a view.

Personally, I think this is not a good approach. I say that because for every new study, they have to modify the query statement to include that study or remove a study once it is over, hence, high maintenance.

I am trying to come up with a solution to have a PL/SQL package or procedure, or someway to query the database to get a list of all active study names (mabe put into a Bulk Cursor). Then for each active study name from the "Cursor", query that study's view and put the data listing somewhere where I can cumulatively collect the data for all active studies.

DECLARE
  TYPE r_cursor is REF CURSOR;
  c1 r_cursor;
  studynme AllStudies.StudyName%TYPE;
begin
  OPEN c1 FOR SELECT StudyName FROM AllStudies WHERE StudyStatus = 'Active';
  LOOP
      FETCH c1 INTO studynme;
      EXIT WHEN c1%notfound;
      EXECUTE IMMEDIATE 'SELECT dataPoint1, dataPoint2
      FROM ' || c1||'$current.dm';
     --at this point, I could insert the values into myView, but this would require that I define the view earlier, right?
  END LOOP;
  CLOSE c1;
END;

I think I see what you are saying about how a view works...unless I am still confused about Views. I am not sure how else to accomplish this.

February 03, 2011 - 2:42 pm UTC

... Personally, I think this is not a good approach. I say that because for every new study, they have to modify the query statement to include that study or remove a study once it is over, hence, high maintenance. ..

agreed, I *hate* this data model, it is a really bad idea.

... --at this point, I could insert the values into myView, but this would
require that I define the view earlier, right? ...

you do not "insert the values into myView" - myview is just a stored query, it is a shortcut, it is an alias. It doesn't contain any data really - just a query.

put the data listing somewhere where I can cumulatively collect the data for all active studies.

I don't know what that means?

I think you are looking for something like this:

ops$tkyte%ORA11GR2> create table abc$current.dm ( datapoint1 varchar2(20), datapoint2 varchar2(20) );

Table created.

ops$tkyte%ORA11GR2> create table xyz$current.dm ( datapoint1 varchar2(20), datapoint2 varchar2(20) );

Table created.

ops$tkyte%ORA11GR2> create table allStudies ( studyName varchar2(3), studyStatus varchar2(10) );

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> insert into allStudies values ( 'abc', 'Active' );

1 row created.

ops$tkyte%ORA11GR2> insert into allStudies values ( 'def', 'InActive' );

1 row created.

ops$tkyte%ORA11GR2> insert into allStudies values ( 'xyz', 'Active' );

1 row created.

ops$tkyte%ORA11GR2> insert into abc$current.dm values ( 'hello', 'world' );

1 row created.

ops$tkyte%ORA11GR2> insert into xyz$current.dm values ( 'goodbye', 'all' );

1 row created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> DECLARE
  2    l_query long;
  3    l_cursor sys_refcursor;
  4  
  5    type array is table of varchar2(20) index by binary_integer;
  6    l_datapoint1 array;
  7    l_datapoint2 array;
  8    l_limit      number := 100;
  9  begin
 10      for x in (select studyName from allStudies where StudyStatus = 'Active' )
 11      loop
 12          l_query := l_query || ' union all select dataPoint1, dataPoint2 from ' || x.studyName || '$current.dm';
 13      end loop;
 14  
 15      open l_cursor for substr( l_query, 11 );
 16  
 17      loop
 18          fetch l_cursor bulk collect into l_datapoint1, l_datapoint2 limit l_limit;
 19  
 20          for i in 1 .. l_datapoint1.count
 21          loop
 22              dbms_output.put_line( l_datapoint1(i) || ', ' || l_datapoint2(i) );
 23          end loop;
 24          exit when l_cursor%notfound;
 25      end loop;
 26      close l_cursor;
 27  END;
 28  /
hello, world
goodbye, all

PL/SQL procedure successfully completed.

couple of notes since you appear to be a beginner at plsql:

if you can - use:

for x in (select ... ) loop

instead of

open cursor
loop
fetch ...
exit when cursor%notfound
end loop;
close cursor

it is

a) less error prone
b) easier to code
c) easier to read
d) more performant - since the for x in (select ) array fetches 100 rows at a time.

However, when using a ref cursor - you have to do it the harder way - which leads to the second note:

use bulk processing in general, I'm array fetching 100 records at a time using the bulk collect from the ref cursor. Just make sure to do the loop exactly as I have - the exit when l_cursor%notfound MUST be after the processing of the data! The location of the fetch and exit are important.

and lastly, EXECUTE IMMEDIATE 'SELECT dataPoint1, dataPoint2
FROM ' || c1||'$current.dm'; would do nothing. To do dynamic sql and fetch the results - you have to do it the way I did - with an open, a fetch, and a close.

Possible data model

Tyson, February 03, 2011 - 3:55 pm UTC

I realize that this does not answer the direct problem, but here is a data model that borrows from the apex websheet design (the websheet design is probably better because the apex team rocks). A generic data storage table with a large amount of generic columns. A table that maps the generic columns to a more meaningful name. a procedure that builds a view against the generic data table which maps meaniningful names to the columns.

CREATE TABLE CASE_STUDIES (
CASE_STUDY_ID NUMBER NOT NULL ENABLE,
NAME VARCHAR2(50 BYTE),
CONSTRAINT CASE_STUDIES_PK PRIMARY KEY (CASE_STUDY_ID),
CONSTRAINT CASE_STUDIES_NAME_UK UNIQUE (NAME)
);

CREATE UNIQUE INDEX CASE_STUDIES_NAME_UK ON CASE_STUDIES (NAME);
CREATE UNIQUE INDEX CASE_STUDIES_PK ON CASE_STUDIES (CASE_STUDY_ID);

CREATE TABLE CASE_STUDY_DATA(
ID NUMBER NOT NULL ENABLE,
CASE_STUDY_ID NUMBER NOT NULL ENABLE,
C001 VARCHAR2(4000 BYTE),
C002 VARCHAR2(4000 BYTE),
C003 VARCHAR2(4000 BYTE),
C004 VARCHAR2(4000 BYTE),
C005 VARCHAR2(4000 BYTE),
C006 VARCHAR2(4000 BYTE),
C007 VARCHAR2(4000 BYTE),
C008 VARCHAR2(4000 BYTE),
C009 VARCHAR2(4000 BYTE),
C010 VARCHAR2(4000 BYTE),
CONSTRAINT CASE_STUDY_DATA_PK PRIMARY KEY (ID),
CONSTRAINT CASE_STUDY_DATA_CASE_ID_FK FOREIGN KEY (CASE_STUDY_ID)
REFERENCES CASE_STUDIES (CASE_STUDY_ID) ENABLE
);

CREATE TABLE CASE_STUDY_COLS(
ID NUMBER NOT NULL ENABLE,
CASE_STUDY_ID NUMBER NOT NULL ENABLE,
DISPLAY_COL_NAME VARCHAR2(30 BYTE) NOT NULL ENABLE,
MAP_COL_NAME VARCHAR2(30 BYTE) NOT NULL ENABLE,
CONSTRAINT CASE_STUDY_COLS_PK PRIMARY KEY (ID),
CONSTRAINT CASE_STUDY_COLS_UK1 UNIQUE (CASE_STUDY_ID, MAP_COL_NAME),
CONSTRAINT CASE_STUDY_COLS_CASE_STUD_FK1 FOREIGN KEY (CASE_STUDY_ID)
REFERENCES CASE_STUDIES (CASE_STUDY_ID) ENABLE
);

CREATE UNIQUE INDEX CASE_STUDY_COLS_PK ON CASE_STUDY_COLS (ID);
CREATE UNIQUE INDEX CASE_STUDY_COLS_UK1 ON CASE_STUDY_COLS (CASE_STUDY_ID, MAP_COL_NAME);

insert into case_studies(case_study_id, name)
values(1,'FISH');

insert into case_study_data( id, case_study_id, C001, C002, C003, C004, C005, C006, C007, C008, C009, C010)
values(1, 1 , 'SALMON', '21', '8', 'M', null, null, null, null, null, null);
insert into case_study_data( id, case_study_id, C001, C002, C003, C004, C005, C006, C007, C008, C009, C010)
values(2, 1 , 'SALMON', '24', '8.7', 'F', null, null, null, null, null, null);
insert into case_study_data( id, case_study_id, C001, C002, C003, C004, C005, C006, C007, C008, C009, C010)
values(3, 1 , 'SALMON', '18', '6.4', 'M', null, null, null, null, null, null);

insert into case_study_cols( id, case_study_id, display_col_name, map_col_name)
values(1, 1, 'SPECIES', 'C001');
insert into case_study_cols( id, case_study_id, display_col_name, map_col_name)
values(2, 1, 'LENGTH INCHES', 'C002');
insert into case_study_cols( id, case_study_id, display_col_name, map_col_name)
values(3, 1, 'WEIGHT POUNDS', 'C003');
insert into case_study_cols( id, case_study_id, display_col_name, map_col_name)
values(4, 1, 'GENDER', 'C004');

create or replace
procedure build_study_view(p_study_name case_studies.name%type)
as

l_nl varchar2(10) := chr(10);
l_sql varchar2(32000);
l_study_name case_studies.name%type := upper(p_study_name);
begin

l_sql := 'create or replace view '|| l_study_name || '$current ' || l_nl ||
'as ' || l_nl ||
'select ';

for rec in(select csc.display_col_name
, csc.map_col_name
from case_studies cs
join case_study_cols csc
on cs.case_study_id = csc.case_study_id
where cs.name = l_study_name)
loop
l_sql := l_sql || '"'|| rec.map_col_name || '" as "' || rec.display_col_name || '", ';
end loop;

l_sql := rtrim(l_sql,', ');

l_sql := l_sql || ' from case_studies cs ' ||
' join case_study_data csd ' ||
' on cs.case_study_id = csd.case_study_id ' ||
'where cs.name = ''' || l_study_name || '''';

dbms_output.put_line(l_sql);
execute immediate l_sql;

end build_study_view;

begin
build_study_view('FISH');
end;

February 03, 2011 - 4:16 pm UTC

but they do not even need to do that, all of their tables have the same columns - they just needed to add ONE MORE COLUMN...

the study name

and then keep it all in a single schema.

Micheal Kutz, February 04, 2011 - 9:21 am UTC

Tom,
If their "studies" is anything like ours, then they might have a legitimate reason for separate schemas.

Outside of political reasons (and FUD), the only thing I can think of stems from the fact that each study is usually customized.

As this is a future project of mine, (and I'd like to keep everything in one schema), I have some questions regarding some database design.

1) How do I handle different lists of Foreign Key constraints (or even the use of Foreign Keys)?
eg. studyABC would have valid values A,B,C for column_3 while studyXYZ would allow only X,Y,Z for column_3
and studyAlphaBetaGamma would allow any value.

2) What about if studyABCs want to enforce the existence of a number for column_4 while studyXYZ doesn't use that column at all?

3) Like applications, studies come and studies go.
Would modifying the CHECK CONSTRAINTS each time a study is added be wise?

4) with the above requirements, does it still make sense to try and use one schema?

Thanks,

MK

February 04, 2011 - 10:08 am UTC

It would seem that in this case - each study is in fact a separate CHILD table of some parent table - or optional attributes of a wider table.

The parent table would contain all of the common attributes - there must be some else we would we not be able to create a union all view of some attributes...

The child table (or set of optional columns) would hold the study unique attributes.

And each could have their own rules.

But in a single schema.

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Possible data model

More to Explore

PL/SQL demos

PL/SQL docs

Creating a view using execute immediate

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Possible data model

More to Explore

PL/SQL demos

PL/SQL docs