CODEWITHSUNDEEP
sqloracle-databaseviewprocedure
Meteor2
Meteor2

Reputation: 73

a Procedure that excutes an Sql code "Create or replace view.."

I have tis procedure that generates an sql code that create a view. At the end of this proc, we get the variable "finale" that contain the code that creates a view. I want to know how to do to execute the code "finale" everytime I run the procedure? 

set serveroutput on
 declare 
compte number;
DQEntity varchar2(50);
DynSQL varchar2(4000);
finale varchar2(4000);
createview varchar2(4000) :=  'CREATE OR REPLACE FORCE EDITIONABLE VIEW "DV2_OBIDMT"."F_GENERAL_DATA_QLTY2" ("SRC_SYS_ID", "SOFT_RULE_NAME", "ENTITY_NAME", "DATE_", "PASSES", "FAILS") AS 

  ('; 



begin select COUNT(*) into compte
    from DV2_OBIADM.IM_LUT_ENTITY_SOFT_RULES;

for ins in (
select ENTITY_NAME as Q1
    from DV2_OBIADM.IM_LUT_ENTITY_SOFT_RULES  

) loop

DQEntity:= ins.Q1;

DynSQL := DynSQL || 'select distinct 
                            SRC.SRC_SYS_ID,
                            EN.SOFT_RULE_NAME,
                            EN.ENTITY_NAME,
                            (to_date(to_char(SRC.LDTS,'||'DD-MM-YY'||'))) as date_,
                            sum(case when SRC.QLTY_TEST= '||'Pass' ||'then 1 else 0 end) as Passes, 
                            sum(case when SRC.QLTY_TEST= '||'Fail' ||'then 1 else 0 end) as Fails
                            from "DV2_OBIADM".IM_LUT_ENTITY_SOFT_RULES EN, ' ||DQEntity|| '  SRC
                            WHERE EN.ENTITY_NAME = ' || DQEntity || '
                            GROUP BY SRC.SRC_SYS_ID, EN.ENTITY_NAME, EN.SOFT_RULE_NAME,(to_date(to_char(SRC.LDTS,'||'DD-MM-YY'||'))) ' ;
compte := compte -1; 

     if compte >= 1 then 

        DynSQL := DynSQL ||chr(13)||chr(10)|| '  UNION ALL ';
   end if; 


end loop;
finale := createview || DynSQL;
finale := finale|| ');';

dbms_output.put_line(finale);



end;

Thank you for your help!

Upvotes: 1

Views: 41

Answers (2)

YLG
YLG

Reputation: 885

Execute Immediate May help to execute the code in procedure each time. 

  asktom.oracle.com/pls/apex/… 
  https://community.oracle.com/thread/493837
  https://community.oracle.com/thread/378545

This references May help for proper understanding.

Upvotes: 0

Radagast81
Radagast81

Reputation: 3006

You can just add the line 

EXECUTE IMMEDIATE finale;

to your code...

Upvotes: 1

Related Questions