Creating a Procedure in Oracle to create View

Question

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.

you need to do something like EXECUTE IMMEDIATE 'CREATE or REPLACE .....' whole of the statement goes inside execute immediate. — Sudipta Mondal
– Sudipta Mondal, Commented Jul 3, 2017 at 8:17
Why would you want to do this? What would you gain by writing a procedure rather than just running the CREATE VIEW statement? — Thorsten Kettner
– Thorsten Kettner, Commented Jul 3, 2017 at 8:24
i would rather run a statement as well but finding it difficult to run directly in PL SQL, Execute Immediate is not available in 6i, and simple execute or execute query aint working for me. — Fawad Pasha
– Fawad Pasha, Commented Jul 3, 2017 at 8:35

Frank · Accepted Answer · 2017-07-03 08:59:55Z

1

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;
/

answered Jul 3, 2017 at 8:59

Frank

8812 gold badges9 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Fawad Pasha Over a year ago

The query to create view works perfect and do what is required. when i put statement in variable as 'view_string_variable ('Create or Replace view Borr_Dur As.....' , I get this error, Error(11,22): PLS-00103: Encountered the symbol "(" when expecting one of the following: constant exception <an identifier> <a double-quoted delimited-identifier> table long double ref char time timestamp interval date binary national character nchar

Frank Over a year ago

One of your sintax error(pharentesis needed) is around here "INTERESTINPUTDATE" <= to_dt)/100 yield , i want suggest you to delete double quotes where you don't need , with strings you should put everything between single quotes , but when you have single quotes inside your script , to escape from sintax error you have to put this between double quotes . ex (''10'',''11'',''12'',''13'')

Fawad Pasha Over a year ago

This is exactly what my problem is, i tried with a simple query it worked, everything else is fine except that i have values passed as string within the query while the query itself is going as string. so values such as ('10','11') etc cannot be compiled. I have tried replacing single quote with double, but it doesnt help.

Frank Over a year ago

But why you need quotes for numbers? are they declared as varchar ? if not delete quotes from there

Fawad Pasha Over a year ago

issue resolved, i actually had to use '' i.e. single quote pressed twice and i was using " double quotes which were creating problem. Thanks for your help frank. really appreciate it.

Collectives™ on Stack Overflow

Creating a Procedure in Oracle to create View

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related