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

Jim Kennedy wrote:

> Daniel,I think the problem is that the version of the MS ODBC driver
> she has does NOT support ref cursors etc. I remember a long time ago
> we couldn't use CLOBS for that very reason. Granted it was just after
> 8.05 came out, but perhaps they have a similar problem. Yes, they
> should upgrade and also upgrade their driver. MS isn't known for
> supporting bleeding edge when it is another vendor.Jim
> --
> Replace part of the email address:
> kennedy-down_with_spammers_at_attbi.com
> with family. Remove the negative part, keep the minus sign. You can
> figure it out.
>
> "DA Morgan" <damorgan_at_exxesolutions.com> wrote in message
> news:3E89A579.B108BFE4_at_exxesolutions.com...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;
> > N-US;Q244661&
> >
> >
> > ttp://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
>

Good point. 8.1.5 is so old it may not support the functionality available for so long in 8.1.7.

Another good reason to upgrade.

Daniel Morgan Received on Tue Apr 01 2003 - 10:19:15 CST