Use Plugin Code Editor - Part 2/4: Intro

I suppose you have viewed demo page and start to import one plugin in your own application.  And you might want to know more about how to use these two plugins and go further to the detail. Then this post is going to introduce the basic interfaces for Code Editor plugin, including both item and region types.

Click the link in TOC  to jump to the section you are interested in or

See more:

• Use Plugin Code Editor - Part 1/4: Demo
• Use Plugin Code Editor - Part 2/4: Intro
• Use Plugin Code Editor - Part 3/4: Custom
• Use Plugin Code Editor - Part 4/4: Retrospect

What's Code Editor?

Code Editor is the plugin for Oracle APEX internal applications that wraps CodeMirror, enables SQL and PL/SQL syntax validation, integrates to APEX item component and provides region type variation named CLOB Code Editor.

See Also:
APEX-5.1 Doc: 7.3 Understanding Page Designer UI Elements

If you check the annotation in file widget.codeEditor.js, you will find:

/*!
 codeEditor - a jQuery UI based widget that wraps CodeMirror
 Copyright (c) 2013, 2015, Oracle and/or its affiliates. All rights reserved.
 */

/**
 * @fileOverview
 * Turns a standard DIV element into a code editor for css, javascript, html and pl/sql
 *   apex.jQuery( "#myEditor" ).codeEditor({...});
 * This is a wrapper around CodeMirror but it also has a fallback mode to use a plain textarea
 * just in case there is an accessibility, performance or browser compatibility issue with CodeMirror.
 *
 * todo:
 *   disabled option/state, jQuery UI widgets have a disabled option and state. Does codeMirror support that?
 *   cache code completion results?
 *
 * Depends:
 *    codemirror-custom.min.js
 *    jquery.ui.core.js
 *    jquery.ui.widget.js
 *    jquery.ui.position.js (for menu)
 *    apex/util.js
 *    apex/debug.js
 *    apex/widget.menu.js
 *    apex/lang.js
 * Depends on a number of strings prefixed with CODE_EDITOR. defined in the apex.lang message facility
 */

Main Features

• SQL and PL/SQL syntax validation
• item and region integration
• QueryBuilder integration
• Support modes: HTML, CSS, Javascript, SQL, DDL and PL/SQL
• Autocompletion
• keybindings
• Search and replace interface
• Bracket and tag matching
• Linter integration
• Mixing font sizes and styles - syntax highlight
• Various themes
• Able to resize to fit content
• gutters
• text read-only

The first 4 points are specific in APEX. Check more from CodeMirror.

Item Type and Region Type

Plugin Code Editor (including item and region plugins) is one of top 2 reference plugin in internal applications. As I mentioned, it supports two types:

• Item type: Code Editor
• Region type: CLOB Code Editor

Code Editor API

Here is a verified API list I used. And there should be some others I have not yet found.

CSS and JS dependence

Code Editor will emit its own plugin css and js files when page rendering. And it utilizes Core.min.css (line 1) to render the toolbar. 

<link href="/i/apex_ui/css/Core.min.css?v=5.1.0.00.45" rel="stylesheet" type="text/css"></link>
<link href="/i/libraries/codemirror/5.16/codemirror-custom.min.css?v=5.1.0.00.45" rel="stylesheet" type="text/css"></link>
<script src="/i/libraries/codemirror/5.16/codemirror-custom.min.js?v=5.1.0.00.45" type="text/javascript"></script>
<script src="/i/apex_ui/js/minified/widget.codeEditor.min.js?v=5.1.0.00.45" type="text/javascript"></script>
<script src="/i/apex_ui/js/minified/builder.plugin.codeEditor.min.js?v=5.1.0.00.45" type="text/javascript"></script>

Binding and Generation

For item type, Code Editor will bind a div which id is Item_ID || "_widget" and emit its elemets into this div as the structure as below. And it will wrap a parent div with class "a-CodeEditor--resizeWrapper ui-resizable", following a placeholder span for in-field error message.

