I'm trying to write a simple program that will reach out to our primary and and replicated Oracle database to verify if they are in sync. The Oracle provided me the command I need to execute in SQLplus to get that information.
Here are the commands:
Connect / as sysdba
select thread#, max(sequence#) "Last Primary Seq Generated" from v$archived_log val, v$database vdb where val.resetlogs_change# = vdb.resetlogs_change# group by thread# order by 1;
If I open up a command prompt and go to SQLplus, then issue each command they work as expected.
Go into PowerShell on the same machine and create a multiline variable to pass to SQL plus if fails.
Define Multiline Variable:
PS C:\Users\wcierkowski01> $SQLQuery = @"
>> Connect / as sysdba
>> select thread#, max(sequence#) "Last Primary Seq Generated" from v$archived_log val, v$database vdb where val.resetlogs_change# = vdb.resetlogs_change# group by thread# order by 1;
>> Exit
>> "@
>>
PS C:\Users\wcierkowski01> $SQLQuery
Connect / as sysdba
select thread#, max(sequence#) "Last Primary Seq Generated" from v val, v vdb where val.resetlogs_change# = vdb.resetlogs_change# group by thread# order by 1;
Exit
Now perform the action:
PS C:\Users\wcierkowski01> $SQLQuery | sqlplus /nolog
SQL*Plus: Release 11.2.0.4.0 Production on Tue Aug 23 09:22:24 2022
Copyright (c) 1982, 2013, Oracle. All rights reserved.
SQL> Connected.
SQL> select thread#, max(sequence#) "Last Primary Seq Generated" from v val, v vdb where val.resetlogs_change# = vdb.resetlogs_change# group by thread# order by 1
*
ERROR at line 1:
ORA-04044: procedure, function, package, or type is not allowed here
SQL> Disconnected from Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
Can someone shed some light on this issue why it doesn't work passing the multi line variable?
TIA..
Passing Multi Line Variable to SQLPlus for Oracle
Forum rules
Do not post any licensing information in this forum.
Any code longer than three lines should be added as code using the 'Select Code' dropdown menu or attached as a file.
Do not post any licensing information in this forum.
Any code longer than three lines should be added as code using the 'Select Code' dropdown menu or attached as a file.
- waynecierkowski
- Posts: 28
- Last visit: Mon Jul 10, 2023 10:51 am
Re: Passing Multi Line Variable to SQLPlus for Oracle
First suggestion: Use @' ... '@ . That is, use single quotes on the outside of your @Here string. This will hide the dollar signs in the text.
$var = @'
string line
string "line2"
string li$ne3
'@
Single quotes make the string constant where double quotes cause the string to be parsed and may be turning strings starting with a dollar sign into a null string.
I recommend using either the Oracle ODA.Net driver or the Microsoft Oracle Net driver instead of SqlPlus. It is easier to manage and has fewer issues with accessing Oracle from PowerShell.
$var = @'
string line
string "line2"
string li$ne3
'@
Single quotes make the string constant where double quotes cause the string to be parsed and may be turning strings starting with a dollar sign into a null string.
I recommend using either the Oracle ODA.Net driver or the Microsoft Oracle Net driver instead of SqlPlus. It is easier to manage and has fewer issues with accessing Oracle from PowerShell.