Kamil Kliczbor @ asptip.net

6Jul/100

PLS-00201: identifier must be declared

Przyczyny powstania błędu o kodzie PLS-00201: identifier must be declared mogą być różne. Sprawdzenie następujących kroków może przyczynić się do znalezienia rozwiązania (http://www.dbforums.com/oracle/1207142-pls-00201-identifier-ordsys-ordimage-must-declared.html):

Cause:
An attempt was made to reference either an undeclared variable, exception, procedure, or other item, or an item to which no privilege was granted or an item to which privilege was granted only through a role.

Action:

Check your spelling and declaration of the referenced name.

Verify that the declaration for the referenced item is placed correctly in the block structure.

If the referenced item is indeed declared but you do not have privileges to refer to that item, for security reasons, you will be notified only that the item is not declared.

If the referenced item is indeed declared and you believe that you have privileges to refer to that item, check the privileges; if the privileges were granted only via a role, then this is expected and documented behavior.

Stored objects (packages, procedures, functions, triggers, views) run in the security domain of the object owner with no roles enabled except PUBLIC. Again, you will be notified only that the item was not declared.

Najczęściej pomaga zdefiniowanie (publicznego) synonimu przy użyciu magicznego zaklęcia:

create or replace public synonym nazwa for schemat.nazwa;
23Mar/100

Generowanie identyfikatora Guid dla Oracle

Próba przeniesienia procedur i tabel z MS-SQLa do Oracla nie zawsze musi byc prosta. Wynika to z faktu, że nie wszystkie typy Oracle są wprost kompatybilne z typami oferowanymi przez engine MSSQLa. Poniższe przykłady mają na celu ułatwienie prób konwersji typu Guid obecnego w MSSQLu oraz platformie .NET, na typ oferowany przez Oracle. Owym typem jest po prostu typ RAW, którego długość wynosi 16 bajtów, czyli ostatecznie RAW(16). Konstrukcja .NETowego Guida określa sztywny format, z jakim mamy do czynienia, tj. nawiasy sześcienne, myślnik oraz grupy liczb zapisanych w formacie heksagonalnym. Więcej o jego budowie można doczytać na stronach MSDNu.
W przypadku Oracle, zapisywane dane są wartościami heksadecymalnymi, bez dodatkowych ubarwień, tj. myśników czy też nawiasów.

Przykład 1. Kod wrzucający wartość .NETowego Guida do Oraclowego Raw-a.

public static void InsertGuid(Guid id)
{
 using (OracleConnection connection = new OracleConnection(ConnectionString))
 {
 const string Query = @"INSERT INTO MESSAGE (MessageId) VALUES (:id);";

 OracleCommand cmd = new OracleCommand(Query);
 cmd.CommandType = CommandType.Text;
 cmd.Connection = connection;
 cmd.Parameters.Add(":id", OracleType.Raw, 16).Value = id.ToByteArray();

 try
 {
 connection.Open();
 cmd.ExecuteNonQuery();
 }
 catch (OracleException exception)
 {
 throw  new InvalidOperationException("Couldn't insert the id", exception);
 }
 finally
 {
 connection.Close();
 }
 }
}

Wyjaśnienie:
W powyższym fragmencie kodu mamy do czynienia ze statyczną metodą, pobierającą Guid jako parametr. Dalej tworzymy połączenie do bazy danych przy użyciu Oracle providera. Nastepnie do obiektu typu OracleCommand dodajemy treść zapytania oraz przypisujemy paramter ":id", którego wartość pobierana jest z Guida jako tablica bajtów. Kolejnym i ostatnim krokiem jest wykonanie ExecuteNonQuery, której celem jest uruchomienie zapytania.

Przykład 2. Metoda pobierająca dane z z bazy.

public static Guid GetGuid(int length)
{
 Guid guid = Guid.Empty;

 using (OracleConnection connection = new OracleConnection(ConnectionString))
 {
 const string Query = @"SELECT TOP 1 MessageId WHERE length = :length;";

 OracleCommand cmd = new OracleCommand(Query);
 cmd.CommandType = CommandType.Text;
 cmd.Connection = connection;
 cmd.Parameters.Add(":length", OracleType.Number).Value = length;

 try
 {
 connection.Open();
 using(OracleDataReader reader = cmd.ExecuteReader())
 {
 reader.Read();
 guid = new Guid((byte[])reader[0]);
 }
 }
 catch (OracleException exception)
 {
 throw  new InvalidOperationException("Couldn't get the message", exception);
 }
 finally
 {
 connection.Close();
 }
 }

 return guid;
}

Wyjaśnienie:
Przykład bazujący na tym, ze zaznaczamy jedną wartość Guid dla wiadomości (z tąd też top 1 w zapytaniu). Oczywiście może to być zupełnie inne i dowolne zapytanie. Celem tego bloku kodu jest pokazanie w jaki sposób można pobrać taki identyfikator. Ciekawostką jest to, że korzystamy z OracleDatReadera, a nie używamy ExecuteScalar().

Przykład 3. Insert rekordu z wygenerowaniem unikalnego identyfikatora.
Dla ms-sqla blok t-sqlowy wyglądałby tak:

DECLARE @MessageId uniqueidentifier;
SET @MessageId  = NEWID();
INSERT INTO Message (MessageId)
VALUES (@MessageId);
SELECT @MessageId;

Dla Oracle razem z "opakowaniem" procedura wygląda tak:

public static Guid InsertEmptyMessage()
{
 using (OracleConnection connection = new OracleConnection(ConnectionString))
 {
 const string Query = @"declare r_id raw(16); " +
 "begin :r_id := SYS_GUID; " +
 "INSERT INTO MESSAGE (MessageId) " +
 "VALUES (:r_id); end;";

 OracleCommand cmd = new OracleCommand(Query);
 cmd.CommandType = CommandType.Text;
 cmd.Connection = connection;
 cmd.Parameters.Add(":r_id", OracleType.Raw, 16).Direction = ParameterDirection.Output;

 try
 {
 connection.Open();
 cmd.ExecuteNonQuery();
 object guid = cmd.Parameters[0].Value;
 return new Guid((byte[])guid);
 }
 catch (OracleException)
 {
 throw;
 }
 finally
 {
 connection.Close();
 }
 }

 return Guid.Empty;
}
Tagged as: , No Comments
23Mar/100

Jak uniknąć błędu PLS-00306: wrong number or types of arguments in call ?

Załóżmy, że mamy do czynienia z następującą procedurą składowaną w ORACLE 10g.


PROCEDURE P_MESSAGE_INSERT(pSubject in nvarchar2, pSubjectEncoding in nvarchar2) AS
 BEGIN
 INSERT INTO Message(Subject, SubjectEncoding)
 VALUES (pSubject, pSubjectEncoding);
 END P_MESSAGE_INSERT;

oraz fragment kodu w ADO.NET, który wykonuje tą procedurkę:

string subject = string.Empty;
string subjectEncoding = null;

using (OracleConnection connection = new OracleConnection(ConnectionString))
{
 OracleCommand cmd = connection.CreateCommand();
 connection.Open();
 OracleTransaction tx = connection.BeginTransaction();
 cmd.Transaction = tx;

 try
 {
 cmd.CommandText = "CRUD_MESSAGE.P_MESSAGE_INSERT";
 cmd.CommandType = CommandType.StoredProcedure;
 cmd.Parameters.Add(new OracleParameter("pSubject", OracleType.NVarChar, 8000));
 cmd.Parameters.Add(new OracleParameter("pSubjectEncoding", OracleType.NVarChar, 8000),

 if (subject != null)
 {
 parameters[0].Value = subject;
 }
 if (message.subjectEncoding!= null)
 {
 parameters[1].Value = subjectEncoding;
 }

 cmd.ExecuteNonQuery();
 tx.Commit();
 }
 catch (OracleException exception)
 {
 tx.Rollback();
 throw new InvalidOperationException("Couldn't add the message", exception);
 }
 finally
 {
 connection.Close();
 }
}
}

Wykonujemy podany kod i otrzymujemy wspaniały błąd:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'P_MESSAGE_INSERT'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Pierwszą czynnością jaka jest do wykonania jest upewnienie się, czy liczba przekazywanych argumentów oraz ich typy się zgadzają. W powyzsym przykładzie istotnie tak jest. Więc gdzie jest błąd ? Otóż należy upewnić się, czy przekazywany string nie jest null-em! A jeżeli jest, to należy parametrowi przypisać wartość DBNull.Value. Powyższy kod należy więc rozszerzyć o dodatkowe sprawdzenie wartości:

if (subject != null)
{
 parameters[0].Value = subject;
}
else
{
 parameters[0].Value = DBNull.Value;
}
Filed under: Oracle, SQL No Comments