Dynamic Forms

Example Use Cases

The following are a few examples of using dynamic forms;

Entering a name in an input field that is then used to search.
Selecting a date from a date picker to be added in the code.

Programmatic Notebook Forms

For programmatic notebook forms, the methods involved in form creation use an optional argument to indicate whether the form should be a notebook form, which is false by default.

Python

%python

ds.textbox(name='<name>', default_value='<default_value>', label='<label>', is_notebook=True)

PGX-Python

%pgx-python

ds.textbox(name='<name>', default_value='<default_value>', label='<label>', is_notebook=True)

PGX-Java

%pgx-java

ds.textbox("<name>", "<default_value>", "<label>", true)

Prerequisites: Import Context

Before creating programmatic dynamic forms, you must import the context that will allow you to display the forms and define your own variable name and instantiate your context.

Note:

If you are using the Standalone Data Studio, you need to install the ds_interpreter_client library to be able to use programmatic dynamic forms.

pip install ds_interpreter_client-<version>-py2.py3-none-any.whl

Version must be the Data Studio version.

Note:

Release candidates are versioned in lowercase and without the - to comply with the PyPA version specification. For example, for Data Studio version 23.3.0-RC2, the appropriate representation would be 23.3.0rc2.

Python

%python

from ds_interpreter_client.context.ds_context import PyDataStudioContext

ds = PyDataStudioContext()

PGX-Python

%pgx-python

from ds_interpreter_client.context.ds_context import PyDataStudioContext

ds = PyDataStudioContext()

PGX-Java

%pgx-java

import oracle.datastudio.interpreter.common.context.JavaDataStudioContext

JavaDataStudioContext ds = interpreter.getJavaDataStudioContext()

Note:

We will use our instantiated variable ds for each example in the following sections.

Note:

For each of the following examples, you must to run the paragraph once for the input form(s) to appear.

Note:

For each function, it is not necessary to start with an argument identifier (For example, method(first_argument=..., second_argument=...)) is only displayed here for clarity

How to Create a Dynamic Form

We will use `<>` to denote placeholders that you can replace with your own values.

Textbox

The Textbox dynamic form allows users to input any string of characters. The following is its format:

Python

%python

ds.textbox(name='<name>', default_value='<default_value>', label='<label>')

PGX-Python

%pgx-python

ds.textbox(name='<name>', default_value='<default_value>', label='<label>')

PGX-Java

%pgx-java

ds.textbox("<name>", "<default_value>", "<label>")

The following are the descriptions of the characters:

name: The name of the dynamic form. It is displayed on top of the dynamic form if no label (see the description for label) is set. If you want to reference a dynamic form multiple times in a paragraph, you can assign the same name to do so and it will only be displayed once.
label: The label that is displayed on top of the dynamic form.
default_value (optional):The default value that is given to the dynamic form when it is first created.

Examples

Python

%python

ds.textbox('Name', 'Default Value', 'Label')

PGX-Python

%pgx-python

ds.textbox('Name', 'Default Value', 'Label')

PGX-Java

%pgx-java

ds.textbox("Name", "Default Value", "Label")

Select

The Select dynamic form allows you to select a value from a drop-down menu. The following is its format:

Python

%python

options = [('<option_value_a>', '<option_label_a>'),('<option_value_b>', '<option_label_b>')]

ds.select(name='<name>', options=options, default_value='<default_value>', label='<label>')

PGX-Python

%pgx-python

options = [('<option_value_a>', '<option_label_a>'),('<option_value_b>', '<option_label_b>')]

ds.select(name='<name>', options=options, default_value='<default_value>', label='<label>')

PGX-Java

%pgx-java

import oracle.datastudio.common.forms.ParamOption

List<ParamOption<String>> options = new ArrayList<>();

options.add(new ParamOption<>("<option_value_a>", "<option_label_a>"));

options.add(new ParamOption<>("<option_value_b>", "<option_label_b>"));

ds.select("<name>", options, "<default_value>", "<label>")

The following are the descriptions of the characters:

name: The name of the dynamic form. It is displayed on top of the dynamic form if no label (see the description for label) is set. If you want to reference a dynamic form multiple times in a paragraph, you can assign the same name and it will only be displayed once.
label (optional): The label that is displayed on top of the dynamic form.
default_value: The default value that is given to the dynamic form when it is first created. It must be one of the option values:
- An option has a option_value and a option_label. The option_value is used to reference which default_value must be selected, the (optional) option_label is displayed in the dropdown list or in respective boxes created by a checkbox.
- An option_value can be either a string or a numeric value
- Options are separated with the | character in parsed forms.

Example

Python

%python

options = [('Value A', 'Label A'),('Value B', 'Label B')]

ds.select('Name', options, 'Value A', 'Label')

PGX-Python

%pgx-python

options = [('Value A', 'Label A'),('Value B', 'Label B')]

ds.select('Name', options, 'Value A', 'Label')

PGX-Java

%pgx-java

import oracle.datastudio.common.forms.ParamOption

List<ParamOption<String>> options = new ArrayList<>();

options.add(new ParamOption<>("Value A", "Label A"));

options.add(new ParamOption<>("Value B", "Label B"));

ds.select("Name", options, "Value A", "label")

Select Multiple

The Select Multiple dynamic form allows the user to select one or multiple values from a list. The following is its format:

Python

%python

options = [('<option_value_a>', '<option_label_a>'),('<option_value_b>', '<option_label_b>')]

ds.select_multiple(name='<name>', options=options, default_value=['<default_value>'], label='<label>')

PGX-Python

%pgx-python

options = [('<option_value_a>', '<option_label_a>'),('<option_value_b>', '<option_label_b>')]

ds.select_multiple(name='<name>', options=options, default_value=['<default_value>'], label='<label>')

PGX-Java

%pgx-java

import oracle.datastudio.common.forms.ParamOption

List<ParamOption<String>> options = new ArrayList<>();

options.add(new ParamOption<>("<option_value_a>", "<option_label_a>"));

options.add(new ParamOption<>("<option_value_b>", "<option_label_b>"));

List<String> defaultValues = new ArrayList<>();

defaultValues.add("<default_value>");

ds.selectMultiple("<name>", options, defaultValues, "<label>")

The following are the descriptions of the characters:

name: The name of the dynamic form. It is displayed on top of the dynamic form if no label (see the description for label) is set. If you want to reference a dynamic form multiple times in a paragraph, you can assign the same name to do so and it will only be displayed once.
label (optional): The label that is displayed on top of the dynamic form.
default_value: The default value that is given to the dynamic form when it is first created. This must be one of the following option values:
- An option has a option_value and a option_label. The option_value is used to reference which default_value should be selected, the (optional) option_label is displayed in the dropdown list or in respective boxes created by a checkbox.
- An option_value can be either a string or a numeric value
- Options are separated with the | character in parsed forms.

Example

Python

%python

options = [('Value A', 'Label A'),('Value B', 'Label B')]

ds.select_multiple('Name', options, ['Value A'], 'Label')

PGX-Python

%pgx-python

options = [('Value A', 'Label A'),('Value B', 'Label B')]

ds.select_multiple('Name', options, ['Value A'], 'Label')

PGX-Java

%pgx-java

List<ParamOption<String>> options = new ArrayList<>();

options.add(new ParamOption<>("Value A", "Label A"));

options.add(new ParamOption<>("Value B", "Label B"));

List<String> defaultValues = List.of("Value A");

ds.selectMultiple("Name", options, defaultValues, "label")

Slider

The Slider dynamic form allows you to select a number from a given range. The following is its format:

Python

%python

ds.slider(name='<name>', min=<minimum>, max=<maximum>, step=<step_size>, default_value=<default_value>, label='<label>')

PGX-Python

%pgx-python

ds.slider(name='<name>', min=<minimum>, max=<maximum>, step=<step_size>, default_value=<default_value>, label='<label>')

PGX-Java

%pgx-java

ds.slider("<name>", <minimum>, <maximum>, <stepSize>, <default_value>, "<label>")

The following are the descriptions of the characters:

name: The name of the dynamic form. It is displayed on top of the dynamic form if no label (see the description for label) is set. If you want to reference a dynamic form multiple times in a paragraph, you can assign the same name to do so and it will only be displayed once.
label (optional): The label that is displayed on top of the dynamic form.
default_value (optional): The default value that is given to the dynamic form when it is first created.
minimum: The minimum value of the slider. The value must be numeric.
maximum: The maximum value of the slider. The value must be numeric.
step_size: The step size of the slider. Must be a number and divider of (maximum - minimum). minimum <= default_value <= maximum.

Example

Python

%python

ds.slider('Name', 0, 10.5, 0.5, 3, 'Label')

PGX-Python

%pgx-python

ds.slider('Name', 0, 10.5, 0.5, 3, 'Label')

PGX-Java

%pgx-java

ds.slider("Name", 0, 10.5, 0.5, 3, "Label")

Checkbox

The Checkbox dynamic form allows the user to select one or multiple values. The format is as follows:

Python

%python

options = [('<option_value_a>', '<option_label_a>'),('<option_value_b>', '<option_label_b>')]

ds.checkbox(name='<name>', options=options, default_value=['<default_value>'], label='<label>')

PGX-Python

%pgx-python

options = [('<option_value_a>', '<option_label_a>'),('<option_value_b>', '<option_label_b>')]

ds.checkbox(name='<name>', options=options, default_value=['<default_value>'], label='<label>')

PGX-Java

%pgx-java

import oracle.datastudio.common.forms.ParamOption

List<ParamOption<String>> options = new ArrayList<>();

options.add(new ParamOption<>("<option_value_a>", "<option_label_a>"));

options.add(new ParamOption<>("<option_value_b>", "<option_label_b>"));

List<String> defaultValues = new ArrayList<>();

defaultValues.add("<default_value>");

ds.checkbox("<name>", options, defaultValues, "<label>")

The following are the descriptions of the characters:

name: The name of the dynamic form. It is displayed on top of the dynamic form if no label (see the description for label) is set. If you want to reference a dynamic form multiple times in a paragraph, you can assign the same name to do so and it will only be displayed once.
label (optional): The label that is displayed on top of the dynamic form.
default_value: The default value that is given to the dynamic form when it is first created. This must be one of the following option values:
- An option has a option_value and a option_label. The option_value is used to reference which default_value must be selected, the (optional) option_label is displayed in the drop-down list or in the respective boxes that are created by a checkbox.
- An option_value can be either a string or a numeric value
- Options are separated with the | character in parsed forms.
join_parameter (fixed forms only): This is the value that will be inserted between multiple selected values. For example, in a checkbox dynamic form with a join parameter of or and two elements A and B, if the user selects the checkbox next to A and and B and runs the paragraph, the result will be A or B.

Example

Python

%python

options = [('Value A', 'Label A'),('Value B', 'Label B')]

PGX-Python

%pgx-python

options = [('Value A', 'Label A'),('Value B', 'Label B')]

ds.checkbox('Name', options, ['Value A'], 'Label')

PGX-Java

%pgx-java

import oracle.datastudio.common.forms.ParamOption

List<ParamOption<String>> options = new ArrayList<>();

options.add(new ParamOption<>("Value A", "Label A"));

options.add(new ParamOption<>("Value B", "Label B"));

List<String> defaultValues = new ArrayList<>();

defaultValues.add("Value A");

ds.checkbox("Name", options, defaultValues, "label")

Date Picker

The Date Picker dynamic form allows the user to select a date. The following is its format:

Python

%python

ds.date_picker(name='<name>', format='<date_format>', default_value='<default_value>', label='<label>')

PGX-Python

%pgx-python

ds.date_picker(name='<name>', format='<date_format>', default_value='<default_value>', label='<label>')

PGX-Java

%pgx-java

ds.datePicker("<name>", "<dateFormat>", "<defaultValue>", "<label>")

The following are the descriptions of the characters:

name: The name of the dynamic form. It is displayed on top of the dynamic form if no label (see the description for label) is set. If you want to reference a dynamic form multiple times in a paragraph, you can assign the same name and it will only be displayed once.
label (optional): The label that is displayed on top of the dynamic form.
date_format (optional, recommended): The date format. This is used for displaying the selected date in the input field and for formatting the resulting date when the paragraph is run. This uses the Java Simple Date Format. Examples include EEEE (which could be Sunday) or yyyy-MM-dd (which could be 2019-12-24). Must be specified according to the date_format or in yyyy-MM-dd format if no date_format is provided.

Python

%python

ds.date_picker('Name', format='yyyy/MM/dd', default_value='2020/12/10', label='Label')

PGX-Python

%pgx-python

ds.date_picker('Name', format='yyyy/MM/dd', default_value='2020/12/10', label='Label')

PGX-Java

%pgx-java

ds.datePicker("Name", "yyyy/MM/dd", "1990/01/01", "Label")

Time Picker

The Time Picker dynamic form allows the user to select a time. The following is its format:

Python

%python

ds.time_picker(name='<name>', format='<time_format>', default_value='<default_value>', label='<label>')

PGX-Python

%pgx-python

ds.time_picker(name='<name>', format='<time_format>', default_value='<default_value>', label='<label>')

PGX-Java

%pgx-java

ds.timePicker("<name>", "<dateFormat>", "<defaultValue>", "<label>")

The following are the descriptions of the characters:

name: The name of the dynamic form. It is displayed on top of the dynamic form if no label (see the description for label) is set. If you want to reference a dynamic form multiple times in a paragraph, you can assign the same name to do so and it will only be displayed once.
label (optional): The label that is displayed on top of the dynamic form.
time_format (optional, recommended): The time format. Used for displaying the selected time in the input field and for formatting the resulting time when the paragraph is run. Uses the Java Simple Date Format. Examples include HH:mm (which could be 13:37) or ha (which could be 3PM).
default_value (optional): The default value that is given to the dynamic form when it is first created.
This must start with T and then be specified according to the time_format or in HH:mm format if no time_format is provided.

Example

Python

%python

ds.time_picker(name='Name', format='HH mm ss', default_value='12 11 10', label='Label')

PGX-Python

%pgx-python

ds.time_picker(name='Name', format='HH mm ss', default_value='12 11 10', label='Label')

PGX-Java

%pgx-java

ds.timePicker("Name", "HH:mm:ss", "12:11:01", "Label")

DateTime Picker

The DateTime Picker dynamic form allows the user to select a date and a time. The following is its format:

Python

%python

ds.date_time_picker('<name>', format='<dateTime_format>', default_value='<default_value>', label='<label>')

PGX-Python

%pgx-python

ds.date_time_picker('<name>', format='<dateTime_format>', default_value='<default_value>', label='<label>')

PGX-Java

%pgx-java

ds.dateTimePicker("<name>", "<dateFormat>", "<defaultValue>", "<label>")

The following are the descriptions of the characters:

name: The name of the dynamic form. It is displayed on top of the dynamic form if no label (see the description for label) is set. If you want to reference a dynamic form multiple times in a paragraph, you can assign the same name and it will only be displayed once.
label (optional): The label that is displayed on top of the dynamic form.
dateTime_format (optional, recommended): The dateTime format. Used for displaying the selected date and time in the input field and for formatting the resulting date and time when the paragraph is run. Uses the Java Simple Date Format. Examples include yyyy-MM-dd HH:mm (which could be 2019-12-24 13:37) or YYYY hh (which could be 2019 13).
default_value (optional): The default value that is given to the dynamic form when it is first created.
This must be specified according to the dateTime_format or in yyyy-MM-dd HH:mm format if no dateTime_format is provided.

Example

Python

%python

ds.date_time_picker('Name', format='yyyy-MM-dd HH:mm:ss', default_value='2010-12-11 12:10:02', label='Label')

PGX-Python

%pgx-python

ds.date_time_picker('Name', format='yyyy-MM-dd HH:mm:ss', default_value='2010-12-11 12:10:02', label='Label')

PGX-Java

%pgx-java

ds.dateTimePicker("Name", "yyyy-MM-dd HH:mm:ss", "1998-12-30 12:11:01", "Label")

Note:

When providing a default format for a date, time or dateTime dynamic form use the RFC2822 or ISO 8601 format. Other formats are strongly discouraged due to browser differences and inconsistencies.

How to Update a Dynamic Form's Value Programmatically

Reset to the Default Value

It is possible to reset a dynamic form's value to the default value programmatically:

Python

%python

ds.reset_form_value(name='<name>')

PGX-Python

%pgx-python

ds.reset_form_value(name='<name>')

PGX-Java

%pgx-java

ds.resetFormValue('<name>')

name: The name of the dynamic form for which to reset the value to the default value.

Set a Value

It's possible to set a value to a dynamic form programmatically as follows:

Python

%python

ds.set_form_value(name='<name>', value=<value>)

PGX-Python

%pgx-python

ds.set_form_value(name='<name>', value=<value>)

PGX-Java

%pgx-java

ds.setFormValue('<name>', <value>)

The following are the descriptions of the characters:

name: The name of the dynamic form for which to set a value.
value: The value to set for the dynamic form. This value is validated, and an error message will appear if the dynamic form does accept the provided value type.

Set a New Default Value

It is possible to set a new default value to a dynamic form programmatically as follows:

Python

%python

ds.set_form_default_value(name='<name>', default_value=<defaultValue>)

PGX-Python

%pgx-python

ds.set_form_default_value(name='<name>', default_value=<defaultValue>)

PGX-Java

%pgx-java

ds.setFormDefaultValue('<name>', <defaultValue>)

The following are the descriptions of the characters:

name: The name of the dynamic form for which to set a value.
defaultValue: The new default value to set for the dynamic form. This value is validated, and an error message will appear if the dynamic form does accept the provided value type.

Dynamic Forms in JDBC

In JDBC, a prepared statement can be used to run a parameterized query. The JDBC interpreter leverages prepared statements and displays the dynamic forms for each parameter. The values of the dynamic forms are then bound to the corresponding parameter.

To display dynamic forms, you can use unnamed parameters (?) as well as named parameters (:<name>).

Example

Named parameters:

%jdbc

SELECT * FROM table WHERE firstName = :name AND age = :age

Unnamed parameters:

%jdbc

SELECT * FROM table WHERE name = ? AND age = ?

Limitations

The following are the JDBC dynamic forms limitations:

Input parameters are in string format only. Use the cast functionality of SQL to convert a value from one datatype to another.
Passing null is currently not supported.
Named parameters and unnamed parameters (?) cannot be used together in a single query.