case study: one class has many students, only one student has reward.
original design:
drop table student;
drop table class;
[Code]...
questions:
1. If I want insert into student values('stu2','cls1','yes'); I can put a trigger on student to check whether it has only one student in class has reward. This trigger should tell me error: I can not let 'stu2' has 'yes' on reward column. But it will lead Oracle error: ora-04091: mutating table. It seems use "after" or "instead of" trigger can solve this problem, so how to do it?
2. Another way to make sure only one student in a class has reward is change design as:
1.I have created table emp_log using structure of emp table
create table emp_log as select * from emp where 1=2;
2.Now I have added some new fields i.e new_sal(updated salary) , upd_by (who updated), upd_date (Salary update date) alter table emp_log add (new_sal number, upd_by varchar2(20),upd_date date);
3. I have made following trigger to insert old and new values (for salary,user and date) in emp_log table whenever I update sal for particular employee in emp table and at the same time i am selecting the updated value of sal into lv_sal variable which will cause mutating error.
create or replace trigger emp_trg after update of sal on emp for each row declare
[code]...
4. Suppose sal of emp is 1000:- update emp set sal=sal+100 where empno=7369; >select * from emp_log; output:- sal= 1000 new_sal=1100
[code]...
Now the question starts if i commment pragma autonomous_tansaction and commit statement then it gives mutating error because at the same time i am updating and selecting value of sal from emp table.
create or replace trigger emp_trg after update of sal on emp for each row declare
[code]...
Questions:- 1.Is pragma autonomous_transaction handling the mutating error and if yes then how? 2.How many ways are there to handle mutating error. 3.What is the exact definition of Mutating Error. 4.An example or query to use lv_sal variable for printing new updated sal. 5.What is Mutating Table , Is it similar to mutating error (Found this topic while searching for mutating error).
I have created a trigger which gets executed whenever there is DML operation happens on it. I am getting the below error. ORA-04091: table RTS_SCHEMA.TBL1 is mutating, trigger/function may not see it...the tigger is on table tbl1 and the structure of the table is
CREATE TABLE TBL1 ( SLNO NUMBER NULL, DES VARCHAR2(20 BYTE) NULL ) LOGGING NOCOMPRESS NOCACHE NOPARALLEL MONITORING; [code]...
I have a table EMPLOYEE with columns employee_id and status. I have a requirement that when an employee status is getting changed, then even its linked employee's status also should be changed to the same status value. For this, I need to handle the updating of linked employee's status using a trigger.
Since we get mutating trigger issue when try to update the originating table, I am trying to go with the approach of "in place of a single AFTER row trigger that updates the original table, resulting in a mutating table error, you may be able to use two triggers. The first is an AFTER row trigger that updates a temporary table, and the second an AFTER statement trigger that updates the original table with the values from the temporary table".
But however I am still facing the same issue.
Test case:
CREATE TABLE EMPLOYEE ( EMPLOYEE_ID VARCHAR2(1), STATUS NUMBER(9) );
INSERT INTO EMPLOYEE VALUES ('A',1); INSERT INTO EMPLOYEE VALUES ('B',1); commit; [code]....
Also, any alternate options (rather than using 2 triggers with temp table).
Not able to understand what's wrong with the code. I am trying to import data to a table using a CSV file. I have exported the data (CSV) from the interactive report and I am just trying to insert the same data to the table, through a process. When, I tried to do so; its throwing an error message saying NO_DATA_FOUND and file is not getting inserted into wwv_flow_files table.
But when I removed the data from the CSV file for the comments field and then tried importing the file, the process worked. I don't understand whats the problem with the code.
I have a sample app setup in my workspace for this weird problem.
[URL]
Workspace details:
CSV file with comments field and data in it - when trying to import - throws an error message NO_DATA_FOUND
CSV file with comments field and without data in it - tried importing - this worked
I am unable to understand why row level triggers cant be used in mutating tables.
If you need to update a mutating table, you could bypass these restrictions by using a temporary table, a PL/SQL table, or a package variable. For example, in place of a single AFTER row trigger that updates the original table, resulting in a mutating table error, you might use two triggers--an AFTER row trigger that updates a temporary table, and an AFTER statement trigger that updates the original table with the values from the temporary table.
I was trying to insert a row into Oracle database consisting 20 columns through IBM Web Experience Factory. I am getting "Array index out of range: 20" error message. To my knowledge insert is working fine till 7 columns. Is it a driver problem or do i need to install any fix pack?
I have created the directory pointing on 'C:data'...And loaded a comma delimited CSV file in there...
- Checked the csv permissions ther are set to 'everyone'
- Checked the previladges of the directory, they are set to 'READ/WRITE'
But when I issue a select statement against the exte table I get an error '
ORA-29913: error in executing ODCIEXTTABLEOPEN callout ORA-29400: data cartridge error KUP-04040: file input.csv in DUMP_TXT not found ORA-06512: at "SYS.ORACLE_LOADER", line 14 ORA-06512: at line 1'
I have a Database that has a frontend in MS Access and a backend in Oracle. When I try to open the Oracle Table/s in the MS Access Frontend, it gives me this error message:
Reserved error (-7711); there is no message for this error.
There is some stuff on the internet about this error but none of it fixes my issue. It seems to be an MS Access error rather than an Oracle error. Only thing is this issue is only on Machines that are running on XP. I have no problem on mine which is Windows 7.
im trying to migrate Sybase 12.5 to oracle 11G R2 on linux all tables are fine expect one which fails with error
"SQL Error: ORA-01841: (full) year must be between -4713 and +9999, and not be 0 01841. 00000 - "(full) year must be between -4713 and +9999, and not be 0" *Cause: Illegal year entered *Action: Input year in the specified range"
i have change the data type from CHAR to VARCHAR2 etc as for the datatype date shows as date on the tool - I'm using sqldeveloper from oracle.
What would cause Oracle to insert duplicate rows into a table? Could a join of two tables in the initial query assigned to an application page cause ORacle to insert an extra row into a table when an update to data value occurs? I have no insert triggers and no foreign keys assigned to the table. I am not sure what would cause Oracle to assume that an insert of a row must occur. I want to prevent that insert.
I needed to create a page on my existing APEX application that would allow the user to upload a file, I followed an online tutorial where the user had created a dummy table and inserted CSV File inserted through APEX into the table. Following that simple example I am able to load the simple CSV file (from tutorial) into a dummy table (from tutorial) but when I attempt to insert actual/dummy data into my actual database (which has a lot more fields of different types), using the exact same process, I am unable to do so.
Ironically, I am unable to insert even dummy values despite the fact that I have been able to insert the same dummy values using SQL Developer. Icing on the cake is that APEX does not produce any error, this lack of debugging feature (especially line by line debugging) is such a pain. Just to add, I can load the values into an Array and can successfully print the delimited values off the array but am still unable to insert the same values into my table. Here is the table that I am attempting to insert into (actual names replaced by Dummyxx):
Note that all the the CSV does not contain all the fields, the CSV files that are expected to be entered into the system contain 65 Fields whereas the Table has 73 Fields. Also note that the process runs fine through SQL loader that is invoked through a different server which I need to release and hence the attempt to load the table this way. Also, the procedure on the SQL Loader server is quite complex and involved JAVA+Unix Shell Scripts etc. which I would prefer to avoid.
how to use APEX_ITEM.text for where the source for this comes from user input values and not for an underlying table.I have a report where three columns are coming from an actual table (one being a checkbox for selection) but then also have two additional fields on the screen that are not from a table but placeholder fields for user's to enter data.
For example, my query looks like this:
select apex_item.checkbox2(10,id), name, telephone, apex_item.text(20,NULL) as "Date Contacted", apex_item.textarea(30,NULL,5,80) as "Comment" from my_table
I am not seeing any values for the two user fields when entering values and doing a view source?
I would like to install Oracle Express Edition on Linux. I have some troubles on x64 architecture of Linux with win32 progs that runs via wine. And I need Oracle for developing. So, I would like install x32 Oracle Express Edition, but I can't find it anywhere. Is it exists.
Why is Oracle Express Edition 32x exist for windows, but there is not Oracle Express Edition 32x for Linux? We are using widely Oracle 10g. And there is many special difference with 11g. But there is no anywhere 10g version. Where can I download it (for Linux)?
I receive this error. In my opinion the problem could be related to the presence of one personalized authentication schemas, based on a DB function, taken from a Packaged application, some years ago, called "menu_framework". This is the error report: ........ -- Now beginning upgrade. This will take several minutes.------- -- Ensuring template names are unique ------- -- Migrating metadata to new schema ------- -- Switching builder to new schema ------- begin * ERRORE alla riga 1: ORA-00600: codice di errore interno, argomenti: [kgiinb_invalid_obj], [0x26962168], [0x1A1042F4], [], [], [], [], [], [], [], [], []
1) after the error trap, I copied the Image folder and tried to launch APEX. It was possible to lunch the development pages, but impossible to execute any application with the error: Authentication Schema not Supported 2) then decided to downgrade to APEX 4.0, following the proper instructions: received a similar error: ....... SQL> exec apex_040000.wwv_flow_upgrade.switch_schemas('APEX_040100','APEX_040000 '); BEGIN apex_040000.wwv_flow_upgrade.switch_schemas('APEX_040100','APEX_040000'); END;
i have ir report and one column value coming from function,when open the IR report its giving error..
IR report code SELECT CS_ID ,CS_NAME, Util_func(PASS_ID) as "ALERT_DAYS" from "CSD_MASTERS" Function code FUNCTION Util_func(PASS_ID NUMBER) RETURN number IS ALERT_DAYS NUMBER:=0; BEGIN [code]........
I first started database, then gone to option Run SQL command line. After that while creating table it is showing error like "SP2-0640: Not connected."
I am getting many warning messages in the error console using Firefox V 13. This one seems to be the most prevalent. It's not causing problems except that on some machines it causes the error console to pop up and is distracting for the users.
I got error "Upload New Image must be specified. Click browse to select an image from your local computer" while i upload image in shared components>image>create ...
my logo is saved in local drive D. when i browse file and click to upload it gives error that i mentioned. This is happening with every uploading weather its image or weather its plugin.
i'm trying to get the difference between two dates so i have two date itemsthen i did daynamic action (set Value) and choose pl/sql function then added the following codeDecalrestart_date date;end_date datebeginstart_date:=to_date(:p20_start_date,'dd/mm/rrrr');select to_date (sysdate,' dd/ mm /rrrr')into end_datefrom dual;return end_date-start_date;end; but when i run the page i got error ora-01861
REVNUMBER is a VARCHAR2. I've set it to increment by letter in alphabet to note the revision of a document, i.e. IR*, A, B, C... . This works beautifully.
VERNUMBER is a VARCHAR2. I've set it to increment by number but starts from null to 'IR' to 1, 2, 3...
Until now, I've been incrementing VERNUMBER without problem if, instead of IR as the first incremented value I leave the field null. The user has asked that IR display. I've tried using IR as the default but I still need to increment from there so I don't see it making a difference between that or dumping it as the first condition of the following. The problem is I get an invalid number error either way.
*'IR' = 'Initial Release'
SELECT CASE WHEN VERNUMBER IS NULL THEN 'IR' WHEN VERNUMBER = 'IR' THEN '1' ELSE REGEXP_REPLACE(VERNUMBER, '[0-9]+$', REGEXP_SUBSTR(UPPER(VERNUMBER), '[0-9]+$') + 1) END NEW_VERSION FROM DOC_INFO WHERE DOC_INFO_ID = :P3_DOC_INFO_ID
A page has a display-only text item that is populated when a rare exceptional condition arises. I'd like for the text to be colored red. While editing the item, in the Element region,
I populated the item "HTML Form Element Attributes" by selecting 'style="font-color:red"' from the drop-down list. When the text displays, however, the color is still the default black. What do I need to do to turn the text red?
My scenario is I need to insert into History table when a record is been updated into a tabular form(insert the updated record along with the additional columns Action_by,Action_type(Like Update or delete) and Action Date Into History table i.e History table contains all the records as the main table which is been visible in tabular form along with these additional columns ...Action_by,action_type and action_date.
So now i dont want to create a befor/after update trigger on base table rather i would like to create a generic procedure which will insert the updated record into history table taking the page alias and pade ID as the parameters(GENERIC procedure is nothing but whcih applies to all the tabular forms(Tables) contained int he application ).
