Strange behavious - inconsistencies between SQL in SQL*PLUS & SQL embedded in PL/SQL

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Ethel Aardvark
    #1

    Strange behavious - inconsistencies between SQL in SQL*PLUS & SQL embedded in PL/SQL

    I have a query which runs fine in SQL*Plus but which will not compile
    into a packaged procedure (claiming that the table can not be found):

    SELECT DISTINCT Folder_ID
    INTO l_RootID -- remove this line in SQL*Plus context
    FROM ifssys.ifs_fold er_items
    WHERE Folder_Name = 'Root Folder'
    AND TYPE = 'FOLDER';

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

    (If I get rid of the "INTO" by using a cursor - making the code
    identical - I still get the same thing.)

    I have tried "AUTHID CURRENT_USER" and "AUTHID DEFINER" in the package
    specification but these do not make any difference (as expected - it
    is not a run-time error).
    I have tried granting "SELECT" on all IFSSYS tables and views, but
    this makes no difference (as expected).

    I am starting to pull my hair out, and I have a horrible feeling I am
    missing something obvious, but I CAN'T SEE IT!

    I am running iAS 9.0.2 and DB 9.0.1 (I am using the DB inside iAS - I
    know it is less than ideal, but I am working on a tight hardware
    budget and this is a development server).

    Please can someone give me some pointers or a solution!

    Many thanks in advance,


    ETA
    Tags: None
    • Jusung Yang
      #2
      Re: Strange behavious - inconsistencies between SQL in SQL*PLUS & SQL embedded in PL/SQL

      bigjobbies@hotm ail.com (Ethel Aardvark) wrote in message news:<1a8fec49. 0307020209.6606 3c4e@posting.go ogle.com>...
      I have a query which runs fine in SQL*Plus but which will not compile
      into a packaged procedure (claiming that the table can not be found):
      >
      SELECT DISTINCT Folder_ID
      INTO l_RootID -- remove this line in SQL*Plus context
      FROM ifssys.ifs_fold er_items
      WHERE Folder_Name = 'Root Folder'
      AND TYPE = 'FOLDER';
      >
      -PL/SQL: ORA-00942: table or view does not exist
      >
      (If I get rid of the "INTO" by using a cursor - making the code
      identical - I still get the same thing.)
      >
      I have tried "AUTHID CURRENT_USER" and "AUTHID DEFINER" in the package
      specification but these do not make any difference (as expected - it
      is not a run-time error).
      I have tried granting "SELECT" on all IFSSYS tables and views, but
      this makes no difference (as expected).
      >
      I am starting to pull my hair out, and I have a horrible feeling I am
      missing something obvious, but I CAN'T SEE IT!
      >
      I am running iAS 9.0.2 and DB 9.0.1 (I am using the DB inside iAS - I
      know it is less than ideal, but I am working on a tight hardware
      budget and this is a development server).
      >
      Please can someone give me some pointers or a solution!
      >
      Many thanks in advance,
      >
      >
      ETA

      Is ifs_folder_item s a public synonym by any chances? If it is, then
      there is no reasons to attach a schema name to it.

      - Jusung Yang

        Comment

        • Chris O
          #3
          Re: Strange behavious - inconsistencies between SQL in SQL*PLUS &amp; SQL embedded in PL/SQL

          "Ethel Aardvark" <bigjobbies@hot mail.comwrote in message
          news:1a8fec49.0 307020209.66063 c4e@posting.goo gle.com...
          I have a query which runs fine in SQL*Plus but which will not compile
          into a packaged procedure (claiming that the table can not be found):
          >
          SELECT DISTINCT Folder_ID
          INTO l_RootID -- remove this line in SQL*Plus context
          FROM ifssys.ifs_fold er_items
          WHERE Folder_Name = 'Root Folder'
          AND TYPE = 'FOLDER';
          >
          -PL/SQL: ORA-00942: table or view does not exist
          >
          (If I get rid of the "INTO" by using a cursor - making the code
          identical - I still get the same thing.)
          >
          I have tried "AUTHID CURRENT_USER" and "AUTHID DEFINER" in the package
          specification but these do not make any difference (as expected - it
          is not a run-time error).
          I have tried granting "SELECT" on all IFSSYS tables and views, but
          this makes no difference (as expected).
          >
          I am starting to pull my hair out, and I have a horrible feeling I am
          missing something obvious, but I CAN'T SEE IT!
          >
          I am running iAS 9.0.2 and DB 9.0.1 (I am using the DB inside iAS - I
          know it is less than ideal, but I am working on a tight hardware
          budget and this is a development server).
          >
          Please can someone give me some pointers or a solution!
          >
          Many thanks in advance,
          >
          >
          ETA
          I won't comment on whether it is obvious as we have all been caught with
          this one.

          You must rember that when running direct SQL queries [say in SQL*Plus] the
          Oracle server
          will use the priviliges assigned to your session roles, however, when the
          quesies are inside a
          package the roles will not be used. You will need to grant SELECT on
          IFSSYS.IFS_FOLD ER_ITEMS
          to the user who is compiling the package.

          Cheers


            Comment

            Previous template Next
            Working...