1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

talbe in procedure

Discussion in 'SQL PL/SQL' started by ssraman, Jan 22, 2013.

  1. ssraman

    ssraman Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    I have the Table name Location and synonym named Location in my DB. I am trying to create the proc where I am tryting to call the table.
    But its not working.


    Example:
    CREATE PROCEDURE TESTPROC
    AS
    BEGIN
    DBMS_OUTPUT.PUT_LINE('testing');
    select count(*) from LOCATION;
    END;


    Compile error:
    Error(5,22): PL/SQL: ORA-00942: table or view does not exist
     
  2. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Have you tried the same query without procedure and does it returns any output or error?
     
  3. ssraman

    ssraman Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Yes I tried and it works fine
     
  4. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Try store the count result into some variable as following.

    Code (SQL):
    CREATE PROCEDURE TESTPROC
    AS
    l_count pls_integer;
    BEGIN
    DBMS_OUTPUT.PUT_LINE('testing');
    SELECT COUNT(*)  INTO l_count FROM LOCATION;
    DBMS_OUTPUT.PUT_LINE(' Number of rows '||l_count);
    END;
     
    Bharat likes this.
  5. a_kamalraj

    a_kamalraj Forum Advisor

    Messages:
    121
    Likes Received:
    23
    Trophy Points:
    280
    Hi Raman,

    I have one clarification on ur question.

    1. Did the synonym is created on the table Location?.
    Just let me know on that i vl help u on this.

    Cheers
    Kamal(kamal.love@gmail.com)
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    The user your connected as doesn't own the LOCATION table, I suspect, and doesn't have direct grants on it, thus PL/SQL can't find it using authid owner compilation (the default). You will need to have direct grants on the object to create a procedure using it. Whoever owns the LOCATION table needs to grant your user at least select on that table before this will work;

    Code (SQL):
    SQL> CREATE OR REPLACE PROCEDURE TESTPROC
      2  AS
      3  BEGIN
      4  DBMS_OUTPUT.PUT_LINE('testing');
      5  SELECT COUNT(*) FROM LOCATION;
      6  END;
      7  /
     
    Warning: PROCEDURE created WITH compilation errors.
     
    SQL> SHOW errors

    Errors FOR PROCEDURE TESTPROC:
     
    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    5/1      PL/SQL: SQL Statement ignored
    5/22     PL/SQL: ORA-00942: TABLE OR VIEW does NOT exist

    SQL> CONNECT / AS sysdba
    Connected.
    SQL> GRANT ALL ON location TO bing;
     
    GRANT succeeded.
     
    SQL> CONNECT bing/bong
    Connected.
    SQL> CREATE OR REPLACE PROCEDURE TESTPROC
      2  AS
      3  BEGIN
      4  DBMS_OUTPUT.PUT_LINE('testing');
      5  SELECT COUNT(*) FROM LOCATION;
      6  END;
      7  /
     
    Warning: PROCEDURE created WITH compilation errors.
     
    SQL> SHOW errors

    Errors FOR PROCEDURE TESTPROC:
     
    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    5/1      PLS-00428: an INTO clause IS expected IN this SELECT statement

    SQL> CREATE OR REPLACE PROCEDURE TESTPROC
      2  AS
      3     loc_Ct NUMBER;
      4  BEGIN
      5  DBMS_OUTPUT.PUT_LINE('testing');
      6  SELECT COUNT(*) INTO loc_ct FROM LOCATION;
      7  dbms_output.put_line(loc_ct);
      8  END;
      7  /
     
    PROCEDURE created.
     
    SQL>
     
    Note you also will have an errror because of the select once you get direct grants on LOCATION.
     
    Bharat likes this.