[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

ORA-06550: line 1, column 7: PLS-00201: identifier 'GET_ALL_USER_SP' must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignored

Posted on 2006-05-16
10
Medium Priority
?
28,039 Views
Last Modified: 2012-12-08
Hi Experts,

I have the error above.

Here is the  sp

CREATE OR REPLACE PROCEDURE get_All_User_sp(crs OUT types_pck.refCursor) AS
BEGIN
  OPEN crs FOR
    SELECT user_id AS "User ID",
             signatoryname AS "Signatory Name",
                email AS "email",
                phone AS "Phone",
                fax AS "Fax",
                jobtitle AS "Job Title",
                location AS "Location"
      FROM users_vw
     
END;
/

and i called it like this

public DataSet dsUsers()
          {

               comm = new OracleCommand("get_All_User_sp", conn);
               comm.CommandType = CommandType.StoredProcedure;
               comm.Parameters.Add("crs", OracleType.Cursor);
             comm.Parameters["crs"].Direction = ParameterDirection.Output;
               da = new OracleDataAdapter(comm);
               ds = new DataSet();
               conn.Open();
               da.Fill(ds);
               conn.Close();
               return ds;

          }

any ideas.

thanks
0
Comment
Question by:SirReadAlot
10 Comments
 
LVL 27

Expert Comment

by:kretzschmar
ID: 16689575
depending on who owns the procedeure and
who accesses this procedure it might be unaccessible for the accessor

create a public synonym or prefix the owner-schema-name in the call
(ensure that the privileges are set correct)

meikl ;-)
0
 

Author Comment

by:SirReadAlot
ID: 16689599
create a public synonym or prefix the owner-schema-name in the call
(ensure that the privileges are set correct)


how go u mean

thanks
0
 

Author Comment

by:SirReadAlot
ID: 16689615
hang on
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 27

Expert Comment

by:kretzschmar
ID: 16689640
?? maybe you should talk with your admin, seems you don't have much oracle experience


tell me, what was unclear?
0
 

Author Comment

by:SirReadAlot
ID: 16689680
i have full access

is anything wromg with the sp
CREATE OR REPLACE PROCEDURE get_All_User_sp(crs OUT types_pck.refCursor) AS
BEGIN
  OPEN crs FOR
    SELECT user_id AS "User ID",
               signatoryname AS "Signatory Name",
                  email AS "email",
                  phone AS "Phone",
                  fax AS "Fax",
                  jobtitle AS "Job Title",
                  location AS "Location"
      FROM users_vw
      
END;
/
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 16689689
as I posted in the other q:

if you don't connect to the database with the same user in the application as when you create the procedure, this error will occur.
you can resolve this by doing the following 2 steps (as pointed out by kretzschmar):
1) create a (public) synonym to the procedure
 => this will make that you don't need to specify the procedure owner when calling the procedure

2) grant permissions to the users on the procedure
 => this will ensure the procedure can not only be seen, but also be executed
 
0
 

Author Comment

by:SirReadAlot
ID: 16689779
thanks guys
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 16696606
thanks for confirm, angel,
disappointed about no grade

meikl ;-)
0
 

Expert Comment

by:bhavanisharansingh
ID: 22666819
I am also getting a similar error.
I am able to execute a different procedure in same schema and same package.
But not able to execute the procedure which is returning a REF Cursor.
Please suggest if anybody has any idea.
0
 

Expert Comment

by:Balachandark12
ID: 38672450
I am also getting same problem
java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00201: identifier 'CMDB_REC' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

We are using ref cursor as output to java code
I have checked with DBA that's all rights are granted properly but still exception exist
If we could tested with SOAP UI or local invocation that's works fine
But if I call via java application things going weird?
The schema also same I have checked ,  am getting this issue in production expecting a solution quickly from experts?
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses
Course of the Month19 days, 22 hours left to enroll

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question