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:

course (id, name, date, master)
grade (id, course_id, course_name, grade)
master (id, stu_id, grade)
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.

Google "dynamic SQL". You may want to consider creating aliases instead of views too. — mustaccio, Dec 24 '14 at 17:40
possible duplicate of [How to write dynamic SQL in Oracle?](http://stackoverflow.com/questions/27644835/how-to-write-dynamic-sql-in-oracle) — user272735, Dec 25 '14 at 07:43
This question is the original of the question indicated in the Close vote. That one is close as a duplicate so please don't vote this as a duplicate too. — APC, Dec 25 '14 at 08:47
@OmidRashin - please do not post duplicate questions. It is bad manners. SO works because we have single questions with dedicated answers; duplicate answers dilute that and just waste people's time. Please remember that SO answers come from volunteers. Also, getting help with your homework is not an emergency (even if you think it is). — APC, Dec 25 '14 at 08:50
Ok, thank you . I am new with stack overflow and dont know this. — Omid Rastin, Dec 25 '14 at 08:53

APC · Accepted Answer · 2014-12-25T08:41:58.073

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;

How to write a procedure using variable?

1 Answers1