Search results for query: *

Wow, this thread was from a while back. No, I could never get VB to pass Oracle an array correctly. The best I was able to do the other way was to create a block of XML in the procedure and pass it up to VB or direct to the ASP level as a CLOB. Both VB and ASP were easily able to interpret...

Ah, students. Well we all cheated at one time or another when we were at school or uni... Try this: select a.shopname, sum(b.qtyinstock*b.price) money_in_stock from shop a, shopstock b where a.shopcode = b.shopcode and ((sum(b.qtyinstock*b.price) =...

The trigger is written so that if the F_STAT column is updated, it will fire. Therefore, using :new.STK_ID, you can access the exact value for STK_ID on the triggering line. Therefore, where I say: t_STK_ID := :new.STK_ID; this will select only the STK_ID for the line on table1 that has been...

OK, I was assuming that STK_ID is unique on table1 and table2. If it's not, then we need some unique value. if I understand correctly table1 can have many rows for each value of STK_ID It must have some unique key value that can link the tables, we need that. It can be referenced in the...

Hi again, Maybe I'm oversimplifying the problem here, but here is an example trigger that fires when the F_STAT column is updated. CREATE OR REPLACE TRIGGER CBR_TRIGGER AFTER UPDATE OF F_STAT ON TABLE1 REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW DECLARE CURSOR c_curs IS...

I'll try this today, thanks!

Hi, I am having a problem with passing pl/sql tables (arrays) as a variable. I can pass such a table between Oracle procedures without a problem, but I need to be able to pass it to VB and would also like to be able to read an array passed to me from VB. I have the following VB code: Dim...

Hi, I am having a problem with passing pl/sql tables as a variable. I can pass such a table between Oracle procedures without a problem, but I need to be able to pass it to VB and would also like to be able to read an array passed to me from VB. I have the following VB code: Dim iCommand As...

Within the body of the trigger, there are very few limitations. You can access other tables just as you would in any other pl/sql block. The only limitation you will have is if you try to examine the table that the trigger is firing on as you will hit a mutating table error. If you need to get...

Hi all, I am having a little problem with ref cursors that I hope someone can help me with. I am trying to use them to generically audit any table on the system. That means, the procedure will never know what the table structure or columns involved are until it is called. This is my code...

Talk about a lifesaver! The code looked a bit daunting to start, but looks to be running well. Does exactly what I need it to do. Thanks for getting back to me on this ghilbert, your help has been much appreciated! Kenneth

Hi All, I have a problem in that I am getting error ORA-04091 in a trigger I am writing. I can see why I get it, but not how to get around it. The basic idea is, I have a table which can have any amount of rows for a project. If a row is deleted, I want to check if there are any other rows in...

Yep, I could see that it was being passed in as a string. I was working on the assumption that it would execute if I could just build up the string as if I typed it directly into a sql editor. Sem, your idea seems to have done the trick. It's running nicely and I can tell my boss that he can...

I have an sql query that hopefully someone will have time to look at: I want to pass a series of numbers into a procedure, ie 1,3,5,6,7,8 and then be able to use them to access a cursor. Unfortunately, it keeps giving me an invalid number error when I try to run it. So, I tried to pass them in...