I have a trigger function that I only want to fire on certain instances of INSERTS, in this case, if do_backup = true. If it fires in all instances, I get an infinite loop. The logic seems pretty simple to me, and the rest of the function works. But the trigger function does not seem to register my conditional and always runs, even when ...
Is there any easy way to query a stored procedure (Oracle - PL/SQL) for what parameters it expects? I know that I can query USER_SOURCE to get the whole procedure but I'd then have to parse the whole procedure, and if the parameter is of type [table].[column]%TYPE I'd then have to query the table schema as well. Either using just sql o...
I have to fetch all sequences with their table name along with the column name on which sequence is applied .Some how i managed to fetch table name corresponding to sequence because in my data base sequence is stored with first name as table name from data dictionary(all_sequences and all_tables) . Please let me know how to fetch corres...
I have four tables TableA: id1 id2 id3 value TableB: id1 desc TableC: id2 desc TableD: id3 desc What I need to do is to check if all combinations of id1 id2 id3 from table B C and D exist in the TableA. In other words, table A should contain all possible combinations of id1 id2 and id3 which are stored in the other three...
The standard SQL aggregate function max() will return the highest value in a group; min() will return the lowest. Is there an aggregate function in Oracle to return a random value from a group? Or some technique to achieve this? E.g., given the table foo: group_id value 1 1 1 5 1 9 2 2 2 4 2 8...
Good Day Gurus, I am trying to concatenate clobs in a PL/SQL loop and it has been returning null whilst when using DBMS_OUTPUT prints out the loop values and when executing each result of the clobs gives an output as well. The system is meant to execute an already stored SQL in a table based on the report name passed into it. This part...
I'm selecting from tables FOO and BAR. I'd like to lock the records of FOO which are being returned, but I don't want the records of BAR to be locked. cursor c_foobar is select foo.*, bar.* from foo, bar where foo.id = bar.foo_id for update of <what should I put here?> It seems like I need to specify individual columns, but I want t...
I would like to restrict users from inserting more than 3 records with color = 'Red' in my FOO table. My intentions are to A) retrieve the current count so that I can determine whether another record is allowed and B) prevent any other processes from inserting any Red records while this one is in process, hence the for update of. I'd l...
Hi, I have a series of scripts running in parallel as a nohup on an AIX server hosting oracle 10g. These scripts are written by somebody else and are meant to be executed concurrently. All the scripts are performing updates on a table. I am getting the error, ORA-00060: deadlock detected while waiting for resource As I g...
I'm trying to use oracle change notifications (API from Dbms_Change_Notification package) to update complex materialized views. I install change notification for my table with my PL/SQL callback and I can see in USER_CHANGE_NOTIFICATION_REGS view that my change notification is installed properly. On development oracle servers with low lo...
Is there any way to convert decimal to binary, or binary to decimal, in Oracle 10g without having to first define a function? I have limited database access (SELECT only) and all the solutions for this I've found online seem to involve CREATE FUNCTION, which does not work for me. ...
I have two tables like the below one in my DB. In the first table ITEM for evry DEPARTMENT_CODE there will be multiple ITEM_CODE. ITEM ---------------------------------------------------- "STORE_CODE" "ITEM_CODE" "DEPARTMENT_CODE" "011" "912003" "14" "011" "912004" "14" "011" "914001" "14" ----------------------...
Through Oracle queries is it possible to find out which views/synonyms/tables a stored procedure use? In PL/SQL Developer if you collapse out a stored procedure it will show "References" which shows all the tables/views/synonyms that the stored procedure uses. I am trying to implement this functionality into a script which will com...
Friends, This Ask Tom thread which I found via another SO question, mentions Table and Transactional API's and I'm trying to understand the difference between them. A Table API (TAPI) is where there is no access to the underlying tables and there are "getters" & "setters" to obtain information. For example to select an address I ...
I'm trying to add an attribute to an already existing Object Type in an Oracle 10.2.0.4 DB. The schema is valid, and everything is working before running the following statement: ALTER TYPE sometype ADD ATTRIBUTE (somefield varchar(14)) CASCADE INCLUDING TABLE DATA / SHOW ERRORS The alter fails with an ORA-22324 and an ORA-21700. Afte...
TYPE point IS RECORD ( X NUMBER, -- The X co-ordinate of the point Y NUMBER -- The Y co-ordingate of the point ); This is defined in my package header. It is then used in a procedure defined in the same package. Is it possible for me to call the procedure via ODP.net? ...
I have PHP code that execute a stored procedure 10 times. If one stored procedure call fails, it should continue on, and at the end commit the transaction. It basically looks like this: $connection = getConn(); foreach($row as $i=>$j) { $SQL = "BEGIN MYPROC.EXECUTE(:VAL1, :VAL2); END;"; $statement = OCIParse($connection, $SQL); ...
Please note: I am asking the question I want answered. I know this question means the database is set up poorly. So I will vote down any questions that suggest changing the way the table is set up. I need to duplicate a bunch of rows, while changing one value. name col1 col2 dave a nil sue b nil sam c 5 needs t...
Is it possible to correctly pass an OracleParameter to a boolean parameter in a pl/sql stored procedure? ...
This question is very much like my previous question, but a bit more complicated. Rob van Wijk's answer worked perfectly for my other question, and I've been using that as a starting point. My problem now is that I am pivoting dates for different fields. Whereas before I cared about getting all open_in and open_out values for a given ...