Link to home
Start Free TrialLog in
Avatar of SirReadAlot
SirReadAlot

asked on

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
Avatar of kretzschmar
kretzschmar
Flag of Germany image

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 ;-)
Avatar of SirReadAlot
SirReadAlot

ASKER

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
hang on
?? maybe you should talk with your admin, seems you don't have much oracle experience


tell me, what was unclear?
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;
/
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
thanks guys
thanks for confirm, angel,
disappointed about no grade

meikl ;-)
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.
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?