Re: Long raw with DynamicSQL?

From: <edwards_at_garland.dnr.state.sc.us>
Date: 1997/04/29
Message-ID: <336656D7.446B@garland.dnr.state.sc.us>#1/1

Markus Schöpflin wrote:

> Question:
> How can I obtain the length of the element in a select/fetch
> using dynamic SQL?

You need to use 'define_column_raw' and 'column_value_raw'. the length of the long raw element will be returned in the 'actual_length' paramater of 'column_value_raw'.

I put together a quick example, If you need more help on this, E-mail me at edwards_at_garland.dnr.state.sc.us

procedure test is
elementsize integer;
icursor integer;
iresult integer;
buffer raw(100);
column_error number(10,5);
actual_length integer;
begin

elementsize := 100;
icursor := DBMS_SQL.open_cursor;

• the 'IMAGE' column is long raw dbms_sql.parse(icursor,'select image from s_image where id = 1',DBMS_SQL.NATIVE);

dbms_sql.define_column_raw(icursor,1,buffer,elementsize);

iresult := dbms_sql.execute(icursor);

dbms_sql.fetch_rows(icursor);

• here is where the length of the image is returned: dbms_sql.column_value_raw(icursor,1,buffer,column_error,actual_length);
  • 'actual_length' should be the exact size of the raw column dbms_output.put_line('actual_length:'||to_char(actual_length));
• now that you have the length of the column, you can allocate your
• memory and just to the 'column_value_raw' again;
• by the way, how do you allocate memory for a variable in pl/sql??
• allocate_mem(buffer,actual_length); -- dbms_sql.column_value_raw(icursor,1,buffer,column_error,actual_length);

dbms_sql.close_cursor(icursor);

exception when others then

    dbms_output.put_line(SQLERRM);
end test; Received on Tue Apr 29 1997 - 00:00:00 CDT