PLSQL ORACLE : Inner join between table variables

Question

I need to create two table-type variables in oracle and make inner join between them. I can't create temporary table in the source database because I dont have privileges. How to create an anonymous plsql block in oracle something relative to this code in SQL server?

DECLARE @TB_PROJETO TABLE
(
    ID INT, 
    NAME NVARCHAR(MAX)
)


DECLARE @TB_CAMERA TABLE
(
    ID INT, 
    NAME NVARCHAR(MAX),
    PROJETOID INT
)

BEGIN

    INSERT INTO @TB_PROJETO
    SELECT [ProjetoId], [Nome] FROM [dbo].[TbProjeto]

    INSERT INTO @TB_CAMERA
    SELECT [CameraId], [Nome],[ProjetoId]  FROM [dbo].[TbCamera]

    SELECT * FROM @TB_PROJETO P INNER JOIN @TB_CAMERA C ON P.ID = C.PROJETOID

END

There is no such thing as a "table variable" in PL/SQL – Dec 13 '18 at 12:52 — , Dec 13 '18 at 12:52

score 0 · Accepted Answer · answered Dec 13 '18 at 13:00

The use of table variables is very common in SQL Server but Oracle doesn't have them because Oracle is pretty good at efficiently joining tables. So in Oracle the equivalent of your T-SQL routine would just be this:

SELECT c.ProjetoId
       , p.Nome as project_nome
       , c.CameraId
       , c.Nome as camera_nome
FROM TbProjeto p
     inner join TbCamera c
ON P.ID = C.PROJETOID

Column aliasing optional but done for clarity

PLSQL ORACLE : Inner join between table variables

1 Answers1