How to retrieve data with single quotes from Oracle database fields

This article explains how to retrieve data that are in single quotes from the Oracle database using the Run Self-Service Reports and the SQL for Confluence apps.

Instructions

First, you must ensure that the table you want data from has data with single quotes. You can perform the following steps to check the data of a specific table:

1. Log in to your Confluence instance.
2. Navigate to the required page and click.
3. Insert SQL Query macro, click Edit, and enter a query as follows:
   

4. Click Save settings and Publish the page. In this example, details from the customer table is displayed on the page as:
   
   

Now, to retrieve data with single quotes from a table on the relevant Confluence page, you can:

1. Insert Run with a user form and parameters macro to build the form and click Edit to open the macro settings window.
   
   

2. Enter the following details in the Field settings and Display settings tabs:
   
   
   
3. Click Save settings and Save the page. The form is displayed as follows:
   
4. Edit the page, and then, edit the Run with a user form and parameters macro.
5. Insert an SQL Query macro inside the Run macro and Edit the macro.
   
   

6. Select the Data source profile as Oracle and enter the required SQL statement based on the Field Key mentioned in step 2. In this example, the Field Key used was num, and so, the following query is used:
   
   

   The Field Key used in the Run Self Service Reports macro inputs the data using the SQL query.

7. Click Save settings and Publish the page.

8. Enter the values to be retrieved from the database in the Data field and click Run.
   
   

   To retrieve data with single quotes from the database, append single triple quotes at the start and the end of the value while using Run Self Service Reports macro.

   Ex: '''2020''', '''2019'''

In this example, the retrieved data is displayed as follows: