locked
Executing Oracle procedure from c# - Issues RRS feed

  • Frage

  • I am able to run the following procedure from Oracle and it works fine.


     

    DECLARE 
    
      RetVal NUMBER;
    
      PLOGINAME VARCHAR2(32767);
    
      PPASSWD VARCHAR2(32767);
    
      PDEFDB VARCHAR2(32767);
    
      PDEFLANGUAGE VARCHAR2(32767);
    
      PFULLNAME VARCHAR2(32767);
    
    
    
    BEGIN 
    
      PLOGINAME := 'user05';
    
      PPASSWD := 'password05';
    
      PDEFDB := NULL;
    
      PDEFLANGUAGE := NULL;
    
      PFULLNAME := 'user05';
    
    
    
      RetVal := ADDLOGIN ( PLOGINAME, PPASSWD, PDEFDB, PDEFLANGUAGE, PFULLNAME );
    
      COMMIT; 
    
    END; 
    
    


    Below is the code sample on how I am running this from c#.

                            try
    
                            {
    
    
    
                                OracleCommand cmdSchema = new OracleCommand("ADDLOGIN", _oraConn1.oraConn);
    
                                cmdSchema.CommandType = CommandType.StoredProcedure;
    
    
    
                                cmdSchema.Parameters.Add(new OracleParameter("@PLOGINAME", OracleDbType.Varchar2));
    
                                cmdSchema.Parameters["@PLOGINAME"].Value = user.ToString();
    
    
    
                                cmdSchema.Parameters.Add(new OracleParameter("@PPASSWD", OracleDbType.Varchar2));
    
                                cmdSchema.Parameters["@PPASSWD"].Value = password.ToString();
    
    
    
                                cmdSchema.Parameters.Add(new OracleParameter("@PDEFDB", OracleDbType.Varchar2));
    
                                cmdSchema.Parameters["@PDEFDB"].Value = "USERS";
    
    
    
                                cmdSchema.Parameters.Add(new OracleParameter("@PDEFLANGUAGE", OracleDbType.Varchar2));
    
                                cmdSchema.Parameters["@PDEFLANGUAGE"].Value = DBNull.Value;
    
    
    
                                cmdSchema.Parameters.Add(new OracleParameter("@PFULLNAME", OracleDbType.Varchar2));
    
                                cmdSchema.Parameters["@PFULLNAME"].Value = user.ToString();
    
    
    
                                //Return Parameter
    
                                cmdSchema.Parameters.Add(new OracleParameter("PRETCD", OracleDbType.Int64));
    
                                cmdSchema.Parameters["PRETCD"].Direction = ParameterDirection.ReturnValue;
    
    
    
                                cmdSchema.ExecuteNonQuery();
    
    
                            }
    
                            catch (OracleException e)
    
                            {
    
                                Console.WriteLine(e.Message);
    
                                errorflag = "Y";
    
                                errordesc = Left(e.Message.ToString(),100);
    
                            }
    
    
    
    



    The following error message is returned when executed from c#.

    <ERRMSG>Error occured while creating user ORA-01935: missing user or role name</ERRMSG>

    My question is why it doesn't work when executed from c#, when it works fine when executed from oracle. Is there anything wrong in the code the way its executed.

    Below is a copy of the function

    CREATE OR REPLACE FUNCTION ADDLOGIN(
    
    
    
    /*************************************************
    
    ** Declare Arguments                            **
    
    *************************************************/
    
    
    
      pLOGINAME           varchar2          ,  /* login name of the new user */
    
      pPASSWD             varchar2 := NULL   ,  /* password of the new user */
    
      pDEFDB              varchar2 := NULL   ,  /* default db for the new user */
    
      pDEFLANGUAGE        varchar2 := NULL   ,  /* default language for the new user */
    
      pFULLNAME           varchar2 := NULL      /* account owner's full name */
    
    
    
    /*************************************************
    
    ** Begin Procedure Code                         **
    
    *************************************************/
    
    
    
    ) return number authid current_user AS
    
    
    
    /*************************************************
    
    ** Declare Local Variables                      **
    
    *************************************************/
    
    
    
    lnRetCd  number := 0;
    
    lxNewPwd varchar2(30);
    
    v_defdb varchar2(30) := pDEFDB;
    
    v_stmt varchar2(1000);
    
    lxUserId   varchar2(30) := lower(pLOGINAME);
    
    lxPassword varchar2(30) := lower(pPASSWD);
    
    
    
    BEGIN
    
    
    
      if v_DEFDB IS NULL then
    
        v_DEFDB := 'USERS';
    
      end if;
    
    
    
    
    
      lnRetCd := ENCODE(
    
                   pcsArg     =>  lxPassword  ,
    
                   pcsSeed    =>  lxUserId,
    
                   pcsResult  =>  lxNewPwd);
    
    
    
      if lnRetCd = 0 then
    
    
    
        v_stmt := 'CREATE USER '||pLOGINAME||' identified by "'||lxNewPwd ||'" DEFAULT TABLESPACE '|| v_defdb||
    
                  ' TEMPORARY TABLESPACE TEMP ACCOUNT UNLOCK';
    
        EXECUTE IMMEDIATE v_stmt;
    
        EXECUTE IMMEDIATE 'GRANT connect, resource to '||pLOGINAME;
    
      end if;
    
    
    
      RETURN 0;
    
    
    
    EXCEPTION
    
      WHEN OTHERS then
    
         ER_SP_SYST_SYST_RAISE_ERROR(SQLCODE, 'Error occured while creating user '||SQLERRM);
    
    END;
    
    /
    
    
    
    

    Appretiate any inputs

    Dienstag, 22. Dezember 2009 05:55

Antworten

  • Make sure _oraConn1.oraConn have required permissions.
    With best regards, Yasser Zamani
    • Als Antwort vorgeschlagen Harry Zhu Montag, 28. Dezember 2009 03:38
    • Als Antwort markiert Harry Zhu Dienstag, 29. Dezember 2009 02:24
    Samstag, 26. Dezember 2009 14:50