Miscellaneous functions

Miscellaneous functions provide the syntax and descriptions of the classes, methods, attributes, and parameters of the application programming interface.

Miscellaneous functions provide the syntax and descriptions of the classes, methods, attributes, and parameters of the application programming interface.

List of Tables Procedure

Return list of tables.

Syntax

Misc.list_tables(owner)

Parameters:

  • owner: This field displays the owner of the object. If this field is missing, the tool uses the current schema owner.

Example

In this example, you can get list of tables:
adp.Misc.list_tables()
 
// Output
 
{
  "nodes": [
    {
 "label": "COSTS",
      "type": "TABLE",
      "id": "\"ADMIN\".\"DB\".\"COSTS\"",
      "data": {
        "name": "COSTS",
        "namespace": "DB",
        "path": "\"DB\".\"COSTS\"",
        "schema": "ADMIN",
        "annotation": {
          "numRows": 82112,
          "status": "VALID",
          "blocks": 370,
          "avgRowLen": 26,
          "partitioned": "NO",
          "external": "NO",
          "sharded": "N",
          "tablespace": "DATA",
          "hasSensitiveColumn": "NO",
          "compression": "DISABLED"
        },
        "application": "DATABASE",
        "created": "2021-06-09T09:16:49Z",
        "updated": "2021-06-09T09:16:49Z",
        "catalog": "LOCAL"
      }
    },...
    ]
}
List of Views Procedure

Return list of views.

Syntax

Misc.list_views(owner)

Parameters:

  • owner: This field displays the owner of the object. If this field is missing, the tool uses the current schema owner.

Example

In this example, you can get the list of views:

adp.Misc.list_views()
 
// Output
{
  "nodes": [
   {
      "label": "COSTS_AVVIEW",
      "type": "VIEW",
      "id": "\"ADMIN\".\"DB\".\"COSTS_AVVIEW\"",
      "data": {
        "name": "COSTS_AVVIEW",
        "namespace": "DB",
        "path": "\"DB\".\"COSTS_AVVIEW\"",
        "schema": "ADMIN",
        "application": "DATABASE",
        "created": "2024-11-21T10:00:16Z",
        "updated": "2024-11-21T10:00:16Z",
        "catalog": "LOCAL"
      },...
    ]
}
Drop Table Procedure

Drop the specified table.

Syntax

Misc.drop_table(table_name)

Parameters:

  • table_name: This field displays the name of the table to drop.

Example

In this example, you can drop table 'TEST':

adp.Misc.drop_table('TEST')
Drop View Procedure

Drop the specified view.

Syntax

Misc.drop_view(view_name)

Parameters:

  • view_name: This field displays the name of the view to drop.

Example

In this example, you can drop view 'VIEW_TEST':
adp.Misc.drop_view('VIEW_TEST')
Drop Tables Procedure

Drop the specified tables

Syntax

Misc.drop_tables(table_names)

Parameters:

  • table_names: This field displays the array of names of the table to drop.

Example

In this example, you can drop table 'TEST':

dp.Misc.drop_tables(['TEST'])
Drop Views Procedure

Drop the specified views.

Syntax

Misc.drop_views(view_names)

Parameters:

  • view_names: This field displays the array of names of the views to drop.

Example

In this example, you can drop views 'VIEW_TEST':

Get Entity DDL Procedure

Generate DDL for creating or replacing entity (TABLE or VIEW)

Syntax

Misc.get_entity_ddl(entity_type, entity_name,
                    owner)

Parameters:

  • entity_type: This field displays the type of the entity. It can be 'TABLE' or 'VIEW'
  • entity_name: This field displays the name of the entity.
  • owner: This field displays the owner of the object. If this field is missing, the tool uses the current schema owner.

Example

In this example, you can view DDL of table 'TEST':

adp.Misc.get_entity_ddl('TABLE', 'TEST')
Run Query Procedure

Return the results of query.

Syntax

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

Parameters:

  • statement: This field displays SQL statement to run
  • offset: This field displays the offset to displayed record. If this field is missing, the tool uses 0 as offset.
  • limit: This field displays maximum number of records to display. If this field is missing, the tool displays all records.
  • asof: AS OF statement to display historic data. If this field is missing, the tool does not use this feature.

Example

In this example, you can drop the view:

adp.Misc.run_query('DROP VIEW "VIEW_TEST"')
Global Search Procedure

Return the result of searching databases, tables and another artifacts from the Data Studio.

Syntax

Misc.global_search(search_string, rowstart, numrow, sortBy,
                          hide_system_tables, hide_private_tables, resultapp,
                          resultannotation)

Parameters:

  • search_string: string to search. It should be in the form "( owner: ADPTEST ) ( type: TABLE ) ( application: DATABASE )
  • rowstart: first index of searching
  • numrow: number of returned rows
  • sortBy: list of sort, e.g. [{column: "entity_name", direction: "asc"}]
  • hideSystemTables: If this field is missing, system tables should be searched too.
  • hidePrivateTables: If this field is missing, system tables should be searched too.
  • resultapp:
  • resultannotation:

Example

In this example, you can drop the view:
adp.Misc.global_search('( owner: ADMIN ) ( type: TABLE ) ( application: DATABASE )', 1, 20001, [], TRUE, True, resultapp="ADPINS")
Get Table Columns Procedure

This function returns the list of the columns information of the table

Syntax

Misc.get_table_columns(table_name, owner, limit,
                      offset)

Parameters:

  • table_name: This field displays the name of the table.
  • owner: This field displays the owner of the object. If this field is missing, the tool uses the current schema owner.
  • limit: The maximum number of columns to display. The default value is 256.
  • offset: the offset of the columns. The default value is 0.

Example

In this example, you can view columns of table 'PERSONS':

adp.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
  }
]
Get Table Constraints Procedure

This function returns the list of the constraints of the table.

Syntax

Misc.get_table_constraints(table_name, owner, limit,
                      offset)

Parameters:

  • table_name: This field displays the name of the table.
  • owner: This field displays the owner of the object. If this field is missing, the tool uses the current schema owner.
  • limit: The maximum number of columns to display. The default value is 256.
  • offset: the offset of the columns. The default value is 0.

Example

In this example, you can view constraints of table 'PERSONS':

adp.Misc.get_table_constraints('PERSONS')
 
Output:
 
[
  {
    "constraint_name": "SYS_C0056209",
    "constraint_type": "Check",
    "search_condition": "\"PERSON_ID\" IS NOT NULL",
...
  }
]
Insert Row Procedure

The function inserts row into the table

Syntax

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

Parameters:

  • table_name: This field displays the name of the table.
  • data: values of the row to insert into table as a form {"data_column" : value,...}.
  • mapping: Map data column names in data with column names of the table. If this field is missing, the tool uses column names of the table
  • owner: This field displays the owner of the object. If this field is missing, the tool uses the current schema owner.

Example

In this example, you can view insert a row to the table 'PERSONS', without mapping and using mapping:

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

Update Row Procedure

The function update existing row in the table.

Syntax

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

Parameters:

  • table_name: This field displays the name of the table.
  • data: values of the row to insert into table as a form {"data_column" : value,...}.
  • where_col: name of the columnt hat will be used in where clause. The value of this column should be in data
  • mapping: Map data column names in data with column names of the table. If this field is missing, the tool uses column names of the table
  • owner: This field displays the owner of the object. If this field is missing, the tool uses the current schema owner.

Example

In this example, you can view insert a row to the table 'PERSONS', without mapping and using mapping:

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