Re: Complex Procedure needs View!

You cannot create a view inside a procedure in pl/sql. You have to allocate your view definition to a string and pass that string to dbms_sql, which will create your view.

e.g v_string := 'Create view king kong as select ename from emp';

See documentation on dbms_sql on usage.

Mark

MADEIRA SALLY wrote in message <3770E284.DDB4A6D5_at_home.com>...
>I have some very complex SQL statements within a procedures that
>will be used to populate a Crystal report. I have had some great
>input but here is another guestion about VIEWS.
>
>Becuase my SQL Clause are very complex, I would like to
>do a CREATE VIEW .... FROM Select.... and then use
>that as my result to create another resultset...
>Then DROP VIEW ....
>
>Problem, I am doing this in a procedure and I keep getting
>an error on the CREATE... Am I setting up my procedure wrong.
>
>See Below:
>***************************************************
>PROCEDURE ABLMACC1.R10_ParticipationListing
>(AsOfDate IN DATE,
> FinalRS_cv IN OUT Cursor_Types.FinalRS_Cur)
>
>IS
>
>/*Get All Participation Records of LoanType = "PT" and PartType <> "IL
>(Interoffice Particpations) */
>/*Get Loan Balances where LoanType not in "LC", "BA", "PT" */
>
>CREATE VIEW Proc_vw AS
>
>SELECT Comdyear.OfficeNum,
> Comdyear.CliNum,
> Comdyear.LoanNum,
> SUM(Get_NonInteroffice_LoanBalance(Comdyear.LoanBal, Comdlon2.PTType,
>Comd02a.LoanType)) EOMBalance,
> SUM(Get_NonParticipant_LoanBalance(Comdyear.LoanBal, Comd02a.LoanType))
>GrossBalance,
> Comd02a.LoanName
>
>FROM Comdyear,Comd02a,Comdlon2
>
>WHERE (Comdyear.LoanNum=Comd02a.LoanNum
> AND Comdyear.Clinum=Comd02a.Clinum
> AND Comdyear.OfficeNum=Comd02a.OfficeNum)
> AND (Comd02a.LoanNum = Comdlon2.LoanNum
> AND Comd02a.CliNum=Comdlon2.CliNum
> AND Comd02a.OfficeNum=Comdlon2.OfficeNum)
> AND (Comdyear.Date_R BETWEEN
>TO_DATE('01'||TO_CHAR(AsOfDate,'MMYYYY'),'DDMMYYYY') AND
>LAST_DAY(AsOfDAte))
>GROUP BY Comdyear.OfficeNum, Comd02a.LoanName, Comdyear.CliNum,
>Comdyear.LoanNum
>
>GROUP BY S.OfficeNum, S.LoanName;
>
>BEGIN
>
> OPEN FinalRS_cv FOR
> SELECT * FROM Proc_vw WHERE ........
>END;
>
>*********************************************
>
>I simplified the procedure bu there would actually be two more SELECT
>statements
>that are very complex.... To bad I just couldn't reference a cursor
>within a
>select statement!!!!
>
>Thanks in advance
>Sally
Received on Wed Jun 23 1999 - 09:12:28 CDT