Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2008
    Posts
    13

    Unanswered: inner join on a PL/SQL table

    I am getting an "ORA-00942: table or view does not exist" error when trying to do an inner join with a PL/SQL table. Does anyone know what I'm doing wrong?

    Code:
    create or replace
    PROCEDURE BLMROUTING (startDate IN DATE, endDate IN DATE, series IN VARCHAR, 
         p_cursor OUT SYS_REFCURSOR) AS
    
    TYPE tableA IS TABLE OF diag.series_sgnl_rec_asgn.sgnl_id%TYPE;
    TYPE tableB IS TABLE OF diag.PV_SGNL_ID_ASSC%ROWTYPE; 
    
    ResultTableA tableA;   
    ResultTableB tableB; 
    
    BEGIN  
    
    SELECT sgnl_id BULK COLLECT INTO ResultTableA FROM diag.series_sgnl_rec_asgn
    WHERE series_id= series ORDER BY disp_ord_nbr;
    
    SELECT pv_id, sgnl_id BULK COLLECT INTO ResultTableB FROM diag.pv_sgnl_id_assc 
    INNER JOIN ResultTableA ON diag.pv_sgnl_id_assc.sgnl_id = ResultTableA.SGNL_ID;
    
    END BLMROUTING;
    Last edited by Solerous; 11-04-08 at 12:47.

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool Join to Array

    You are "JOIN"ing to a PL/SQL array:

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

  3. #3
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking

    Or just try something like this:
    Code:
    -- etc --
    SELECT pv_id,
           sgnl_id
    BULK COLLECT INTO resulttableb
      FROM diag.pv_sgnl_id_assc a INNER JOIN diag.series_sgnl_rec_asgn b
           ON a.sgnl_id = b.sgnl_id
     WHERE b.series_id = series;
    -- etc --
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  4. #4
    Join Date
    Mar 2007
    Posts
    623
    When you state ResultTableA directly, Oracle treats it as a table name.
    When using collection, you shall cast it using TABLE operator. The documentation link for 10gR2: http://download.oracle.com/docs/cd/B...htm#sthref2435.
    In some Oracle versions, using CAST operator is optional and may be omitted.

    However, I am not sure whether this approach may be used for PL/SQL collections (which you use); if not, then creating and using correspondent SQL collections (CREATE TYPE) is the only way to go.

  5. #5
    Join Date
    Nov 2008
    Posts
    13
    Thanks guys! I ended up using a slightly modified version:

    Code:
    SELECT pv_id, ta.sgnl_id BULK COLLECT INTO ResultTableB
      FROM diag.pv_sgnl_id_assc ta 
        INNER JOIN diag.series_sgnl_rec_asgn tb
           ON ta.sgnl_id = tb.sgnl_id
        inner join diag.blm_acct tc on ta.pv_id = tc.pv_id
    WHERE tb.series_id = series ;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •