Miscellaneous functions

The Adp.Misc class contains function for running SQL queries, and working with views and tables.

Misc.list_tables(owner=None)

The above function returns list of tables, owner is the schema name of the tables.None means that the current schema is used.

Misc.list_views(owner=None)

The above function returns list of views, owner is a schema name of the views. None means that the current schema is used.

Misc.drop_table(table_name)

The above function drops the table tableName.

Misc.drop_view(view_name)

The above function drops the view viewName.

Misc.drop_tables(table_names)

The above function drops the tables from the list tableNames.

Misc.drop_views(view_names)

The above function drops the views from the list viewNames.

Misc.run_query(statement, offset=None, limit=None, asof=None)

The above function returns the results of query.

Misc.global_search(search_string, rowstart, numrow, sortBy=[], hide_system_tables=False, hide_private_tables=False, resultapp=None, resultannotation=None)

The above function returns the result of searching databases, tables and another artifacts from the ORDS.

search_string should be in the form "( owner: ADPTEST ) ( type: TABLE ) ( application: DATABASE )"

Misc.get_table_columns(table_name, owner=None, limit=256, offset=0)

This function returns the list of the columns information of the table table_name of the schema owner. If owner is not used or equal to None, the current schema is used.

Misc.get_table_constraints(table_name, owner=None, limit=256, offset=0)

This function returns the list of the constraints of the table table_name of the schema owner. If owner is not used or equal to None, the current schema is used.

Misc.insert_row(table_name, data, mapping=None, owner=None)

This function inserts data data into the table table_name of the schema owner. If owner is not used or equal to None, the current schema is used. Data has a form {"data column" : value,...}. To match data columns with table columns mapping json is used, skip this argument if data contains column names of the table.

Misc.update_row(table_name, data, where_col, mapping=None, owner=None)

This function updates data data from the specified row of the table table_name of the schema owner. If owner is not used or equal to None, the current schema is used. data has a form {"data column" : value,...}, value of where_col contains column name that will be used in where clause. To match data columns with table columns mapping json is used, skip this argument if data contains column names of the table.

Examples of Writeback functions

This section provides the following examples to help you get started using the writeback API:

List of columns of the table
List of constraints of the table
Insert row without mapping
Insert row with mapping
Update row without mapping
Update row with mapping

Before using these examples, you must create an ADP instance and connect to the ORDS.

List of columns of the table

ords.Misc.get_table_columns('PERSONS')



Output:

[

  {

    "column_name": "PERSON_ID",

    "data_type": "NUMBER",

    "nullable": "No",

    "data_default": "\"ADMIN\".\"ISEQ$$_329201\".nextval",

    "column_id": 1,

    "comments": null

  },

  {

    "column_name": "FIRST_NAME",

    "data_type": "VARCHAR2(50 BYTE)",

    "nullable": "No",

    "data_default": null,

    "column_id": 2,

    "comments": null

  },

  {

    "column_name": "LAST_NAME",

    "data_type": "VARCHAR2(50 BYTE)",

    "nullable": "No",

    "data_default": null,

    "column_id": 3,

    "comments": null

  }

]

List of constraints of the table

ords.Misc.get_table_constraints('PERSONS')



Output:



[

  {

    "constraint_name": "SYS_C0056209",

    "constraint_type": "Check",

    "search_condition": "\"PERSON_ID\" IS NOT NULL",

...

  }

]

Insert row without mapping

ords.Misc.insert_row('PERSONS',
      {"PERSON_ID":1, "FIRST_NAME":"John", "LAST_NAME":"Smith"})

Insert row with mapping

The last parameter is a mapping between column aliases and column names.

ords.Misc.insert_row('PERSONS', {"ID":1, "FIRST":"John", "LAST":"Smith"},
      {"ID":"PERSON_ID", "FIRST":"FIRST_NAME", "LAST":"LAST_NAME"})

Update row without mapping

ords.Misc.update_row('PERSONS',
      {"PERSON_ID":1, "FIRST_NAME":"Adam"},  'PERSON_ID')

The Where_col string is a alias name that will construct where clause in the query in the form <where_col> = <value>, where <value> is a value of the corresponding item in the data.

Update row with mapping

The last parameter is a mapping between column aliases and column names. The Where_col string is a alias name that will construct where clause in the query in the form <where_col> = <value>, where <value> is a value of the corresponding item in the data.

ords.Misc.update_row('PERSONS', {"ID":1, "FIRST":"Adam"}, 'ID', {"ID":"PERSON_ID", "FIRST":"FIRST_NAME"})