Re: Problem in inserting utf8(or multibyte char) through Perl DBI
Nilanjan, The database should know that your program (the client) is feeding it UTF8 data. You can make the database know that by setting the NLS_LANG environment variable correctly. Set it for example to American_America.UTF8. Brgds, Mark - Original Message - From: Nilanjan Das [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, December 15, 2001 6:20 AM Subject: Problem in inserting utf8(or multibyte char) through Perl DBI Hi Gurus, I tried all tricks but still no luck. I am trying to do an insert in Oracle database, through Perl DBI. My data contains multibyte character(utf8). The insert goes fine but the inserted data loose the control chars. Specially if the data contains multibyte stuffs, then only one byte is getting inserted. Is there any way to make DBI compatible with this or should I set anything in Perl before doing this? Please help. Thanks, Nilanjan
Re: Oracle Insert Problem-Urgent
The more generic solution would be to use DBI's quote function : eg.
my $st = $db-prepare(insert into Tname values(.
$db-quote(Gopi's) . , '20'));
Hope this helps,
Mark
Hardy Merrill wrote:
I think I remember that to escape a single quote so that you
can include it in a string, you need to escape a single quote
with a single quote, like this:
inset into Tname values('Gopi''s','20');
HTH.
--
Hardy Merrill
Mission Critical Linux, Inc.
http://www.missioncriticallinux.com
gopinath r [[EMAIL PROTECTED]] wrote:
Hai
my problem is to insert a varchar2 datatype which
contains single quote(') into Oracle table from Perl.
i am using ActivePerl for windows...how to handle this
problem.
eg. inset into Tname values('Gopi's','20');
--
Thanks
gopi
__
Do You Yahoo!?
Make a great connection at Yahoo! Personals.
http://personals.yahoo.com
Re: problems with unicode
Nikko, Depending on what you are trying to do, it might not really matter whether DBI supports Unicode or not. Oracle always does a characterset translation between client and server. It translates data coming from the server to the characterset specified in your NLS_LANG environment variable. It defaults to US7ASCII if this variable is not set. You should set the characterset part of your NLS_LANG environment variable to either AL24UTFFSS (if your client is pre-8.1) or UTF8 (8.1 or higher). Then all data fetched from the database will end up encoded in UTF-8 in your Perl variables. What happens after that is up to your program. There is no direct support for UCS-2 encoding that I know of. But translation shouldn't be hard. I saw some module on CPAN that could do this. By the way : when Oracle doesn't know how to translate a character to your client characterset, it replaces it by a '?'. Hope this helps, Mark - Original Message - From: Nikko Odiseos [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, October 08, 2001 9:30 PM Subject: problems with unicode Trying to get DBI to select from a database with unicode characters Anyone out there successfully know of what I need to do to get unicode data out of a database? I've seen a lot of postings on the net with people having issues with this my result set gets ? instead... It seems to be DBI, the DBD, or the ODBC driver that's returning I know what's going on.. but I'm at a loss what to do to fetch the nvar data. And I know that the characters are encoded in the database as Unicode. Can someone confirm or tell me the bad news definitivelyu that DBI whether DBI supports Unicode. Nikko
Re: date format in Oracle
Jie,
Oracle doesn't support timezones in date format strings, unfortunately.
Brgds,
Mark
jie zhang wrote:
Somehow, TZ is not recognized in my system. I am using oracle 8.1.7 on
solaris 5.8.
Did you actually tried out in your system ? Do I need to
set up any NLS variable in order to use the 'TZ' keyword ?
Thanks,
Jie
Anna Fong wrote:
Use Oracle's to_date function.
Example:
select ename, hiredate
from emp
where hiredate = TO_DATE('Thu Sep 20 15:03:10 PDT 2001','DY MON DD
HH24:MI:SS TZ ');
At 03:58 PM 10/01/2001 -0700, jie zhang wrote:
Does anybody know if Oracle can take the date format as 'Thu Sep 20
15:03:10 PDT 2001') ?
Thanks in advance,
Jie
Anna Q. Fong, Webmaster
California Data Exchange Center
--
Mark Vandenbroeck Mobile : +32-495-59.55.62
Business Process Manager Email : [EMAIL PROTECTED]
EMEA Support Information Systems AIM: markvdb
Re: Correct Oracle environment Needed for DBI
Susan, My guess is that you installed DBD::Oracle with your environment pointing to 8.1.7. When you install DBD::Oracle, it is linked with the Oracle client libraries. If you run a program linked in an 8.1.7 environment, but (because of your environment variables) it picks up the dynamic libraries of an older version, you're asking for trouble. You should provide your DBD::Oracle with the client libraries it was linked with at install time. So, if you have a mixed version environment, the best way to go is : * Install DBD::Oracle against the highest version * Always keep your environment variables (ORACLE_HOME, PATH and LD_LIBRARY_PATH) pointing to this same environment. If you need to connect to other versions, use SQL*Net. Hope this helps, Brgds, Mark [EMAIL PROTECTED] wrote: I have a program that sets the Oracle environment for 8.1.7 and connects to the database just fine. If I resent the environment to 8.0.5 and try to connect to an 8.0.5 database, it fails with a OCIEnvInit error. If I leave the environment at 8.1.7 and connect to the 8.0.5 database, it succeeds. If I run from cron with no environment set, it works fine. I am obviously missing something basic about the Oracle environment the dbd needs to run. Should an environment never be set or should it always be set to 8i? Thanks Susan * PRIVILEGED AND CONFIDENTIAL: This communication, including attachments, is for the exclusive use of addressee and may contain proprietary, confidential and/or privileged information. If you are not the intended recipient, any use, copying, disclosure, dissemination or distribution is strictly prohibited. If you are not the intended recipient, please notify the sender immediately by return e-mail, delete this communication and destroy all copies. * -- Mark Vandenbroeck Mobile : +32-495-59.55.62 Business Process Manager Email : [EMAIL PROTECTED] EMEA Support Information Systems AIM: markvdb
Re: Oracle prepare failed?
Kevin, Your error is 'table or view does not exist'. Did you connect as the same Oracle user in SQL*Plus and perl ? Mark Kevin Bass wrote: Please post your entire statement: my $sth = $dbh-prepare(qq(SELECT fuser.us_initl, . . . Thanks - Original Message - From: Michael Ragsdale [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, September 17, 2001 3:00 PM Subject: Oracle prepare failed? I have a query that works just fine in Oracle SQL*Plus, but I receive the following error when run through DBI. Line 35 states: my $sth = $dbh-prepare(qq(SELECT fuser.us_initl, I'm running: Redhat 7.1, Perl 5.6.0, DBI 1.14, DBD::Oracle 1.06 Here is the trace file: DBI 1.14-nothread dispatch trace level set to 2 - prepare for DBD::Oracle::db (DBI::db=HASH(0x81c17bc)~0x81c17ec 'SELECT fuser.us_initl, finventory.inumber, faccounttrans.at_amount, finventory.iposscasenumber, finventory.inotes FROM finventory, fpermittype, ...') !! ERROR: 942 'ORA-00942: table or view does not exist (DBD ERROR: OCIStmtExecute/Describe)' - prepare= undef at cron.permit_replace.pl line 35. - DESTROY for DBD::Oracle::st (DBI::st=HASH(0x81c175c)~INNER) - DESTROY= undef at cron.permit_replace.pl line 66. - DESTROY for DBD::Oracle::db (DBI::db=HASH(0x81c17ec)~INNER) - DESTROY= undef at unknown location! -- DBI::END - disconnect_all for DBD::Oracle::dr (DBI::dr=HASH(0x816e084)~0x81c1804) - disconnect_all= '' at DBI.pm line 450. - DESTROY in DBD::_::common for DBD::Oracle::dr (DBI::dr=HASH(0x81c1804)~INNER) - DESTROY= undef during global destruction. -Mike -- Mark Vandenbroeck Mobile : +32-495-59.55.62 Business Process Manager Email : [EMAIL PROTECTED] EMEA Support Information Systems AIM: markvdb
Re: performance comparison of two update statements
Hi,
I can only speak for Oracle. I don't know about other databases. But since
most databases implement indexes based on B-trees or something similar, I
guess this will hold for most, if not all, of them.
If you update an indexed column, the index entry is deleted and a new one
inserted. This is how B-trees and derived algorithms work. That means that a
generic update statement that updates all columns will be slower if there
are indexed columns.
Brgds,
Mark
- Original Message -
From: Dong Wang [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Tuesday, September 18, 2001 7:14 PM
Subject: performance comparison of two update statements
Hi,
When designing some library tools using dbi/dbd to update a table in an
Oracle db, we are facing the following scenario:
Let's say there is a table created as:
SQL create table test1 (c1 varchar2(10) primary key, c2 varchar2(10), c3
varchar2(10));
SQL create index test1_ix1 on test1(c2);
SQL insert into test1 values ('a', 'b', 'c');
SQL commit;
Now compare the following two update statements:
statement1: update test1 set c3 = 'd' where c1 = 'a';
statement2: update test1 set c2 = 'b', c3 = 'd' where c1 = 'a';
They both result in the same final result, but does statement2 involves
some
extra work on the index created on c2 so statement1 is more efficient?
From
the programmers point of view, it is easier to write some generic update
statements without know exactly which column is being actually updated.
Thanks.
Dong Wang
Grand Central Networks
[EMAIL PROTECTED]
Re: ORA-00936:missing expression when insert
Mikyung,
What ARE the values in $values[n], where n in (0, 8, 9, 10) ? Insert a
print statement before the execute. If any of these values is undefined or
an empty string, you got a syntax error. Which is what the ORA-936 is
telling you.
Brgds,
Mark
M. Jang wrote:
Thank you but I counted again to find the number of columns are same 20.
$values[n] (n=0,8,9,10) are numbers and others
(n=1,2,3,4,5,6,7,11,12,13,14,15,16,17,18,19) given with execute
are string.
any idea?
dirk van der Giesen wrote:
Hi Mikyung,
You indeed got one value missing ;)
The insert has 20 values and the execute 19.
So add one for your execute or delete one from the
table.
Dirk van der Giesen
--- M. Jang [EMAIL PROTECTED] wrote:
Hello,
I wrote a simple insert program like
$sth = $dbh-prepare( qq{
INSERT INTO $table VALUES
($values[0],?,?,?,?,?,?,?,$values[8],
$values[9], $values[10],?,?,?,?,?,?,?,?,?)
} );
$sth-execute($values[1],$values[2],$values[3],$values[4],$values[5],
$values[6],$values[7],$values[11],$values[12],$values[13],
$values[14],$values[15],$values[16],$values[17],$values[18],
$values[19]);
and got ORA-00936:missing expression error message.
I saw each $values[n] string has correct value.
any suggestion, plz?
Mikyung/
__
Terrorist Attacks on U.S. - How can you help?
Donate cash, emergency relief information
http://dailynews.yahoo.com/fc/US/Emergency_Information/
--
Mark Vandenbroeck Mobile : +32-495-59.55.62
Business Process Manager Email : [EMAIL PROTECTED]
EMEA Support Information Systems AIM: markvdb
Re: CTX_QUERY - Intermedia
Hi Simon,
Your application must have been developed against an older version of
Oracle/Intermedia. It uses the so-called 2-step query, which isn't supported
anymore in 8.1.6. Only the one-step query is supported now. But in return,
it became blazingly fast, compared to older versions.
You should check your Intermedia manual.
In any case, this isn't a DBI or DBD::Oracle problem.
Brgds,
Mark
- Original Message -
From: Simon Mailhot [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Tuesday, June 26, 2001 8:23 PM
Subject: CTX_QUERY - Intermedia
Hi,
I'm installing a perl program to a new computer (new installation of the
DB on another computer, new installation of Apache, ...) . The program is
using CTX_QUERY but it dosent work. Intermedia is installed with Oracle
8.1.6 and I'm using a Apache 1.3.20 server on a RedHat 6.2 system.
Error in the error_log of Apache:
PLS-00302: component 'HITTAB' must be declared
ORA-06550: line 3, column 13:
PL/SQL: Statement ignored (DBD ERROR: OCIStmtExecute)
Search-lookup: execute failed: ORA-06550: line 3, column 23:
PLS-00302: component 'CONTAINS' must be declared
ORA-06550: line 3, column 13:
PL/SQL: Statement ignored (DBD ERROR: OCIStmtExecute)
The Perl source code look like this:
my $sth = $self-{DBH}-prepare(q{
BEGIN
ctx_query.gettab(CTX_QUERY.HITTAB, ?, 2);
END;
});
my $sth = $self-{DBH}-prepare(q{
BEGIN
ctx_query.contains(?, ?, ?);
END;
});
Can you help me ?
Tanks
Simon
Re: Help with semi colons and Oracle blocks through dbi
Dave, Intended behavior. Check any SQL manual, the syntax descriptions won't speak about an ending semi-colon. It is not part of the statement. The semi-colon is syntax specific to SQL*Plus, telling it when your statement ends, so that it can start sending it to the server. It works only in SQL*Plus. Brgds, Mark Dave Anderson wrote: Hi, I'm getting well confused about this - This statement $sth = $dbh-prepare(drop table ADDRESS); $sth-execute(); works, while this one $sth = $dbh-prepare(drop table ADDRESS;); $sth-execute(); gives DBD::Oracle::st execute failed: ORA-00900: invalid SQL statement (DBD ERROR: OCIStmtExecute) I have a script for ddl stuff, with various statements ended by semi colons. These give me invalid sql statements in dbi, even though they work with sql plus. So I had my perl script strip out the semi colons, and each time it does that, execute the current block. That all works fine through dbi, until I come to create a trigger: $sth = $dbh-prepare(CREATE OR REPLACE TRIGGER ADDRESS_ID_TRIG BEFORE INSERT ON ADDRESS FOR EACH ROW BEGIN SELECT ADDRESS_SEQ.NEXTVAL INTO :NEW.ADDRESS_ID FROM DUAL); $sth-execute(); $sth = $dbh-prepare(END); $sth-execute(); This also gives DBD::Oracle::st execute failed: ORA-00900: invalid SQL statement (DBD ERROR: OCIStmtExecute) for the second execute. the original sql, with DUAL; END; works fine through sqlplus. I must be doing this all wrong. Can anyone help? many thanks in advance Dave -- Mark Vandenbroeck Mobile : +32-495-59.55.62 Business Process Manager Email : [EMAIL PROTECTED] EMEA Support Information Systems AIM: markvdb
Re: Apache::DBI / DBD::Oracle error
Hi all, ORA-3113 means that the client process (DBD in this case) lost it's connection with the server process. This can be caused by network problems, but most often it's because the server process crashed unexpectedly. In that case, it always leaves a tracefile behind (in user_dump_dest), which should contain more information on what happened. Brgds, Mark Ged Haywood wrote: Hi there, On Mon, 11 Jun 2001, Eric Kolve wrote: I recently upgraded to DBI 1.18 + DBD::Oracle 1.07 and upon restarts with mod_perl, I get the following error: DBD::Oracle::db rollback failed: Error while trying to retrieve text for error ORA-03113 (DBD ERROR: OCITransRollback) at /usr/lib/perl5/site_perl/5.005/Apache/DBI.pm line 153. I am running Apache::DBI 0.88. I didn't see this error with DBI 1.14 + DBD::Oracle 1.06. Anyone have any ideas? Have you done something else as well? I don't think it's necessarily DBI/DBD related, I think It's somthing to do with National Language Support (NLS). Have you got NLS_LANG or ORA_NLS33 set to something? 73, Ged. -- Mark Vandenbroeck Mobile : +32-495-59.55.62 Business Process Manager Email : [EMAIL PROTECTED] EMEA Support Information Systems AIM: markvdb
Re: Apache::DBI / DBD::Oracle error
Hi all, There seems to be some confusion and even incorrectness in this trail. Let's get some things straight : * There is nothing wrong with NLS_LANG. In fact, it is extremely useful in an international setting. Your value is fine. * ORA_NLS33 is obsolete in 8.1.7 and should not be set * Failure to translate error number to text : Oracle constructs the name of it's message file from the ORACLE_HOME, plus some path, some language code and some product code. You get the error when it can't find the message file. Either the file doesn't exist (shouldn't happen) or it can't find it. Best guess is the ORACLE_HOME is not set or not set correctly. * You can always find the error text yourself in $ORACLE_HOME/rdbms/mesg/oraus.msg by grepping for the error number * First step to tackle an ORA-3113 (End-of-file on Communication Channel) is _always_ to find the tracefile in user_dump_dest on the server side and interpret what's in there. The stuff in there is not usually fit for human consumption, but intended for Oracle Support. Nevertheless, it contains all the clues. I should know, I did this kind of support for 7 years. Hope this helps, Mark Ged Haywood wrote: Hi again, On Tue, 12 Jun 2001, Eric Kolve wrote: Yes, I have NLS_LANG set to AMERICAN_AMERICA.WE8ISO8859P1. I seemed to get on better by removing NLS_LANG and ORA_NLS33 from my environment altogether. BTW, I am also using the latest oracle client libs (8.17) if this makes any difference; Dunno! as well, I am using perl 5.005. Shouldn't think that would stop you, but I'm using 5.7.0. DBD::Oracle::db rollback failed: Error while trying to retrieve text for error ORA-03113 (DBD ERROR: OCITransRollback) at This is really two errors. The first (03113) is a connect problem as was mentioned by another correspondent, the second is translating the numeric error into a text message for you to read, which is a very frustrating thing that Oracle seems to do a lot. I found that some of the errors weren't a problem if the text could be shown to you, but if not then they were show-stoppers. Hence the concern with the language support stuff. Have you tried the Oracle knowledge base? It's really good. 73, Ged. -- Mark Vandenbroeck Mobile : +32-495-59.55.62 Business Process Manager Email : [EMAIL PROTECTED] EMEA Support Information Systems AIM: markvdb
Re: Connection pb with 2 oracle version! Please help!
Vincent,
If it works in that direction, it's pure luck. After all, it's not because we
make a new version, that we make EVERYTHING incompatible :-)
Brgds,
Mark
Vincent Roquencourt wrote:
Thanks guys!!! it works this way BUT... (there's is always one).. the
strange thing is that the same script works if i compile DBI /DBD with the
lowest oracle version (7.3.4).Why? Do we have upward (and not backward)
compatibility?
THANKS AGAIN :)
(FYI , it also works connecting via OCI and bypassing tnsnames.ora file with
7.3.4' compiled DBI but i won't use it for update :) )
-Original Message-
From: Mark Vandenbroeck [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, May 29, 2001 1:50 PM
To: Vincent Roquencourt
Cc: Tim Scott; [EMAIL PROTECTED]
Subject: Re: Connection pb with 2 oracle version! Please help!
Vincent,
Your DBD is linked against the 8.1.6 libs, but you set your ORACLE_HOME to
the 7.3.4 distribution. This is, as said before, asking for trouble. You
should set your ORACLE_HOME to point to the 8.1.6 distribution, since that's
where the libs reside you need to link with.
If your tnsnames.ora is set up correctly, everything should work.
Oh, don't forget to check your LD_LIBRARY_PATH. It should contain
$ORACLE_HOME/lib (where ORACLE_HOME points to 8.1.6, of course).
Brgds,
Mark
Vincent Roquencourt wrote:
On Tue, May 29, 2001 at 10:28:01AM +0100, Tim Scott wrote:
Voncent,
You'd be better to setup a tnsnames entry in your Oracle8.1.6 area to
point
at your Oracle 7.3.4 database and using this as a connect string.
If you don't do this, you're using Oracle8 function calls from your
Perl/DBI
build to try to talk to an Oracle7 database. This is asking for trouble -
even if you do eventually connect.
Regards,
Tim
Hi Tim,
thanks for your quick reply, however i just tested it:
$ENV{ORACLE_HOME}= '/export/home/oracle/app/oracle/product/7.3.4';
$ENV{ORACLE_SID}='mydb';
print $ENV{ORACLE_HOME} $ENV{ORACLE_SID}\n;
if( !( $dbh = DBI-connect($ENV{ORACLE_SID},$USER,$PASS, 'Oracle') )
) {
print not connected\n $DBI::errstr;
}
else{
print connected\n
};
#this is supposed to connect using tnsnames.ora (sqlplus $USER@mysid works
with both db)
i get the same error with the 7.3.4:
/export/home/oracle/app/oracle/product/7.3.4 mydb
not connected
ORA-01005: null password given; logon denied (DBD ERROR: OCISessionBegin)
but not with the 8.1.6!
/export/home/oracle/app/oracle/product/8.1.6 deuro
connected
user exists on both db (tested with sqlplus via sqlnet)
HLp!!! :)
--
Mark Vandenbroeck Mobile : +32-495-59.55.62
Business Process Manager Email : [EMAIL PROTECTED]
EMEA Support Information Systems AIM: markvdb
--
Mark Vandenbroeck Mobile : +32-495-59.55.62
Business Process Manager Email : [EMAIL PROTECTED]
EMEA Support Information Systems AIM: markvdb
Re: starting Oracle 8i
Denis, Although this has nothing to do with this list : you could put this command in an rc script. By the way, if yr init.ora is in $ORACLE_HOME/dbs, no need to specify the pfile parameter. Brgds, Mark [EMAIL PROTECTED] wrote: Hi gurus, I have installed Oracle 8i on RedHat 6.2. Every time after starting listener, i have to manually mount database using startup open pfile=$ORACLE_HOME/dbs/initoralin.ora; from SQL prompt. Is there any way to mount and open database automatically ?? Thanx Denis -- Mark Vandenbroeck Mobile : +32-495-59.55.62 Business Process Manager Email : [EMAIL PROTECTED] EMEA Support Information Systems AIM: markvdb
Re: Connection pb with 2 oracle version! Please help!
Michael, Not entirely true. Compatibility between the libs used in the client application (here DBD::Oracle) and ORACLE_HOME should be 100%. I mean, they should be *exactly* the same version. The slightest version difference is unsupported. If it works otherwise, it's pure luck. Compatibility between client application and target database is much more lenient, and conforms to what you said. Brgds, Mark Michael A. Chase wrote: The compatibility we need to worry about is between the DBD::Oracle we build and the version of Oracle pointed to by ORACLE_HOME. If they are compatible, the OCI handles the database communication, normally via SQL*Net. SQL*Net v2 (or 8) can talk to any Oracle database from 7.1 through 8.x (and probably 9i as well). -- Mac :}) ** I normally forward private database questions to the DBI mail lists. ** Give a hobbit a fish and he'll eat fish for a day. Give a hobbit a ring and he'll eat fish for an age. - Original Message - From: Mark Vandenbroeck [EMAIL PROTECTED] To: Vincent Roquencourt [EMAIL PROTECTED] Cc: Tim Scott [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, May 29, 2001 07:47 Subject: Re: Connection pb with 2 oracle version! Please help! If it works in that direction, it's pure luck. After all, it's not because we make a new version, that we make EVERYTHING incompatible :-) Vincent Roquencourt wrote: Thanks guys!!! it works this way BUT... (there's is always one).. the strange thing is that the same script works if i compile DBI /DBD with the lowest oracle version (7.3.4).Why? Do we have upward (and not backward) compatibility? (FYI , it also works connecting via OCI and bypassing tnsnames.ora file with 7.3.4' compiled DBI but i won't use it for update :) ) -- Mark Vandenbroeck Mobile : +32-495-59.55.62 Business Process Manager Email : [EMAIL PROTECTED] EMEA Support Information Systems AIM: markvdb
Re: executing atomic transactions in DBI
Traja, Yes, that would work. That's the whole purpose of commit; Mark - Original Message - From: traja [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, May 04, 2001 11:46 PM Subject: executing atomic transactions in DBI Hi, I have 2 insert statements which insert data into two different tables. These 2 inserts need to be run atomically, How can I do this? If I commit at the end of the two inserts, would that work? $sth = $dbh-prepare( insert into main(main_id, domain_id, font_size, max_width_chars, max_width_pixels) values(main_id_seq.nextval, $domain_id, 12,0,0) ) || die $dbh-errstr; $sth-execute || die $dbh-errstr; $sth = $dbh-prepare( insert into language(language_id,main_id, language, string,translation_req) values(language_id_seq.nextval, main_id_seq.currval, 'eng', $str, 1) ) || die $dbh-errstr; $sth-execute || die $dbh-errstr;
Re: (Fwd) Further Oracle Character Set Issues
Andy, Can you check what got into the database ? You can do a : SELECT .. DUMP(column, 16) FROM ... This will give you a hex dump of the physical contents of the column. If you have your NLS_LANG and the database both using the same characterset, then Oracle will pass on all characters untranslated. This is the so even if the characterset is 7 bit and the characters have the 8th bit set ! So, your problem must come from elsewhere, not from Oracle. It couldn't be simply a display problem ? Like if you use a terminal emulator configured for 7 bit and stripping the 8th ? I have seen that happen before. Brgds, Mark - Original Message - From: Tim Bunce [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, April 30, 2001 11:52 PM Subject: (Fwd) Further Oracle Character Set Issues - Forwarded message from Andy Brick [EMAIL PROTECTED] - From: Andy Brick [EMAIL PROTECTED] To: Tim Bunce [EMAIL PROTECTED] Subject: Further Oracle Character Set Issues Date: Mon, 30 Apr 2001 22:42:05 +0100 In-Reply-To: [EMAIL PROTECTED] Hi I have my Perl script working fine on NT and Oracle 8.1.5-ish now. International characters from ISO8859-1 work fine, and I'm translating the extra Window 1252 characters without a hitch. However, on Linux, with the same script, same data and same database, the eighth/top bit of each character gets reset and lost in Oracle. Oracle is using WE8ISO8859P1 for both NLS character sets, and the fact that the file is Code Page 1252 encoded shouldn't be an issue - the extra 1252 characters are translated by the Perl and the rest is compatible with WE8ISO8859P1 - plus it works on NT. I've dumped the hex of the string I am passing to Oracle and it has the eighth bit there, with no hassles. Am I missing something here ? Any and all help greatly appreciated !! Andy Brick [EMAIL PROTECTED] - End forwarded message -
Re: Can DBD::Oracle connect to various versions of Oracle?
David, Sure, no problem. I have Oracle 8.1.7, Perl and DBI, DBD::Oracle on my Linux laptop and connect without problem to a plethora of Oracle versions, ranging from 7.1.6 to 9i beta. The only limitation is that you have to use SQL*Net v2 on older versions of Oracle. Up to 7.3.4, SQL*Net v1 was still supported. Starting from 7.3.4, you can't use v1 anymore. So, if you want to connect to pre-7.3.4 versions, make sure you have the SQL*Net v2 listener configured and running. Hope this helps, Mark David Good wrote: I've been wondering. We support a number of clients running various versions of Oracle and I'd like to have just one copy of DBD::Oracle that would be able to connect to them all. We currently have some custom Pro*C apps and to hear the developers talk about it, they have to be compiled alot differently for each version of Oracle they want it to run against. Is it possible to have one copy of DBD::Oracle that'll work with Oracle 7, 8 and maybe even 9? -- David Good [EMAIL PROTECTED] This space intentionally left blank. -- Mark Vandenbroeck Mobile : +32-495-59.55.62 Business Process Manager Email : [EMAIL PROTECTED] EMEA Support Information Systems AIM: markvdb
Re: Oracle Character Set problem
Andy, I'm not familiar with Code Page 1252, but if it's a standard Latin-1 encoding, you should set your NLS_LANG environment variable to 'WE8ISO8859P1' before connecting to Oracle. Everything should work fine then. Hope this helps, Mark Andy Brick wrote: Hi all, I'm using DBD:Oracle and having problems with international characters on an insert - the data is read from a file which is encoded using Code Page 1252/Latin-1 encoding, and Oracle is set to WE8ISO8859P1, but when I do a select back from Oracle I get weird characters instead of e acute or whatever. Any ideas ? Desperate for solution !! Andy -- Mark Vandenbroeck Mobile : +32-495-59.55.62 EMEA Support Information Systems Email : [EMAIL PROTECTED] AIM: markvdb
Re: Oracle Character Set problem
Andy, Let's first check if our assumptions are correct. Can you do a SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_CHARACTERSET'; This will give us the characterset used to create the database. Please let me know the version and platform of your database, also. Then try with NLS_LANG set to American_America.WE8MSWIN1252. Insert some value. Select it back again. If it still doesn't work, let me know the hex values of what you inserted and a SELECT DUMP(column_name, 16) FROM your_table; Please use short strings. Thanks and best regards, Mark Andy Brick wrote: Setting NLS_LANG to WE8ISO8859P1 or WE8MSWIN1252 stops me establishing a connection - DBI reports a login failure. Setting NLS_LANG to ENGLISH.WE8MSWIN1252 allows a login but does not solve the character set issue. Help !! Regards Andy Brick [EMAIL PROTECTED] -- Mark Vandenbroeck Mobile : +32-495-59.55.62 EMEA Support Information Systems Email : [EMAIL PROTECTED] AIM: markvdb
Re: What is happening for error:end-of-file on communication channel
Susan, ORA-3113 means the client lost contact with the server process. Most probably because the server process crashed. This is almost always an Oracle bug. You should contact your local Oracle support organization. Best regards, Mark yi susan wrote: I'm counting following problem many times. Error msg : |ORA-03113: end-of-file on communication channel (DBD ERROR: OCIStmtExecute). It is happened when i am updating database with above ten thouands of records ,while my program has not finished yet. Oracle: 8.1.7 DBI : 1.14 Thanks in advanced susan __ Do You Yahoo!? Yahoo! Auctions - buy the things you want at great prices http://auctions.yahoo.com/ -- Mark Vandenbroeck Mobile : +32-495-59.55.62 EMEA Support Information Systems Email : [EMAIL PROTECTED] AIM: markvdb
Re: Netscape server/ Oracle/ Perl
Execute permission on /home/iw-home/iw-perl/bin/perl ?
Prasanna Gopalakrishna wrote:
Find an snippent of my script below...
Also, I gave full permission to script(chmod 777 scriptname.pl). Other perl program
works with 777 though! But while connecting to database it says 'permission denied'
Is anything to do with webserver/database connectivity?
Thanks
---
#!/home/iw-home/iw-perl/bin/perl
use CGI qw(:standard);
use DBI;
use strict;
print Content-type: text/html\n\n;
my $dbName = DBI:Oracle:CSPET101.WORLD;
my $dbUserName = userid;
my $dbPassword = password;
#Connecting to database
my ($sql,$dbh,$dataObject);
$dbh = DBI-connect($dbName, $dbUserName, $dbPassword,
{
PrintError =0,
RaiseError =0
}) or print Can't Connect to the database:\n $DBI::errstr\n;
$sql = select * from GROUP_TBL;
$dataObject = $dbh-prepare($sql) or print $DBI::errstr SQL Error;
$dataObject-execute() or print Error Message: Requested table(s) might not exist
$DBI::errstr\n;
while (my @result_set=$dataObject-fetchrow_array)
{
print @result_set\n;
}
---
From: Sterin, Ilya [EMAIL PROTECTED]
Date: 2001/04/24 Tue AM 12:10:57 EDT
To: Prasanna Gopalakrishna [EMAIL PROTECTED], [EMAIL PROTECTED]
Subject: RE: Netscape server/ Oracle/ Perl
The web server runs as user www or nobody (usually), so you must first
change the excution permissions for the script (chmod 755 script_name.cgi)
Also a snippet of your script would be helpful.
Ilya Sterin
-Original Message-
From: Prasanna Gopalakrishna [mailto:[EMAIL PROTECTED]]
Sent: Monday, April 23, 2001 4:25 PM
To: [EMAIL PROTECTED]
Subject: Netscape server/ Oracle/ Perl
Sensitivity: Confidential
Hi all,
I'm trying to connect oracle(8.0.3) database via Netscape Enterprise
server(3.6) on solaris 5.7
I able to connect to database from perl program at unix prompt and not from
the web server.
Do I need to do anything at perl program or webserver configuration
log says 'permission denied'
any suggestions?
Thanks
--
Mark Vandenbroeck Mobile : +32-495-59.55.62
EMEA Support Information Systems Email : [EMAIL PROTECTED]
AIM: markvdb
Re: HP-UX 11.0, DBD::Oracle problem
Ted, dld.sl is trying to load libwtc8.sl because libclntsh.sl (Oracle's client library) was linked with it. My guess is your LD_LIBRARY_PATH isn't set correctly. It should contain $ORACLE_HOME/lib Hope this helps, Mark Peterson, Ted wrote: I'd greatly appreciate a clue about this problem; I've been wandering about the mail archives, and don't see mention of a solution. I've built a non-threaded perl5.6.0, adding -lcl and -lpthread to config.sh, then built DBI 1.14, then built DBD::Oracle 1.06 with the two patches to the Makefile (removing -Wl,+[sn]; adding lqsmashr), all without error until I get to make test for the DBD. I get these errors when running make test: PERL_DL_NONLAZY=1 /usr/bin/perl -Iblib/arch -Iblib/lib -I/u1/webadm/perl5/lib/5.6.0/PA-RISC2.0 -I/u1/webadm/perl5/lib/5.6.0 -e 'use Test::Harness qw(runtests $verbose); $verbose=0; runtests @ARGV;' t/*.t t/base../usr/lib/dld.sl: Can't open shared library: /t/a/oracle/lib//libwtc8.sl /usr/lib/dld.sl: No such file or directory t/base..ok 3/5FAILED tests 4-5 Failed 2/5 tests, 60.00% okay t/general.../usr/lib/dld.sl: Can't open shared library: /t/a/oracle/lib//libwtc8.sl /usr/lib/dld.sl: No such file or directory install_driver(Oracle) failed: Can't load 'blib/arch/auto/DBD/Oracle/Oracle.sl' for module DBD::Oracle: No such file or directory at /u1/webadm/perl5/lib/5.6.0/PA-RISC2.0/DynaLoader.pm line 200. at (eval 1) line 3 Compilation failed in require at (eval 1) line 3. Perhaps a required shared library or dll isn't installed where expected at t/general.t line 20 -- snip -- Does anyone know why dld.sl is trying to load /t/a/oracle/lib//libwtc8.sl? Thanks, Ted Peterson Pfizer, Inc Ann Arbor, MI Summary of my perl5 (revision 5.0 version 6 subversion 0) configuration: Platform: osname=hpux, osvers=11.00, archname=PA-RISC2.0 uname='hp-ux aaegret b.11.00 a 9000800 864780421 two-user license ' config_args='-Ubincompat5005' hint=recommended, useposix=true, d_sigaction=define usethreads=undef use5005threads=undef useithreads=undef usemultiplicity=undef useperlio=undef d_sfio=undef uselargefiles=define use64bitint=undef use64bitall=undef uselongdouble=undef usesocks=undef Compiler: cc='cc', optimize='-O', gccversion= cppflags='-D_HPUX_SOURCE -Aa +z' ccflags =' -D_HPUX_SOURCE +z -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -Ae' stdchar='unsigned char', d_stdstdio=define, usevfork=false intsize=4, longsize=4, ptrsize=4, doublesize=8 d_longlong=define, longlongsize=8, d_longdbl=define, longdblsize=16 ivtype='long', ivsize=4, nvtype='double', nvsize=8, Off_t='off_t', lseeksize=8 alignbytes=8, usemymalloc=y, prototype=define Linker and Libraries: ld='ld', ldflags =' -L/usr/local/lib' libpth=/usr/local/lib /lib /usr/lib /usr/ccs/lib libs=-lnsl -lnm -lndbm -ldld -lm -lc -lndir -lcrypt -lsec -lcl -lpthread libc=/lib/libc.sl, so=sl, useshrplib=false, libperl=libperl.a Dynamic Linking: dlsrc=dl_hpux.xs, dlext=sl, d_dlsymun=undef, ccdlflags='' cccdlflags='+z', lddlflags='-b +vnocompatwarnings -L/usr/local/lib' Characteristics of this binary (from libperl): Compile-time options: USE_LARGE_FILES Built under hpux Compiled at Apr 23 2001 11:06:57 @INC: /u1/webadm/perl5/lib/5.6.0/PA-RISC2.0 /u1/webadm/perl5/lib/5.6.0 /u1/webadm/perl5/lib/site_perl/5.6.0/PA-RISC2.0 /u1/webadm/perl5/lib/site_perl/5.6.0 /u1/webadm/perl5/lib/site_perl . -- Mark Vandenbroeck Mobile : +32-495-59.55.62 EMEA Support Information Systems Email : [EMAIL PROTECTED] AIM: markvdb
Re: (Fwd) using perl with oracle 8.1.6
Yoel, This is because DBD::Oracle was linked against the 8.1.5 libclntsh.so from Oracle, and now your providing it the 8.1.6 version. Solution is to 'make' DBD::Oracle again. Mark Tim Bunce wrote: - Forwarded message from Yoel Steinmets - BH Photo Video [EMAIL PROTECTED] - From: Yoel Steinmets - BH Photo Video [EMAIL PROTECTED] To: [EMAIL PROTECTED] Date: Wed, 18 Apr 2001 14:45:42 -0400 Subject: using perl with oracle 8.1.6 Priority: normal Hi Tim maybe you can help me with this i updated my Oracle datebase from 8.1.50 to 8.1.6 and now i cant connect to Oracle i am getting install_driver(Oracle) failed: Can't load '/usr/local/lib/perl5/site_perl/5.005/sun4-solaris/auto/DBD/Oracle/O racle.so' for module DBD::Oracle: ld.so.1: /usr/local/bin/perl: fatal: relocation error: file /usr/local/lib/p erl5/site_perl/5.005/sun4-solaris/auto/DBD/Oracle/Oracle.so: symbol nauzaoss: referenced symbol not found at / usr/local/lib/perl5/5.00503/sun4-solaris/DynaLoader.pm line 169. at (eval 4) line 3 however if my invironment variables is pointing to the old version i have no problaims i appriciate your help my email [EMAIL PROTECTED] phone 212-239-7500 X2167 thank you - End forwarded message - -- Mark Vandenbroeck Mobile : +32-495-59.55.62 EMEA Support Information Systems Email : [EMAIL PROTECTED] AIM: markvdb
Re: Can't build DBD with Oracle 8.1.6 and OAS 4
Agamemnon, This hasn't anything to do with DBI or DBD anymore, but here it is : Yes, you shouldn't mix both sets of libraries in your LD_LIBRARY_PATH. What I usually do is modify oraenv (there's a place provided for custom code near the end of the script) to set LD_LIBRARY_PATH so that it only contains directories from the ORACLE_HOME belonging to the ORACLE_SID given as arguments. Then you run oraenv each time you change environment (which is the purpose of oraenv anyway). Hope this helps, Mark Agamemnon Kaimakis wrote: Mark, thanks for the reply. Indeed, I use different ORACLE_HOMEs for the various products. Also, before installing the DBD I use oraenv to set ORACLE_HOME to the RDBMS. I've noticed that my problem is with the LD_LIBRARY_PATH as follows: 1- If I remove the OAS lib paths - DBD works, OAS won't start 2- If I place the OAS lib paths at the end - the same 3- If I put the OAS lib paths at the beginning - OAS is ok, DBD won't work! The 3rd is the default cause the LD_LIBRARY_PATH is set as follows in the profile: export LD_LIBRARY_PATH=/opt/ext/oracle/product/8.1.6/lib:/opt/ext/oracle/product/8.1.6/jdbc/lib:/opt/ext/oracle/product/dev6i/lib:/usr/lib:/usr/openwin/lib:/usr/dt/lib:/usr/ucblib and then OAS and Dev add to it through the scripts: source /opt/ext/oracle/product/oas/ows/4.0/install/owsenv_bsh.sh source /opt/ext/oracle/product/dev6i/dev6i.sh Is there a way to overcome this problem? It seems that there is some kind of incompatibility among the RDBMS and OAS4 libs. Regards, Agamemnon Mark Vandenbroeck wrote: Agamemnon, The solution to your problem (and the recommended way of installing, regardless of DBD stuff) is to install your RDBMS and OAS in a separate ORACLE_HOME. When you install DBD, your ORACLE_HOME should be pointing to the RDBMS. If you don't install in separate ORACLE_HOMEs, you will run into problems sooner or later, even if you manage to get DBD working. Hope this helps, Mark Agamemnon Kaimakis wrote: Hi dbi-users, I have problems building DBD 1.06 with Oracle 8.1.6 and Oracle Application Server 4.0.8.2 I upgraded Oracle from 8.0.4 - 8.1.6 and OAS from 3.0.2 - 4.0.8.2 (configuration: Solaris 2.6, Perl 5.005_03, DBI 1.15 and DBD 1.06). Make test returns the message "You must install a Solaris patch to run this version of the Java runtime." Full outcome of make and make test is at the end of the message. Any help would be really appreciated. I've searched extensively the archives of dbi-users and clp.modules newsgroup and I've tried several workarounds. My problem is not with the RDBMS because during the installation I followed custom installation and chose NOT to install the Advanced Security option. This option (with its java components) is causing the problems to the DBD. But I couldn't do the same with OAS. I tried 2 of the suggestions of README.java with no success: 1) OAS won't work without the java stuff. 2) LD_PRELOAD produces core dumps in shell commands I haven't tried re-building Perl with thread support cause it's a production system and (according to the readme file) it's still experimental. Any other ideas? Is the solution of this problem planned for the next DBD release? Thanks and regards, Agamemnon - perl -V - Summary of my perl5 (5.0 patchlevel 5 subversion 3) configuration: Platform: osname=solaris, osvers=2.6, archname=sun4-solaris uname='sunos beavis 5.6 generic_105181-20 sun4u sparc sunw,ultra-250 ' hint=recommended, useposix=true, d_sigaction=define usethreads=undef useperlio=undef d_sfio=undef Compiler: cc='gcc', optimize='-O', gccversion=2.8.1 cppflags='-I/usr/local/include' ccflags ='-I/usr/local/include' stdchar='unsigned char', d_stdstdio=define, usevfork=false intsize=4, longsize=4, ptrsize=4, doublesize=8 d_longlong=define, longlongsize=8, d_longdbl=define, longdblsize=16 alignbytes=8, usemymalloc=y, prototype=define Linker and Libraries: ld='gcc', ldflags =' -L/usr/local/lib' libpth=/usr/local/lib /lib /usr/lib /usr/ccs/lib libs=-lsocket -lnsl -ldl -lm -lc -lcrypt libc=/lib/libc.so, so=so, useshrplib=false, libperl=libperl.a Dynamic Linking: dlsrc=dl_dlopen.xs, dlext=so, d_dlsymun=undef, ccdlflags=' ' cccdlflags='-fPIC', lddlflags='-G -L/usr/local/lib' Characteristics of this binary (from libperl): Built under solaris Compiled at Jul 3 2000 12:07:04 @INC: /opt/ojs/u01/ojs2/perl5/lib/5.00503/sun4-solaris /opt/ojs/u01/ojs2/perl5/lib/5.00503 /opt/ojs/u01/ojs2/perl5/lib/site_perl/5.005/sun4-solaris /opt/ojs/u01/ojs2/perl5/lib/site_
Re: Reading a trace file through Oracle? POSSIBLE??
Mark, Is this really necessary ? All you can find in the controlfile tracefile, you can also find in the V$ views, and much easier : everything (and much more) is there neatly organized and you can use simple SQL statements. Check the "Server Reference" for more information. Brgds, Mark Mark Wagner wrote: I am working on making a tool with perl DBI/DBD Oracle to document Oracle Databases... One of the things I want to do is back the control file up to the trace file then read the trace file. But heres the *hard* part: I am not running this locally so using sql*net to connect to Oracle with perl DBI/DBD, I need to make Oracle read the trace file in a way that I can have it accessible by perl to print out. Any help would be greatly appreciated. Thanks Mark -- Mark Wagner Phone - (510) 683-9681 Database Administrator [EMAIL PROTECTED] * www.oralinux.orgwww.orasolaris.orgwww.orant.org * * Dark Oracle of the Sysadmin Sith Darth SYSDBA * the shit ain't over till the last record spins... -- Mark Vandenbroeck Mobile : +32-495-59.55.62 EMEA Support Information Systems Email : [EMAIL PROTECTED] AIM: markvdb
Re: Oracle 8i DBD connection on local server
Rob, First of all : turning the listener on requires only minimal resources. Secondly: to make a local connection, you should leave the 'connectstring' empty in the DBI connect call, but set your ORACLE_SID environment variable to point to the database you want to connect to. Best regards, Mark Rob Messer wrote: I've been using DBD::Oracle for a while on several Linux servers that have Oracle 8i installed on the same server. It works well, but there is a nagging problem -- I can't get the DBD to Oracle connection to work without turning the listener on. The database is on the same server as the perl code, so as I understand it I shouldn't need the Oracle listener on, and I'd much prefer to turn it off to save memory and prevent any external sources from trying to connect with the database. I've tried all the variations in the DBD::Oracle help file, but none of them seem to work. Can anyone point me to a good explanation of how this connection should work with the database on the same server as the DBD::Oracle code? Thanks, Rob -- Mark Vandenbroeck Mobile : +32-495-59.55.62 EMEA Support Information Systems Email : [EMAIL PROTECTED] AIM: markvdb
Re: Problem with DBI direct connect to Oracle
Hardy,
There are numerous possibilities. One of the first things to check for is a
tracefile on the server side in user_dump_dest. If you don't know what
user_dump_dest is, mail me off-line, as this has nothing to do with DBI.
You mention the Oracle version is 8.1.6. On both sides (client and server) ?
Brgds,
Mark
Hardy Merrill wrote:
Redhat 6.1 Linux
Perl 5.005_03
Oracle 8i (8.1.6)
DBI 1.14
DBD::Oracle 1.06
According to the DBD::Oracle perldoc, I'm using the
"Connecting without environment variables or tnsname.ora file"
method, like:
my $dsn = "DBI:Oracle:host=my.domain.com;sid=MYSID";
my $dbh = DBI-connect($dsn,
'user',
'password',
{ RaiseError = 1, AutoCommit = 1 }
);
on an Oracle "client" machine to connect to an Oracle "server".
Using this connection method, have any of you received ORA-12547
errors:
12547, 0, "TNS:lost contact"
// *Cause: Partner has unexpectedly gone away, usually during process
// startup.
// *Action: Investigate partner application for abnormal termination. On an
// Interchange, this can happen if the machine is overloaded.
when trying to connect? This seems to occur intermittantly under
consistent(regular selects/inserts/updates) light load. Any ideas
for how to figure out what is causing this?
TIA.
--
Hardy Merrill
Mission Critical Linux, Inc.
http://www.missioncriticallinux.com
--
Mark Vandenbroeck Mobile : +32-495-59.55.62
EMEA Support Information Systems Email : [EMAIL PROTECTED]
AIM: markvdb
Re: Can I execute an OS command through oracle using dbi?
Another possibility is to use 'external procedure calls', which allow you to call a shared lib or DLL from PL/SQL. See the Oracle documentation on how to do this. Mark "Michael A. Chase" wrote: What kind of commands are you considering? It might be better to do them directly in Perl. You can do it in DBI if you can from PL/SQL. Note that it would run on the database server though, not your local host. You could also have a server process listen on a database pipe for commands and have it perform the desired operations. I wouldn't want to try sending too much data that way though. -- Mac :}) ** I normally forward private database questions to the DBI mail lists. ** Give a hobbit a fish and he'll eat fish for a day. Give a hobbit a ring and he'll eat fish for an age. - Original Message - From: "Mark Wagner" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, March 22, 2001 12:21 PM Subject: Can I execute an OS command through oracle using dbi? Is it possible to execute an os command through oracle using dbi? -- Mark Vandenbroeck Mobile : +32-495-59.55.62 EMEA Support Information Systems Email : [EMAIL PROTECTED] AIM: markvdb
Re: How can i execute a Oracle function from Perl code.
This is explained very well in the DBD::Oracle documentation, complete with examples. If you installed DBD::Oracle, you have this documentation. Just type 'man DBD::Oracle'. Otherwise, you can download it form www.cpan.org. Mark - Original Message - From: "I.Sals - Listn.com" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, March 05, 2001 11:22 AM Subject: How can i execute a Oracle function from Perl code. Hi there. My question is very easy. How can i execute a Oracle Function (into ORacle package body) from perl code (Cgi), and catch the return code after execution that Oracle function? Ptda: excuseme for my poor english, sorry. Thank's you very much.
Re: SQL newbie question ....
Sean,
It would help us help you if you could explain :
- what you expect this to do
- why you think it doesn't work (statement failing, error codes, ...)
- what DBD you use
- ...
Mark
- Original Message -
From: "Sean Cooper" [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Thursday, March 01, 2001 3:30 PM
Subject: SQL newbie question
Hello ..
could somebody please explain why this doesn't work, and what I need to be
doing
to get it to work like I think it should?
$MDG_Updates_sth = $MDG_dh-prepare(qq{SELECT * FROM MDG_Updates WHERE
update_key = ? ORDER BY update_key});
$MDG_Updates_sth-bind_param(1,qq{$key});
$MDG_Updates_sth-execute();
also -- does anyone know of a way to search this list.
Thanks
Re: How to use a database with Oracle?
Please note that the "ALTER SESSION SET CURRENT_SCHEMA = ..." only changes
the way names are
resolved. That is: it changes the schema that will be searched by default.
It does not change in any way your privileges.
Mark
From: "Tony Foiani" [EMAIL PROTECTED]
To: "Tim Bunce" [EMAIL PROTECTED]
Cc: "Mitchell, Louise M" [EMAIL PROTECTED]; "'Joern Reder'"
[EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Wednesday, February 21, 2001 3:17 PM
Subject: Re: How to "use" a database with Oracle?
"Tim" == Tim Bunce [EMAIL PROTECTED] writes:
Tim I think in very recent Oracle's there's an SQL command (probably
Tim 'SET ...') that lets you switch default schema.
Is this what you are thinking of?
ALTER SESSION SET CURRENT_SCHEMA =
We use "ALTER SESSION" extensively in our project here, primarily to
set the NLS_DATE_FORMAT to be something sane. It's described in the
_Oracle8i SQL Reference_ (Release 2, 8.1.6, A76989-01), Chapter 7
("SQL Statements").
t.
Re: bind_in_out
I guess the problem is with the type you bind to : ORA_RSET is for binding to
REF CURSORs, not for ints.
Why don't you try {ora_type = ORA_NUMBER} ?
Mark
[EMAIL PROTECTED] wrote:
I apologize for not including an example earlier, I was hoping someone
would identify an known error. I have made a smaller example of the
larger picture:
Code Sample:
my $stmt=$DBH-prepare('BEGIN
test_intonly( ? );
END;');
my $result;
my $rv = $stmt-bind_param_inout(1, \$result, 0, { ora_type = ORA_RSET });
$stmt-execute;
Oracle Procedure:
teedev desc test_intonly;
PROCEDURE test_intonly
Argument Name TypeIn/Out Default?
-- --- --
OUT_INTNUMBER IN/OUT
Error Message:
DBD::Oracle::st execute failed: ORA-06550: line 2, column 33:
PLS-00306: wrong number or types of arguments in call to 'TEST_INTONLY'
ORA-06550: line 2, column 33:
PL/SQL: Statement ignored (DBD ERROR: OCIStmtExecute) at ./s.pl line 20.
--
C Wayne Huling [EMAIL PROTECTED]
--
Mark Vandenbroeck Mobile : +32-495-59.55.62
EMEA Support Information Systems Email : [EMAIL PROTECTED]
AIM: markvdb
Re: (Fwd) DBI/DBD for Oracle help
Did you have $ORACLE_HOME/lib in your LD_LIBRARY_PATH ? Mark Tim Bunce wrote: - Forwarded message from "Patel, Raju" [EMAIL PROTECTED] - From: "Patel, Raju" [EMAIL PROTECTED] To: "'[EMAIL PROTECTED]'" [EMAIL PROTECTED] Cc: "Harris, Jamey" [EMAIL PROTECTED], "Patel, Raju" [EMAIL PROTECTED] Subject: DBI/DBD for Oracle help Date: Fri, 16 Feb 2001 14:20:42 -0600 Tim, While using DBI/DBD for Oracle, we are having the following error: ld.so.1: /usr/local/bin/perl: fatal: relocation error: file /usr/local/lib/perl5/site_perl/5.005/sun4-olaris/auto/DBD/Oracle/Oracle.so: symbol OCIInitialize: referenced symbol not found Killed The environment we are running as follows: Perl 5.005_03 DBD Oracle 1.06 DBI 1.14 Oracle 8i Enterprise edition Release 8.1.6.2.0 After searching the web, we found that some one has a similar problem. The message suggest the following fix: Change the following line in Makefile.PL from $::opt_p = ''; # alter preference for oracle.mk over proc to $::opt_p = 1; # alter preference for oracle.mk over proc We have tried this fix but it did not work. Please help us resolve the issue. Your help is greatly appreciated. Raj Patel - End forwarded message - -- Mark Vandenbroeck Mobile : +32-495-59.55.62 EMEA Support Information Systems Email : [EMAIL PROTECTED] AIM: markvdb
Re: (Fwd) DBI/DBD for Oracle help
Raj,
No, I don't mean PATH, I mean LD_LIBRARY_PATH. This is the environment variable
that holds a list of directories to be searched for dynamic libraries.
Try the following before starting your perl program :
$ . oraenv
ORACLE_SID = [V816] ? fill in your ORACLE_SID
$ LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
$ export LD_LIBRARY_PATH
$ perl -w your_perl_script
Please check your Oracle and DBD::Oracle documentation for more information.
Brgds,
Mark
"Patel, Raju" wrote:
The PATH is set while running the perl script when I encounter the error.
I will check with the UNIX admin whether he set the PATH while creating make
install.
Thanks for you help.
Raj Patel
-Original Message-----
From: Mark Vandenbroeck [mailto:[EMAIL PROTECTED]]
Sent: Monday, February 19, 2001 9:42 AM
To: Patel, Raju
Cc: Tim Bunce; [EMAIL PROTECTED]
Subject: Re: (Fwd) DBI/DBD for Oracle help
Did you have $ORACLE_HOME/lib in your LD_LIBRARY_PATH ?
Mark
Tim Bunce wrote:
- Forwarded message from "Patel, Raju" [EMAIL PROTECTED]
-
From: "Patel, Raju" [EMAIL PROTECTED]
To: "'[EMAIL PROTECTED]'" [EMAIL PROTECTED]
Cc: "Harris, Jamey" [EMAIL PROTECTED],
"Patel, Raju" [EMAIL PROTECTED]
Subject: DBI/DBD for Oracle help
Date: Fri, 16 Feb 2001 14:20:42 -0600
Tim,
While using DBI/DBD for Oracle, we are having the following error:
ld.so.1: /usr/local/bin/perl: fatal: relocation error: file
/usr/local/lib/perl5/site_perl/5.005/sun4-olaris/auto/DBD/Oracle/Oracle.so:
symbol OCIInitialize: referenced symbol not found
Killed
The environment we are running as follows:
Perl 5.005_03
DBD Oracle 1.06
DBI 1.14
Oracle 8i Enterprise edition Release 8.1.6.2.0
After searching the web, we found that some one has a similar problem.
The message suggest the following fix:
Change the following line in Makefile.PL from
$::opt_p = ''; # alter preference for oracle.mk over proc
to
$::opt_p = 1; # alter preference for oracle.mk over proc
We have tried this fix but it did not work.
Please help us resolve the issue.
Your help is greatly appreciated.
Raj Patel
- End forwarded message -
--
Mark Vandenbroeck Mobile : +32-495-59.55.62
EMEA Support Information Systems Email : [EMAIL PROTECTED]
AIM: markvdb
--
Mark Vandenbroeck Mobile : +32-495-59.55.62
EMEA Support Information Systems Email : [EMAIL PROTECTED]
AIM: markvdb
Re: DBD::Oracle persistent connection
Paul,
It might help if you gave us the error message(s).
Mark
- Original Message -
From: "Paul Tjapkes" [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Monday, February 19, 2001 11:59 AM
Subject: DBD::Oracle persistent connection
I have a script that needs to be connected to Oracle
all the time. Everything works fine until Oracle goes
down for a cold backup.
My code looks something like this:
# Start of script
$dbh = DBI-Connect("dbi:Oracle:$db",$un,$pw);
# Within a large loop
unless ($dbh-ping) {
$dbh-disconnect;
undef $dbh;
sleep(1800);
$dbh = DBI-Connect("dbi:Oracle:$db",$un,$pw);
}
The problem is that the connect will continue to fail,
even when Oracle is back up. The only way to
reconnect is to restart the script. And ideas?
TIA
__
Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail - only $35
a year! http://personal.mail.yahoo.com/
Re: sqlplus login error via perl system call
David,
You must have a wrong PATH setting : SQL*Plus v3.3.4.0.1 belongs to Oracle7
v7.3.4. In that version the UTF8 characterset did not exist yet (we used AL24UTFFSS
in those days, which is Unicode v1), hence your error message.
You should use the SQL*Plus of your 8.1.6 Oracle environment. Probably your path is
pointing to the $ORACLE_HOME/bin of an older 7.3.4 environment.
Hope this helps,
Mark
David Sherrington - Enterprise Services- Data Management Systems Engineer wrote:
Hello,
I know that this might not be a DBI issue, sorry, but I'm not sure where to look
next!
My setup is: perl 5.005 on Solaris 2.6 running Oracle 8.1.6.
I am getting the following error when I try to run sqlplus via a perl system
call. I have checked all the environment variables all look fine, in fact when I
run the same sqlplus statement from the unix prompt (with the same environment
settings) it works fine.
Any ideas as to what might be causing this would be gratefully received.
Many thanks
Regards
David
Below is the code and error:
$return_code = (system("sqlplus user/passwd \@mysql ") / 256);
SQL*Plus: Release 3.3.4.0.1 - Production on Wed Feb 14 05:33:42 2001
Copyright (c) Oracle Corporation 1979, 1996. All rights reserved.
ERROR: ORA-12705: invalid or unknown NLS parameter value specified
Enter user-name:
My NLS_LANG setting is:
NLS_LANG=american_america.UTF8
--
Mark Vandenbroeck Mobile : +32-495-59.55.62
EMEA Support Information Systems Email : [EMAIL PROTECTED]
AIM: markvdb
Re: DBD:DBI (Oracle)
Ignacio,
17-19 seconds look too much, but it's a bit hard to say why, because you give little
information.
It could be network latency, Win98, SQL*Net config, ...
Mark
Ignacio Sals Villanueva wrote:
Hi all
It's working but very slow (i tried connect's only)
Look this statisticts ... is it normal?
Excuse me for my poor English.
Conectando -A-
Tiempo utilizado para la Conexin -A- 0 das, 0 horas, 0 minutos y 19 segundos
Conectando -B-
Tiempo utilizado para la Conexin -B- 0 das, 0 horas, 0 minutos y 18 segundos
Conectando -C-
Tiempo utilizado para la Conexin -C- 0 das, 0 horas, 0 minutos y 17 segundos
Conectando -X-
Tiempo utilizado para la Conexin -X- 0 das, 0 horas, 0 minutos y 18 segundos
Indigo Apache Net8 8.1.5 (Client) is running under Win98
Oracle 7.3 is runnin in Unix
Thank's again for your patient and your time.
I.Salis
==
Source Code
==
print "Conectando -A-BR";
# Mtodo A
--
my $cuenta = "$user\@$connect_string";
$open_connect = time;
my $dbh = DBI-connect('dbi:Oracle:',$cuenta,$passwd) or die "Unable to connect:
$DBI::errstr";
$close_connect = time;
$dbh-disconnect();
print "Tiempo utilizado para la Conexin -A- ".Calcula_orquilla($open_connect,
$close_connect)."BR";
#
---
print "Conectando -B-BR";
my $cuenta = "$user/$passwd\@$connect_string";
# Mtodo B
--
$open_connect = time;
my $dbh = DBI-connect('dbi:Oracle:',$cuenta,'');
$close_connect = time;
$dbh-disconnect();
print "Tiempo utilizado para la Conexin -B- ".Calcula_orquilla($open_connect,
$close_connect)."BR";
#
---
print "Conectando -C-BR";
my $cuenta = "dbi:Oracle:$connect_string";
# Mtodo C
--
$open_connect = time;
$dbh = DBI-connect($cuenta,$user,$passwd);
$close_connect = time;
$dbh-disconnect();
print "Tiempo utilizado para la Conexin -C- ".Calcula_orquilla($open_connect,
$close_connect)."BR";
#
---
# whitout tnsnames.ora
print "Conectando -X-BR";
# Mtodo X
--
my $cuenta = "$user/$passwd\@(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(COMMUNITY = tcp.world)
(PROTOCOL = TCP)
(Host = ---.53.90.147) # Security Reasons Sorry :_(
(Port = 1521)
)
(ADDRESS =
(COMMUNITY = tcp.world)
(PROTOCOL = TCP)
(Host = ---.53.90.147)
(Port = 1526)
)
) (CONNECT_DATA = (SID = ---)))";
$open_connect = time;
my $dbh = DBI-connect('dbi:Oracle:', $cuenta, "");
$close_connect = time;
$dbh-disconnect();
print "Tiempo utilizado para la Conexin -X- ".Calcula_orquilla($open_connect,
$close_connect)."BR";
#
---
--
Mark Vandenbroeck Mobile : +32-495-59.55.62
EMEA Support Information Systems Email : [EMAIL PROTECTED]
AIM: markvdb
Re: Help, please with getting DBD::Oracle working on Debian Linux
Stan, You could try adding $ORACLE_HOME/lib to your LD_LIBRARY_PATH. That helped for me on RH 6.2. Best regards, Mark Stan Brown wrote: I'm having a fit getting the latest DBD::Oracle working on a fresh Debian Linux 2.2 install. I have installed Oracle 8i, and the ProC optional product. The DB works fine. The folowing steps run without error: perl Makefile.PL ; make The test pretty much all fail. I looked at test.pl, and it was assuming that perl was in /usr/local/bin, whne it's actually in /usr/bin. I made a caopy of tets.pl, adn fixed this. When I run this script, it complains (Dynaloader) about Oracle.so not being able to find libclntsh.so.8.0 This library is present in $ORACLE_HOME/lib and hasa symlink called libclntsh.so. I am doing all of this loged in as the Oracle user, and $ORACLE_HOME is set in that envrionment. Also the scott/tiger accoun is still enabled. I desperatly need to get this working to move on with a project I am in a time crunch on. What should I try next? -- Stan Brown [EMAIL PROTECTED]843-745-3154 Charleston SC. -- Windows 98: n. useless extension to a minor patch release for 32-bit extensions and a graphical shell for a 16-bit patch to an 8-bit operating system originally coded for a 4-bit microprocessor, written by a 2-bit company that can't stand for 1 bit of competition. - (c) 2000 Stan Brown. Redistribution via the Microsoft Network is prohibited. -- Mark Vandenbroeck EMEA Support Information Systems Mobile : +32-495-59.55.62 Email : [EMAIL PROTECTED]
