Oracle AI Database 26aiのGraphQLとOracle REST Data ServicesのGraphQLを比較する

Oracle AI Database 26aiよりGraqhQLを実行する表関数が、正式にサポートされました。

Oracle Database Support for GraphQL Developer's Guide, Release 26

https://docs.oracle.com/en/database/oracle/oracle-database/26/gphql/

この機能に関するブログ記事としては、ORACLE-BASE（Tim Hallさんによる著名なサイト）の以下が良記事だと思います。

GraphQL in Oracle Database 23ai/26ai

https://oracle-base.com/articles/23/graphql-23#graphql-and-ords

同じことを調べても仕方がないので、少し方向性を変えて、Oracle REST Data ServicesがサポートしているGraphQLと比較してみます。Oracle REST Data Services 23.3より、GraphQLを検索に限りサポートしています。GraphQLを実行するには、JavaScriptの実行エンジンを組み込んだGraalVM上で、ORDSを実行する必要があります。

オラクルが公開しているOracle REST Data Servicesのコンテナ・イメージは、GraalVM Enterprise Editionを使用しています。そのため、このコンテナ・イメージを使用してORDSの環境を構築していれば、GraphQLも使用できる状態です。

[oracle@apex ords]$ java -version

java version "17.0.15" 2025-04-15 LTS

Java(TM) SE Runtime Environment GraalVM EE 21.3.14 (build 17.0.15+9-LTS-jvmci-21.3-b110)

Java HotSpot(TM) 64-Bit Server VM GraalVM EE 21.3.14 (build 17.0.15+9-LTS-jvmci-21.3-b110, mixed mode, sharing)

[oracle@apex ords]$ 

以前の記事「Oracle REST Data Services Developer's Guideに載っているGraphQLのサンプルを実行する」で実行したGraphQLのクエリとほぼ同等のクエリを、Oracle AI Database 26ai Freeで実行してみます。この記事ではGitHubにあるOracle Databaseのサンプル・スキーマのHuman Resourcesをインストールして問い合わせを実施していますが、今回はその代わりに、もっと一般的に使用されているEMP/DEPTのサンプルを使用します。

Oracle APEXのサンプル・データセットを開き、EMP/DEPTをインストールします。

EMP/DEPTのデータ・セットが未インストールの場合は、インストールのボタンが表示されます。それをクリックしてインストール作業を実施します。

データセットの言語は英語を選択します。

データセットのインストールを実行します。

表EMPとDEPT、ビューEMP_DEPT_Vが作成されます。終了をクリックします。

表EMPとDEPTをOracle REST Data ServicesのGraphQLでアクセスできるように、AutoRESTを有効にします。以下のスクリプトを実行します。

begin
    ords.enable_schema(
        p_enabled => true
    );
    ords.enable_object(
        p_object => 'EMP'
        ,p_object_type => 'TABLE'
    );
    ords.enable_object(
        p_object => 'DEPT'
        ,p_object_type => 'TABLE'
    );
end;

SQLワークショップのSQLコマンドで実行します。

ORDSのGraphQLの実行は、GraphiQLで確認します。

http://localhost:8181/ords/apexdev/_/graphiql

APEXのワークスペース・スキーマをユーザー名（本記事ではWKSP_APEXDEV）、パスワードにスキーマのパスワードを与えて、GraphiQLにサインインします。

サインインすると、GraphiQLのページが開きます。

以前の記事と同様に、以下のドキュメントに記載されているクエリを、表EMP/DEPTを使用するように書き換えて実行します。

Oracle REST Data Services Developer's Guide, Release 25.3

14 GraphQL in Oracle REST Data Services

https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/25.3/orddg/graphql-oracle-rest-data-services.html

GraphQL Developer's Guideの最初に、26aiのGraphQLと標準のGraphQLとの間の、文法およびレスポンスの違いが記載されています。ORDSは、概ねGraphQL標準に準拠しているようです。

1 Overview of GraphQL
Oracle Database Support for GraphQL

ORDSのGraphQLでは、表EMPを検索するクエリを以下のように記述します。

query Employees {
  employees: emp {
    employeeId: empno
    employeeName: ename
    job: job
    salary: sal
  }
}

以下のレスポンスが返されます。

{
  "data": {
    "employees": [
      {
        "employeeId": 7839,
        "employeeName": "KING",
        "job": "PRESIDENT",
        "salary": 5000
      },
      {
        "employeeId": 7698,
        "employeeName": "BLAKE",
        "job": "MANAGER",
        "salary": 2850
      },
      {
        "employeeId": 7782,
        "employeeName": "CLARK",
        "job": "MANAGER",
        "salary": 2450
      },
      {
        "employeeId": 7566,
        "employeeName": "JONES",
        "job": "MANAGER",
        "salary": 2975
      },
      {
        "employeeId": 7788,
        "employeeName": "SCOTT",
        "job": "ANALYST",
        "salary": 3000
      },
      {
        "employeeId": 7902,
        "employeeName": "FORD",
        "job": "ANALYST",
        "salary": 3000
      },
      {
        "employeeId": 7369,
        "employeeName": "SMITH",
        "job": "CLERK",
        "salary": 800
      },
      {
        "employeeId": 7499,
        "employeeName": "ALLEN",
        "job": "SALESMAN",
        "salary": 1600
      },
      {
        "employeeId": 7521,
        "employeeName": "WARD",
        "job": "SALESMAN",
        "salary": 1250
      },
      {
        "employeeId": 7654,
        "employeeName": "MARTIN",
        "job": "SALESMAN",
        "salary": 1250
      },
      {
        "employeeId": 7844,
        "employeeName": "TURNER",
        "job": "SALESMAN",
        "salary": 1500
      },
      {
        "employeeId": 7876,
        "employeeName": "ADAMS",
        "job": "CLERK",
        "salary": 1100
      },
      {
        "employeeId": 7900,
        "employeeName": "JAMES",
        "job": "CLERK",
        "salary": 950
      },
      {
        "employeeId": 7934,
        "employeeName": "MILLER",
        "job": "CLERK",
        "salary": 1300
      }
    ]
  }
}

26aiのGRAPHQL表関数に与えるGraphQLでは、クエリを規定するqueryの指定は省かれます。よって、上記のクエリは以下のように記述します。

select data from graphql(q'[
  employees: emp {
    employeeId: empno
    employeeName: ename
    job: job
    salary: sal
  }
]');

以下の検索結果が得られます。標準のレスポンスではdata属性の配列の要素として返される内容が、検索結果になります。

SQL> select data from graphql(q'[

  2    employees: emp {

  3      employeeId: empno

  4      employeeName: ename

  5      job: job

  6      salary: sal

  7    }

  8* ]');

DATA                                                                          

_____________________________________________________________________________ 

{"employeeId":7839,"employeeName":"KING","job":"PRESIDENT","salary":5000}     

{"employeeId":7698,"employeeName":"BLAKE","job":"MANAGER","salary":2850}      

{"employeeId":7782,"employeeName":"CLARK","job":"MANAGER","salary":2450}      

{"employeeId":7566,"employeeName":"JONES","job":"MANAGER","salary":2975}      

{"employeeId":7788,"employeeName":"SCOTT","job":"ANALYST","salary":3000}      

{"employeeId":7902,"employeeName":"FORD","job":"ANALYST","salary":3000}       

{"employeeId":7369,"employeeName":"SMITH","job":"CLERK","salary":800}         

{"employeeId":7499,"employeeName":"ALLEN","job":"SALESMAN","salary":1600}     

{"employeeId":7521,"employeeName":"WARD","job":"SALESMAN","salary":1250}      

{"employeeId":7654,"employeeName":"MARTIN","job":"SALESMAN","salary":1250}    

{"employeeId":7844,"employeeName":"TURNER","job":"SALESMAN","salary":1500}    

{"employeeId":7876,"employeeName":"ADAMS","job":"CLERK","salary":1100}        

{"employeeId":7900,"employeeName":"JAMES","job":"CLERK","salary":950}         

{"employeeId":7934,"employeeName":"MILLER","job":"CLERK","salary":1300}       

14行が選択されました。 

SQL> 

部門に所属する従業員を一覧するGraphQLを、ORDS向けに記述しました。この中の属性emp_deptnoは、表の参照制約よりORDSが作成しています。

query Departments {
  department: dept {
    departmentId: deptno
    departmentName: dname
    location: loc
    employees: emp_deptno {
      employeeId: empno
      employeeName: ename
    }
  }
}

以下のレスポンスが返されます。

{
  "data": {
    "department": [
      {
        "departmentId": 10,
        "departmentName": "ACCOUNTING",
        "location": "NEW YORK",
        "employees": [
          {
            "employeeId": 7782,
            "employeeName": "CLARK"
          },
          {
            "employeeId": 7934,
            "employeeName": "MILLER"
          },
          {
            "employeeId": 7839,
            "employeeName": "KING"
          }
        ]
      },
      {
        "departmentId": 20,
        "departmentName": "RESEARCH",
        "location": "DALLAS",
        "employees": [
          {
            "employeeId": 7902,
            "employeeName": "FORD"
          },
          {
            "employeeId": 7788,
            "employeeName": "SCOTT"
          },
          {
            "employeeId": 7566,
            "employeeName": "JONES"
          },
          {
            "employeeId": 7369,
            "employeeName": "SMITH"
          },
          {
            "employeeId": 7876,
            "employeeName": "ADAMS"
          }
        ]
      },
      {
        "departmentId": 30,
        "departmentName": "SALES",
        "location": "CHICAGO",
        "employees": [
          {
            "employeeId": 7521,
            "employeeName": "WARD"
          },
          {
            "employeeId": 7654,
            "employeeName": "MARTIN"
          },
          {
            "employeeId": 7844,
            "employeeName": "TURNER"
          },
          {
            "employeeId": 7900,
            "employeeName": "JAMES"
          },
          {
            "employeeId": 7499,
            "employeeName": "ALLEN"
          },
          {
            "employeeId": 7698,
            "employeeName": "BLAKE"
          }
        ]
      },
      {
        "departmentId": 40,
        "departmentName": "OPERATIONS",
        "location": "BOSTON",
        "employees": []
      }
    ]
  }
}

