procedures and table scope... PLS-00201

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

    procedures and table scope... PLS-00201

    I'm having trouble writing a simple procedure.

    I'm trying to simply select against a table in a different schema.

    I first wrote a simple procedure that selected against a table in my
    schema, which worked.

    CREATE OR REPLACE PROCEDURE test
    IS
    CURSOR cur_test
    IS
    select *
    from tablename;
    BEGIN
    FOR itemrec IN cur_test
    LOOP
    DBMS_OUTPUT.PUT _LINE('record!' );

    END LOOP;
    END test;

    A simple change to this is to change my select line to:

    select *
    from otherTablespace .tablename;

    This gives me an error saying "otherTablespac e.tablename must be
    declared."

    My first thought was to create a public synonym, so I did.

    CREATE PUBLIC SYNONYM mySyn FOR otherTablespace .tablename

    I also change my select line to read:

    select *
    from mySyn

    PLS-00201: identifier mySyn' must be declared.

    What am I missing? Why can't I "see" the other schemas table in a
    procedure even when explicitly referenced using the
    tablespace.tabl ename?

    I am able to access the table through SQLPlus...?

    Thanks.

    _Am
    Tags: None
    • VC
      #2
      Re: procedures and table scope... PLS-00201

      Hello Andrew,

      A privilege ('select/update/delete') has to be granted directly to the
      procedure executor, not via a role.

      sqlplus can read the table because the access is granted via a role.

      Rgds.

      "Andrew Metcalfe" <chicagoandy@ho tmail.comwrote in message
      news:a2588ec3.0 312120944.ad318 8f@posting.goog le.com...
      I'm having trouble writing a simple procedure.
      >
      I'm trying to simply select against a table in a different schema.
      >
      I first wrote a simple procedure that selected against a table in my
      schema, which worked.
      >
      CREATE OR REPLACE PROCEDURE test
      IS
      CURSOR cur_test
      IS
      select *
      from tablename;
      BEGIN
      FOR itemrec IN cur_test
      LOOP
      DBMS_OUTPUT.PUT _LINE('record!' );
      >
      END LOOP;
      END test;
      >
      A simple change to this is to change my select line to:
      >
      select *
      from otherTablespace .tablename;
      >
      This gives me an error saying "otherTablespac e.tablename must be
      declared."
      >
      My first thought was to create a public synonym, so I did.
      >
      CREATE PUBLIC SYNONYM mySyn FOR otherTablespace .tablename
      >
      I also change my select line to read:
      >
      select *
      from mySyn
      >
      PLS-00201: identifier mySyn' must be declared.
      >
      What am I missing? Why can't I "see" the other schemas table in a
      procedure even when explicitly referenced using the
      tablespace.tabl ename?
      >
      I am able to access the table through SQLPlus...?
      >
      Thanks.
      >
      _Am

        Comment

        • FaheemRao
          #3
          Re: procedures and table scope... PLS-00201

          When are you refering a table in Stored procedure from other schema
          you need to grant the select on that table directly without involving
          any role.



          Faheem



          "


          VC" <boston103@hotm ail.comwrote in message news:<m8oCb.375 468$ao4.1254277 @attbi_s51>...
          Hello Andrew,
          >
          A privilege ('select/update/delete') has to be granted directly to the
          procedure executor, not via a role.
          >
          sqlplus can read the table because the access is granted via a role.
          >
          Rgds.
          >
          "Andrew Metcalfe" <chicagoandy@ho tmail.comwrote in message
          news:a2588ec3.0 312120944.ad318 8f@posting.goog le.com...
          I'm having trouble writing a simple procedure.

          I'm trying to simply select against a table in a different schema.

          I first wrote a simple procedure that selected against a table in my
          schema, which worked.

          CREATE OR REPLACE PROCEDURE test
          IS
          CURSOR cur_test
          IS
          select *
          from tablename;
          BEGIN
          FOR itemrec IN cur_test
          LOOP
          DBMS_OUTPUT.PUT _LINE('record!' );

          END LOOP;
          END test;

          A simple change to this is to change my select line to:

          select *
          from otherTablespace .tablename;

          This gives me an error saying "otherTablespac e.tablename must be
          declared."

          My first thought was to create a public synonym, so I did.

          CREATE PUBLIC SYNONYM mySyn FOR otherTablespace .tablename

          I also change my select line to read:

          select *
          from mySyn

          PLS-00201: identifier mySyn' must be declared.

          What am I missing? Why can't I "see" the other schemas table in a
          procedure even when explicitly referenced using the
          tablespace.tabl ename?

          I am able to access the table through SQLPlus...?

          Thanks.

          _Am

            Comment

            • Andrew Metcalfe
              #4
              Re: procedures and table scope... PLS-00201

              Thanks, Rgds and Faheem you both hit the nail straight on.

              _Am

              faheemrao@yahoo .com (FaheemRao) wrote in message news:<43b58913. 0312121851.99ea 764@posting.goo gle.com>...
              When are you refering a table in Stored procedure from other schema
              you need to grant the select on that table directly without involving
              any role.
              >
              >
              >
              Faheem

                Comment

                Previous template Next
                Working...