Is it possibly that the loParameter.Type below says long vartype? =
Have you tried the Binary types(adLongVarBinary 204 and adVarBinary =
205)? Have you tried sending through just a tiny portion of the binary =
file to make sure you have data-types and conversions correct, and it's =
not simply a size limitation somewhere?=20
--=20
Derek
> Thanks for the suggestion Derek. I hadn't tried that yet.=20
> Unfortunately it
> returned the same error from the OLEDB provider:
>=20
> OLE IDispatch exception code 0 from OraOLEDB: ORA-01460:=20
> unimplemented or
> unreasonable conversion requested..
>=20
> Thanks,
> Bo Durban
>=20
>=20
>=20
>=20
> Derek Said:
>=20
> Does Oracle have an OLEDB/ADO provider you can use=20
> directly with ADO
> instead of passing everything through ODBC?
>=20
> --
> Derek
>=20
> > Anyone have experience passing ~2MB sized data to an Oracle SP using
> > SQLEEXEC( ) or ADO? I need to pass entire files to an Oracle
> > SP as an input
> > parameter of type BLOB or CLOB but cannot get it to work.
> >
> > Thanks,
> > Bo Durban
> >
> >
> > ** VFP Example:
> > nSQL =3D SQLSTRINGCONNECT( cOracleConnect )
> > cData =3D FILETOSTR("Document.pdf") && 2MB file
> > nResult =3D SQLEXEC(nSQL,"{call insert_file_blob(?cData)}") &&
> > C'005 error
> >
> > cData =3D FILETOSTR("Document.pdf") && 2MB file
> > cData =3D STRCONV(cData, 13) && Convert to base64
> > nResult =3D SQLEXEC(nSQL,"{call insert_file_clob(?cData)}") &&
> > C'005 error
> >
> >
> > ** ADO Example:
> > cData =3D FILETOSTR("Document.pdf") && 2MB file
> > cData =3D STRCONV(cData, 13) && Convert to base64
> >
> > loConnection =3D CREATEOBJECT( "ADODB.Connection" )
> > loConnection.CursorLocation =3D 2 && server-side cursor
> > loConnection.Open( cOracleConnect )
> >
> > loCommand =3D CREATEOBJECT( "ADODB.Command" )
> > loCommand.CommandType =3D 4 && by SP name
> > loCommand.CommandText =3D "insert_file_clob"
> > loCommand.ActiveConnection =3D loConnection
> >
> > loParameter =3D loCommand.CreateParameter()
> > loParameter.Name =3D "p_lob" && name we assign
> > loParameter.Type =3D 201 && long varchar string type
> > loParameter.Direction =3D 1 && input parameter
> > loParameter.Size =3D LEN(cData) && maximum length
> > loParameter.Value =3D cData && initialize the parameter
> > loCommand.Parameters.Append( loParameter )
> >
> > loCommand.Execute() && Error:
> > && OLE IDispatch exception code 0 from Microsoft OLE=20
> DB Provider
> > && for ODBC Drivers: [Oracle][ODBC][Ora]ORA-01460:=20
> unimplemented
> > && or unreasonable conversion requested
> >
> > RELEASE loCommand
> > loConnection.Close
> > RELEASE loConnection
>=20
> [excessive quoting removed by server]
>=20
[excessive quoting removed by server]
©2002 Derek J. Kalweit |
