Posted - 12/18/2003 : 2:26:24 PM
--------------------------------------------------------------------------------

Hi experts!
I'm new to VB and oracle stored procedure. I had created a
VB code to execute a stored procedure in Oracle,, and in the stored procedure, a link to another oracle database is created.
I created a stored procedure in Oracle database "A" and want to get some data information from oracle database "EVF16" which I don't have any right to create a store procedure or nothing...
Here is my VB code:
'I had something like database connection here...

Dim Cmd As New ADODB.Command
' Use a connection string or a Connection object.
Cmd.ActiveConnection = "Provider=MSDASQL;DSN=" & strDBNAME & ";UID=" & strID & ";PWD=" & strPW & ";DBQ=" & strDBNAME & ";ASY=OFF;"
Cmd.CommandText = "test2"
Cmd.CommandTimeout = 30
Cmd.CommandType = adCmdStoredProc
Cmd.Execute

and here's my stored procedure:
/* Declare string to store SQL Statement*/
v_sql varchar2(1000);

/* Declare string to store SQL Statement*/
v_sql varchar2(1000);

begin
delete from AV_project where proj in (Select proj from av_project@evf16 where proj='xxxx' );
v_sql := 'execute test2';
execute immediate v_sql;
end;

The error's something like : Oracle transaction is not open..or somethign like that...

Please help me..thanks

ANd even I've to add BeginTrans and CommitTrans but still not work.

Is the error coming from VB or Oracle?. Check the ADODB.Errors collection and see what's in there. The begintrans and committrans, need's to reside in the Stored procedure and Not VB.

You also need to have the correct access rights set in the DB for the user you are logging in as for the table.

If all the above are/or appear ok, then first of all remove the VB from the equation and first try and run the procedure from within Oracle itself. This will at least narrow where to concentrate your efforts (vb or Oracle).

Last but not least, ensure you have the correct Oracle Drivers installed on your PC (e.g. using Oracle 9, but have Oracle 8 drivers installed).