LONG datatypes

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

Here is the section of code thats causing problems, can you tell me how to make the insert work? The variable causing problems is $admin. In my case the lenght of $admin is 2573 bytes. If I print $admin to the screen it displays fine.

#capture the admin file to be inserted into database 
$admin = `cat /tmp/$basename.admin.$$`; 
 
#Insert the data into the RPS database 

  @sqlca = &sql(q#insert into BSM_ADMINFILE 
                       (patch_id,adminfile) 
               values('$patch_id','$admin')#); 
  &checkReturnCode(10,*sqlca,"Unable to insert."); 
  &saveDBwork();
[download]

I am tring to insert this into an Oracle 7 database with the feild adminfile declared as LONG I get the following error: Unexpected Error -1704 at newbsmpatch line 97: ORA-01704: string literal too long

Comment on LONG datatypes Download Code

Replies are listed 'Best First'.
Re: LONG datatypes by buckaduck (Chaplain) on May 31, 2001 at 23:15 UTC
The following observation is probably NOT your problem, but rather a typo. Nevertheless... You will need to use `qq# ... #` if you want the value of `$admin` to be interpolated into your SQL query. Your version uses a single-quoted string. Update: Clarified the first sentence a bit buckaduck	[reply] [d/l] [select]
Re: LONG datatypes by IraTarball (Monk) on May 31, 2001 at 22:32 UTC
This is just a guess but it sounds like you're trying to insert a blob of text into a numeric field. If `$admin` is 2573 bytes then it can't possibly be a type LONG which is probably only 32 bytes, or maybe 64, or 128 depending on platform but definitely not 2573. I haven't used Oracle much but look into the docs for the proper field type for your data, I'm guessing a blob. Rock on brother, Ira "So... What do all these little arrows mean?" ~unknown	[reply] [d/l]
Re: Re: LONG datatypes by DrSax (Sexton) on May 31, 2001 at 23:16 UTC
I think he means an "Oracle" LONG, which is not the same as a long datatype in a language like 'C'. An Oracle LONG can hold up to 2 gigs of variable-length character data in version 8, perhaps less in version 7, but certainly enough to hold 2573 bytes of information. Brian - a.k.a. DrSax	[reply]
Re: LONG datatypes by ckohl1 (Hermit) on May 31, 2001 at 22:50 UTC
You can build an SQL string that utilizes Oracle's DECLARE function: `DECLARE new_text LONG; BEGIN new_text := '$ScalerWithNewValue'; UPDATE YourDB.YourTable SET YourColumn = new_text END;` [download] Then pass the SQL string through your DB interface. Chris 'You can't get there from here.'	[reply] [d/l]
Re: LONG datatypes by BigJoe (Curate) on Jun 01, 2001 at 16:34 UTC
When I dump stuff to a Long this is the format that I do. I use the DBI. $dbh is my database connection. This works for me. `my $sth = $dbh->prepare("INSERT INTO TABLE (ID, HEXPIC, CLASSIFICATION +) VALUES (classifieds_seq.nextval, ?, '$item_type')")or print "$DBI: +:errstr"; $sth->bind_param( 1, $hex, SQL_LONGVARCHAR); $sth->execute or print"$DBI::errstr \n";` [download] --BigJoe Learn patience, you must. Young PerlMonk, craves Not these things. Use the source Luke.	[reply] [d/l]