Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2011
    Posts
    3

    Unanswered: Stored Procedure: ora-00942 table or view does not exist

    Hi all,

    I'm new to oracle SQL Developer, currently using Oracle 11g R2. I need to write Stored Procedure to retrieve data and work with Crystal Report. I just started to leaned this a week ago.

    I having a problem when I compile the Stored Procedure, it appears the errors:

    Error(8,14): PL/SQL: SQL Statement ignored
    Error(8,58): PL/SQL: ORA-00942: table or view does not exist


    My Stored Procedure coding:
    create or replace
    PROCEDURE TEST3_SP AS
    PO_Num VARCHAR2(9);

    CURSOR PO_Crs IS

    SELECT erpln.ttdpur400223.t$orno FROM erpln.ttdpur400223;
    BEGIN
    OPEN po_crs;
    LOOP
    FETCH PO_Crs INTO PO_Num;
    EXIT WHEN PO_Crs%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(PO_Num);
    END LOOP;
    CLOSE PO_Crs;
    END TEST3_SP;


    It works fine at SQL Worksheet to the SQL Command (SELECT erpln.ttdpur400223.t$orno FROM erpln.ttdpur400223) and returned values.


    Does anyone encounterd this error before? How to solve it?

    URGENT!!!


    Best Regards,
    Grace

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    If you don't own the table, then its owner has to grant you privileges to access it. For example, he granted SELECT to you.

    However: if he granted it via a role, bad news: privileges acquired via roles won't work in PL/SQL. Ask the owner to grant these privileges directly to you.

  3. #3
    Join Date
    Oct 2011
    Posts
    3
    Hi,

    Thanks, I got to solve it.
    Actually I dont know where and how to access to Grant SELECT & Privileges. Finally I do it at SQL Developer, it works.

    How to Grant Privileges at SQL Developer (version 1.5.5 / Oracle 11g R2):
    1.) Open the Connection
    2.) Click on 'Other Users'
    3.) Select user name
    4.) Right click, edit user
    5.) Go to [Roles], select [Grant All]
    6.) Go to [System Priviledges], select [Grant All]
    7.) Click Apply.

    Hope this can help other people who is facing the same problem.

    Thanks.

    -- Grace --

  4. #4
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by GraceCheong View Post
    Hope this can help other people who is facing the same problem.
    If with "help" you mean allow the creator of that stored procedure to access/change nearly everything in database (so totally break Oracle security concept), then yes, it will do it.

    However, it will "work" only if you connect as SYS/SYSTEM or any user with enough privileges in SQL Developer; it will fail with "ORA-01031: insufficient privileges" when connected e.g. as the user who creates the procedure (unless he has role allowing it; however, it is not wise too).

    Also, it will be useless for those who do not use SQL Developer. Doing this clicking exercise for all users who require this access may be quite annoying too. It would be probably better to include the proper GRANT SQL statement in the script and run it as ERPLN user (you will need to connect as ERPLN to create those tables anyway).

    Generally, you should GRANT only those privileges which are necessary; in your case direct SELECT on the table should satisfy.

  5. #5
    Join Date
    Sep 2011
    Posts
    75
    Hello,

    The error code means that you are using a table which does not exists or you dont have rights on it

Posting Permissions

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