

### Export Oracle database objects
<a name="export-oracle-database-objects"></a>

For an offline source, you upload exported script files that describe your source database objects. You can export them using any available tool. The following tabs describe two approaches: Oracle SQL Developer and Oracle SQLcl.

------
#### [ SQL Developer ]

1. In SQL Developer, choose **Tools**, and then choose **Database Export**.

1. For **Connection**, choose your database connection.

1. Select **Export DDL** and clear **Export Data**.

1. Set the DDL options according to the following table.

1. For **Save As**, choose **Separate Files**.

1. For **Encoding**, choose **UTF8**.

1. For **File**, enter the path to a directory for the output files. We recommend creating a directory with the same name as the schema.

1. Choose **Next**.

1. On the **Types to Export** page, select **Toggle All Standard** and **Toggle All Extension** to include all object types.

1. Choose **Next**.

1. On the **Specify Objects** page, for **Schema** choose the schema to export. For **Type**, choose **ALL OBJECTS**. Choose **Lookup** to populate the object list.

1. Choose **Next**, review the export summary, and then choose **Finish**.

**DDL export options**  
Set the following options on the **Source/Destination** page.


**DDL export options**  

| Option | Value | 
| --- | --- | 
| Partitioning | True | 
| Show Schema | True | 
| Terminator | True | 
| Storage | True | 
| Tablespace | True | 
| Drops | False | 
| Cascade Drops | False | 
| Grants | False | 
| Dependents | False | 
| Pretty Print | True | 
| Add BYTE keyword | True | 
| Add Force to Views | False | 
| Version | COMPATIBLE | 

SQL Developer exports database objects in its own way, and the results can vary. For the best conversion results, use complete and accurate scripts.

------
#### [ SQLcl ]

With Oracle SQLcl and the `project export` command, you can fully automate and script the export of database DDL.

**Prerequisites**  
To use Oracle SQLcl to export DDL scripts, you need the following:
+ PowerShell 7.x (`pwsh`)
+ Oracle SQLcl 26.2.0 or later
+ Java 17 or later
+ Oracle Database 11g R2 or later
+ Database user with `SELECT_CATALOG_ROLE` or equivalent privileges

**Export script**  
The following PowerShell script initializes an SQLcl project, configures export filters, and exports one SQL file for each database object.

```
param(
    [Parameter(Mandatory)][string]$Server,
    [string]$Port = "1521",
    [Parameter(Mandatory)][string]$Service,
    [Parameter(Mandatory)][string]$User,
    [Parameter(Mandatory)][string]$Password,
    [Parameter(Mandatory)][string]$Schema,
    [string]$OutputDir = "./$Schema",
    [string]$SqlclBin = "sql"
)

$ErrorActionPreference = "Stop"
$Project = "migration_project"
$ConnStr = "${User}/${Password}@${Server}:${Port}/${Service}"

if (-not (Test-Path $OutputDir)) {
    New-Item -ItemType Directory -Path $OutputDir -Force | Out-Null
}
$OutputDir  = (Resolve-Path -LiteralPath $OutputDir).Path
$ProjectDir = Join-Path $OutputDir $Project
$FiltersDir = Join-Path (Join-Path $ProjectDir ".dbtools") "filters"
$LogFile    = Join-Path $OutputDir "export.log"

function Invoke-Sqlcl([string]$Name, [string]$Content) {
    $file = Join-Path $OutputDir $Name
    Set-Content -Path $file -Value $Content -Encoding utf8NoBOM
    & $SqlclBin $ConnStr "@$file"
    if ($LASTEXITCODE -ne 0) { throw "SQLcl failed ($Name), exit code $LASTEXITCODE. See $LogFile" }
}

Write-Host "Exporting schema [$Schema] from ${Server}:${Port}/${Service} ..."

Invoke-Sqlcl "export-init.sql" @"
spool "$LogFile"
project init -d "$OutputDir" -n $Project -s "$Schema" -mr
spool off
exit
"@

if (-not (Test-Path $FiltersDir)) { throw "SQLcl project was not created: $FiltersDir not found. See $LogFile" }
Set-Content -Path (Join-Path $FiltersDir "ddl.filters") -Encoding utf8NoBOM -Value @(
    " owner =ANY('$Schema')"
    ",export_type != 'ALL_TAB_PRIVS'"
    ",export_type != 'USER_SYS_PRIVS'"
    ",export_type != 'ALL_USERS'"
    ",export_type != 'APEX_APPLICATIONS'"
    ",export_type != 'ORDS_SCHEMA'"
)
Set-Content -Path (Join-Path $FiltersDir "dml.filters") -Encoding utf8NoBOM -Value @(
    " export_type != 'TABLES_DML'"
    ",export_type != 'USER_ROLE_PRIVS'"
    ",export_type != 'ALL_USERS'"
)

# Step 3: configure the DDL transforms and export one file for each object.
Invoke-Sqlcl "export.sql" @"
spool "$LogFile" append
cd "$ProjectDir"

project config set -name export.format.enable -value true
project config set -name export.setTransform.constraintsAsAlter -value true
project config set -name export.setTransform.constraints -value true
project config set -name export.setTransform.emitSchema -value true
project config set -name export.setTransform.sizeByteKeyword -value true
project config set -name export.setTransform.sqlterminator -value true
project config set -name export.setTransform.partitioning -value true
project config set -name export.setTransform.segmentAttributes -value true
project config set -name export.setTransform.storage -value true
project config set -name export.setTransform.tablespace -value true

project export -t 8 -v

spool off
exit
"@

Write-Host "Done."
```

