기본 콘텐츠로 건너뛰기

[Oracle] Oracle Schemas Metadata…

역시나 이번 프로젝트에서 새롭게 추가할 항목이 Oracle Database에 대한 Code Generator (C#) 이다.
어떻게 하면 쉽고, 간편하게(?) 스키마 정보를 가져올 수 있을까 생각하고 있던 중에 예전에 잠시 보았던 DbConnection 개체의 GetSchema 메서드를 활용해 보기로 했다. 별도의 코드 작성 없이도 구성할 수 있는 부분이 매력적이다.
Tool 로 구성하기 위해서는 범용적인 부분을 염두에 두어야 하지만, 당장은 프로젝트에 적용해야 하는 문제로 Oracle에 대해서만 정리해 놓도록 한다.
GetSchema() 를 이용하면 일반적인 DBMS 의 각종 스키마들을 모두 또는 지정한 것들만 DataTable 형식으로 받아서 처리할 수 있다. 물론 Oracle 도 DbConnection을 구현한 것이기 때문에 다른 DBMS도 동일하게 적용될 수 있다.
사용 방법
public virtual abstract DataTable GetSchema();

public virtual abstract DataTable GetSchema(string collectionName);

public virtual abstract DataTable GetSchema(string collectionName, string[] restrictionValues);


collectionName 은 찾고자 하는 스키마의 종류를 의미하고, restrictionValues 는 스키마 정보를 조회할 때 사용할 제약정보들을 지정하면 된다. 자세한 부분은 참고와 샘플로 확인한다.

참고 사항

collectionName 은 다음과 같은 것들을 지정할 수 있다.

Collection Name
Number of restrictions
Remarks
MetaDataCollections0
Returns this list. Same as using GetSchema() method without parameters.
ReservedWords0
Lists all reserved words used in the server.
Users1
Lists all users on the server.
When restricted by username, returns information about specific user.
Tables2
GetSchema("Tables") returns the list of all tables on the server that you have access to.
  • The first restriction for this collection is name of a schema. If specified, the method returns all tables within the schema.
  • The second restriction is table name. Note that masks are not allowed in dotConnect for Oracle.
Views2
GetSchema("Views") returns the list of all views on the server that you have access to.
  • The first restriction for this collection is name of a schema. If specified, the method returns all views within the schema.
  • The second restriction is the name of the view.
Columns3
Returns the list of columns, their type and some extra information.
GetSchema("Columns") returns the list of all columns in all schemas of the table.
  • Restricted by schema name, the method returns all columns in the specified schema.
  • The second restriction is a name of a table that GetSchema method should search in.
  • At last, you can specify column name.
Indexes4
Returns the list of indexes and their details.
  • The first restriction is name of a schema the indexes belongs to.
  • The second restriction is name of the index.
  • The third restriction is name of a table that uses the index.
  • The last restriction is name of schema the table belongs to.
IndexColumns5
Returns information about columns included in indexes. The following restrictions may be specified:
  • The first restriction is name of the schema for indexes;
  • Index name;
  • Name of the schema for tables;
  • Table name;
  • Column name.
Functions2
Returns the list of functions on the server. The following restrictions may be specified:
  • Schema name;
  • Function name.
Procedures3
Returns the list of procedures on the server. The following restrictions may be specified:
  • Schema name;
  • Package name;
  • Procedure name.
Arguments4
Returns the list of procedure and function arguments. The following restrictions may be specified:
  • Schema name;
  • Package name;
  • Procedure name;
  • Argument name. 
Synonyms2
Returns the list of synonyms on the server. The following restrictions may be specified:
  • Schema name;
  • Synonym name.
Sequences2
Returns the list of sequences on the server. The following restrictions may be specified:
  • Schema name;
  • Sequence name.
Packages2
Returns the list of packages on the server. The following restrictions may be specified:
  • Schema name;
  • Package name.
PackageBodies2
Returns the list of package bodies on the server that you have access to. The following restrictions may be specified:
  • Schema name;
  • Package name.
PrimaryKeys3
Returns the list of primary keys on the server. The following restrictions may be specified:
  • Schema name;
  • Key name;
  • Table name.
PrimaryKeyColumns3
Returns the list of columns of primary keys on the server. The following restrictions may be specified:
  • Schema name;
  • Key name;
  • Table name.
ForeignKeys3
Returns the list of foreign keys on the server. The following restrictions may be specified:
  • Schema name;
  • Key name;
  • Table name.
ForeignKeyColumns3
Returns the list of columns of foreign keys on the server. The following restrictions may be specified:
  • Schema name;
  • Key name;
  • Table name.
Triggers2
Returns the list of triggers on the server that you have access to. The following restrictions may be specified:
  • Schema name;
  • Trigger name.
Clusters2
Returns the list of clusters on the server that you have access to. The following restrictions may be specified:
  • Schema name;
  • Cluster name.
QueuePublishers3
Returns the list of queue publishers on the server. The following restrictions may be specified:
  • Schema name;
  • Queue name;
  • Publisher name.
Queues5
Returns the list of queues on the server. The following restrictions may be specified:
  • Schema name;
  • Queue name;
  • Queue table name;
  • Queue payload type;
  • Queue payload object type.
QueueTables4
Returns the list of queue tables on the server. The following restrictions may be specified:
  • Schema name;
  • Queue table name;
  • Queue payload type;
  • Queue payload object type.
QueueSubscribers4
Returns the list of subscribers to a queue or queues on the server. The following restrictions may be specified:
  • Schema name;
  • Queue name;
  • Queue table name;
  • Subscriber name.



샘플

// 사용자와 테이블명을 지정하고 테이블 정보를 조회한 경우

DataTable dt = myDbConnection.GetSchema(“Tables”, new string[] { “SCOTT”, “DEPARTMENT” });

// 사용자와 테이블명을 지정하고 컬럼 정보를 조회한 경우

DataTable dt = myDbConnection.GetSchame(“Columns”, new string[] { “SCOTT”, “DEPARTMENT” });



위의 정보를 이용해서 다양한 Metadata 정보 처리가 가능해 진다.

댓글