CODEWITHSUNDEEP
oracle-databaseplsqloracle11gdynamic-sql
bhoshan moheeputh
bhoshan moheeputh

Reputation: 23

PLSQL - FORALL in immediate execute

I want to use execute immediate for the forall syntax as i want to use the variable 'schemasname' instead of 'dcbhmoh1'. Thanks

DECLARE
    schemasname varchar2(25) := 'dcbhmoh1';
    datet char(6) := '102024';

    TYPE anames IS table of dcbhmoh1.F01131M%ROWTYPE;
ar anames;

BEGIN

       EXECUTE IMMEDIATE 'SELECT * FROM ' || schemasname || '.F01131M WHERE ZMDTI >= ' || datet BULK COLLECT INTO ar;
dbms_output.put_line(ar.count);
    IF ar.Count > 0 THEN
       EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || schemasname || '.F01131M';

       FORALL i in ar.first .. ar.last INSERT INTO dcbhmoh1.F01131M VALUES ar(i);

   END IF;
 END;

Upvotes: 2

Views: 376

Answers (1)

J. Chomel
J. Chomel

Reputation: 8395

You could try to define schema for your work session with 

EXECUTE IMMEDIATE 'Alter session set current_schema='|| schemasname ;

Like this:

DECLARE
    schemasname varchar2(25) := 'dcbhmoh1';
    datet char(6) := '102024';

    TYPE anames IS table of dcbhmoh1.F01131M%ROWTYPE;
ar anames;

BEGIN
  -- set schema
  EXECUTE IMMEDIATE 'Alter session set current_schema='|| schemasname ;
  -- do your work  
  EXECUTE IMMEDIATE 'SELECT * FROM F01131M WHERE ZMDTI >= ' || datet BULK COLLECT INTO ar;
  dbms_output.put_line(ar.count);
  IF ar.Count > 0 THEN
    EXECUTE IMMEDIATE 'TRUNCATE TABLE F01131M';    
    FORALL i in ar.first .. ar.last INSERT INTO F01131M VALUES ar(i);    
  END IF;
END;

(not sure about the complete syntax of the FORALL though).

Upvotes: 1

Related Questions