Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
cancel
Showing results for 
Search instead for 
Did you mean: 

Help with a Perl expression...

MAD_2
Super Advisor

Help with a Perl expression...

I would like to know if someone can help me with the following; I am trying to get a query running from within a perl script, it reads as follows:

my $schema = 'test_schema';
my $queuename = 'Q$_FIELD_QUEUE';

my $query0 = 'select /* q-field-query */ count(*) from ' . "$schema" . '.FIELD_QUEUE where Q_NAME=' . "'" . '"' . "'$queuename'" . '"' . "'" . ';';

The query I obtain when I run the above is:

select /* q-field-query */ count(*) from test_schema.FIELD_QUEUE where Q_NAME='"'Q$_FIELD_QUEUE'"';"

However, the results are not correct as they do not match what I get when I run the query in a sqlplus session. That embedded '$' in the field that is being searched has become quite problematic, the SQL I'd like to run should be:

select /* q-field-query */ count(*) from test_schema.FIELD_QUEUE where Q_NAME='Q$_FIELD_QUEUE';

Any advise on how exactly I can get that result?

Thanks,

MAD
Contrary to popular belief, Unix is user friendly. It's just very particular about who it makes friends with
4 REPLIES
James R. Ferguson
Acclaimed Contributor

Re: Help with a Perl expression...

Hi:

Perhaps:

my $query0 = 'select /* q-field-query */ count(*) from ' . "$schema" . '.FIELD_Q
UEUE where Q_NAME=' . "'" . $queuename . "'" . ';';

Regards!

...JRF...
MAD_2
Super Advisor

Re: Help with a Perl expression...

Thank you for your reply James... I ommitted a very crucial part of information in my previous thread I'd like to include now.

This part of code is actually being used to build a full statement that is used to ssh into another system to run the SQL there.

The problem I have discovered now is that when feeding the code, the $ is truncated from the rest of the SQL code when running the statement. The ssh should be running this chunk of code (don't worry about the $ORACLE_SERVICE below, it's defined elsewhere in the code and since it's a variable obtained from the environment it's passed correctly):

ssh account@system '. /envpath/env; ORACLEPASS=`/usr/local/bin/sudo -u accountowner -H grep "^$ORACLE_USER" /passpath/.pwd | cut -f 2`; echo "alter session set current_schema = test_schema;
select /* q-field-query */ count(*) from test_schema.FIELD_QUEUE where Q_NAME='Q$_FIELD_QUEUE';" | /oracle/admin/bin/sqlplus -S ${ORACLE_USER}/${ORACLEPASS}@${ORACLE_SERVICE};'

The problem is occurring on the 'literal' "$" in the "Q$FIELD_QUEUE". How can I make it so that when this portion is passed to the ssh, it's taken as a literal in that context?

The way it's actually reading the SQL statement is now truncated to read only:

alter session set current_schema = test_schema;
select /* q-field-query */ count(*) from test_schema.FIELD_QUEUE where Q_NAME='Q';

And of course, that defined field does not exist so the query returns a false result.


MAD

Contrary to popular belief, Unix is user friendly. It's just very particular about who it makes friends with
Stuart Browne
Honored Contributor

Re: Help with a Perl expression...

The issue is that you're echo'n a double-quoted string, which allows the interpretation of the variables within it.

You need to echo the the query as a single-quoted echo, i.e.:

echo 'alter session set current_schema = test_schema; select .....

So you'll need to change the thing that generates that select to use "'s, or escaped ''s.

This should stop the 'echo' form interpreting the $'d Q_NAME in the shell.
One long-haired git at your service...
James R. Ferguson
Acclaimed Contributor

Re: Help with a Perl expression...

Hi (again):

Instead of writing:

Q_NAME='Q$_FIELD_QUEUE'

do:

Q_NAME='Q\$_FIELD_QUEUE'

Regards!

...JRF...