<div class="a-CodeEditor--resizeWrapper ui-resizable">
    <div id="P1_SQL_widget" class="a-CodeEditor" style="height: 176px; width: 1312px;">
        <div class="a-CodeEditor-toolbar">...</div>
        <div class="a-CodeEditor-searchBar" style="display:none;">...</div>
        <div class="a-CodeEditor-notification" style="display:none;">...</div>
        <textarea name="P1_SQL" rows="6" cols="40" maxlength="300" id="P1_SQL" style="display: none;">
         &nbsp -- This is default SQL code select 'Hello APEX!' as " " from dual;
        </textarea>
        <div class="CodeMirror cm-s-solarized cm-s-dark" style="height: 136px; width: 1310px;"></div>
    </div>
    <div class="ui-resizable-handle ui-resizable-s" style="z-index: 90;" tabindex="0"></div>
</div>
<span id="P1_SQL_error_placeholder" class="a-Form-error" data-template-id="16993035364305904345_ET"></span>

For region type, CLOB Code Editor will bind a div which id is Region_Static_ID || "_widget" and emit its elements into this div as the structure as below:

<div id="P1_PLSQL_EXP_V_widget" class="a-CodeEditor" style="height: 240px; width: 1312px;">
    <div class="a-CodeEditor-toolbar">...</div>
    <div class="a-CodeEditor-searchBar" style="display:none;">...</div>
    <div class="a-CodeEditor-notification" style="display:none;">...</div>
    <div class="a-CodeEditor-message">...</div>
    <textarea rows="10" cols="60" style="display: none;"></textarea>
    <div class="CodeMirror cm-s-solarized cm-s-dark" style="height: 200px; width: 1310px;">...</div>
</div>

Here for both, its jQuery selector is this div tag id.

When rendering, the function invoking below will generate and initialize corresponding code editor widget to page.

// "#P2_SQL_widget"" is the selector, and P2_SQL is the item name or region static ID
// adjustableHeight: enable or disable height adjustable, only for item type Code Editor plugin 
// mode: code mode assigned in plugin properties
// validate: enable or disable validation from Code Editor toolbar
// queryBuilder: enable or disable query builder from Code Editor toolbar
// parsingSchema: parsing schema for syntax
// readOnly: code readonly or not
// settings: attributes in setting pop-menu
// ajaxIdentifier: AJAX ID for this component
// appId: null default for current application ID
apex.builder.plugin.codeEditor('#P2_SQL_widget', {
    "adjustableHeight": true,
    "mode": "text\u002Fx-plsql",
    "validate": false,
    "queryBuilder": false, 
    "parsingSchema": "OOS_USER",  
    "readOnly": false, 
    "settings": "|f|solarized dark|4|4|f|t|t|", 
    "ajaxIdentifier": "AIZnyjl7ClPEyOC_8H7Tk01WKNQ9iQWJvqQnoV1IpFAhgBfq6_xo29619i6-X_Ac", 
    "appId": "" 
});

Note: Here the ajaxIdentifier is returned by the back-end PL/SQL GET_AJAX_IDENTIFIER Function(click to check APEX5.1 doc), which only works in the context of a plug-in rendering function call and only if the plug-in has defined an Ajax function callback in the plug-in definition as mentioned in the doc.

Button and Resize

The buttons in Code Editor widget can be translated to local language base on your browser setting and APEX environment, I guess. Because these button titles or button names (depending on icon or text type) are formatted by APEX API apex.lang.formatMessage when page rendering.

Some buttons in toolbar, such as search, replace, and resize button at the bottom of the item type widget can enable widget resize. Take an example, when you click search first and then click close search, the widget will resize.

And Code Editor provides an event to resize widget. Actually, it's a window resize event according to Oracle APEX UT demo. You can use this to trigger Code Editor also. You can trigger it as below.

$(window).trigger("apexwindowresized");

This will trigger all Code Editor widgets on the page to resize according to current window size but this is not workable when Code Editor is in a dialog.

And there is a resize event on widget div. So you can trigger it as below.

// give proper widget selector
apex.jQuery("#P1_SQL_widget").trigger("resize")

getValue and setValue

From front-end, typically from JavaScript scope, you can get and set value of Code Editor widget (both item type and region type), which is workable from browser console when debugging.

