Can SQL*PLUS features be set via the DBI module ?

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

Esteemed Elders,

I am using the DBI module to connect to an Oracle database and insert text. Of course the text has characters that would cause SQL*PLUS to puke, particularly the ampersand and quotes. To get around this I have to set the escape character to a certain symbol and then escape all SQL special characters that appear in the text. My question is, are there any methods/attributes available in the DBI module to set SQL*PLUS features for each connection?

Having asked that, let me say this... from the 'Programming the Perl BDI' book, I get the sense that there isnt a way to do this. It says the DBI has no concept of a "current session", so I guess that means you cannot set environment variables to last the duration of a session. I also read that statement handles are insulated from each other even if they share the same parent database handle, that suggests that the set SQL*PLUS features would not last across statement handles.

Anyway, I was hoping someone out there had encoutered this issue before and had some thoughts about it.

Comment on Can SQL*PLUS features be set via the DBI module ?

Replies are listed 'Best First'.
Re: Can SQL*PLUS features be set via the DBI module ? by ViceRaid (Chaplain) on Feb 26, 2002 at 16:48 UTC
I'd have thought that if you're using DBI with placeholders (either DBI-style ? or Oracle-style :1), the DBI should handle all your, err, quoting needs, without needing to use Oracle-specific features. However, the Oracle and Oracle8 DBD drivers do offer some support for Oracle-specific features, such as specifying the handling of BLOBs/LOBs/CLOBs. Some of these features are done through private methods on Oracle DBI objects, called via func(). (such as `$oradbh->func('plsql_errstr')`), and some are done as private attributes on the DBI handler. According to the DBI specification, private (database-specific attributes) of a handler are distinguished from DBI-global ones by starting with a lower-case letter `$dbh->LongTruncOK; # all DBI handles have this $dbh->specAttribute; # not portable across databases` [download] hth /=\ update: removed an innaccurate comment about which DBI attributes are DBD-specific, and corrected syntax for dbi private method calls.	[reply] [d/l]
Re: Re: Can SQL*PLUS features be set via the DBI module ? by brahma (Novice) on Feb 26, 2002 at 21:42 UTC
I went back and made a couple of test runs and you are right, DBI did handle the ampersands etc. just fine. I guess I was confused (as pointed out by perrin) about how DBI works. Since there is no SQLPLUS involved, there is no need for the SQLPLUS settings. Thanks to everybody who replied.	[reply]
Re: Can SQL*PLUS features be set via the DBI module ? by perrin (Chancellor) on Feb 26, 2002 at 17:19 UTC
I think you're confused about what DBI is doing. It doesn't use SQLPLUS. It uses the Oracle C libraries, which are also used by SQLPLUS, but that's as far as the similarity goes, and features of SQL*PLUS which are not generic Oracle features are not supported by DBI. However, DBI will do the quoting for you when you use bind variables.	[reply]
Re: Can SQL*PLUS features be set via the DBI module ? by pileswasp (Monk) on Feb 26, 2002 at 16:53 UTC
I've done things like: `$dbh->do( q(ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY') );` through the DBI before if that's the kind of thing you're after. I've tended to avoid SQL*PLUS in favour of the dbish whenever possible, though....	[reply] [d/l]
Re: Can SQL*PLUS features be set via the DBI module ? by rbc (Curate) on Feb 26, 2002 at 18:25 UTC
I don't think so. I don't think that DBI interfaces to SQLPlus therefore SQLPlus commands like ... edit, spool, set pagesize etc ... would just been seen as bad sql statements. But if you wanted to exec SQL*Plus commands from perl you could try something like this ... `open SQLPLUS, "sqlplus whatever/whoknows@somewhere\|"; print SQLPLUS << COMMANDS; set pageszie 1000 spool myfile desc myTable select blah from myTable / spool off / COMMANDS` [download] ... beware that the above is off the top of my head and I am not 100% sure that you can cut-n-paste it and have it run. I hope that some version of te above snippet can point you in the right direction.	[reply] [d/l]
Re: Can SQL*PLUS features be set via the DBI module ? by derby (Abbot) on Feb 26, 2002 at 17:23 UTC
brahma Will the quote method work for you? I know it will take care of quotes but I'm not certain about the ampersand but check DBD::Oracle to see if it has any special quote methods. `$var = $dbh->quote( "This is a & funny ones'" ); print $var, "\n";` [download] -derby	[reply] [d/l]
Re: Can SQL*PLUS features be set via the DBI module ? by subbu (Initiate) on Apr 27, 2004 at 09:07 UTC
Hi All, In the same line i have another problem. Using DBI modules "execute" function when i tried to created a relatively smaller procedure I was able to create it. But with the same "execute" function when i tried creating a procedure which is a bit larger in size (1100 lines) its the function is cribbing with an oracle error number 24344 and corresponding description says ORA-00000 normal, successfull completion. When I refered to the error cause i found that it was a compilation error. But when I ran the same procedure using "@" operator in SQL prompt procedure got created without any compilation error. What could be the reason and how to solve / overcome it ?	[reply]