Re: HELP! VERY New to Oracle

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sun, 20 Jun 1999 18:12:07 GMT
Message-ID: <37732c7d.3691878@newshost.us.oracle.com>

A copy of this was sent to MADEIRA SALLY <softsense_at_home.com> (if that email address didn't require changing) On Sun, 20 Jun 1999 16:35:37 GMT, you wrote:

>I am very new to PL/SQL and Oracle in general. I am taking all that
>was done in MS SQL 7.0 and now using oracle. My precudures are
>very complex taking multiple tables and creating SQL statements
>then needing to taking the result set from that resultset and
>creating another SQL Statement. In MS SQL 7.0 the INTO was great
>becuase I could then reference a temp table and use it in another
>SQL Statement!
>
>What do I need to do to this Procedure to select from the
>SQL Statement. I can not reference the INTO variable!.
>

select ... INTO ... in Oracle is not the same as select into in sqlserver.

select .. into .. from table .. in Oracle selects a single row into a local variable -- not into a temporary table.

Your procedure in Oracle would be:

>**********************************************
>PROCEDURE R10_ParticipationListing
>(BeginDate IN DATE:='1-JAN-99',
> EndDate IN DATE:='31-APR-99',
> 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" */
>
>
>BEGIN
>
>Open finalRS_cv for

 SELECT 	Comdyear.OfficeNum,


>	Comdyear.CliNum,


>	SUM(Get_LoanBalance(Comdyear.LoanBal,'EOM', Comdlon2.PTType,


>Comd02a.LoanType)) EOMBalance,


>        SUM(Get_LoanBalance(Comdyear.LoanBal,'Gross', Comdlon2.PTType,


>Comd02a.LoanType)) GrossBalance,


>	Comd02a.LoanName


>INTO    Proc_Rec


>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 BeginDate AND EndDate)

>GROUP BY Comdyear.OfficeNum, Comd02a.LoanName, Comdyear.CliNum;
>
>
>END;
>

In 99.999% of the cases I have seen, the use of temp tables in sqlserver was to avoid optimizer issues with 3 or more tables in a query or because you hit the 16 table limit. Neither of these will be an issue -- having many tables in a join and have lots more then 16 referenced objects in a query is not a problem. You will want to recode your multi step queries into a single query in Oracle. Inline views (the ability to select from a select) make this pretty easy to accomplish and do the same thing as a temp table more or less. Lets say in sqlserver you had three tables:

DEPT, EMP, and PROJECTS

There is a 1:m relationship between DEPT and EMP. there is a 1:m relationship between DEPT and PROJECTS.

You need to write a query that puts together the deptno, dept_name, count of employees and count of projects -- you would do this for a given deptno. In sqlserver you might have:

begin
select count(*) as empcnt into #t1 from emp where deptno = 10; select count(*) as projcnt into #t2 from projects where deptno = 10;

select deptno, dept_name, empcnt, projcnt from deptno, #t1, #t2 where deptno = 10;
end;

In Oracle you would simply code:

is

   SOME_VARIABLE number := 10;
begin

   open x for

      select d.deptno, d.dept_name, e.empcnt, p.projcnt
        from dept d,
        (select count(*) empcnt from emp where deptno = SOME_VARIABLE ) e,
        (select count(*) projcnt from projects where deptno = SOME_VARIABLE )p
       where d.deptno = SOME_VARIABLE;

end;

>I get Error:
>Error at Line 43, Column 9
> PLS-00356: "Proc_Rec' Must be a table to which the user has access
>**********************************
>
>Again, I am very new and (three days!)
>
>Please Help

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Fine Grained Access Control", added June 8'th  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sun Jun 20 1999 - 13:12:07 CDT