Creating Package Using Views 1

[ScottXJ]

Hello,

I am fairly new to the process of creating packages in Oracle and would like to know if it is possible to create a package which is based on several views. The views that are currently being used are not very complex but do contain some UNION statements.

An example of how to create a package using views would be appreciated.

Thanks,
Scott

 

[LKBrwnDBA]

What do you mean by "create a package using views", in any PL/SQL code you can either refer to tables and views.


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[ScottXJ]

I apologize, I should have been more specific. I would like to replace the views with a package. I was wondering if it is a simple matter of inserting the SELECT statements that are currently being used in the view into the package structure. If anyone could post an example of a package which utilizes a SELECT statement or series of SELECT statements, it would be appreciated.

 

[LKBrwnDBA]

I would like to replace the views with a package.
...inserting the SELECT statements that are currently being used in the view into the package structure

Views are queries and packages are "procedures".

Procedures can return the result of a query as a reference "cursor", here is a sample "pseudo code" on how to do it in a procedure (which is same as in package):

CREATE OR REPLACE PROCEDURE MYVIEW
(IN_CSR OUT REFCSRTYPE)
IS
BEGIN
  OPEN IN_CSR AS 'SELECT * FROM MYTABLE';
END;

PS: REFCSRTYPE in this case would be a user define "REF CURSOR" type, in a package you could define in package header.







----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb