Help Center> Data Warehouse Service (DWS)> Tool Guide (Paris Region)> Data Studio - Integrated Database Development Tool> Working with Functions/Procedures> Executing a Function/Procedure
Updated on 2023-03-17 GMT+08:00
View PDF

Executing a Function/Procedure

After you connect to the database, all the stored functions/procedures and tables will be automatically populated in the Object Browser pane. You can use Data Studio to execute PL/SQL programs or SQL functions.

  • Blank lines occurring above or below in a function/procedure will be trimmed by Data Studio before being sent to the server. Blank lines will also be trimmed when displaying the source received from the server.
  • To execute a function/procedure, enter the same values in Data Studio and the gsql client. If you do not enter any value in Data Studio, then NULL is considered as the input.

    For example:

    - To execute the function/procedure with string, enter the value as data.

    - To execute the function/procedure with date, enter the value as to_date('2012-10-10', 'YYYY-MM-DD').

  • A function/procedure with OUT and INOUT parameter types cannot be executed directly.
  • Data Studio will not execute any function/procedure with unknown data type parameters.

You can right-click the function/procedure in the Object Browser to perform the following operations:

  • Refresh the program to get the latest program from the server.
  • Execute the function/procedure or SQL function.
  • Debug a PL/SQL function.
  • Drop the debug object.

Executing a PL/SQL Program or SQL Function

Follow the steps below to execute a PL/SQL program or SQL function:

  1. Double-click and open the PL/SQL program or SQL function. Each debug object will be opened in a new tab. You can open a maximum of 100 tabs in Data Studio.
  2. Click on the toolbar or choose Run > Execute from the main menu,

    or right-click the program name in the Object Browser and select Execute.

  3. The Execute Function/Procedure dialog box is displayed prompting for your input.

    If there is no input parameter, then the Execute Function/Procedure dialog box will not appear. Instead, the PL/SQL program will be executed, and the result (if any) will be displayed in the Result tab.

  4. Provide your input for the function/procedure in the Execute PL/pgSQL dialog box and click OK.

    To set NULL as the parameter value, enter NULL or null.

    • If you do not provide a value that starts with a single quote, then a single quote (') will be added by Data Studio before and after the value and typecasting is done.
    • If you provide a value that starts with a single quote, an additional single quote will not be added by Data Studio, but data type typecasting is done. For example:

      For supported data types, the execution queries are as follows:

      1
2
3
      		 
      select func('1'::INTEGER);
select func('1'::FLOAT);
select func('xyz'::VARCHAR);
    • If quotes are already provided, you need to take care of escaping the quotes.

      Example: If the input value is ab'c, then you need to enter ab''c.

      The PL/SQL program is executed in the SQL Terminal tab and the result is displayed in the Result tab. You can copy the contents of the Result tab by clicking . Refer to Using SQL Terminals for more information on toolbar options..

      Refer to Executing SQL Queries for information on reconnect option in case connection is lost during execution.