Passing Long RAW

Passing Long RAW

Post by Prakas » Sun, 31 Dec 1899 09:00:00



Can anyone tell me how to assign Long raw to a variable? Here is my
problem:

When I assign long raw data from my database to a variable, in my Stored
Procedure, it accepts if the value is < 32 K. Otherwise throws the
exception of Numeric or Value Error.But Database can hold upto 2 GB of
data on the same column.

Please help me. Thanks

Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

Passing Long RAW

Post by Hakan Ere » Sun, 31 Dec 1899 09:00:00


Hi

You cannot assign a long col. to a variable in pl/sql.

it is good to know:(copied from Oracle Doc)
  LONG columns store variable length character strings
  containing up to 2 gigabytes.  
  The use of LONG values are subject to some restrictions:
    o  A table cannot contain more than one LONG column.
    o  LONG columns cannot appear in integrity constraints
       (except for NULL and NOT NULL constraints).
    o  LONG columns cannot be indexed.
    o  A stored function cannot return a LONG value.
    o  Within a single SQL statement, all LONG columns,
       updated tables, and locked tables must be located
       on the same database.
  LONG columns cannot appear in certain SQL statements:
    o  CREATE SNAPSHOT
  LONG columns cannot appear in certain parts of SQL statements:
    o  WHERE clause
    o  GROUP BY clause
    o  ORDER BY clause
    o  CONNECT BY clause
    o  with the DISTINCT operator in SELECT statements
    o  UNIQUE clause of a SELECT statement
    o  column datatype clause of a CREATE CLUSTER statement
    o  SQL functions (such as SUBSTR or INSTR)
    o  expressions or conditions
    o  select lists of queries containing GROUP BY clauses.
    o  select lists of subqueries or queries combined by
       set operators (UNION, UNION ALL, INTERSECT, and MINUS).
    o  select lists of CREATE TABLE...AS SELECT statements;
       LONG columns cannot be referenced when you create a
       table with rows returned by a subquery .
    o  select lists of subqueries in INSERT statements;
       LONG columns cannot be referenced when you insert
       rows returned by a subquery into a table or view
       (INSERT INTO...SELECT...)
  Triggers can use the LONG datatype in the following manner:
    o  A SQL statement within a trigger can insert data
       into a LONG column.
    o  If data from a LONG column can be converted to a
       constrained datatype (such as CHAR or VARCHAR2),
       a LONG column can be referenced in a SQL statement
       within a trigger.  Note that the maximum length
       for these datatypes is 32 Kbytes.
    o  Variables or arguments in triggers or PL/SQL program
       units cannot be declared using the LONG datatype.
    o  :NEW and :OLD cannot be used with LONG columns.

However there are some ways like using dbms_sql package, pro*c or oci.

Anyway let's discuss the issue more. As we understand
We can insert a long into a long database column.
On the other end we cannot select a value longer than  
32760 bytes from a long column into a long variable
in pl/sql. (this is true for long raw as well). Right?
If we need to exceed 32K we may use dynamic sql. (one method)
the following is a sample for manipulating long data in pl/sql:

SQLWKS> drop table long_temp
     2> /
Statement processed.
SQLWKS> create table long_temp
     2>  (rec_id varchar2(10),
     3>   long_text LONG)
     4> /
Statement processed.
SQLWKS> insert into long_temp
     2>  values('1', lpad('fjdfsjsdjlk', 32000,
'lsd[]234]234234]\423900@#$)(@#$*$@@&($@#'))
     3> /
1 row processed.

...........that was 32000K because lpad function. don't expect me to
type,copy/paste.
...........this is just a sample.
SQLWKS> commit
Statement processed.
SQLWKS> /
SQLWKS> CREATE OR REPLACE PROCEDURE readlongdata(rec_id IN NUMBER) IS
     2>   data_chunk VARCHAR2(254);  -- these can go higher
     3>   chunk_size NUMBER:=254;    -- up to 32k
     4>   chunk_size_returned NUMBER;
     5>   mycursor NUMBER;
     6>   sql_stmt VARCHAR2(1024);
     7>   cur_pos NUMBER:=0;
     8>   rows NUMBER;
     9>   dummy NUMBER;
    10> BEGIN
    11>   sql_stmt:= 'SELECT long_text FROM long_temp WHERE rec_id =
:doctoget';
    12>   mycursor:= dbms_sql.open_cursor;
    13>   dbms_sql.parse(mycursor, sql_stmt, dbms_sql.v7);
    14>   dbms_sql.bind_variable(mycursor, ':doctoget', rec_id);
    15>   dbms_sql.define_column_long(mycursor, 1);
    16>   dummy := dbms_sql.execute(mycursor);
    17>   rows:=dbms_sql.fetch_rows(mycursor);
    18>   LOOP
    19>     dbms_sql.column_value_long(mycursor, 1, chunk_size, cur_pos,
data_chunk, chunk_size_returned);
    20>     dbms_output.put_line(data_chunk);
    21>     cur_pos:=cur_pos + chunk_size;
    22>     EXIT WHEN chunk_size_returned = 0;
    23>   END LOOP;
    24>   dbms_sql.close_cursor(mycursor);
    25> END readlongdata;
    26> /
Statement processed.
SQLWKS> set serveroutput on
Server Output                   ON
SQLWKS> begin
     2>   readlongdata('1');
     3> end;
     4> /
Statement processed.
lsd[]234]234234]\423900@#$)(@#$*$@@&($@#lsd[]234]234234]\423900@#$)(@#$*$@@&($@#lsd[]234]234234]\423900@#$)(@#$*$@@&($@#lsd[]234]234234]\423900@#$)(@#$*$@@&($@#lsd[]234]234234]\423900@#$)(@#$*$@@&($@#lsd[]234]234234]\423900@#$)(@#$*$@@&($@#lsd[]234]23423
4]\423900@#$)(@#$*$@@&($@#lsd[]234]234234]\423900@#$)(@#$*$@@&($@#lsd[]234]234234]\423900@#$)(@#$*$@@&($@#lsd[]234]234234]\423900@#$)(@#$*$@@&($@#lsd[]234]234234]\423900@#$)(@#$*$@@&($@#lsd[]234]234234]\423900@#$)(@#$*$@@&($@#lsd[]234]234234]\423900@#$)(
@#$*$@@&($@#lsd[]234]234234]\423900@#$)(@#$*$@@&($@#lsd[]234]234234]\423900@#$)(@#$*$@@&($@#lsd[]234]234234]\423900@#$)(@#$*$@@&($@#lsd[]234]234234]\423900@#$)(@#$*$@@&($@#lsd[]234]234234]\423900@#$)(@#$*$@@&($@#lsd[]234]234234]\423900@#$)(@#$*$@@&($@#ls
d[]234]234234]\423900@#$)(@#$*$@@&($@#lsd[]234]234234]\423900@#$)(@#$*$@@&($@#lsd[]234]234234]\423900@#$)(@#$*$@@&($@#lsd[]234]234234]\423900@#$)(@#$*$@@&($@#lsd[]234]234234]\423900@#$)(@#$*$@@&($@#lsd[]234]234234]\423900@#$)(@#$*$@@&($@#lsd[]234]234234]
\423900@#$)(@#$*$@@&($@#lsd[]234]234234]\423900@#$)(@#$*$@@&($@#lsd[]234]234234]\423900@#$)(@#$*$@@&($@#lsd[]234]234234]\423900@#$)(@#$*$@@&($@#lsd[]234]234234]\423900@#$)(@#$*$@@&($@#lsd[]234]234234]\423900@#$)(@#$*$@@&($@#lsd[]234]234234]\423900@#$)(@#
$*$@@&($@#lsd[]234]234234]\423900@#$)(@#$*$@@&($@#lsd[]234]234234]\423900@#$)(@#$*$@@&($@#lsd[]234]234234]\423900@#$)(@#$*$@@&($@#lsd[]234]234234]\423900@#$)(@#$*$@@&($@#lsd[]234]234234]\423900@#$)(@#$*$@@&($@#lsd[]234]234234]\423900@#$)(@#$*$@@&($@#lsd[
]234]234234]\423900@#$)(@#$*$@@&($@#lsd[]234]234234]\423900@#$)(@#$*$@@&($@#lsd[]234]234234]\423900@#$)(@#$*$@@&($@#lsd[]234]234234]\423900@#$)(@#$*$@@&($@#lsd[]234]234234]\423900@#$)(@#$*$@@&($@#lsd[]234]234234]\423900@#$)(@#$*$@@&($@#lsd[]234]234234]\4
23900@#$)(@#$*$@@&($@#lsd[]234]234234]\423900@#$)(@#$*$@@&($@#lsd[]234]234234]\423900@#$)(@#$*$@@&($@#lsd[]234]234234]\423900@#$)(@#$*$@@&($@#lsd[]234]234234]\423900@#$)(@#$*$@@&($@#lsd[]234]234234]\423900@#$)(@fjdfsjsdjlk
etc..

SQLWKS>

Also You can manipulate 2G data in a precompiler program
with regular embedded SQL by using datatype equivalencing to declare
a host variable of type long varchar.
Also there should be an example under in pro*c folder.
I wrote an app a long time ago. I cannot locate it though.
But it is same approach. IMHO Pro*c would be ideal solution.

one min, let me look at my oracle documentation. Yes it is sample4.pc.
Analyze it please.

Another note would be there is a change in processing of those columns
in Oracle 8. There are other datatypes (clob, blob etc.) with increased
limits.
Or Oracle context option...
I assumed you have Oracle7.

Good Luck

Hakan Eren

Prakash wrote:

> Can anyone tell me how to assign Long raw to a variable? Here is my
> problem:

> When I assign long raw data from my database to a variable, in my Stored
> Procedure, it accepts if the value is < 32 K. Otherwise throws the
> exception of Numeric or Value Error.But Database can hold upto 2 GB of
> data on the same column.

> Please help me. Thanks

> Sent via Deja.com http://www.deja.com/
> Before you buy.

clean text for testing pl/sql code:

drop table long_temp
/
create table long_temp
 (rec_id varchar2(10),
  long_text LONG)
/
insert into long_temp
 values('1', lpad('fjdfsjsdjlk', 32000,
'lsd[]234]234234]\423900@#$)(@#$*$@@&($@#'))
/
commit
/
CREATE OR REPLACE PROCEDURE readlongdata(rec_id IN NUMBER) IS
  data_chunk VARCHAR2(254);  -- these can go higher
  chunk_size NUMBER:=254;    -- up to 32k
  chunk_size_returned NUMBER;
  mycursor NUMBER;
  sql_stmt VARCHAR2(1024);
  cur_pos NUMBER:=0;
  rows NUMBER;
  dummy NUMBER;
BEGIN
  sql_stmt:= 'SELECT long_text FROM long_temp WHERE rec_id = :doctoget';
  mycursor:= dbms_sql.open_cursor;
  dbms_sql.parse(mycursor, sql_stmt, dbms_sql.v7);
  dbms_sql.bind_variable(mycursor, ':doctoget', rec_id);
  dbms_sql.define_column_long(mycursor, 1);
  dummy := dbms_sql.execute(mycursor);
  rows:=dbms_sql.fetch_rows(mycursor);
  LOOP
    dbms_sql.column_value_long(mycursor, 1, chunk_size, cur_pos,
data_chunk, chunk_size_returned);
    dbms_output.put_line(data_chunk);
    cur_pos:=cur_pos + chunk_size;
    EXIT WHEN chunk_size_returned = 0;
  END LOOP;
  dbms_sql.close_cursor(mycursor);
END readlongdata;
/
set serveroutput on
begin
  readlongdata('1');
end;
/