How to write a procedure using variable?

Question

I have two schema in my Oracle database. The first schema is "STUDENT" schema, that has 4 tables:

1. course (id, name, date, master)
2. grade (id, course_id, course_name, grade)
3. master (id, stu_id, grade)
4. detail (id, stu_id, master_id,date)

Also I have another schema that name is "ALLDATA", I have a table in this schema named "table_schema" the structure of this table is like below.(this table only have some of the STUDENT schema s table s name.)

id             table_name        
----------       ----------
 1                 course
 2                 grade
 3                 master

I have another table named view_schema:

enter code here
    id            schema_name        
----------       ----------
 1                 STUDENT

I want to write a procedure in ALLDATA schema that loop on "table_schema" and create view for the tables name from STUDENT schema. Actually I want my procedure to do something like below:

create or replace PROCEDURE  VIEWINSTRACTOR                                                                              
 is
 begin
 for j in (select SCHEMA_NAME from ALLDATA.VIEW_SCHEMA)
 loop
  for i in (select table_name from ALLDATA.VIEW_TABLE)
     loop
      execute immediate 'create or replace view ALLDATA.'|| i.table_name 
      ||' as select * from '|| j.SCHEMA_NAME.i.table_name;
  end loop;
end loop;

end;

but I got errors.

Answer 1

When we want to drive a series of statements from varying input data we need to use dynamic SQL. We also need to use dynamic SQL to execute DDL in a stored procedure.

I have corrected your syntax when referencing the cursor's projection. I also suggest you use the CREATE OR REPLACE VIEW syntax, as it's more robust.

create or replace procedure createViewTables
 is
begin
 for i in (select table_name from ALLDATA.table_schema)
 loop
   execute immediate 'create or replace view ALLDATA.'|| i.table_name 
           ||' as select * from STUDENT.'|| i.table_name;
 end loop;
end;

Note that this procedure will happily attempt to create the views. However, the procedure will only succeed if STUDENT has granted privileges on its tables to ALLDATA. Otherwise it will hurl ORA-00942: table or view does not exist.

---

"Actually I want my procedure to do something like below:"

Bonus answer, even though there's no more points on offer, because it's Christmas (at least in this timezone) :)

Dynamic SQL is harder than static SQL because we need to get our actual statement correct , then we need to disassemble into boilerplate and variables. This is even trickier for people who aren't familiar with SQL because they lack sufficient understanding to spot syntax errors.

In your case the problem is how you've concatenated the schema name with the table name. The dot between them needs to be in the actual SQL statement, so it must be boilerplate text. What you need is this statement:

execute immediate 'create or replace view ALLDATA.'|| i.table_name 
  ||' as select * from '|| j.SCHEMA_NAME ||'.'|| i.table_name;