I have a procedure that creates a table using the input parameter.

CREATE OR REPLACE PROCEDURE ppp(in_val in varchar2) is BEGIN EXECUTE IMMEDIATE 'create table tmp#ppp as select * from tmp#table_1 where in_v = ''' || in_val || '''' ; END ppp;

in_val is an input parameter, it turns out when you call a procedure, you have to enter the parameter manually every time.

There is another table tmp#table_2 with columns in_val_id - the parameter identifier, in_val_name - ready-made parameters.

Now we need to use the loop to go through the table tmp#table_2 , take each ready-made parameter from there and use it as an input parameter for the procedure.

In short, you need to use the procedure created earlier in the loop, as I understand it. Please help! In PL \ SQL newbie !!!

  • You want to create tables of the same type in a loop. This is a very strange approach and it is not clear what tasks force you to do this. Maybe you should change the approach, and then one table will suffice - Viktorov
  • I just need to pull out and compare two tables using minus for individual attributes. of course, you can pull out all the attributes with one request, then it will be difficult to figure out where the difference is - assyl.d
  • Strange approach. You can pull out of the same table. If the task is not educational, then in the future there may be problems with maintenance. It is better to try to avoid such an architecture - Viktorov

2 answers 2

Immediately you can see that you worked with mssql. Firstly in oracle there are no such temporary houses as in mssql. The timeout in the oracle is created as a regular table (only with a note that it is temporary), which itself keeps track of the session and for which it is indicated at creation when it is deleted when the session is completed or the transaction is completed. As for going through the cycle and calling up the procedure for each iteration, it’s just:

 declare in_val number:=1; begin for i in ( select поле_1 ,поле_2 from схема.таблица where поле = in_val )loop схема.имя_пакета.имя_процедуры (параметр_1=>i.поле_1,параметр_2=>i.поле_2); end loop; end;

Just be careful, if you name the variable as well as the field in the table, then oracle will not say anything, but just the field will compare, with that field whose name matches the variable name.

     for x in (select in_val_id from tmp#table_2) loop ppp(x.in_val_id); end loop;