// get value and log to console
console.log(apex.jQuery("#P1_SQL_widget").codeEditor("getValue"));
// set value to "new value"
apex.jQuery("#P1_SQL_widget").codeEditor("setValue", "new value");

Get Data and Set Data

When handling Code Editor in APEX scope, sometimes you might need to get data or set data.

For Item Type, treat it as a normal APEX item.

From APEX scope, typically in page designer, you can get the data for item type Code Editor easily as handling a normal item by using ":". Taking an example, :P1_SQL is the value of item type Code Editor named P1_SQL. So you also can set default value as Static Value in properties panel, and value is :

-- This is default SQL code
select 'Hello APEX!' as " " from dual;

For Region Type (CLOB), it's a little bit complicated and it's an integrated process to handle data from and to database. Please see next section.

Select and Update

When interacting with database, you need get data or update data (including insert or delete) for Code Editor.

For Item Type, you can easily build a DML form page and bind corresponding database column to Code Editor item. After enable supported operations (insert, update and delete) in process, you could operate the column data binding with this Code Editor item. You also can use customized PL/SQL code to handle item data as you want.

For Region Type, there are 4 steps for select and update as below.

Step1: Select

This is getting data from column plsql_express_b in table plugin_supported_codes. When create CLOB Code Editor, add this select clause to its source (SQL Query)

select plsql_expression_b 
  from plugin_supported_codes 
 where current_session = :P1_CURRENT_SESSION;

Step2: Submit

Use JavaScript dynamic action to submit the data in CLOB Code Editor.

// first parameter is widget selector
// f01 is the input named f01, which will store CLOB data in wwv_flow form session
// request is defined request type, here is "APPLY_CHANGES"
// showWait is the setting for showing the spinning waiting progress or not when submit
apex.builder.plugin.codeEditor.saveSubmit(
    "#" + wID,
    f01, {
        request: "APPLY_CHANGES",
        showWait: true
    });

This JavaScript function will generate one or more input(s) named "f01" for storing CLOB Code Editor data, depending on the length of the data.

Step3: Save to collection

From session states, you need to transfer the data from f01 to apex_collection by using computation after submit defined as PL/SQL Function Body type below.

-- get clob data from f01 and save clob data to apex collection named "P1_PLSQL_EXP_B"
declare
    l_code        clob := empty_clob;
begin    
    sys.dbms_lob.createtemporary( l_code, false, sys.dbms_lob.SESSION );
   
    apex_collection.create_or_truncate_collection(
        p_collection_name => 'P1_PLSQL_EXP_B');

    for i in 1..wwv_flow.g_f01.count loop
      sys.dbms_lob.writeappend(l_code,length(wwv_flow.g_f01(i)),wwv_flow.g_f01(i));        
    end loop;        
    
    apex_collection.add_member(
        p_collection_name => 'P1_PLSQL_EXP_B',
        p_clob001         => l_code);


    return null;        
end;

Here APEX engine helps to store f01 input data to Global input values wwv_flow.g_f01.

Step4: Update to column

Then you need to define a PL/SQL Code type process in page processing after submit, to update the date to corresponding CLOB column.

declare
  l_clob   clob := empty_clob;
begin

  for c1 in (select clob001 script
             from apex_collections
             where collection_name = 'P1_PLSQL_EXP_B') loop
    l_clob := c1.script;
  end loop;
  
  update plugin_supported_codes
     set plsql_expression_b = l_clob
   where current_session = :APP_SESSION;
    
end;

BTW, you can also use apex_collection to get CLOB column data from database (transfer clob to apex_collection and select from collection). But using a simple select clause directly to the target table should be the easiest solution.

Syntax Validation and Return Message

Code Editor provides two ways for syntax validation, one is from toolbar, the other is from PL/SQL procedures. Actually, the back-end checking procedure are both in package wwv_flow_f4000_util (under apex schema).

For the first one, you can click the validate button from Code Editor toolbar after enable the "validate" option when rendering. The click event will trigger an ajax call to check the syntax. If you enable LEVEL9 debug mode, you can capture this calling detail in debug view.  This way can be used for both Item and region type Code Editor.

