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

Caroline wrote:

> 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

I am certainly not an expert on things Microsoft but first off why Oracle 8.1.5. It is an antique and you should upgrade to at least 8.1.7.4 immediately.

But I am quite certain one can use REF CURSORS and CLOBs across ODBC no matter what Microsoft may say. I would suggest you look elsewhere than a Microsoft web site. Certainly VB, Crystal Reports and many others have no problem doing this.

Daniel Morgan Received on Tue Apr 01 2003 - 08:43:05 CST