APEX 21.2 quickie: syntax highlighting with prism.js

To show (not to edit) pretty code inside an APEX application in the past I had used the libraries that were deployed along with APEX, like CodeMirror (see https://svenweller.wordpress.com/2015/12/07/apex-5-syntax-highlighting/) and CkEditor. In APEX 21 CkEditor got a new version and CodeMirror is not supplied anymore since several APEX versions now. But there is a new very lightweight alternative, which is prism.

In my use case I need this to quickly present currently running edition based plsql code.

Implementation

Step 1) Load code into a hidden page item

I use a before region process to load the data (=code) into an item. Assuming item name P1_CODE for further references.

Step 2) Add prism files to the page

Javascript file urls

#PRISMJS_DIRECTORY#prism.js

CSS file urls

#PRISMJS_DIRECTORY#prism.css

Note that the substitution variable PRISMJS_DIRECTORY is automatically provided and resolved to the path “/i/libraries/prismjs/1.24.1/” and is updated in future versions.

Step 3) Show item in html region and choose the language

Add this to the html code section of a static region.

Note that you should set the correct language that you want to show. In my case it is language-plsql.

<pre><code class="language-plsql">&P1_CODE.</code></pre>

The value in the item is automatically html escaped. In case the logic that loads the code into the item did already escape it, you can also choose not to escape it again, by using an escape filter like &P1_CODE!RAW. . Just be aware of potential security issues, if you do not do that correctly.

A list of possible languages can be found here. However not all of those languages are added in the APEX deployment.

Step 4) Customize a few colors

I didn’t like the coloring for plsql or sql code. Especially I prefer to show comments in green. This can be changed with a few lines of css.

Add this to the inline css section of the page

/* prism plsql colorization */
.token.comment {
  color: #119b01;
}
.token.string {
  color: #0c6cb97c;
}
.token.keyword {
  color: #8C009B;
  font-weight: bold;
}

Very quick and easy to implement.

If you want different settings, simply inspect the code snippet using the browser. That is one of the advantages of prism. The code is tokenized using span tags and the appropriate classes. We can easily see what kind of token a certain element is and simply change the style for the class we want.

Result

Here is a crude overview how the result looks like

Additional considerations

prism has the possibility to add many additional features. However those features come as plugins that are packed directly into the .css and .js files. To use them, one would have to add/replace the library that is used by APEX. I didn’t investigate in detail, which plugins are already inclued. For example line numbering does NOT seem to work out of the box.

This would do the trick with the line numbers, if the line-numbers plugin is integrated.

<pre class="line-numbers" data-start="50"><code class="language-plsql">&P1_CODE.</code></pre>

Conclusion

For simple requirements prism allows a very easy and quick way to add syntax highlighting to code. For more complex requirements (like editing code) a different library might be better suited. The possibility to have dedicated subtitution strings for several libraries is very welcome.

One additional note. I tried to use the markdown editor item type as an alternative, but the effort to make it work for my simple requirements, was too high. A markdown editor simply serves a different purpose.

ORA-14097 “column type or size mismatch in ALTER TABLE EXCHANGE PARTITION” even when using FOR EXCHANGE

Vector image by VectorStock / Anastasia8

This is something I read about and forgot until Chris Saxon mentioned and showcased it during todays AskTOM Office Hour session.

In Oracle 12.2 the create table command was enhanced to avoid the error

ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION

during an exchange partition operation. We can now do create table ... for exchange.

The basic idea is that the for exchange syntax enhancement considers things like invisible columns that are usually not created and by that it avoids complications during an exchange partition at a later time. For further details see this blog post by Connor McDonald.

Here I show a situation, where the ORA-14097 still happens, even if the for exchange syntax is used. Just something to be aware of and watch out for.

demo

First we create two identical partitioned tables, the source table A and the destination table B.

-- create the source table using NULLABLE columns
create table a (col1 number null, col2 number null, col3 number null)
PARTITION BY range (col1) interval (1)
  (PARTITION old_data VALUES LESS THAN (0)
  );
;
-- add a primary key constraint
alter table a add primary key (col1, col2);