For the second one, you can create a validation with PL/SQL Function Body Returning Error Text type for corresponding item. So this way is only for item type Code Editor but not for region type CLOB Code Editor.  Here is a full list for different supported code modes.

-- for SQL
return wwv_flow_f4000_util.check_sql(
    p_sql              => :P2_SQL,
    p_flow_id          => :APP_ID,
    p_parse_as_schema  => :P2_PARSING_SCHEMA,
    p_min_column_count   => 1,
    p_max_column_count   => 999,
    p_invalid_sql_error  => '[ SQL - Code Editor ]: SQL Syntax Error',
    p_column_count_error => '[ SQL - Code Editor ]: SQL Column Count Error'
                                     );

-- for PL/SQL Block
return wwv_flow_f4000_util.check_plsql (
    p_sql               => 'begin ' || :P2_PLSQL_BLOCK || chr(10) || 'end;',
    p_flow_id           => :APP_ID,
    p_security_group_id => :WORKSPACE_ID );          
     
-- for PL/SQL Functions/Procedures
return wwv_flow_f4000_util.check_plsql (
    p_sql               => 'declare '||:P2_PLSQL_FP||chr(10)||'begin null; end;',
    p_flow_id           => :APP_ID,
    p_security_group_id => :WORKSPACE_ID );

-- for PL/SQL Expression reture Varchar2     
return wwv_flow_f4000_util.check_plsql (
    p_sql               => 'declare l_dummy varchar2(32767); begin l_dummy := '||:P2_PLSQL_EXP_V||chr(10)||'; end;',
    p_flow_id           => :APP_ID,
    p_security_group_id => :WORKSPACE_ID );   
     
-- for PL/SQL Expression reture Boolean    
return wwv_flow_f4000_util.check_plsql (
    p_sql               => 'declare l_dummy boolean; begin l_dummy := '||:P2_PLSQL_EXP_B||chr(10)||'; end;',
    p_flow_id           => :APP_ID,
    p_security_group_id => :WORKSPACE_ID );

-- for PL/SQL Function Body return Varchar2     
return wwv_flow_f4000_util.check_plsql (
    p_sql               => 'declare l_dummy varchar2(32767); function a return varchar2 is begin '||:P2_PLSQL_FUNCBODY_V||chr(10)
                              ||'return null; end; begin l_dummy:=a; end;',
    p_flow_id           => :APP_ID,
    p_security_group_id => :WORKSPACE_ID );     
 
-- for PL/SQL Function Body return Boolean
return wwv_flow_f4000_util.check_plsql (
    p_sql               => 'declare l_dummy boolean; function a return boolean is begin '||:P2_PLSQL_FUNCBODY_B||chr(10)
                              ||'return null; end; begin l_dummy:=a; end;',
    p_flow_id           => :APP_ID,
    p_security_group_id => :WORKSPACE_ID ); 

After page submission, APEX engine will return the message(s) for syntax validation by using JavaScript API apex.message.showErrors(). It supports 3 positions: inline with filed(below the Code Editor), in notification(page notification area) and in row located in Code Editor.

Issues

Button Title

If you use Code Editor plugin directly, you will get lots of errors  logged in console as below for button title/name translation. And from page, you will find these button title letters are upper case.

Format(CODE_EDITOR.SHORTCUT_TITLE): too many arguments. Expecting 0, got 2

Message

If you set error display location to be "inline with Field and in Notification" or "inline with Field" when using validations,  you will get error information from browser console log as below.
Uncaught TypeError: f.addClass is not a function
And the error message will only be displayed in the field, typically below the widget.

Maximum Length

When you use item type Code Editor, you might want to define max-length for code input. Unfortunately, you can not.  Even though you can set the value for this property, it doesn't work.

Resize

Sometimes, you might want to expand Code Editor to utilize your screen width for better experience, typically in dialog as what I did in my demo. 

Validate

Same as resize, validate is a great feature for Code Editor in APEX. And it's disabled as default and there is no interface provided to enable it from page designer.

Can we fix these issues and maximize the advantages of Code Editor as showing in my demo?  Please check my next post.

• Use Plugin Code Editor - Part 3/4: Custom