Ignoring "$" sign in sql-statement to Oracle

gizmojunkee has asked for the wisdom of the Perl Monks concerning the following question:

Hello Monks, I was wondering if someone can help me out here. I work on some Reporting scripts and now came across the following Problem. I access a Oracle Database via the DBA Module and while with other statements I have no Problem this particular Table I want to query is called v$sessions. As you can see the Table name got the "$" in the name. With my statement I need somehow to ignore the "$" sign and just parse it as the table name. How can I do that?

#!/usr/bin/perl -w
use DBI qw(:sql_types);

my ($SID,$SERIAL,$USERNAME,$STATUS,$SCHEMANAME,$OSUSER,
$PROCESS,$MACHINE,$TERMINAL,$PROGRAM,$TYPE,$MODULE,
$LOGON_TIME,$STATE,$SERVICE_NAME);

my $sql = qq { 
select SID,SERIAL,USERNAME,STATUS,SCHEMANAME,OSUSER,
PROCESS,MACHINE,TERMINAL,PROGRAM,TYPE,MODULE,
to_char(LOGON_TIME,'DD-MON-YY HH24:MI:SS')"LOGON_TIME",
STATE,SERVICE_NAME from v$session order by LOGON_TIME DESC};

my $dbh = DBI->connect($parm,$user,$password, { RaiseError => 1, AutoC
+ommit => 0 });

$sth = $dbh->prepare($sql) or die "Couldn't prepare statement: " . $db
+h->errstr;

$sth->execute() or die "Couldn't execute statement: " . $dbh->errstr;

print "@row\n";
[download]

Thanks for some input

Comment on Ignoring "$" sign in sql-statement to Oracle Download Code

Replies are listed 'Best First'.
Re: Ignoring "$" sign in sql-statement to Oracle by kennethk (Abbot) on Mar 29, 2011 at 15:05 UTC
Your issue is that the `qq` delimiter interpolates variables (see Quote and Quote like Operators). You can either swap to an operator that does not interpolate, such as `q`, or you can escape the `$` with a backslash, like `my $sql = qq { select SID,SERIAL,USERNAME,STATUS,SCHEMANAME,OSUSER, PROCESS,MACHINE,TERMINAL,PROGRAM,TYPE,MODULE, to_char(LOGON_TIME,'DD-MON-YY HH24:MI:SS')"LOGON_TIME", STATE,SERVICE_NAME from v\$session order by LOGON_TIME DESC};` [download] If I were writing this, I would likely use a here-doc: `my $sql = <<'EOSQL'; select SID,SERIAL,USERNAME,STATUS,SCHEMANAME,OSUSER, PROCESS,MACHINE,TERMINAL,PROGRAM,TYPE,MODULE, to_char(LOGON_TIME,'DD-MON-YY HH24:MI:SS')"LOGON_TIME", STATE,SERVICE_NAME from v$session order by LOGON_TIME DESC} EOSQL` [download] Note that the `'` in the here-doc suppresses interpolation.	[reply] [d/l] [select]
Re^2: Ignoring "$" sign in sql-statement to Oracle by gizmojunkee (Novice) on Mar 29, 2011 at 15:43 UTC
Awesome, thanks for your swift help guys that totally made sense.	[reply]
Re: Ignoring "$" sign in sql-statement to Oracle by locked_user sundialsvc4 (Abbot) on Mar 29, 2011 at 16:42 UTC
Here are a few specific suggestions from painful experience: As noted, always be mindful of how Perl treats single vs. double quotes. A literal quote-mark must be escaped with a backslash in a string surrounded by double quotes, but in a string surrounded by single quotes nothing is changed at all. Always use query-parameters in your statements. These are literal (un-quoted) question-marks. Once you `prepare` the statement, parameters are substituted when you `execute` it. This is not only more efficient for the SQL system, but it also eliminates any threat of “SQL injection,” or simply, “statements that may or may not work, depending on the input data.” (It will happen to you ... and always at three o’clock in the morning.) I suggest retrieving the data using `fetchrow_hashref` vs. the various other alternatives. This will return a hashref in which the key is the column-name. Data::Dumper is also your best friend, because it lets you easily see (while you are writing and then debugging the thing) exactly what the hash-keys will be... whether they are upper or lower or mixed-case or what have you. Be mindful of exactly how the various calls return error-conditions. Make sure that your code actually does test for these errors, and does respond appropriately to them. (`die` or `croak` (mod:://Carp) are very good ways to “hit the ejection-seat” in the middle of complex logic when something unexpectedly goes South.) Actually test error cases, as well as ones that work properly.
Re^2: Ignoring "$" sign in sql-statement to Oracle by cavac (Prior) on Mar 29, 2011 at 17:22 UTC
Check if your particular database and databse driver support prepare_cached(). On complex statements, this can speed up the compile/optimization process and will lighten the CPU load on the database server quite a lot. You will need to read your database documentation on which queries get cached and which don't, but it can be very rewarding in terms of not having to buy a bigger, more expensive server.	[reply]
Re: Ignoring "$" sign in sql-statement to Oracle by Anonymous Monk on Mar 29, 2011 at 15:08 UTC
Unless you coincidentally have a variable already call $session that coincidentally contains '$session', then you need to prevent the interpolation of the '$' that is being caused by use of 'qq', simply placing a '\' in front will achieve this. Alternatively, as there is no real need for the interpolation in this case you could simple use 'q' instead of 'qq' Example 1 `my $sql = qq { select SID,SERIAL,USERNAME,STATUS,SCHEMANAME,OSUSER, PROCESS,MACHINE,TERMINAL,PROGRAM,TYPE,MODULE, to_char(LOGON_TIME,'DD-MON-YY HH24:MI:SS')"LOGON_TIME", STATE,SERVICE_NAME from v\$session order by LOGON_TIME DESC};` [download] Example 2 `my $sql = q { select SID,SERIAL,USERNAME,STATUS,SCHEMANAME,OSUSER, PROCESS,MACHINE,TERMINAL,PROGRAM,TYPE,MODULE, to_char(LOGON_TIME,'DD-MON-YY HH24:MI:SS')"LOGON_TIME", STATE,SERVICE_NAME from v$session order by LOGON_TIME DESC};` [download]	[reply] [d/l] [select]