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;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 - 09:31:34 CST
