Re: Stored Proc for CLOB to recordset of varchar2(4000)

Thank you for your reply. I should of explained better. So here is the problem.

I am using oracle 8.1.5, NT4 and Microsoft ODBC for Oracle connection. I do not have the option of updating the servers, connection or database. According to the link below the CLOB and a few others are not supported by this connection. Therefore I am unable to obtain the CLOB from the database, via the ASP page directly.

http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q244661&

http://support.microsoft.com/default.aspx?scid=kb;EN-US;255043, is a very useful document, but here again the connection does not suppport the use of ref cursors.

Therefore I have find the solution which is to use a stored proc on the database and split the CLOB into packages of varchar2(4000) and delivery these in a recordset to the ASP.

My problem is I have never created a stored proc on the oracle database until now, and I do not know how to program this solution. I do have TOAD which will enable me to write and test the stored proc.

This is what I have so far.

TYPE CONTENT_ARRAY as TABLE (CONTENTSTR VARCHAR2(4000))

CREATE OR REPLACE procedure SP_OBTAINCLOB(TABLENAME in varchar2, COLUMNNAME in varchar2, COLUMNIDNAME in varchar2, COLUMNID in number) as
id integer;
ro integer;
und integer;
u varchar2(4000);
v number;

begin
id:=dbms_sql.open_cursor;
dbms_sql.parse(id,'SELECT '|| COLUMNNAME ||' FROM '|| TABLENAME ||' where '|| COLUMNIDNAME ||' = '|| COLUMNID,dbms_sql.native); dbms_sql.define_column_long(id,1);
und:=dbms_sql.execute(id);
ro:=dbms_sql.fetch_rows(id);
while ro!=0
loop
dbms_sql.column_value_long(id,1,4000,0,u,v); CONTENT_ARRAY(v):= u;
ro:=dbms_sql.fetch_rows(id);
end loop;
commit;
dbms_sql.close_cursor(id);

end SP_OBTAINCLOB;
/

I do think this is probably completely wrong through!

I believe, but I do not fuller know that I should have a value in the procedure pararmeter list which has an out varaible. Therefore the stored proc can delivery something out. Is this correct?

Basically I am at a bit of a lose with the stroed proc and would be very grateful for any help.

Thank you in advance.

Caroline

DA Morgan <damorgan_at_exxesolutions.com> wrote in message news:<3E888BBD.EA184791_at_exxesolutions.com>...
> Caroline wrote:
>
> > If this is simple please forgive me.
> >
> > With the oracle and connection which has been installed for my
> > website, I am unable to obtain a CLOB from the database. Therefore the
> > solution is creating a stored procedure which gets the CLOB and
> > converts it into a recordset of chucks of varchar2(4000).
> >
> > My knowledge of stored procs is souly based on sql server. So how can
> > this be done in Oracle.
> >
> > Also is there a reference for stored procedures for oracle?
> >
> > Very very grateful
> >
> > Caroline
>
> I see no reason why you can not return a CLOB. But there is a huge amount
> of information missing from your post required for anyone to help you.
>
> 1. Version and edition of Oracle
> 2. Front-end
> 3. What is the connection to the database
>
> The similarities between Oracle and SQL Server are superficial. Go to
> http://tahiti.oracle.com and search the concepts and architecture docs as
> well as those related to CLOB.
>
> BTW: VARCHAR2 is 32K not 4K when talking about variables rather than data
> types.
>
> Daniel Morgan
Received on Tue Apr 01 2003 - 02:19:37 CST