26aiでは以下のように記述します。

select data from graphql(q'[
  department: dept {
    departmentId: deptno
    departmentName: dname
    location: loc
    employees: emp {
      employeeId: empno
      employeeName: ename
    }
  }
]');

SQL> select data from graphql(q'[

  2    department: dept {

  3      departmentId: deptno

  4      departmentName: dname

  5      location: loc

  6      employees: emp {

  7        employeeId: empno

  8        employeeName: ename

  9      }

 10    }

 11* ]');

DATA                                                                                                                                                                                                                                                                                                                                                  

_____________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________ 

{"departmentId":10,"departmentName":"ACCOUNTING","location":"NEW YORK","employees":[{"employeeId":7839,"employeeName":"KING"},{"employeeId":7782,"employeeName":"CLARK"},{"employeeId":7934,"employeeName":"MILLER"}]}                                                                                                                                

{"departmentId":20,"departmentName":"RESEARCH","location":"DALLAS","employees":[{"employeeId":7566,"employeeName":"JONES"},{"employeeId":7788,"employeeName":"SCOTT"},{"employeeId":7902,"employeeName":"FORD"},{"employeeId":7369,"employeeName":"SMITH"},{"employeeId":7876,"employeeName":"ADAMS"}]}                                               

{"departmentId":30,"departmentName":"SALES","location":"CHICAGO","employees":[{"employeeId":7698,"employeeName":"BLAKE"},{"employeeId":7499,"employeeName":"ALLEN"},{"employeeId":7521,"employeeName":"WARD"},{"employeeId":7654,"employeeName":"MARTIN"},{"employeeId":7844,"employeeName":"TURNER"},{"employeeId":7900,"employeeName":"JAMES"}]}    

{"departmentId":40,"departmentName":"OPERATIONS","location":"BOSTON","employees":[]}                                                                                                                                                                                                                                                                  

SQL> 

上記のGraphQLでは表EMPおよびDEPTに設定されている参照制約を参照することで、いくつかの記述を省略しています。より厳密には、@linkディレクティブを使って以下のように記述します。検索結果は同じです。

select data from graphql(q'[
  department: dept {
    departmentId: deptno
    departmentName: dname
    location: loc
    employees: emp @link(from: ["DEPTNO"], to: ["DEPTNO"]) [{
      employeeId: empno
      employeeName: ename
    }]
  }
]');

従業員の一覧に所属している部署を含めます。ORDS向けに記述したGraphQLのクエリです。

query Employees {
    employees : emp {
        employeeId: empno
        employeeNane: ename
      	department: dept_deptno {
        	departmentId: deptno
        	departmentName: dname
      	}
    }
}

以下のレスポンスが返されます。

{
  "data": {
    "employees": [
      {
        "employeeId": 7839,
        "employeeNane": "KING",
        "department": [
          {
            "departmentId": 10,
            "departmentName": "ACCOUNTING"
          }
        ]
      },
      {
        "employeeId": 7782,
        "employeeNane": "CLARK",
        "department": [
          {
            "departmentId": 10,
            "departmentName": "ACCOUNTING"
          }
        ]
      },
      {
        "employeeId": 7934,
        "employeeNane": "MILLER",
        "department": [
          {
            "departmentId": 10,
            "departmentName": "ACCOUNTING"
          }
        ]
      },
      {
        "employeeId": 7566,
        "employeeNane": "JONES",
        "department": [
          {
            "departmentId": 20,
            "departmentName": "RESEARCH"
          }
        ]
      },
      {
        "employeeId": 7788,
        "employeeNane": "SCOTT",
        "department": [
          {
            "departmentId": 20,
            "departmentName": "RESEARCH"
          }
        ]
      },
      {
        "employeeId": 7902,
        "employeeNane": "FORD",
        "department": [
          {
            "departmentId": 20,
            "departmentName": "RESEARCH"
          }
        ]
      },
      {
        "employeeId": 7369,
        "employeeNane": "SMITH",
        "department": [
          {
            "departmentId": 20,
            "departmentName": "RESEARCH"
          }
        ]
      },
      {
        "employeeId": 7876,
        "employeeNane": "ADAMS",
        "department": [
          {
            "departmentId": 20,
            "departmentName": "RESEARCH"
          }
        ]
      },
      {
        "employeeId": 7698,
        "employeeNane": "BLAKE",
        "department": [
          {
            "departmentId": 30,
            "departmentName": "SALES"
          }
        ]
      },
      {
        "employeeId": 7499,
        "employeeNane": "ALLEN",
        "department": [
          {
            "departmentId": 30,
            "departmentName": "SALES"
          }
        ]
      },
      {
        "employeeId": 7521,
        "employeeNane": "WARD",
        "department": [
          {
            "departmentId": 30,
            "departmentName": "SALES"
          }
        ]
      },
      {
        "employeeId": 7654,
        "employeeNane": "MARTIN",
        "department": [
          {
            "departmentId": 30,
            "departmentName": "SALES"
          }
        ]
      },
      {
        "employeeId": 7844,
        "employeeNane": "TURNER",
        "department": [
          {
            "departmentId": 30,
            "departmentName": "SALES"
          }
        ]
      },
      {
        "employeeId": 7900,
        "employeeNane": "JAMES",
        "department": [
          {
            "departmentId": 30,
            "departmentName": "SALES"
          }
        ]
      }
    ]
  }
}

26aiでは以下のように記述します。@linkは省略しています。

select data from graphql(q'[
    employees : emp {
        employeeId: empno
        employeeNane: ename
      	department: dept {
        	departmentId: deptno
        	departmentName: dname
      	}
    }
]');

SQL> select data from graphql(q'[

  2      employees : emp {

  3          employeeId: empno

  4          employeeNane: ename

  5         department: dept {

  6             departmentId: deptno

  7             departmentName: dname

  8         }

  9      }

 10* ]');

DATA                                                                                                          

_____________________________________________________________________________________________________________ 

{"employeeId":7839,"employeeNane":"KING","department":{"departmentId":10,"departmentName":"ACCOUNTING"}}      

{"employeeId":7698,"employeeNane":"BLAKE","department":{"departmentId":30,"departmentName":"SALES"}}          

{"employeeId":7782,"employeeNane":"CLARK","department":{"departmentId":10,"departmentName":"ACCOUNTING"}}     

{"employeeId":7566,"employeeNane":"JONES","department":{"departmentId":20,"departmentName":"RESEARCH"}}       

{"employeeId":7788,"employeeNane":"SCOTT","department":{"departmentId":20,"departmentName":"RESEARCH"}}       

{"employeeId":7902,"employeeNane":"FORD","department":{"departmentId":20,"departmentName":"RESEARCH"}}        

{"employeeId":7369,"employeeNane":"SMITH","department":{"departmentId":20,"departmentName":"RESEARCH"}}       

{"employeeId":7499,"employeeNane":"ALLEN","department":{"departmentId":30,"departmentName":"SALES"}}          

{"employeeId":7521,"employeeNane":"WARD","department":{"departmentId":30,"departmentName":"SALES"}}           

{"employeeId":7654,"employeeNane":"MARTIN","department":{"departmentId":30,"departmentName":"SALES"}}         

{"employeeId":7844,"employeeNane":"TURNER","department":{"departmentId":30,"departmentName":"SALES"}}         

{"employeeId":7876,"employeeNane":"ADAMS","department":{"departmentId":20,"departmentName":"RESEARCH"}}       

{"employeeId":7900,"employeeNane":"JAMES","department":{"departmentId":30,"departmentName":"SALES"}}          

{"employeeId":7934,"employeeNane":"MILLER","department":{"departmentId":10,"departmentName":"ACCOUNTING"}}    

14行が選択されました。 

SQL> 

26aiではunnestディレクティブを指定することにより、部門の情報を従業員のオブジェクトにフラットな形で含めることができます。

select data from graphql(q'[
    employees : emp {
        employeeId: empno
        employeeNane: ename
      	dept @unnest {
        	departmentId: deptno
        	departmentName: dname
      	}
    }
]');

SQL> select data from graphql(q'[

  2      employees : emp {

  3          employeeId: empno

  4          employeeNane: ename

  5         dept @unnest {

  6             departmentId: deptno

  7             departmentName: dname

  8         }

  9      }

 10* ]');

DATA                                                                                           

______________________________________________________________________________________________ 

{"employeeId":7839,"employeeNane":"KING","departmentId":10,"departmentName":"ACCOUNTING"}      

{"employeeId":7698,"employeeNane":"BLAKE","departmentId":30,"departmentName":"SALES"}          

{"employeeId":7782,"employeeNane":"CLARK","departmentId":10,"departmentName":"ACCOUNTING"}     

{"employeeId":7566,"employeeNane":"JONES","departmentId":20,"departmentName":"RESEARCH"}       

{"employeeId":7788,"employeeNane":"SCOTT","departmentId":20,"departmentName":"RESEARCH"}       

{"employeeId":7902,"employeeNane":"FORD","departmentId":20,"departmentName":"RESEARCH"}        

{"employeeId":7369,"employeeNane":"SMITH","departmentId":20,"departmentName":"RESEARCH"}       

{"employeeId":7499,"employeeNane":"ALLEN","departmentId":30,"departmentName":"SALES"}          

{"employeeId":7521,"employeeNane":"WARD","departmentId":30,"departmentName":"SALES"}           

{"employeeId":7654,"employeeNane":"MARTIN","departmentId":30,"departmentName":"SALES"}         

{"employeeId":7844,"employeeNane":"TURNER","departmentId":30,"departmentName":"SALES"}         

{"employeeId":7876,"employeeNane":"ADAMS","departmentId":20,"departmentName":"RESEARCH"}       

{"employeeId":7900,"employeeNane":"JAMES","departmentId":30,"departmentName":"SALES"}          

{"employeeId":7934,"employeeNane":"MILLER","departmentId":10,"departmentName":"ACCOUNTING"}    

14行が選択されました。 

SQL> 

従業員とその上司を一覧します。ORDSでは以下のように記述します。

query Employees {
  employees: emp {
    employeeId: empno
    employeeName: ename
    managerId: mgr
    manager: mgr_emp {
      employeeId: empno
      employeeName: ename
      job: job
    }
  }
}

以下のレスポンスが返されます。

{
  "data": {
    "employees": [
      {
        "employeeId": 7698,
        "employeeName": "BLAKE",
        "managerId": 7839,
        "manager": [
          {
            "employeeId": 7839,
            "employeeName": "KING",
            "job": "PRESIDENT"
          }
        ]
      },
      {
        "employeeId": 7782,
        "employeeName": "CLARK",
        "managerId": 7839,
        "manager": [
          {
            "employeeId": 7839,
            "employeeName": "KING",
            "job": "PRESIDENT"
          }
        ]
      },
      {
        "employeeId": 7566,
        "employeeName": "JONES",
        "managerId": 7839,
        "manager": [
          {
            "employeeId": 7839,
            "employeeName": "KING",
            "job": "PRESIDENT"
          }
        ]
      },
      {
        "employeeId": 7499,
        "employeeName": "ALLEN",
        "managerId": 7698,
        "manager": [
          {
            "employeeId": 7698,
            "employeeName": "BLAKE",
            "job": "MANAGER"
          }
        ]
      },
      {
        "employeeId": 7521,
        "employeeName": "WARD",
        "managerId": 7698,
        "manager": [
          {
            "employeeId": 7698,
            "employeeName": "BLAKE",
            "job": "MANAGER"
          }
        ]
      },
      {
        "employeeId": 7654,
        "employeeName": "MARTIN",
        "managerId": 7698,
        "manager": [
          {
            "employeeId": 7698,
            "employeeName": "BLAKE",
            "job": "MANAGER"
          }
        ]
      },
      {
        "employeeId": 7844,
        "employeeName": "TURNER",
        "managerId": 7698,
        "manager": [
          {
            "employeeId": 7698,
            "employeeName": "BLAKE",
            "job": "MANAGER"
          }
        ]
      },
      {
        "employeeId": 7900,
        "employeeName": "JAMES",
        "managerId": 7698,
        "manager": [
          {
            "employeeId": 7698,
            "employeeName": "BLAKE",
            "job": "MANAGER"
          }
        ]
      },
      {
        "employeeId": 7934,
        "employeeName": "MILLER",
        "managerId": 7782,
        "manager": [
          {
            "employeeId": 7782,
            "employeeName": "CLARK",
            "job": "MANAGER"
          }
        ]
      },
      {
        "employeeId": 7788,
        "employeeName": "SCOTT",
        "managerId": 7566,
        "manager": [
          {
            "employeeId": 7566,
            "employeeName": "JONES",
            "job": "MANAGER"
          }
        ]
      },
      {
        "employeeId": 7902,
        "employeeName": "FORD",
        "managerId": 7566,
        "manager": [
          {
            "employeeId": 7566,
            "employeeName": "JONES",
            "job": "MANAGER"
          }
        ]
      },
      {
        "employeeId": 7876,
        "employeeName": "ADAMS",
        "managerId": 7788,
        "manager": [
          {
            "employeeId": 7788,
            "employeeName": "SCOTT",
            "job": "ANALYST"
          }
        ]
      },
      {
        "employeeId": 7369,
        "employeeName": "SMITH",
        "managerId": 7902,
        "manager": [
          {
            "employeeId": 7902,
            "employeeName": "FORD",
            "job": "ANALYST"
          }
        ]
      },
      {
        "employeeId": 7839,
        "employeeName": "KING",
        "managerId": null,
        "manager": []
      }
    ]
  }
}

26aiでは以下のように記述します。このとき@linkは省略できません。省略すると接続が反対方向に解釈され、従業員の上司ではなく部下の配列が返されます。

select data from graphql(q'[
  employees: emp {
    employeeId: empno
    employeeName: ename
    managerId: mgr
    manager: emp @link (from: ["MGR"], to: ["EMPNO"]) {
      employeeId: empno
      employeeName: ename
      job: job
    }
  }
]');

SQL> select data from graphql(q'[

  2    employees: emp {

  3      employeeId: empno

  4      employeeName: ename

  5      managerId: mgr

  6      manager: emp @link (from: ["MGR"], to: ["EMPNO"]) {

  7        employeeId: empno

  8        employeeName: ename

  9        job: job

 10      }

 11    }

 12* ]');

DATA                                                                                                                                 

____________________________________________________________________________________________________________________________________ 

{"employeeId":7839,"employeeName":"KING","managerId":null,"manager":{}}                                                              

{"employeeId":7698,"employeeName":"BLAKE","managerId":7839,"manager":{"employeeId":7839,"employeeName":"KING","job":"PRESIDENT"}}    

{"employeeId":7782,"employeeName":"CLARK","managerId":7839,"manager":{"employeeId":7839,"employeeName":"KING","job":"PRESIDENT"}}    

{"employeeId":7566,"employeeName":"JONES","managerId":7839,"manager":{"employeeId":7839,"employeeName":"KING","job":"PRESIDENT"}}    

{"employeeId":7788,"employeeName":"SCOTT","managerId":7566,"manager":{"employeeId":7566,"employeeName":"JONES","job":"MANAGER"}}     

{"employeeId":7902,"employeeName":"FORD","managerId":7566,"manager":{"employeeId":7566,"employeeName":"JONES","job":"MANAGER"}}      

{"employeeId":7369,"employeeName":"SMITH","managerId":7902,"manager":{"employeeId":7902,"employeeName":"FORD","job":"ANALYST"}}      

{"employeeId":7499,"employeeName":"ALLEN","managerId":7698,"manager":{"employeeId":7698,"employeeName":"BLAKE","job":"MANAGER"}}     

{"employeeId":7521,"employeeName":"WARD","managerId":7698,"manager":{"employeeId":7698,"employeeName":"BLAKE","job":"MANAGER"}}      

{"employeeId":7654,"employeeName":"MARTIN","managerId":7698,"manager":{"employeeId":7698,"employeeName":"BLAKE","job":"MANAGER"}}    

{"employeeId":7844,"employeeName":"TURNER","managerId":7698,"manager":{"employeeId":7698,"employeeName":"BLAKE","job":"MANAGER"}}    

{"employeeId":7876,"employeeName":"ADAMS","managerId":7788,"manager":{"employeeId":7788,"employeeName":"SCOTT","job":"ANALYST"}}     

{"employeeId":7900,"employeeName":"JAMES","managerId":7698,"manager":{"employeeId":7698,"employeeName":"BLAKE","job":"MANAGER"}}     

{"employeeId":7934,"employeeName":"MILLER","managerId":7782,"manager":{"employeeId":7782,"employeeName":"CLARK","job":"MANAGER"}}    

14行が選択されました。 

SQL> 

主キーである従業員番号でフィルタします。ORDSでは以下のように記述します。クエリに名前を付けない場合は、query定義を省略できます。ただしqueryを省略しても{ ... }で囲む必要はあります。

{
  employees: emp(primaryKey: {empno: 7788}) {
    employeeId: empno
    employeeName: ename
    job
    salary: sal
  }
}

以下のレスポンスが返されます。

{
  "data": {
    "employees": [
      {
        "employeeId": 7788,
        "employeeName": "SCOTT",
        "job": "ANALYST",
        "salary": 3000
      }
    ]
  }
}

26aiの記述も同じです。

select data from graphql(q'[
  employees: emp(primaryKey: {empno: 7788}) {
    employeeId: empno
    employeeName: ename
    job
    salary: sal
  }
]');

SQL> select data from graphql(q'[

  2    employees: emp(primaryKey: {empno: 7788}) {

  3      employeeId: empno

  4      employeeName: ename

  5      job

  6      salary: sal

  7    }

  8* ]');

DATA                                                                        

___________________________________________________________________________ 

{"employeeId":7788,"employeeName":"SCOTT","job":"ANALYST","salary":3000}    

SQL> 

eq（equal）オペレータを使って、ジョブがANALYSTの従業員を一覧します。ORDSでは以下のように記述します。

{
  employees: emp(where: {job: {eq: "ANALYST"}}) {
    employeeId: empno
    employeeName: ename
    job
    salary: sal
  }
}

以下のレスポンスが返されます。

{
  "data": {
    "employees": [
      {
        "employeeId": 7788,
        "employeeName": "SCOTT",
        "job": "ANALYST",
        "salary": 3000
      },
      {
        "employeeId": 7902,
        "employeeName": "FORD",
        "job": "ANALYST",
        "salary": 3000
      }
    ]
  }
}

26aiでは以下のように記述します。等値オペレータとしてeqの代わりに_eqを使用します。

select data from graphql(q'[
  employees: emp(check: {job: {_eq: "ANALYST"}}) {
    employeeId: empno
    employeeName: ename
    job
    salary: sal
  }
]');

ドキュメントの記載が見つからなかったのですが、checkの代わりにwhereが使えました。以下のクエリも同じ動作になります。以降ではORDSに合わせてcheckではなくwhereを使用します。

select data from graphql(q'[
  employees: emp(where: {job: {_eq: "ANALYST"}}) {
    employeeId: empno
    employeeName: ename
    job
    salary: sal
  }
]');

SQL> select data from graphql(q'[

  2    employees: emp(where: {job: {_eq: "ANALYST"}}) {

  3      employeeId: empno

  4      employeeName: ename

  5      job

  6      salary: sal

  7    }

  8* ]');

DATA                                                                        

___________________________________________________________________________ 

{"employeeId":7788,"employeeName":"SCOTT","job":"ANALYST","salary":3000}    

{"employeeId":7902,"employeeName":"FORD","job":"ANALYST","salary":3000}     

SQL> 

gt（greater-tham）オペレータを使って、1982年1月１日より後に採用した従業員を一覧します。ORDSでは以下のように記述します。

{
  employees: emp(where: {hiredate: {gt: "1982-01-01T00:00:00Z"}}) {
    employeeId: empno
    employeeName: ename
    hireDate: hiredate
  }
}

以下のレスポンスが返されます。

{
  "data": {
    "employees": [
      {
        "employeeId": 7788,
        "employeeName": "SCOTT",
        "hireDate": "1982-12-09T00:00:00Z"
      },
      {
        "employeeId": 7876,
        "employeeName": "ADAMS",
        "hireDate": "1983-01-12T00:00:00Z"
      },
      {
        "employeeId": 7934,
        "employeeName": "MILLER",
        "hireDate": "1982-01-23T00:00:00Z"
      }
    ]
  }
}

26aiでは以下のように記述します。gtオペレータの代わりに_gtを使用します。また、日付はISO8861ではなく（ISO8861形式で指定すると「ORA-01861: リテラルが書式文字列と一致しません」が発生します）、NLS_DATE_FORMATに一致する文字列を指定します。

select data from graphql(q'[
  employees: emp(where: {hiredate: {_gt: "1982-01-01"}}) {
    employeeId: empno
    employeeName: ename
    hireDate: hiredate
  }
]');

SQL> select data from graphql(q'[

  2    employees: emp(where: {hiredate: {_gt: "1982-01-01"}}) {

  3      employeeId: empno

  4      employeeName: ename

  5      hireDate: hiredate

  6    }

  7* ]');

DATA                                                                            

_______________________________________________________________________________ 

{"employeeId":7788,"employeeName":"SCOTT","hireDate":"1982-12-09T00:00:00"}     

{"employeeId":7876,"employeeName":"ADAMS","hireDate":"1983-01-12T00:00:00"}     

{"employeeId":7934,"employeeName":"MILLER","hireDate":"1982-01-23T00:00:00"}    

SQL> 

likeオペレータを使用して、名前がSで始まる従業員を一覧します。ORDSでは以下のように記述します。

{
  employees: emp(where: {ename: {like: "S%"}}) {
    employeeId: empno
    employeeName: ename
  }
}

以下のレスポンスが返されます。

{
  "data": {
    "employees": [
      {
        "employeeId": 7788,
        "employeeName": "SCOTT"
      },
      {
        "employeeId": 7369,
        "employeeName": "SMITH"
      }
    ]
  }
}

26aiでは以下のように記述します。likeオペレータの代わりに_likeを使用します。

select data from graphql(q'[
  employees: emp(where: {ename: {_like: "S%"}}) {
    employeeId: empno
    employeeName: ename
  }
]');

SQL> select data from graphql(q'[

  2    employees: emp(where: {ename: {_like: "S%"}}) {

  3      employeeId: empno

  4      employeeName: ename

  5    }

  6* ]');

DATA                                          

_____________________________________________ 

{"employeeId":7788,"employeeName":"SCOTT"}    

{"employeeId":7369,"employeeName":"SMITH"}    

SQL> 

inオペレータを使用して、ジョブがANALYSTまたはPRESIDENTの従業員を一覧します。ORDSでは以下のように記述します。

{
  employees: emp(where: {job: {in: ["ANALYST", "PRESIDENT"]}}) {
    employeeId: empno
    employeeName: ename
    job
    salary: sal
  }
}

以下のレスポンスが返されます。

{
  "data": {
    "employees": [
      {
        "employeeId": 7839,
        "employeeName": "KING",
        "job": "PRESIDENT",
        "salary": 5000
      },
      {
        "employeeId": 7788,
        "employeeName": "SCOTT",
        "job": "ANALYST",
        "salary": 3000
      },
      {
        "employeeId": 7902,
        "employeeName": "FORD",
        "job": "ANALYST",
        "salary": 3000
      }
    ]
  }
}

26aiでは以下のように記述します。inオペレータの代わりに_inを使用します。

select data from graphql(q'[
  employees: emp(where: {job: {_in: ["ANALYST", "PRESIDENT"]}}) {
    employeeId: empno
    employeeName: ename
    job
    salary: sal
  }
]');

SQL> select data from graphql(q'[

  2    employees: emp(where: {job: {_in: ["ANALYST", "PRESIDENT"]}}) {

  3      employeeId: empno

  4      employeeName: ename

  5      job

  6      salary: sal

  7    }

  8* ]');

DATA                                                                         

____________________________________________________________________________ 

{"employeeId":7839,"employeeName":"KING","job":"PRESIDENT","salary":5000}    

{"employeeId":7788,"employeeName":"SCOTT","job":"ANALYST","salary":3000}     

{"employeeId":7902,"employeeName":"FORD","job":"ANALYST","salary":3000}      

SQL> 

給与が1000から4000の間ではない従業員を一覧します。ORDSでは以下のように記述します。

query Employees {
  employees: emp(where: {not: {sal: {btwn: [1000, 4000]}}}) {
    employeeId: empno
    employeeName: ename
    job
    salary: sal
  }
}

以下のレスポンスが返されます。

{
  "data": {
    "employees": [
      {
        "employeeId": 7839,
        "employeeName": "KING",
        "job": "PRESIDENT",
        "salary": 5000
      },
      {
        "employeeId": 7369,
        "employeeName": "SMITH",
        "job": "CLERK",
        "salary": 800
      },
      {
        "employeeId": 7900,
        "employeeName": "JAMES",
        "job": "CLERK",
        "salary": 950
      }
    ]
  }
}

26aiには、notオペレータがないようです。また、btwnオペレータの代わりは_betweenオペレータです。notオペレータがないため、そのままではクエリを移行できません。

26aiでは@whereディレクティブを使用することで、SQLのwhere句に与える条件を指定できます。@whereを使って、以下のようにGraphQLを記述します。

select data from graphql(q'[
  employees: emp @where (sql: "sal not between 1000 and 4000") {
    employeeId: empno
    employeeName: ename
    job
    salary: sal
  }
]');

SQL> select data from graphql(q'[

  2    employees: emp @where (sql: "sal not between 1000 and 4000") {

  3      employeeId: empno

  4      employeeName: ename

  5      job

  6      salary: sal

  7    }

  8* ]');

DATA                                                                         

____________________________________________________________________________ 

{"employeeId":7839,"employeeName":"KING","job":"PRESIDENT","salary":5000}    

{"employeeId":7369,"employeeName":"SMITH","job":"CLERK","salary":800}        

{"employeeId":7900,"employeeName":"JAMES","job":"CLERK","salary":950}        

SQL> 

ジョブがANALYSTで、かつ給与が2000から8000の間にある従業員を一覧します。ORDSでは以下のように記述します。

query Employees {
  employees: emp(
    where: {and: [{job: {eq: "ANALYST"}}, {sal: {btwn: [2000, 8000]}}]}
  ) {
    employeeId: empno
    employeeName: ename
    job
    salary: sal
  }
}

以下のレスポンスが返されます。

{
  "data": {
    "employees": [
      {
        "employeeId": 7788,
        "employeeName": "SCOTT",
        "job": "ANALYST",
        "salary": 3000
      },
      {
        "employeeId": 7902,
        "employeeName": "FORD",
        "job": "ANALYST",
        "salary": 3000
      }
    ]
  }
}

26aiでは以下のように記述します。and、eq、btwnオペレータの代わりに_and、_eq、_betweenを使用します。

select data from graphql(q'[
  employees: emp(
    where: {_and: [{job: {_eq: "ANALYST"}}, {sal: {_between: [2000, 8000]}}]}
  ) {
    employeeId: empno
    employeeName: ename
    job
    salary: sal
  }
]');

SQL> select data from graphql(q'[

  2    employees: emp(

  3      where: {_and: [{job: {_eq: "ANALYST"}}, {sal: {_between: [2000, 8000]}}]}

  4    ) {

  5      employeeId: empno

  6      employeeName: ename

  7      job

  8      salary: sal

  9    }

 10* ]');

DATA                                                                        

___________________________________________________________________________ 

{"employeeId":7788,"employeeName":"SCOTT","job":"ANALYST","salary":3000}    

{"employeeId":7902,"employeeName":"FORD","job":"ANALYST","salary":3000}     

SQL> 

ジョブがANALYSTまたはPRESIDENTの従業員を一覧します。ORDSでは以下のように記述します。

query Employees {
  employees: emp(where: {or: [{job: {eq: "ANALYST"}}, {job: {eq: "PRESIDENT"}}]}) {
    employeeId: empno
    employeeName: ename
    job
    salary: sal
  }
}

以下のレスポンスが返されます。

{
  "data": {
    "employees": [
      {
        "employeeId": 7839,
        "employeeName": "KING",
        "job": "PRESIDENT",
        "salary": 5000
      },
      {
        "employeeId": 7788,
        "employeeName": "SCOTT",
        "job": "ANALYST",
        "salary": 3000
      },
      {
        "employeeId": 7902,
        "employeeName": "FORD",
        "job": "ANALYST",
        "salary": 3000
      }
    ]
  }
}

26aiでは以下のように記述します。or、eqオペレータの代わりに_or、_eqを使用します。

select data from graphql(q'[
  employees: emp(where: {_or: [{job: {_eq: "ANALYST"}}, {job: {_eq: "PRESIDENT"}}]}) {
    employeeId: empno
    employeeName: ename
    job
    salary: sal
  }
]');

SQL> select data from graphql(q'[

  2    employees: emp(where: {_or: [{job: {_eq: "ANALYST"}}, {job: {_eq: "PRESIDENT"}}]}) {

  3      employeeId: empno

  4      employeeName: ename

  5      job

  6      salary: sal

  7    }

  8* ]');

DATA                                                                         

____________________________________________________________________________ 

{"employeeId":7839,"employeeName":"KING","job":"PRESIDENT","salary":5000}    

{"employeeId":7788,"employeeName":"SCOTT","job":"ANALYST","salary":3000}     

{"employeeId":7902,"employeeName":"FORD","job":"ANALYST","salary":3000}      

SQL> 