desc a;

Name Null?    Typ    
---- -------- ------ 
COL1 NOT NULL NUMBER 
COL2 NOT NULL NUMBER 
COL3          NUMBER 

As you can see the table looks as if col1 and col2 are not null. This is because the primary key requires all columns to be not null.

Now we do exactly the same for table B. in my real world case scenarion table A was on a remote database and table B was a local one, created by a tablespace transport. So source and destination are identical.

create table b (col1 number null, col2 number null, col3 number null)
PARTITION BY range (col1) interval (1)
  (PARTITION old_data VALUES LESS THAN (0)
  );
;

alter table b add primary key (col1, col2);

Now we fill the source table A with some dummy data.

-- add some data into a
insert into a (col1, col2, col3)
select mod(level,10) ,level, mod(level,100) from dual connect by level <= 1000;

1000 rows inserted.

commit;

Commit finished.

Next step is to create and fill a staging table e which will hold the partition that we want to add to B.
Notice that during the create table command the FOR EXCHANGE syntax is used. Unfortunatly it seems we can not do a CTAS when using the syntax enhancement, so two commands are used: create + insert.

create table e for exchange with table b;
insert into e select * from a partition for (9);

Side note: Because the table is interval partitioned, here “partition for” is useful to specify the source partition.

desc e;

Name Null? Typ    
---- ----- ------ 
COL1       NUMBER 
COL2       NUMBER 
COL3       NUMBER

The exchange table e is declared with NULL columns. Although this reflects the original table without the PK, it will lead to a problem during the exchange.

alter table b exchange partition for (9) with table e;

ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION
*Cause:    The corresponding columns in the tables specified in the
           ALTER TABLE EXCHANGE PARTITION are of different type or size
*Action:   Ensure that the two tables have the same number of columns
           with the same type and size.

This error will not happen if we do the same commands, but without adding the primary key constraint.

Conclusion

Even when using for exchange, you can still run into the “mismatched columns” problem (ORA-14097). In this particular example the problem is, that the addition of the primary key converts the key columns to NOT NULL. But this conversion is not reflected in the CREATE TABLE .. FOR EXCHANGE command.

Btw: The error can be avoided by creating the columns with NOT NULL in the first place.

SQL*Plus Basics – automate logfile name based upon script name

tl;dr

-- enable sql*plus to track the name of any currently running script as module name in application_info
set appinfo on

-- define sql*plus substitution variables SCRIPTNAME and LOGFILENAME
column script_name new_value scriptname
column logfile_name new_value logfilename

-- fetch name of the currently running SQL*plus script 
select regexp_replace(sys_context('userenv','module'),'^\d*@ *') as script_name from dual;

-- change suffix from .sql to .log and use as name for the log
select replace('&scriptname.','.sql$','.log') as logfile_name from dual;

-- start the log
spool &logfilename.

Purpose

Typical for an sql*plus script is that we spool the output to another file. Sometimes to export data and even more often to capture what happend in case of an error. The latter case is a typical example where the presented code will help to reduce hard coding the name of the script file into the file itself. So if the script name changes we don’t need to remember to change the hardcoded values inside the script.

Problem demonstration

$ sqlplus /nolog @myScript.sql
connect myUser/myPwd@myTnsAlias

spool myScript.log;

declare
  v_script varchar2(128);
begin
  v_script := 'myScript.sql';
  doSomething;
exception
   when others then 
     logger.log_error ('Error in Script '||v_script);
     raise;
end;
/

spool off;

The example shows that the name of the script “myScript” is hardcoded two times inside the script itself.

So whenever the name of the script changes, there is a good chance we need to change those two hardcoded occurences too. This includes cases where we copy this script as the basis for another script,

Here is how we can avoid the hard coding.

Solution

$ sqlplus /nolog @myScript.sql

-- enable sql*plus to track the name of any currently running script as module name in application_info
set appinfo on

-- define sql*plus substitution variables SCRIPTNAME and LOGFILENAME
column script_name new_value scriptname
column logfile_name new_value logfilename

-- fetch name of the currently running SQL*plus script 
select regexp_replace(sys_context('userenv','module'),'^\d*@ *') as script_name from dual;

-- change suffix from .sql to .log and use as log name
select replace('&scriptname.','.sql$','.log') as logfile_name from dual;

-- start logging
spool &logfilename.

declare
  v_script varchar2(128);
begin
  -- move sql*plus substitution variable into plsql variable
  v_script := '&scriptname.';
  doSomething;
exception
   when others then 
     -- add name of currently running script to the error log
     logger.log_error ('Error in Script '||v_script);
     raise;
end;
/
spool off;

Detailed code explanation

Some words what exactly is happening in the script.

Step 1) enable appinfo

First we need to enable SQL*plus to track the name of any script that is running as the module name in the application information of the session.

-- enable sql*plus to track the name of any currently running script 
-- as module name in application_info
set appinfo on

There are several ways to retrieve this information. We can see it in v$session or v$sqlarea, we can use dbms_application_info.read_module or we can check the userenv context module .

Step 2) define sql*plus variables

The name of the script should be stored in a sql*plus substituition variable scriptname which is not a bind variable.

Typically a substitution variable is created using the DEFINE command. But here we use the lesser known way of matching a column name from a select to such a variable.
column <column> new_value <variable>

-- define sql*plus substitution variables SCRIPTNAME and LOGFILENAME
column script_name new_value scriptname
column logfile_name new_value logfilename

To use such a variable the syntax is “&variable.” (without the “). The value of a substitution variable replaces (substitutes) the name of the variable anywhere inside the script. Ambersand “&” marks always the beginning and dot “.” can mark the end of the variable name, but the dot is optional. If a normal character (or even a dot) follows the substitution, then the additional dot is important to specify, where the name of the substition variable ends.

Because it is a sql*plus variable, this can replace values inside of strings.

Step 3) get the script name

Assumption is that a script was started, either by calling start scriptname (or short @scriptname) or by providing the name of the script when calling sqlplus from the shell. Both ways work.

We read the name of the script from the userenv: sys_context('userenv','module')

The value that is delivered seems to have some additional stuff at the beginning. This is something you should check in your system, how the file name exactly looks like. In my case it was the string
“01@ myScript.sql“

Some string manipulation is needed to get rid of the first part “01@ ” of the returned string. I used a regular expression to do so. Choose whatever you prefer.

-- fetch name of the currently running SQL*plus script 
select regexp_replace(sys_context('userenv','module'),'^\d*@ *') as script_name from dual;

The regexp replace command removes any number of digits “\d*” followed by “@” followed by a blank ” ” from the beginning “^” of the string and keeps the remainder intact.

Step 4) Get the logfilename from the scriptname and spool the log

Remember because of the column mapping earlier, the column logfile_name provides the value for our substitution variable logfilename.

'&scriptname.' is resolved at runtime into 'myScript.sql'.

Assuming that the script has a suffix .sql (default), we replace that suffix with .log.

-- change suffix to .log and use as log name
select replace('&scriptname.','.sql','.log') as logfile_name from dual;

-- start the log
spool &logfilename.

Step 5) Track the script name for error logging

  -- move sql*plus substitution variable into plsql variable
  v_script := '&scriptname.';

Recommendation is to use a plsql variable and put the SQL*plus substitution variable into the plsql variable. Then use this plsql variable wherever you need it, including the error handler. In general this should only be done for code directly inside your script, not inside some plsql packaged procedure for example. Reason is, that there is no guarantee, that the sql*plus variable is set during all executions of the packaged procedure.

criticism

• A working connection to the database is needed. So the log does not store every command that runs inside the script. Especially it can not include any potential connect commands nor the commands to find out about the name of the script.
• We can not generalize this code (step 3) into another script, because there the script name would be different.
• Because substitution variables need the ambersand “&” it is not easy to make sql*plus avoid interpreting ambersands that are part of normal strings as the beginning of a substitution variable.
  Or to say it in another way. Careful with set define off. A workaround sometimes can be to set define off at a later time in the script, when our variables are not used anymore or even to move the value from a substitution to a bind variable.