Results 1 to 5 of 5

Thread: Odd problem...

  1. #1
    Join Date
    Feb 2004
    Location
    Charlotte, NC
    Posts
    79

    Question Unanswered: Odd problem...

    Hi

    I am trying to write stored procedures in packages, and have been successful with 16 so far. I am trying to write a new package with 2 procedures, and I keep getting the error message:

    PL/SQL: ORA-00942: table or view does not exist

    With both procedures this happens on the line with the where clause.

    If I copy the query and replace the two date parameters with real dates, then the SQL will run fine in TOAD or SQL*Plus and provide valid data.

    Does anyone know why I might be getting this problem? I have looked at the code surrounding the SQL to see if there are any errors, but it all seems fine.

    Here is a shortened version of the procedures:



    CREATE OR REPLACE package body hma_33
    as

    procedure hma_33_ (st_date In date, end_date In date, rc_results In out rc)
    is
    begin

    open rc_results for
    select
    sum(tot_qt),
    sum(thirty_dpd),
    ...
    sum(DECODE(rtrim(region), 'West', ninety_plus_dpd)),
    sum(DECODE(rtrim(region), 'West', fcls))
    From pii.mrt_state_delinq_fcls_mth, pii.mrt_state_region_xref
    where pii.mrt_state_delinq_fcls_mth.st_nm = pii.mrt_state_region_xref.st_nm
    and (asof_dt >= st_date) and (asof_dt < end_date);
    end;

    procedure hma_33_st (st_date In date, end_date In date, rc_results In out rc)
    is
    begin

    open rc_results for
    select
    sum(DECODE(st_nm, 'AK', tot_qt)),
    sum(DECODE(st_nm, 'AK', thirty_dpd)),
    ...
    sum(DECODE(st_nm, 'WY', ninety_plus_dpd)),
    sum(DECODE(st_nm, 'WY', fcls))
    From pii.mrt_state_delinq_fcls_mth
    where (asof_dt >= st_date) and (asof_dt < end_date);
    end;

    end;
    /



    I'd appreciate any help or suggestions.

    Thanks

    Michael

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Privileges acquired via a ROLE are used when in SQL*Plus.
    Privileges acquired via a ROLE are NOT used within PL/SQL procedures.
    GRANT SELECT ON <TABLE_NAME> TO <PACKAGE_OWNER>
    must be explicitly done to SELECT from a table owned by another user.

  3. #3
    Join Date
    Feb 2004
    Location
    Charlotte, NC
    Posts
    79
    I looked at the privileges, and on the new table I am granted select privilege through a role, so I guess this is the cause of the problem. Unfortunately I have to go through a time consuming process to get the DBA to make changes so I can't test it, but I will assume that this is the correct answer.

    Thanks for your help!

    Michael

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    If you can log into the DB as two different users/schemas,
    this can be easily tested & verified. I assure you this is the
    most likely cause of the error(s) you are reporting.

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    IS "PII" THE SCHEMA OWNER OR THE TABLE ALIAS??

    add a table alias to your statements
    like:
    Code:
    From 
    pii.mrt_state_delinq_fcls_mth fcls, 
    pii.mrt_state_region_xref region
    also, use your column alias when referring to "asofdate"

    so: fcls.asofdate
    Last edited by The_Duck; 03-11-04 at 18:32.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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