77 lines
2.7 KiB
PowerShell
77 lines
2.7 KiB
PowerShell
# roa-sql.ps1 - ruleaza SQL prin sqlplus, determinist.
|
|
# Elimina refacerea manuala de fiecare data: scris fisier .sql ASCII + setat TNS_ADMIN/NLS_LANG
|
|
# + rulat sqlplus. Capcanele rezolvate: BOM-ul care sparge sqlplus (SP2-0734) si `termout off`
|
|
# care ascunde erorile (nu-l foloseste).
|
|
#
|
|
# Utilizare:
|
|
# powershell -File COMUN\utile\roa-sql.ps1 -Schema MARIUSM_AUTO -Sql "select user from dual"
|
|
# powershell -File COMUN\utile\roa-sql.ps1 -Schema MARIUSM_AUTO -Fisier "D:\cale\q.sql"
|
|
# powershell -File COMUN\utile\roa-sql.ps1 -Con "ROMFAST/<parola>@ROA_ROMFAST" -Sql "select user from dual"
|
|
#
|
|
# Parametri:
|
|
# -Schema numele schemei; completeaza /<parola standard din fisierul de secrete>@<alias>
|
|
# -Con sirul de conectare complet (schema/parola@alias) - foloseste-l pentru productie
|
|
# -Alias alias TNS cand folosesti -Schema (default ROA_CENTRAL)
|
|
# -Sql instructiune SQL directa -- SAU -- -Fisier fisier .sql existent
|
|
#
|
|
# Parola NU e hardcodata: -Schema o citeste din COMUN\docs\local\oracle.md (fisier de secrete,
|
|
# NEVERSIONAT in git). Pentru productie, da -Con cu parola din acelasi fisier.
|
|
#
|
|
# Atentie: interogarile pe productie se fac doar SELECT, cu `fetch first N rows only`.
|
|
param(
|
|
[string]$Schema,
|
|
[string]$Con,
|
|
[string]$Alias = 'ROA_CENTRAL',
|
|
[string]$Sql,
|
|
[string]$Fisier
|
|
)
|
|
|
|
$ErrorActionPreference = 'Stop'
|
|
$sqlplus = 'D:\ROA\instantclient_19_18\sqlplus.exe'
|
|
$env:TNS_ADMIN = 'D:\ROA\instantclient_19_18'
|
|
$env:NLS_LANG = 'AMERICAN_AMERICA.AL32UTF8'
|
|
|
|
function Ia-ParolaStandard {
|
|
$fisier = Join-Path $PSScriptRoot '..\docs\local\oracle.md'
|
|
if (-not (Test-Path -LiteralPath $fisier)) { return $null }
|
|
$text = [IO.File]::ReadAllText($fisier)
|
|
if ($text -match 'MARIUSM_AUTO/([A-Za-z0-9_]+)@ROA_CENTRAL') {
|
|
return $Matches[1]
|
|
}
|
|
return $null
|
|
}
|
|
|
|
if ($Con) {
|
|
$conn = $Con
|
|
} elseif ($Schema) {
|
|
$parola = Ia-ParolaStandard
|
|
if (-not $parola) {
|
|
Write-Output 'roa-sql: nu gasesc parola standard in COMUN\docs\local\oracle.md - foloseste -Con.'
|
|
exit 1
|
|
}
|
|
$conn = "$Schema/$parola@$Alias"
|
|
} else {
|
|
Write-Output 'roa-sql: da -Schema (dev) sau -Con (productie).'
|
|
exit 1
|
|
}
|
|
|
|
if ($Fisier) {
|
|
$sqlText = [IO.File]::ReadAllText((Resolve-Path $Fisier))
|
|
} elseif ($Sql) {
|
|
$sqlText = $Sql
|
|
} else {
|
|
Write-Output 'roa-sql: da -Sql sau -Fisier.'
|
|
exit 1
|
|
}
|
|
|
|
$tmp = Join-Path $env:TEMP ('roa-sql-' + [guid]::NewGuid().ToString('N') + '.sql')
|
|
try {
|
|
$sqlText = $sqlText.Trim()
|
|
if (-not $sqlText.EndsWith(';')) { $sqlText += ';' }
|
|
$sqlText += "`r`nexit`r`n"
|
|
[IO.File]::WriteAllText($tmp, $sqlText, (New-Object System.Text.UTF8Encoding($false)))
|
|
& $sqlplus -S -L $conn ('@' + $tmp)
|
|
} finally {
|
|
Remove-Item -LiteralPath $tmp -Force -ErrorAction SilentlyContinue
|
|
}
|