**Important considerations**  
With SQLcl, some objects might not be exported because of the following known behaviors:
+ SQLcl converts all object names to lowercase when creating filenames. Objects whose names differ only by letter case (for example, `PKG_NAME` and `Pkg_Name`) produce the same filename, and the second object silently overwrites the first.
+ SQLcl cannot export object names that contain characters not supported by the file system. On Windows, the characters `: * ? " < > |` are not allowed in filenames. Object names that contain the dollar sign (`$`) character cause errors in the SQLcl file-writing mechanism.
+ On macOS, objects whose names differ only by Unicode representation (precomposed versus combining accent) produce the same filename because of filesystem normalization. This does not affect Linux or Windows.
+ SQLcl silently skips database objects in an invalid state (for example, a view that references a dropped table). Review the export log to identify any skipped objects.

------

Export tools vary in which object types they export and how they write each object. Oracle SQL Developer and Oracle SQLcl are two examples. As a result, the source objects that DMS Schema Conversion shows for an offline source can depend on the tool you used. After you export, compare the exported objects with your source database and add scripts for any that are missing. Don't upload full exports from more than one tool for the same schema, because this creates duplicate objects in the offline source.

The following object categories are the ones most likely to differ between export tools. Check each one after you export:
+ Users and schemas
+ Database links
+ Scheduler jobs, programs, and schedules

DMS Schema Conversion determines the schema that owns an object as follows. For most objects, it uses the schema in the object's fully qualified name. If the name doesn't include a schema, DMS Schema Conversion assigns the object to the `PUBLIC` schema. For a database link, if a `CREATE USER` script is in the same folder as the link script, DMS Schema Conversion assigns the link to that schema. Otherwise, DMS Schema Conversion assigns the link to the `PUBLIC` schema. Place each object's script in the folder for its owner. For more information about the `CREATE USER` requirement and the folder structure, see [Prerequisites for an offline source](#virtual-source-prerequisites).

Some tools don't export every category. For example, SQL Developer doesn't export `CREATE USER` statements, and the SQLcl `project export` command doesn't export database links. When a category is missing, add the scripts yourself.

**Example: Add a missing schema definition**  
SQL Developer doesn't export a `CREATE USER` script, so the schema node is missing from the object tree. Add a file to the schema's folder that contains the following statement, replacing {{HR}} with your schema name:  

```
CREATE USER {{HR}} NO AUTHENTICATION;
```

**Example: Add a missing database link**  
The SQLcl `project export` command doesn't export database links, so you add them yourself. DMS Schema Conversion assigns a database link to the schema whose `CREATE USER` script is in the same folder as the link script. Put the link script in the owner's folder. For example, place a link owned by {{HR}} in the same folder as the `HR` `CREATE USER` script.  
If the folder has no `CREATE USER` script, DMS Schema Conversion assigns the link to the `PUBLIC` schema.