Oracle procedure's LAST_DDL_TIME is not changed

Question

I am currently engaged in the development of a deployment scenario utilizing Octopus Technology for SQL Scripts. During this process, I have encountered an unusual issue. Specifically, when executing objects through scripts (such as PowerShell or Batch Files), the LAST_DDL_TIME column fails to update. This discrepancy has prompted my inquiry into the root cause of this behavior.

The following outlines the observed behavior when executing DDL statements directly from SQL Developer versus executing them from a script:

CREATE OR REPLACE procedure malath_dev.malath_test as
begin
DBMS_OUTPUT.put_line('test malath');
DBMS_OUTPUT.put_line('test malath2');
end;
/
  
CREATE OR REPLACE procedure malath_dev.print_time as
begin
DBMS_OUTPUT.put_line(to_char(sysdate, 'HH24:Mi:ss'));
end;
/  
GRANT INHERIT PRIVILEGES ON USER malath_dev TO PUBLIC;  

but if we execute the same DDL statements using a PowerShell script then nothing will be changed in LAST_DDL_TIME column.

$ExtractedPath = "D:\Projects\"
Write-Host "PWD: $PWD"
Write-Host "ExtractedPath: $ExtractedPath"
ls $ExtractedPath
$Username="malath_dev"
$Password = "malath_dev"
$ServiceName="orclpdb"
Write-Host "Schema Information : UserName : $Username Password: $Password ServiceName: $ServiceName"
$DBConnection = "$Username/$Password@$ServiceName"
$ORACLE_HOME="D:\Installation_Files\Oracle_DB_21\WINDOWS.X64_213000_db_home"
$SQLPLUS_PATH="$ORACLE_HOME\bin\sqlplus.exe"
  
$files = Get-ChildItem $ExtractedPath -Filter *.sql
for ($i=0; $i -lt $files.Count; $i++) 
{
    Write-Host "i: $i"
    $FileName = $files[$i].Name
    $SCRIPT_PATH = "@$ExtractedPath\$FileName"
    Write-Host "SCRIPT_PATH: $SCRIPT_PATH"
    Write-Host "SQLPLUS_PATH: $SQLPLUS_PATH"
    Write-Host "DBConnection: $DBConnection"
    Add-Content -Path "$ExtractedPath\$FileName" -Value "EXIT"
    & $SQLPLUS_PATH $DBConnection $SCRIPT_PATH  
}

Start-Sleep

The issue at hand is that the column is not consistently updated. To clarify, my investigation has revealed that the update occurs only when the script is executed following a prior execution through SQL Developer. Attempting to run the script multiple times at different intervals, for instance, results in the column not being updated.

Answer 1

The last_ddl_time is not updated if the procedure definition doesn't change. From the documentation it is:

Timestamp for the last modification of the object and dependent objects resulting from a DDL statement (including grants and revokes)

It seems that if the procedure code is exactly the same, including capitalisation and whitespace, then it is not seen as modified.

If you change the code at all, or force recompilation, then it will be seen as a modification, and the last_ddl_time will then be updated.

fiddle

If you have a support account, also see Document ID 472221.1.

If you are seeing this inconsistently for different procedures when you recreate them from SQL Developer then perhaps whitespace is being add/removed or converted (e.g. tabs to spaces) as you transfer the code, but that's just a guess. You might be doing other things with dependencies that are affecting it too.