Search code examples
powershellazure-pipelinessqlplus

Azure Pipeline Powershell Task Executing SQL*Plus Always Results in SP2-0734

Within an Azure pipeline on the build server I want to do the following things in a Powershell script:

  1. Deploy the latest version of a stored procedure on the Oracle DB server
  2. Execute this stored procedure

Therefore I use SQL*Plus to deploy and execute the procedure. For some reason at #2 SQL*Plus always reports the following error: SP2-0734: unknown command beginning "execute..." - rest of line ignored.

This is the Powershell script:

$db_user = "user"
$db_pw = "password"
$db_conn = "server"

# 1. deploy stored procedure
sqlplus -s $db_user/$db_pw@$db_conn @Scripts\my_stored_procedure.prc

# 2. execute
"execute my_stored_procedure;" | sqlplus $db_user/$db_pw@$db_conn

It works locally.

For a minimal example, this one also works locally but not in the Azure pipeline:

"select 1 from dual;" | sqlplus $db_user/$db_pw@$db_conn

SP2-0734: unknown command beginning "select ..." - rest of line ignored.

Software information:

  • Azure DevOps Server 2020 Update 1.1
  • Oracle 19.11.0.0.0
  • SQL*Plus 18.3.0.0.0
Solution

  • As a workaround I created a "generic" SQL*Plus script which simply executes any PL/SQL code passed as a parameter:

    WHENEVER SQLERROR exit
exec &&1;
exit

    So I can call it like this in the Powershell task:

    # 2. execute
sqlplus -s $db_user/$db_pw@$db_conn @Scripts\execute_any_plsql.sql 'my_stored_procedure'

    I haven't found the cause for the original error, though.