CODEWITHSUNDEEP
sqloracle-databasestored-procedures
Fawad Pasha
Fawad Pasha

Reputation: 43

Creating a Procedure in Oracle to create View

I am trying to create a procedure in oracle, which upon calling from PL SQL block will create a view in database from which i will query data for a report. I am new to Oracle and need help with this code. 

CREATE OR REPLACE PROCEDURE CREATE_VIEW 
(
  TO_DT IN Date 
) AS 
BEGIN
  Create or Replace view BORR_DUR As
  SELECT e."Deal_No", (Select "DeskName" From MM_S_DESK Where e."DeskCode" = MM_S_DESK."DeskCode") Facility, e."Remarks" Counterparty, 
m."MaturityDate", m."PriRedem" Principal, 

(select MAX("INTEREST_RATE") from MM_BOR_PLA_PAR d 
WHERE e."Deal_No" = d."DEAL_NO" and "INTERESTINPUTDATE" <= to_dt)/100 yield, (m."MaturityDate" - To_date(to_dt,'dd/mm/yyyy')) Days_to_Mat,
Round(((m."MaturityDate" - To_date(to_dt,'dd/mm/yyyy'))/365)/ (1+((select MAX("INTEREST_RATE") from MM_BOR_PLA_PAR d 
WHERE e."Deal_No" = d."DEAL_NO" and "INTERESTINPUTDATE" <= to_dt)/100)),4) MDURATION


FROM MM_T_BORROWING e, MM_T_BORROWING_PM_DETAIL m
Where e."DeskCode" in ('10','11','12','13') and e."Value_Date" <= to_dt and e."Maturity_Date" > to_dt and e."Status" not in ('C', 'D', 'Z', '0','X') 
and e."Deal_No" = m."Deal_No" and "PriRedem" > '0' and m."MaturityDate" > to_dt;

END CREATE_VIEW;

On Compilation, i get PLS00103 error which says

encountered the symbol "Create" when expecting one of the following....

Any help in solving this issue will be greatly appreciated.

Upvotes: 3

Views: 8654

Answers (1)

Frank
Frank

Reputation: 881

When you want execute SQL statement which is dynamic you have to use EXECUTE IMMEDIATE statement

First , you don't need double quotes in fields name , after that you can try the query of the view and check if it runs without errors .

Put the create replace view... statement in an variable and in your procedure call :

BEGIN
   EXECUTE IMMEDIATE view_string_variable ;
END;
/

Upvotes: 1

Related Questions