Q
Problem solve Get help with specific problems with your technologies, process and projects.

Implementing a view that requires variable instance input

I want to define a parameterized view as a view that requires variable instance input on invocation. How can I implement this in Oracle using SQL and PL/SQL syntax? Can you give me an example please?
CREATE OR REPLACE PROCEDURE employee_view(p_emp_id IN NUMBER, p_mgr_ID IN 
   NUMBER) IS
    V_BASE_VIEW  VARCHAR2(400) := 
'CREATE OR REPLACE FORCE VIEW INDIVIDUAL_VIEW AS SELECT PAYROLL_DATE, ' ||' 
PAY_AMOUNT FROM ALL_EMPLOYEE_RECORDS ';
BEGIN
  V_BASE_VIEW := V_BASE_VIEW || ' WHERE EMPLOYEE_ID = '||  p_emp_id ;


  EXECUTE IMMEDIATE V_BASE_VIEW;
        

END;

Dig Deeper on Using Oracle PL-SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close