ジョブがPRESIDENTの従業員の部下、つまり社長直轄の部下を一覧します。ORDSでは以下のように記述できます。

{
  employees: emp {
    employeeId: empno
    employeeName: ename
    job
    salary: sal
    manager: mgr_emp (where: {job: {eq: "PRESIDENT"}}) {
      employeeId: empno
      employeeName: ename
      job
    }
  }
}

以下のレスポンスが返されます。

{
  "data": {
    "employees": [
      {
        "employeeId": 7698,
        "employeeName": "BLAKE",
        "job": "MANAGER",
        "salary": 2850,
        "manager": [
          {
            "employeeId": 7839,
            "employeeName": "KING",
            "job": "PRESIDENT"
          }
        ]
      },
      {
        "employeeId": 7782,
        "employeeName": "CLARK",
        "job": "MANAGER",
        "salary": 2450,
        "manager": [
          {
            "employeeId": 7839,
            "employeeName": "KING",
            "job": "PRESIDENT"
          }
        ]
      },
      {
        "employeeId": 7566,
        "employeeName": "JONES",
        "job": "MANAGER",
        "salary": 2975,
        "manager": [
          {
            "employeeId": 7839,
            "employeeName": "KING",
            "job": "PRESIDENT"
          }
        ]
      }
    ]
  }
}

26aiでほぼ等価な記述は以下になります。ORDSの場合は上司がPRESIDENTである従業員として、３行が返されました。26aiではすべての従業員が返され、上司がPRESIDENTの場合に限り、上司の情報が含まれています。

select data from graphql(q'[
  employees: emp {
    employeeId: empno
    employeeName: ename
    job
    salary: sal
    manager: emp (where: {job: {_eq: "PRESIDENT"}}) @link (from: "MGR", to: "EMPNO") {
      employeeId: empno
      employeeName: ename
      job
    }
  }
]');

SQL> select data from graphql(q'[

  2    employees: emp {

  3      employeeId: empno

  4      employeeName: ename

  5      job

  6      salary: sal

  7      manager: emp (where: {job: {_eq: "PRESIDENT"}}) @link (from: "MGR", to: "EMPNO") {

  8        employeeId: empno

  9        employeeName: ename

 10        job

 11      }

 12    }

 13* ]');

DATA                                                                                                                                              

_________________________________________________________________________________________________________________________________________________ 

{"employeeId":7839,"employeeName":"KING","job":"PRESIDENT","salary":5000,"manager":{}}                                                            

{"employeeId":7698,"employeeName":"BLAKE","job":"MANAGER","salary":2850,"manager":{"employeeId":7839,"employeeName":"KING","job":"PRESIDENT"}}    

{"employeeId":7782,"employeeName":"CLARK","job":"MANAGER","salary":2450,"manager":{"employeeId":7839,"employeeName":"KING","job":"PRESIDENT"}}    

{"employeeId":7566,"employeeName":"JONES","job":"MANAGER","salary":2975,"manager":{"employeeId":7839,"employeeName":"KING","job":"PRESIDENT"}}    

{"employeeId":7788,"employeeName":"SCOTT","job":"ANALYST","salary":3000,"manager":{}}                                                             

{"employeeId":7902,"employeeName":"FORD","job":"ANALYST","salary":3000,"manager":{}}                                                              

{"employeeId":7369,"employeeName":"SMITH","job":"CLERK","salary":800,"manager":{}}                                                                

{"employeeId":7499,"employeeName":"ALLEN","job":"SALESMAN","salary":1600,"manager":{}}                                                            

{"employeeId":7521,"employeeName":"WARD","job":"SALESMAN","salary":1250,"manager":{}}                                                             

{"employeeId":7654,"employeeName":"MARTIN","job":"SALESMAN","salary":1250,"manager":{}}                                                           

{"employeeId":7844,"employeeName":"TURNER","job":"SALESMAN","salary":1500,"manager":{}}                                                           

{"employeeId":7876,"employeeName":"ADAMS","job":"CLERK","salary":1100,"manager":{}}                                                               

{"employeeId":7900,"employeeName":"JAMES","job":"CLERK","salary":950,"manager":{}}                                                                

{"employeeId":7934,"employeeName":"MILLER","job":"CLERK","salary":1300,"manager":{}}                                                              

14行が選択されました。 

SQL> 

GraphQLの標準には規定されていない部分であり、実装に依存して動作が異なっています。あらかじめ、動作を理解してGraphQLのクエリを記述する必要があります。

以下のようにSELECT文を記述することで、検索結果を上司が存在する従業員に限定できます。

select data from graphql(q'[
  employees: emp {
    employeeId: empno
    employeeName: ename
    job
    salary: sal
    manager: emp (where: {job: {_eq: "PRESIDENT"}}) @link (from: "MGR", to: "EMPNO") {
      employeeId: empno
      employeeName: ename
      job
    }
  }
]')
where json_exists(data, '$.manager');

SQL> select data from graphql(q'[

  2    employees: emp {

  3      employeeId: empno

  4      employeeName: ename

  5      job

  6      salary: sal

  7      manager: emp (where: {job: {_eq: "PRESIDENT"}}) @link (from: "MGR", to: "EMPNO") {

  8        employeeId: empno

  9        employeeName: ename

 10        job

 11      }

 12    }

 13  ]')

 14* where json_exists(data, '$.manager');

DATA                                                                                                                                              

_________________________________________________________________________________________________________________________________________________ 

{"employeeId":7698,"employeeName":"BLAKE","job":"MANAGER","salary":2850,"manager":{"employeeId":7839,"employeeName":"KING","job":"PRESIDENT"}}    

{"employeeId":7782,"employeeName":"CLARK","job":"MANAGER","salary":2450,"manager":{"employeeId":7839,"employeeName":"KING","job":"PRESIDENT"}}    

{"employeeId":7566,"employeeName":"JONES","job":"MANAGER","salary":2975,"manager":{"employeeId":7839,"employeeName":"KING","job":"PRESIDENT"}}    

SQL> 

1980年から1981年にかけて採用された従業員を一覧します。ORDSでは以下のように記述します。検索対象であるhiredateのデータ型がDateの場合は、日付にYYYY-MM-DDの書式を使えます。

{
  employees: emp(where: {hiredate: {btwn: ["1980-01-01", "1981-01-01"]}}) {
    employeeId: empno
    employeeName: ename
    job
    salary: sal
    hireDate: hiredate
  }
}

以下のレスポンスが返されます。

{
  "data": {
    "employees": [
      {
        "employeeId": 7369,
        "employeeName": "SMITH",
        "job": "CLERK",
        "salary": 800,
        "hireDate": "1980-12-17T00:00:00Z"
      }
    ]
  }
}

26aiでは以下のように記述します。btwnオペレータの代わりに_betweenを使用します。日付の書式にはNLS_DATE_FORMATが採用されているはずです。

select data from graphql(q'[
  employees: emp(where: {hiredate: {_between: ["1980-01-01", "1981-01-01"]}}) {
    employeeId: empno
    employeeName: ename
    job
    salary: sal
    hireDate: hiredate
  }
]');

SQL> select data from graphql(q'[

  2    employees: emp(where: {hiredate: {_between: ["1980-01-01", "1981-01-01"]}}) {

  3      employeeId: empno

  4      employeeName: ename

  5      job

  6      salary: sal

  7      hireDate: hiredate

  8    }

  9* ]');

DATA                                                                                                      

_________________________________________________________________________________________________________ 

{"employeeId":7369,"employeeName":"SMITH","job":"CLERK","salary":800,"hireDate":"1980-12-17T00:00:00"}    

SQL> 

@whereディレクティブで条件を指定することで、任意の書式フォーマットを採用できます。以下のSELECT文でも検索結果は変わりません。

select data from graphql(q'[
  employees: emp @where(sql: "hiredate between to_date('1980-01-01','YYYY-MM-DD') and to_date('1981-01-01','YYYY-MM-DD')") {
    employeeId: empno
    employeeName: ename
    job
    salary: sal
    hireDate: hiredate
  }
]');

従業員番号の降順でソートします。ORDSでは以下のように記述します。

{
  employees: emp(sort: [{empno: "desc"}]) {
    employeeId: empno
    employeeName: ename
    salary: sal
  }
}

以下のレスポンスが返されます。

{
  "data": {
    "employees": [
      {
        "employeeId": 7934,
        "employeeName": "MILLER",
        "salary": 1300
      },
      {
        "employeeId": 7902,
        "employeeName": "FORD",
        "salary": 3000
      },
      {
        "employeeId": 7900,
        "employeeName": "JAMES",
        "salary": 950
      },
      {
        "employeeId": 7876,
        "employeeName": "ADAMS",
        "salary": 1100
      },
      {
        "employeeId": 7844,
        "employeeName": "TURNER",
        "salary": 1500
      },
      {
        "employeeId": 7839,
        "employeeName": "KING",
        "salary": 5000
      },
      {
        "employeeId": 7788,
        "employeeName": "SCOTT",
        "salary": 3000
      },
      {
        "employeeId": 7782,
        "employeeName": "CLARK",
        "salary": 2450
      },
      {
        "employeeId": 7698,
        "employeeName": "BLAKE",
        "salary": 2850
      },
      {
        "employeeId": 7654,
        "employeeName": "MARTIN",
        "salary": 1250
      },
      {
        "employeeId": 7566,
        "employeeName": "JONES",
        "salary": 2975
      },
      {
        "employeeId": 7521,
        "employeeName": "WARD",
        "salary": 1250
      },
      {
        "employeeId": 7499,
        "employeeName": "ALLEN",
        "salary": 1600
      },
      {
        "employeeId": 7369,
        "employeeName": "SMITH",
        "salary": 800
      }
    ]
  }
}

26aiで同様のソートを行うために以下を記述しましたが、@orderbyディレクティブは無視されました。

select data from graphql(q'[
  employees: emp @orderby (sql: "empno desc") {
    employeeId: empno
    employeeName: ename
    salary: sal
  }
]');

SQL> select data from graphql(q'[

  2    employees: emp @orderby (sql: "empno desc") {

  3      employeeId: empno

  4      employeeName: ename

  5      salary: sal

  6    }

  7* ]');

DATA                                                         

____________________________________________________________ 

{"employeeId":7839,"employeeName":"KING","salary":5000}      

{"employeeId":7698,"employeeName":"BLAKE","salary":2850}     

{"employeeId":7782,"employeeName":"CLARK","salary":2450}     

{"employeeId":7566,"employeeName":"JONES","salary":2975}     

{"employeeId":7788,"employeeName":"SCOTT","salary":3000}     

{"employeeId":7902,"employeeName":"FORD","salary":3000}      

{"employeeId":7369,"employeeName":"SMITH","salary":800}      

{"employeeId":7499,"employeeName":"ALLEN","salary":1600}     

{"employeeId":7521,"employeeName":"WARD","salary":1250}      

{"employeeId":7654,"employeeName":"MARTIN","salary":1250}    

{"employeeId":7844,"employeeName":"TURNER","salary":1500}    

{"employeeId":7876,"employeeName":"ADAMS","salary":1100}     

{"employeeId":7900,"employeeName":"JAMES","salary":950}      

{"employeeId":7934,"employeeName":"MILLER","salary":1300}    

14行が選択されました。 

SQL> 

ただし、ドキュメントに「 @orderby directive should be used on those object fields which return an array.」とあり、empへの指定はこの条件（配列を返す属性）には合っていません。

より、ドキュメントの条件に合致している以下のGraqhQLでは、指定通りに部下が従業員番号の降順でソートされます。

select data from graphql(q'[
  employees: emp {
    employeeId: empno
    employeeName: ename
    salary: sal
    manager: emp @link (from: "EMPNO", to: "MGR") @orderby (sql: "empno desc")
    {
        employeeId: empno
        employeeName: ename
    }
  }
]');

SQL> select data from graphql(q'[

  2    employees: emp {

  3      employeeId: empno

  4      employeeName: ename

  5      salary: sal

  6      manager: emp @link (from: "EMPNO", to: "MGR") @orderby (sql: "empno desc")

  7      {

  8          employeeId: empno

  9          employeeName: ename

 10      }

 11    }

 12* ]');

DATA                                                                                                                                                                                                                                                                                            

_______________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________ 

{"employeeId":7839,"employeeName":"KING","salary":5000,"manager":[{"employeeId":7782,"employeeName":"CLARK"},{"employeeId":7698,"employeeName":"BLAKE"},{"employeeId":7566,"employeeName":"JONES"}]}                                                                                            

{"employeeId":7698,"employeeName":"BLAKE","salary":2850,"manager":[{"employeeId":7900,"employeeName":"JAMES"},{"employeeId":7844,"employeeName":"TURNER"},{"employeeId":7654,"employeeName":"MARTIN"},{"employeeId":7521,"employeeName":"WARD"},{"employeeId":7499,"employeeName":"ALLEN"}]}    

{"employeeId":7782,"employeeName":"CLARK","salary":2450,"manager":[{"employeeId":7934,"employeeName":"MILLER"}]}                                                                                                                                                                                

{"employeeId":7566,"employeeName":"JONES","salary":2975,"manager":[{"employeeId":7902,"employeeName":"FORD"},{"employeeId":7788,"employeeName":"SCOTT"}]}                                                                                                                                       

{"employeeId":7788,"employeeName":"SCOTT","salary":3000,"manager":[{"employeeId":7876,"employeeName":"ADAMS"}]}                                                                                                                                                                                 

{"employeeId":7902,"employeeName":"FORD","salary":3000,"manager":[{"employeeId":7369,"employeeName":"SMITH"}]}                                                                                                                                                                                  

{"employeeId":7369,"employeeName":"SMITH","salary":800,"manager":[]}                                                                                                                                                                                                                            

{"employeeId":7499,"employeeName":"ALLEN","salary":1600,"manager":[]}                                                                                                                                                                                                                           

{"employeeId":7521,"employeeName":"WARD","salary":1250,"manager":[]}                                                                                                                                                                                                                            

{"employeeId":7654,"employeeName":"MARTIN","salary":1250,"manager":[]}                                                                                                                                                                                                                          

{"employeeId":7844,"employeeName":"TURNER","salary":1500,"manager":[]}                                                                                                                                                                                                                          

{"employeeId":7876,"employeeName":"ADAMS","salary":1100,"manager":[]}                                                                                                                                                                                                                           

{"employeeId":7900,"employeeName":"JAMES","salary":950,"manager":[]}                                                                                                                                                                                                                            

{"employeeId":7934,"employeeName":"MILLER","salary":1300,"manager":[]}                                                                                                                                                                                                                          

14行が選択されました。 

SQL> 

以下のように、GraphQLの外側にORDER BY句を付けることで、従業員番号でソートできます。

select data from graphql(q'[
  employees: emp {
    employeeId: empno
    employeeName: ename
    salary: sal
    manager: emp @link (from: "EMPNO", to: "MGR") @orderby (sql: "empno desc")
    {
        employeeId: empno
        employeeName: ename
    }
  }
]')
order by data."employeeId" desc;

SQL> select data from graphql(q'[

  2    employees: emp {

  3      employeeId: empno

  4      employeeName: ename

  5      salary: sal

  6      manager: emp @link (from: "EMPNO", to: "MGR") @orderby (sql: "empno desc")

  7      {

  8          employeeId: empno

  9          employeeName: ename

 10      }

 11    }

 12  ]')

 13* order by data."employeeId" desc;

DATA                                                                                                                                                                                                                                                                                            

_______________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________ 

{"employeeId":7934,"employeeName":"MILLER","salary":1300,"manager":[]}                                                                                                                                                                                                                          

{"employeeId":7902,"employeeName":"FORD","salary":3000,"manager":[{"employeeId":7369,"employeeName":"SMITH"}]}                                                                                                                                                                                  

{"employeeId":7900,"employeeName":"JAMES","salary":950,"manager":[]}                                                                                                                                                                                                                            

{"employeeId":7876,"employeeName":"ADAMS","salary":1100,"manager":[]}                                                                                                                                                                                                                           

{"employeeId":7844,"employeeName":"TURNER","salary":1500,"manager":[]}                                                                                                                                                                                                                          

{"employeeId":7839,"employeeName":"KING","salary":5000,"manager":[{"employeeId":7782,"employeeName":"CLARK"},{"employeeId":7698,"employeeName":"BLAKE"},{"employeeId":7566,"employeeName":"JONES"}]}                                                                                            

{"employeeId":7788,"employeeName":"SCOTT","salary":3000,"manager":[{"employeeId":7876,"employeeName":"ADAMS"}]}                                                                                                                                                                                 

{"employeeId":7782,"employeeName":"CLARK","salary":2450,"manager":[{"employeeId":7934,"employeeName":"MILLER"}]}                                                                                                                                                                                

{"employeeId":7698,"employeeName":"BLAKE","salary":2850,"manager":[{"employeeId":7900,"employeeName":"JAMES"},{"employeeId":7844,"employeeName":"TURNER"},{"employeeId":7654,"employeeName":"MARTIN"},{"employeeId":7521,"employeeName":"WARD"},{"employeeId":7499,"employeeName":"ALLEN"}]}    

{"employeeId":7654,"employeeName":"MARTIN","salary":1250,"manager":[]}                                                                                                                                                                                                                          

{"employeeId":7566,"employeeName":"JONES","salary":2975,"manager":[{"employeeId":7902,"employeeName":"FORD"},{"employeeId":7788,"employeeName":"SCOTT"}]}                                                                                                                                       

{"employeeId":7521,"employeeName":"WARD","salary":1250,"manager":[]}                                                                                                                                                                                                                            

{"employeeId":7499,"employeeName":"ALLEN","salary":1600,"manager":[]}                                                                                                                                                                                                                           

{"employeeId":7369,"employeeName":"SMITH","salary":800,"manager":[]}                                                                                                                                                                                                                            

14行が選択されました。 

SQL> 

部門IDを降順、給与を昇順でソートします。ORDSでは以下のように記述します。

{
  employees: emp(sort: [{deptno: "desc"}, {sal: "asc"}]) {
    employeeId: empno
    employeeName: ename
    salary: sal
    departmentId: deptno
  }
}

以下のレスポンスが返されます。

{
  "data": {
    "employees": [
      {
        "employeeId": 7900,
        "employeeName": "JAMES",
        "salary": 950,
        "departmentId": 30
      },
      {
        "employeeId": 7654,
        "employeeName": "MARTIN",
        "salary": 1250,
        "departmentId": 30
      },
      {
        "employeeId": 7521,
        "employeeName": "WARD",
        "salary": 1250,
        "departmentId": 30
      },
      {
        "employeeId": 7844,
        "employeeName": "TURNER",
        "salary": 1500,
        "departmentId": 30
      },
      {
        "employeeId": 7499,
        "employeeName": "ALLEN",
        "salary": 1600,
        "departmentId": 30
      },
      {
        "employeeId": 7698,
        "employeeName": "BLAKE",
        "salary": 2850,
        "departmentId": 30
      },
      {
        "employeeId": 7369,
        "employeeName": "SMITH",
        "salary": 800,
        "departmentId": 20
      },
      {
        "employeeId": 7876,
        "employeeName": "ADAMS",
        "salary": 1100,
        "departmentId": 20
      },
      {
        "employeeId": 7566,
        "employeeName": "JONES",
        "salary": 2975,
        "departmentId": 20
      },
      {
        "employeeId": 7902,
        "employeeName": "FORD",
        "salary": 3000,
        "departmentId": 20
      },
      {
        "employeeId": 7788,
        "employeeName": "SCOTT",
        "salary": 3000,
        "departmentId": 20
      },
      {
        "employeeId": 7934,
        "employeeName": "MILLER",
        "salary": 1300,
        "departmentId": 10
      },
      {
        "employeeId": 7782,
        "employeeName": "CLARK",
        "salary": 2450,
        "departmentId": 10
      },
      {
        "employeeId": 7839,
        "employeeName": "KING",
        "salary": 5000,
        "departmentId": 10
      }
    ]
  }
}

26aiではトップレベルでは@orderby句が効かないため、以下のように記述します。

select data from graphql(q'[
  employees: emp {
    employeeId: empno
    employeeName: ename
    salary: sal
    departmentId: deptno
  }
]')
order by data."departmentId" desc, data."salary" asc;

SQL> select data from graphql(q'[

  2    employees: emp {

  3      employeeId: empno

  4      employeeName: ename

  5      salary: sal

  6      departmentId: deptno

  7    }

  8  ]')

  9* order by data."departmentId" desc, data."salary" asc;

DATA                                                                           

______________________________________________________________________________ 

{"employeeId":7900,"employeeName":"JAMES","salary":950,"departmentId":30}      

{"employeeId":7521,"employeeName":"WARD","salary":1250,"departmentId":30}      

{"employeeId":7654,"employeeName":"MARTIN","salary":1250,"departmentId":30}    

{"employeeId":7844,"employeeName":"TURNER","salary":1500,"departmentId":30}    

{"employeeId":7499,"employeeName":"ALLEN","salary":1600,"departmentId":30}     

{"employeeId":7698,"employeeName":"BLAKE","salary":2850,"departmentId":30}     

{"employeeId":7369,"employeeName":"SMITH","salary":800,"departmentId":20}      

{"employeeId":7876,"employeeName":"ADAMS","salary":1100,"departmentId":20}     

{"employeeId":7566,"employeeName":"JONES","salary":2975,"departmentId":20}     

{"employeeId":7788,"employeeName":"SCOTT","salary":3000,"departmentId":20}     

{"employeeId":7902,"employeeName":"FORD","salary":3000,"departmentId":20}      

{"employeeId":7934,"employeeName":"MILLER","salary":1300,"departmentId":10}    

{"employeeId":7782,"employeeName":"CLARK","salary":2450,"departmentId":10}     

{"employeeId":7839,"employeeName":"KING","salary":5000,"departmentId":10}      

14行が選択されました。 

SQL> 

ORDSのGraphQLではlimitとoffsetという指定を使って、ページ送りを実装できます。

query Employees {
  employees: emp(limit: 3, offset: 5) {
    employeeId: empno
    employeeName: ename
  }
}

以下のレスポンスが返されます。

{
  "data": {
    "employees": [
      {
        "employeeId": 7902,
        "employeeName": "FORD"
      },
      {
        "employeeId": 7369,
        "employeeName": "SMITH"
      },
      {
        "employeeId": 7499,
        "employeeName": "ALLEN"
      }
    ]
  }
}

26aiで同等の設定は見つかりませんでした。強いて記述すると、以下のようなSELECT文で同等のページングを実装できます。

select data from (
    select data, row_number() over (order by null) n from (
        -- 元のGraphQL
        select data from graphql(q'[
            employees: emp {
                employeeId: empno
                employeeName: ename
            }
        ]')
        --
    )
) where n between (5+1) and (5+3);

SQL> select data from (

  2      select data, row_number() over (order by null) n from (

  3          -- 元のGraphQL

  4          select data from graphql(q'[

  5              employees: emp {

  6                  employeeId: empno

  7                  employeeName: ename

  8              }

  9          ]')

 10          --

 11      )

 12* ) where n between (5+1) and (5+3);

DATA                                          

_____________________________________________ 

{"employeeId":7902,"employeeName":"FORD"}     

{"employeeId":7369,"employeeName":"SMITH"}    

{"employeeId":7499,"employeeName":"ALLEN"}    

SQL> 

ORDSでは、以下のようにソートとページングを組み合わせることができます。

{
  employees: emp(sort: [{empno: "DESC"}], limit: 3, offset: 2) {
    employeeId: empno
    employeeName: ename
    salary: sal
    departmentId: deptno
  }
}

以下のレスポンスが返されます。

{
  "data": {
    "employees": [
      {
        "employeeId": 7900,
        "employeeName": "JAMES",
        "salary": 950,
        "departmentId": 30
      },
      {
        "employeeId": 7876,
        "employeeName": "ADAMS",
        "salary": 1100,
        "departmentId": 20
      },
      {
        "employeeId": 7844,
        "employeeName": "TURNER",
        "salary": 1500,
        "departmentId": 30
      }
    ]
  }
}

26aiで同等の処理を行なうSQLは、以下のように記述できます。

select data from (
    select data, row_number() over (order by json_value(data, '$.employeeId') desc) n from (
        -- 元のGraphQL
        select data from graphql(q'[
            employees: emp {
                employeeId: empno
                employeeName: ename
                salary: sal
                departmentId: deptno
            }
        ]')
    )
) where n between (2+1) and (2+3);

SQL> select data from (

  2      select data, row_number() over (order by json_value(data, '$.employeeId') desc) n from (

  3          -- 元のGraphQL

  4          select data from graphql(q'[

  5              employees: emp {

  6                  employeeId: empno

  7                  employeeName: ename

  8                  salary: sal

  9                  departmentId: deptno

 10              }

 11          ]')

 12      )

 13* ) where n between (2+1) and (2+3);

DATA                                                                           

______________________________________________________________________________ 

{"employeeId":7900,"employeeName":"JAMES","salary":950,"departmentId":30}      

{"employeeId":7876,"employeeName":"ADAMS","salary":1100,"departmentId":20}     

{"employeeId":7844,"employeeName":"TURNER","salary":1500,"departmentId":30}    

SQL> 

ORDSのドキュメントの14.7.2 Example: Pagination in Nested Typesに、ネストしたオブジェクトにたいしてlimit指定をしているGraphQLが記載されています。

{
  employees: emp(limit: 1) {
    employeeId: empno
    employeeName: ename
    job
    salary: sal
    manager: emp_mgr(limit: 2) {
      employeeId: empno
      employeeName: ename
    }
  }
}

以下のレスポンスが返されます。

{
  "data": {
    "employees": [
      {
        "employeeId": 7839,
        "employeeName": "KING",
        "job": "PRESIDENT",
        "salary": 5000,
        "manager": [
          {
            "employeeId": 7698,
            "employeeName": "BLAKE"
          },
          {
            "employeeId": 7782,
            "employeeName": "CLARK"
          }
        ]
      }
    ]
  }
}

26aiで同等の処理を行なうSQLは、以下のように記述できます。

select data from graphql(q'[
    employees: emp {
        employeeId: empno
        employeeName: ename
        job
        salary: sal
        manager: emp @where (sql: "rownum < 3") @link (from: "EMPNO", to: "MGR") {
            employeeId: empno
            employeeName: ename
        }
    }
]')
fetch first 1 rows only;

SQL> select data from graphql(q'[

  2      employees: emp {

  3          employeeId: empno

  4          employeeName: ename

  5          job

  6          salary: sal

  7          manager: emp @where (sql: "rownum < 3") @link (from: "EMPNO", to: "MGR") {

  8              employeeId: empno

  9              employeeName: ename

 10          }

 11      }

 12  ]')

 13* fetch first 1 rows only;

DATA                                                                                                                                                                           

______________________________________________________________________________________________________________________________________________________________________________ 

{"employeeId":7839,"employeeName":"KING","job":"PRESIDENT","salary":5000,"manager":[{"employeeId":7698,"employeeName":"BLAKE"},{"employeeId":7782,"employeeName":"CLARK"}]}    

SQL> 

ORDSではGraphQLに変数を使うことができます。GraphQLとして以下を記述します。

query Employees($job_id: String, $min_salary: Int, $max_salary: Int) {
  employees: emp(
    where: {and: [{job: {eq: $job_id}}, {sal: {btwn: [$min_salary, $max_salary]}}]}
  ) {
    employeeId: empno
    managerId: mgr
    commission_pct: comm
    departmentId: deptno
    salary: sal
    employeeName: ename
    job
    hireDate: hiredate
  }
}

以下のJSONで、変数に値を割り当てます。

{
  "job_id": "ANALYST",
  "min_salary": 1000,
  "max_salary": 6000
}

GraphiQLの画面では、以下のように指定します。

curlでの呼び出しは以下になります。 GraphQLのフォーマットに準拠した呼び出し形式です。

curl --request POST \
  --url http://localhost:8181/ords/apexdev/_/graphql \
  --header 'Content-Type: application/json' \
  --header 'User-Agent: insomnia/12.0.0' \
  --data '{
  "query": "query Employees($job_id: String, $min_salary: Int, $max_salary: Int) {\n  employees: emp(\n    where: {and: [{job: {eq: $job_id}}, {sal: {btwn: [$min_salary, $max_salary]}}]}\n  ) {\n    employeeId: empno\n    managerId: mgr\n    commission_pct: comm\n    departmentId: deptno\n    salary: sal\n    employeeName: ename\n    job\n    hireDate: hiredate\n  }\n}",
  "operationName": "Employees",
  "variables": {
    "job_id": "ANALYST",
    "min_salary": 1000,
    "max_salary": 6000
  }
}'

% curl --request POST \

  --url http://localhost:8181/ords/apexdev/_/graphql \

  --header 'Content-Type: application/json' \

  --header 'User-Agent: insomnia/12.0.0' \

  --data '{

  "query": "query Employees($job_id: String, $min_salary: Int, $max_salary: Int) {\n  employees: emp(\n    where: {and: [{job: {eq: $job_id}}, {sal: {btwn: [$min_salary, $max_salary]}}]}\n  ) {\n    employeeId: empno\n    managerId: mgr\n    commission_pct: comm\n    departmentId: deptno\n    salary: sal\n    employeeName: ename\n    job\n    hireDate: hiredate\n  }\n}",

  "operationName": "Employees",

  "variables": {

    "job_id": "ANALYST",

    "min_salary": 1000,

    "max_salary": 6000

  }

}'

{"data":{"employees":[{"employeeId":7788,"managerId":7566,"commission_pct":null,"departmentId":20,"salary":3000,"employeeName":"SCOTT","job":"ANALYST","hireDate":"1982-12-09T00:00:00Z"},{"employeeId":7902,"managerId":7566,"commission_pct":null,"departmentId":20,"salary":3000,"employeeName":"FORD","job":"ANALYST","hireDate":"1981-12-03T00:00:00Z"}]}}                                                              

% 

26aiで同等の処理を行なうSQLは、以下のように記述できます。

select data from graphql(q'[
  employees: emp(
    where: {_and: [{job: {_eq: $job_id}}, {sal: {_between: [$min_salary, $max_salary]}}]}
  ) {
    employeeId: empno
    managerId: mgr
    commission_pct: comm
    departmentId: deptno
    salary: sal
    employeeName: ename
    job
    hireDate: hiredate
  }
]' passing 'ANALYST' as "job_id", 1000 as "min_salary", 6000 as "max_salary" );

SQL> select data from graphql(q'[

  2    employees: emp(

  3      where: {_and: [{job: {_eq: $job_id}}, {sal: {_between: [$min_salary, $max_salary]}}]}

  4    ) {

  5      employeeId: empno

  6      managerId: mgr

  7      commission_pct: comm

  8      departmentId: deptno

  9      salary: sal

 10      employeeName: ename

 11      job

 12      hireDate: hiredate

 13    }

 14* ]' passing 'ANALYST' as "job_id", 1000 as "min_salary", 6000 as "max_salary" );

DATA                                                                                                                                                                  

_____________________________________________________________________________________________________________________________________________________________________ 

{"employeeId":7788,"managerId":7566,"commission_pct":null,"departmentId":20,"salary":3000,"employeeName":"SCOTT","job":"ANALYST","hireDate":"1982-12-09T00:00:00"}    

{"employeeId":7902,"managerId":7566,"commission_pct":null,"departmentId":20,"salary":3000,"employeeName":"FORD","job":"ANALYST","hireDate":"1981-12-03T00:00:00"}     

SQL> 

この他に、Oracle AI Database 26aiでのGraphQLの動的呼び出しについて確認しました。

2025年11月26日追記：

SQL Language Reference, Release 26

GRAPHQL Table Function

Purpose

....

The GRAPHQL table function evaluates the embedded GraphQL query that is passed in as a string literal. 

....

https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/graphql-table-function.html

GraphQL queryはstring literalとして渡すと記載されているので、動的に文字列を渡すとエラーが発生するのは仕様です。

追記終わり

GRAPHQL表関数は、クエリ文字列を引数として受け取る表関数なので、以下のように、クエリ文字列を変数として渡せるように思えます。

declare
  l_cursor sys_refcursor;
  l_graphql_query varchar2(3000);
  l_data clob;
begin
  l_graphql_query := q'[
    employees: emp {
      empno
      ename
    }
]';
  open l_cursor for
    select data from graphql(l_graqphql_query);

  loop
    fetch l_cursor into l_data;
    exit when l_cursor%notfound;
    dbms_output.put_line(l_data);
  end loop;
  close l_cursor;
end;
/

しかし、実際には原因がわからないエラーが発生します。

SQL> declare

  2    l_cursor sys_refcursor;

  3    l_graphql_query varchar2(3000);

  4    l_data clob;

  5  begin

  6    l_graphql_query := q'[

  7      employees: emp {

  8        empno

  9        ename

 10      }

 11  ]';

 12    open l_cursor for

 13      select data from graphql(l_graqphql_query);

 14  

 15    loop

 16      fetch l_cursor into l_data;

 17      exit when l_cursor%notfound;

 18      dbms_output.put_line(l_data);

 19    end loop;

 20    close l_cursor;

 21  end;

 22* /

declare

*

行でエラーが発生しました 1:

ORA-06550: 行13、列46:

PL/SQL: ORA-24558: 入力文字列で構文エラーが発生しました

ORA-06550: 行13、列5:

PL/SQL: SQL Statement ignored

https://docs.oracle.com/error-help/db/ora-06550/

More Details :

https://docs.oracle.com/error-help/db/ora-06550/

SQL> 

GraphQLのクエリ文字列は一切変えず、SELECT文全体を動的SQLとして実行すると、期待通りに動作します。

declare
    l_cursor sys_refcursor;
    l_graphql_query varchar2(3000);
    l_query varchar2(4000);
    l_data clob;
begin
    l_graphql_query := q'[
        employees: emp {
            empno
            ename
        }
]';
    l_query := 'select data from graphql(''' || l_graphql_query || ''')';
    open l_cursor for l_query;
    loop
        fetch l_cursor into l_data;
        exit when l_cursor%notfound;
        dbms_output.put_line(l_data);
    end loop;
    close l_cursor;
end;
/

SQL> set serveroutput  on

SQL> declare

  2      l_cursor sys_refcursor;

  3      l_graphql_query varchar2(3000);

  4      l_query varchar2(4000);

  5      l_data clob;

  6  begin

  7      l_graphql_query := q'[

  8          employees: emp {

  9              empno

 10              ename

 11          }

 12  ]';

 13      l_query := 'select data from graphql(''' || l_graphql_query || ''')';

 14      open l_cursor for l_query;

 15      loop

 16          fetch l_cursor into l_data;

 17          exit when l_cursor%notfound;

 18          dbms_output.put_line(l_data);

 19      end loop;

 20      close l_cursor;

 21  end;

 22* /

{"empno":7839,"ename":"KING"}

{"empno":7698,"ename":"BLAKE"}

{"empno":7782,"ename":"CLARK"}

{"empno":7566,"ename":"JONES"}

{"empno":7788,"ename":"SCOTT"}

{"empno":7902,"ename":"FORD"}

{"empno":7369,"ename":"SMITH"}

{"empno":7499,"ename":"ALLEN"}

{"empno":7521,"ename":"WARD"}

{"empno":7654,"ename":"MARTIN"}

{"empno":7844,"ename":"TURNER"}

{"empno":7876,"ename":"ADAMS"}

{"empno":7900,"ename":"JAMES"}

{"empno":7934,"ename":"MILLER"}

PL/SQLプロシージャが正常に完了しました。

SQL> 

上記のようなエラーが発生するため、GRAPHQL表関数に与えるクエリ文字列を変数として渡すことはできないようです。これが仕様なのかもしれませんが、使いにくいことは確かです。

また、GraphQLのクエリには変数を含めることができますが、passing句にバインド変数を指定するとエラーが発生します。

以下のスクリプトで動作を確認しました。

var job_id varchar2(20);
begin
:job_id := 'ANALYST';
end;
/
print job_id

select * from emp where job = :job_id;

rem works as expected.
select data from graphql(q'[
    emp(
       check: {job: {_eq: $job_id}}
    ) {
        empno
        ename
    }
]' passing 'ANALYST' as "job_id");

rem failed with ORA-600, literal only?
select data from graphql(q'[
    emp(
       check: {job: {_eq: $job_id}}
    ) {
        empno
        ename
    }
]' passing :job_id as "job_id");

最初にバインド変数JOB_IDを定義し、値としてANALYSTを設定します。

SQL> var job_id varchar2(20);

SQL> begin

:job_id := 'ANALYST';

end;

/  2    3    4  

PL/SQL procedure successfully completed.

SQL> print job_id

JOB_ID

--------------------------------

ANALYST

SQL> 

バインド変数は使わず、GraphQLによる問い合わせを実行します。正常に検索できます。

SQL> select data from graphql(q'[

    emp(

       check: {job: {_eq: $job_id}}

    ) {

        empno

        ename

    }

]' passing 'ANALYST' as "job_id");  2    3    4    5    6    7    8  

DATA

--------------------------------------------------------------------------------

{"empno":7788,"ename":"SCOTT"}

{"empno":7902,"ename":"FORD"}

SQL> 

"job_id"の割り当てにバインド変数を使用します。エラーが発生し、サーバー・プロセスが終了します。

SQL> set serveroutput on

SQL> select data from graphql(q'[

    emp(

       check: {job: {_eq: $job_id}}

    ) {

        empno

        ename

    }

]' passing :job_id as "job_id");  2    3    4    5    6    7    8  

select data from graphql(q'[

*

ERROR at line 1:

ORA-03113: end-of-file on communication channel

Process ID: 38767

Session ID: 205 Serial number: 12802

Help: https://docs.oracle.com/error-help/db/ora-03113/

ERROR:

ORA-03114: not connected to ORACLE

Help: https://docs.oracle.com/error-help/db/ora-03114/

SQL> 

ドキュメントにはバインド変数が使用できると記述されていないので、これが仕様かもしれません。しかし、使いにくいことは確かです。

GRAPHQL表関数の用途について考えてみます。

JSON Relational Duality ViewをGraphQLで定義するにあたり、GRAPHQL表関数からクエリを実行することで、ビューを作成せずにクエリを確認できる、というのはあるかと思います。しかし、表やビューからJSONを生成できる関数としてPL/SQLのコード中でGRAPHQL表関数を使おうとすると、SELECT文の評価時に与えたGraphQLクエリを変更できなかったり、バインド変数が割り当てられなかったりと、色々と制約があります。

今までは表やビューからJSONを生成するために、JSON_OBJECTやJSON_ARRAYAGGを使って、以下のようなSELECT文を書く必要がありました。

select json_object(
    key 'employeeId'   value e.empno,
    key 'employeeName' value e.ename,
    key 'job'          value e.job,
    key 'salary'       value e.sal,
    key 'member'       value (
        select json_arrayagg(json_object(
            key 'employeeId' value m.empno,
            key 'employeeName' value m.ename,
            key 'job' value m.job
        ))
        from emp m
        where e.empno = m.mgr
    )
)
from emp e;

GraphQL表関数では、以下のように記述できます。静的なSELECT文を置き換える簡易記法として活用できます。

select data from graphql(q'[
    employees: emp {
        employeeId: empno
        employeeName: ename
        job
        salary: sal
        member: emp @link (from: "EMPNO", to: "MGR") [{
            employeeId: empno
            employeeName: ename
            job
       }]
    }
 ]');

今回の記事は以上になります。

完