Help with a Perl expression...

[MAD_2]

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

[James R. Ferguson]

Hi:

Perhaps:

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

Regards!

...JRF...

[MAD_2]

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

[Stuart Browne]

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.

[James R. Ferguson]

Hi (again):

Instead of writing:

Q_NAME='Q$_FIELD_QUEUE'

do:

Q_NAME='Q\$_FIELD_QUEUE'

Regards!

...JRF...