存储过程与图象存取(在线等待）

Question

create or replace procedure 过程名(Name in out type, Name in out type, ...) is
begin
end

Answer 1

create or replace procedure p_intoblob(p_a1,number,p_filename varchar2)
as
l_blob  blob;
l_bfile bfile;
begin
 insert into b values(p_a1,empty_blob())
  return  x1 into l_blob;
  l_bfile:=bfilename('dir',p_filename);
  dbms_lob.open(l_bfile);
  dbms_lob.loadfromfile(l_blob,l_bfile,dbms_lob.getlength(l_blob));
  dbms_lob.close(l_bfile);
  commit;
end;

Answer 2

在使用oracle的过程中，如果要写入blob字段一定要使用dbms包。

Answer 3

存储过程：
"CREATE OR REPLACE PROCEDURE TEST_BLOB ( "
"p_USERNAME in CHAR,p_OLD in CHAR,p_PHOTO in BLOB default empty_blob() ) "
"as "
"begin "
"begin "
"insert into USERPHOTO( "
"USERNAME,OLD,PHOTO) "
"values (p_USERNAME,p_OLD,p_PHOTO); "
"commit; "
"end; "
"end TEST_BLOB;" 
调用它的vc代码
// ocistudy.cpp : Defines the entry point for the console application.
//#include "stdafx.h"//pass blob to oracle stored procedure 
//build by masterz 20050227 with VC2003, Windows 2003, Oracle 10g.
//The tip: user oledb provider string!
#include "stdafx.h"
#import "C:\PROGRA~1\COMMON~1\System\ado\msado15.dll" rename( "EOF", "adoEOF" )
struct InitOle
{
InitOle()  { ::CoInitialize(NULL); }
~InitOle() { ::CoUninitialize();  }
} _init_InitOle_;
void PrintProviderError(ADODB::_ConnectionPtr pConnection);void print_properties(LPCTSTR name, ADODB::PropertiesPtr Properties)
{
long prop_count = Properties->GetCount();
printf("%s property count = %d\n",name,prop_count);
for(long i=0;i<prop_count;i++)
{
printf("%s property [%d]:%s\n",name,i,(LPCSTR)Properties->GetItem(i)->Name);
}
}
int main(int argc, char* argv[])
{
ADODB::_ConnectionPtr  Conn1;
ADODB::_CommandPtr    Cmd1;
ADODB::_ParameterPtr  oldParam= NULL;
ADODB::_ParameterPtr inParam=NULL;
ADODB::_ParameterPtr blobParam=NULL;
_variant_t  vtEmpty (DISP_E_PARAMNOTFOUND, VT_ERROR);
_variant_t  vtEmpty2 (DISP_E_PARAMNOTFOUND, VT_ERROR);
_bstr_t    bstrConnect="Provider=OraOLEDB.Oracle;Data Source=orcl;User Id=system;Password=oracle;";
_bstr_t    bstrCreate ( "CREATE OR REPLACE PROCEDURE TEST_BLOB ( "
"p_USERNAME in CHAR,p_OLD in CHAR,p_PHOTO in BLOB default empty_blob() ) "
"as "
"begin "
"begin "
"insert into USERPHOTO( "
"USERNAME,OLD,PHOTO) "
"values (p_USERNAME,p_OLD,p_PHOTO); "
"commit; "
"end; "
"end TEST_BLOB;" );
_bstr_t    bstrSP("{CALL TEST_BLOB(?,?,?)}" );
try
{
_variant_t varOptional(DISP_E_PARAMNOTFOUND,VT_ERROR); 
ADODB::_StreamPtr adostream;
adostream.CreateInstance(_T("ADODB.Stream"));
adostream->Type = ADODB::adTypeBinary;
adostream->Open(varOptional,ADODB::adModeUnknown, ADODB::adOpenStreamUnspecified, _T(""), _T("")); 
adostream->LoadFromFile("C:\\masterz\\app.zip");
_variant_t vReadTo = adostream->Read(ADODB::adReadAll); 
long blob_size = adostream->GetSize();
adostream->Close(); _bstr_t bstrEmpty;
Conn1.CreateInstance( __uuidof( ADODB::Connection ) );
Cmd1.CreateInstance( __uuidof( ADODB::Command ) );
Conn1->ConnectionString = bstrConnect;
Conn1->Open( bstrConnect, bstrEmpty, bstrEmpty, -1 );
Conn1->Execute(bstrCreate,NULL,ADODB::adCmdText);
Cmd1->ActiveConnection = Conn1;
Cmd1->CommandText      = bstrSP;
Cmd1->CommandType      = ADODB::adCmdText;
Conn1->Properties->Refresh();
inParam = Cmd1->CreateParameter(_bstr_t("p_USERNAME"),ADODB::adChar,ADODB::adParamInput,2,_variant_t( "a" ));
Cmd1->Parameters->Append(inParam); oldParam = Cmd1->CreateParameter(_bstr_t("p_OLD"),ADODB::adChar,ADODB::adParamInput,4,_variant_t( "yes" ));
Cmd1->Parameters->Append(oldParam); blobParam = Cmd1->CreateParameter(_bstr_t("p_PHOTO"),ADODB::adLongVarBinary,ADODB::adParamInput,blob_size,vReadTo);
Cmd1->Parameters->Append(blobParam);
Cmd1->Properties->Refresh();
print_properties("Cmd1",Cmd1->Properties); ADODB::PropertyPtr prop = Cmd1->Properties->GetItem("SPPrmsLOB");
prop->PutValue(_variant_t(VARIANT_TRUE,VT_BOOL)) ;
Cmd1->Execute(NULL,NULL,ADODB::adExecuteNoRecords);
prop = Cmd1->Properties->GetItem("SPPrmsLOB");
prop->PutValue(_variant_t(VARIANT_FALSE,VT_BOOL)) ;
Conn1->Close();
}
catch(_com_error &e)
{
_bstr_t bstrSource(e.Source());
_bstr_t bstrDescription(e.Description());
printf("\nCOM error occurred, Source : %s \n Description : %s \n",(LPCSTR)bstrSource,(LPCSTR)bstrDescription);
PrintProviderError(Conn1);
}
printf("\nprogram end\n");
return 0;
}
VOID PrintProviderError(ADODB::_ConnectionPtr pConnection)
{
ADODB::ErrorPtr  pErr = NULL;
long      nCount = 0;
long      i = 0;
if( (pConnection->Errors->Count) > 0)
{
nCount = pConnection->Errors->Count;
for(i = 0; i < nCount; i++)
{
pErr = pConnection->Errors->GetItem(i);
printf("\n\t Error number: %x\t%s", pErr->Number, (LPCSTR)pErr->Description);
}
}
}

Answer 4

请问楼上的，我在DELPHI7如何调用