CODEWITHSUNDEEP
asp.netoracle-databaseplsql
Khurram Zulfiqar Ali
Khurram Zulfiqar Ali

Reputation: 241

ERROR at line : PL/SQL: SQL Statement ignored. while creating/replacing package body

I am using Visual Studio 2010 asp.net 4.5 Oracle 10.2.0.1.

This is my repository code for checking user credentials:

 using (OracleConnection oracleConnection = new BaseRepository().Connection)
                {
                    oracleConnection.Open();
                    OracleCommand command = new OracleCommand("PACKAGE_ACCOUNT.USP_GET_USER_BY_CREDENTIALS", oracleConnection);
                    command.CommandType = System.Data.CommandType.StoredProcedure;

                    command.Parameters.Add("SP_CURSOR", OracleDbType.RefCursor, System.Data.ParameterDirection.Output);
                    command.Parameters.Add("SP_LOGIN_NAME", OracleDbType.Varchar2, 50, loginName, System.Data.ParameterDirection.Input);
                    command.Parameters.Add("SP_LOGIN_PASSWORD", OracleDbType.Varchar2, 50, Security.EncryptText(loginPassword), System.Data.ParameterDirection.Input);

                    Mapper.CreateMap<IDataReader, ApplicationUser>();
                    dataReader = command.ExecuteReader(); // exception arises here.

                    List<ApplicationUser> lstUsers = Mapper.Map<List<ApplicationUser>>(dataReader);
                    return lstUsers.FirstOrDefault();
                }

this is package specification:

CREATE OR REPLACE PACKAGE PACKAGE_ACCOUNT
AS
TYPE T_CURSOR IS REF CURSOR;
PROCEDURE USP_GET_USER_BY_CREDENTIALS(SP_CURSOR OUT T_CURSOR, SP_LOGIN_NAME IN VARCHAR2, SP_LOGIN_PASSWORD IN VARCHAR2);
END PACKAGE_ACCOUNT;
/

this is my package body:

CREATE OR REPLACE PACKAGE BODY PACKAGE_ACCOUNT
AS 
PROCEDURE USP_GET_USER_BY_CREDENTIALS(SP_CURSOR OUT T_CURSOR, SP_LOGIN_NAME IN VARCHAR2, SP_LOGIN_PASSWORD IN VARCHAR2)
IS 
BEGIN 
OPEN SP_CURSOR FOR 
SELECT "ApplicationUser".*, ora_rowscn as TimeStamp 
FROM "ApplicationUser" 
WHERE "LoginName" = SP_LOGIN_NAME
AND "LoginPassword" = SP_LOGIN_PASSWORD
AND "IsDeleted" = 'N'; 
END USP_GET_USER_BY_CREDENTIALS; 
END PACKAGE_ACCOUNT;
/

Exception is :

 ORA-04063: package body "OPTIMUS_USER.PACKAGE_ACCOUNT" has errors
ORA-06508: PL/SQL: could not find program unit being called: "OPTIMUS_USER.PACKAGE_ACCOUNT"
ORA-06512: at line 1

I have no idea where the problem is.

While running the package body script in oracle home page. It gives error:

ERROR at line 5: PL/SQL: SQL Statement ignored.

Upvotes: 0

Views: 236

Answers (1)

Md.Rajibul Ahsan
Md.Rajibul Ahsan

Reputation: 853

Please check OPTIMUS_USER schema does the schema contains the package PACKAGE_ACCOUNT or the PACKAGE_ACCOUNT account schema belongs to any other schema.

Upvotes: 1

Related Questions