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

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
SirReadAlotAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
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
 
kretzschmarCommented:
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
 
SirReadAlotAuthor Commented:
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
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
SirReadAlotAuthor Commented:
hang on
0
 
kretzschmarCommented:
?? maybe you should talk with your admin, seems you don't have much oracle experience


tell me, what was unclear?
0
 
SirReadAlotAuthor Commented:
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
 
SirReadAlotAuthor Commented:
thanks guys
0
 
kretzschmarCommented:
thanks for confirm, angel,
disappointed about no grade

meikl ;-)
0
 
bhavanisharansinghCommented:
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
 
Balachandark12Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.