Passing Multi Line Variable to SQLPlus for Oracle

Ask your PowerShell-related questions, including questions on cmdlet development!
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.
This topic is 1 year and 7 months old and has exceeded the time allowed for comments. Please begin a new topic or use the search feature to find a similar but newer topic.
Locked
User avatar
waynecierkowski
Posts: 28
Last visit: Mon Jul 10, 2023 10:51 am

Passing Multi Line Variable to SQLPlus for Oracle

Post by waynecierkowski »

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..
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: Passing Multi Line Variable to SQLPlus for Oracle

Post by jvierra »

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.
This topic is 1 year and 7 months old and has exceeded the time allowed for comments. Please begin a new topic or use the search feature to find a similar but newer topic.
Locked