SQL & PL/SQL :: Further Changes To Block By Transaction Not Allowed
Jun 6, 2011
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE10.2.0.4.0Production
TNS for Solaris: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
We are running a code through informatica and are frequently getting the ORA-08007. The error and the code causing it are mentioned below. (The '?' implies a variable which is supplied by informatica). The source is a flat file.
ORA-08007: Further changes to this block by this transaction not allowed
1. Tried initially by reducing the commit interval to commit at 100 rows. But still getting the same error.
2. DBA has installed the patch # 6790768. But it still fails.
I create databalock - LC_REQ_PO_ITM_DTLS...In design time Insert and update allowed for that block - YES. While i execute query, i write procedure like this
but when i press execute button , then data comes properly on that bloack..but i click on that block, error comes like this - 'You can not create record here '.and data disappearing..
I 've noticed transactional triggers, as a value for Query data block property, How can i use it, and is it really 's mode advantages than using from clause and procedure based with DMLs on non-based items?
I have a following select statement where I am not generating a sequence in any of these WHERE clause, GROUP BY or ORDER BY clause, DISTINCT clause, along with a UNION or INTERSECT or MINUS or sub-query.
I have query like this
"SELECT AER_ID_TEMP.AER_ID, AER_ID_TEMP.D__PRNO, AER_ID_TEMP.D__PCNT, CAST((select char_value from aris_param_data where param_id = 101)||seq_record_id.nextval AS INT) as RECORD_ID FROM AER_ID_TEMP"
but still encountering a error "ORA-02287 sequence number not allowed"
create or replace PACKAGE TABLE_PKG1 AS PROCEDURE make(table_name VARCHAR2, colspec varchar2); PROCEDURE add_row(table_name VARCHAR2, value_s VARCHAR2,cols VARCHAR2); END TABLE_PKG1 CREATE OR REPLACE PACKAGE BODY TABLE_PKG1 AS
[code]....
The package and the package body compiles successfully. When I add table-
BEGIN TABLE_PKG1.make('EMP_new','country varchar2(20), currency varchar2(20)'); END;
The table is created successfully.However when I invoke the package using the following anonymous block for adding the row-
SET SERVEROUTPUT ON BEGIN TABLE_PKG1.add_row('EMP_new','"India","Rs"','Country,Currency'); END;
The it generates the following error-
Error report:
ORA-00984: column not allowed here ORA-06512: at "SCOTT.TABLE_PKG1", line 14 ORA-06512: at line 2 00984. 00000 - "column not allowed here"
Still today user can enter any special character value in stud_name like L'Pradhan . I want to put a restriction on stud_name so that user can enter only alphabetical value into stud_name column.
i create form,when i press F11, it displays this error:Frm-41009 function key not allowed ,press ctrl+k for list of valid values.And i can't enter value into fields to query records.
I've got the following SQL script that I'm trying to run against a database: -
CREATE TABLE jdp_parameters ( id NUMBER(9) CONSTRAINT pk_jdp_parameters PRIMARY KEY CONSTRAINT ck_jdp_parameters_id CHECK (id = 0),
[code]....
On running it I get the following errors: -
pricelevel_id NUMBER(9) CONSTRAINT fk_jdp_pricelevel_id * ERROR at line 10: ORA-02253: constraint specification not allowed here Sequence created. REFERENCES jdp_parameters(id)
I am trying to create one Materialized view from a Select Statement and I am getting the following error
SQL Error: ORA-01723: zero-length columns are not allowed
I have checked the table and found nothing like varchar2(0) or char(0) , but the column in my select statment may get a Null value. Will it result in error like above ?
Normal View : I have created this because I think a materialized view would not be created if we have a Sub query.
CREATE OR REPLACE VIEW mx_test AS SELECT t."EXTN_SERVICE_REQUEST_NO", t."EXTN_SERVICE_TYPE",
[Code]....
Materialized View :
CREATE materialized VIEW mx_ashok AS SELECT * FROM mx_test
Error :
Error starting at line 1 in command: CREATE materialized VIEW mx_ashok AS SELECT * FROM mx_test Error at Command Line:3 Column:16 Error report: SQL Error: ORA-01723: zero-length columns are not allowed 01723. 00000 - "zero-length columns are not allowed" *Cause: *Action:
Do I need to use NVLs for all columns which may result in NULL value ?
create sequence seq minvalue 1 maxvalue 99999 start with 1 increment by 1; create table t ( id number, b date); insert into t values (seq.nextval,sysdate); update t set b=sysdate-1 where id=seq.currval; update t set b=sysdate-1 where id=seq.currval * ERROR at line 1: ORA-02287: sequence number not allowed here
1. we have to use shared server mode with Xa transactions, if not we get this error:
QUOTE ORA-24777: use of non-migratable database link not allowed
Cause: The transaction, which needs to be migratable between sessions, tried to access a remote database from a non-multi threaded server process
2. The SGA and PGA_AGGREGATE targets are limitted to 4GB as an Oracle consultant's recommendation [I don't know the reason, but I have been informed that it is recommended so I don't dare to increase it]
Taking into consideration the above points and the below information, what should I do then... I'm not willling to change the workarea size policy to manual as I'm affraid of the consequences...
QUOTE MTS becomes downright dangerous when Automatic Shared Memory Management (ASMM) or Automatic Memory Management (AMM) is in place.
When you use MTS and AMM (or ASMM) together, PL/SQL programs that try to create large collections can effectively consume all available server memory with disastrous consequences . .
AMM allocates virtually all memory on the system to the large pool in order to accommodate the PL/SQL memory request. First it consumes the buffer cache, then it reduces the PGA_AGGREGATE_TARGET - all the way to zero!"
Our product runs a lot of stored procedures in the background to do various things. These stored procedures obviously include a ton of select statements, insert statements, etc. Some of them get pretty complex. Once in a while, we run across the following error: "ORA-03127: no new operations allowed until the active operation ends(3127)." Once this happens, pretty much everything breaks with this error for a while. Eventually (LOOOONG time), this error "resolves itself" and things start working again. Conceptually, I understand that there seems to be some blocking operation on the DB, but because we run a LOT of stored procedures and SQL statements, it's extremely difficult to pin this down.
i have multi data block filed. and checkbox field which based on control block...My task is when i check checkbox only one field should enabled and my mouse goes to that field
My item field based on data block and checkbox based on control block,while i checked chkbox1 , only item31 on that current record should be enabled and i changed value only on that field
when i checked chkbox1 , my cursor goes to item31...not item32
i have a master detail form, In Master block we have one field cheque amount and in Detail block we have field receiveable amount invoice wise. if company paid us a cheque amount we will enter this amount in Master block field Cheque amount and in detail block there will be invoice wise receivable amounts. i want to distribute the cheque amount in detail block invoice wise for example
Cheque amount in master block = 291
Invoice wise receiveable amount is as follows
Invoice No , receivable amount , Received amount
10, 196 , 0 20 , 95 , 0 30 , 54 , 0
Result should be as follows:
Invoice No , receivable amount , Received amount
10, 196, 196 20 , 95 , 95 30 , 54, 0
Received amount field should be distributed according to the receivable amount when recevied amount = cheque amount then remaining will be 0.
emp_id number, name varchar2(30), from_dt date, remarks varchar2(60)
insert into MY_TAB values (1,'TOM','01-JAN-13', 'some remark'); insert into MY_TAB values (1,'TOM','02-JAN-13', 'some remark'); insert into MY_TAB values (2,'TOM','01-JAN-13', 'some remark'); insert into MY_TAB values (3,'TOM','01-JAN-13', 'some remark'); insert into MY_TAB values (4,'TOM','01-JAN-13', 'some remark'); insert into MY_TAB values (4,'TOM','02-JAN-13', 'some remark');
How do I ensure that when a user tries to insert record with emp_id as 1, then he should only be allowed to enter another from_dt but the value in the name column have to be the same as in the previous row of emp_id 1.
insert into MY_TAB values (1,'TOOM','03-JAN-13') --shld not be allowed.
I have a function that returns the situation for one month for some database. I need to implement it in some report medium for one year. The one year function works ok.
My problem is when trying to make another function that runs the monthly function 12 times and that error is "PLS-00653: aggregate/table functions are not allowed in PL/SQL scope".am trying to get around some restrictions and somehow until this part things seem to be ok.
I tried to use a union with 12 blocks but it works very slow in the reporting environment and now i want to try to make another function that runs another function 12 times depending on the parameter.
here is the code (there might be some name misuse since i had to change the names of the original code -
CREATE OR REPLACE FUNCTION anual(monthh in varchar2, year IN VARCHAR2) return anual_REP_var PIPELINED is BR anual_REP:=anual_REP(NULL,NULL, NULL,NULL); contor INT(2);
SQL> insert into t51 values (100000000000000000.00000); insert into t51 values (100000000000000000.00000) * ERROR at line 1: ORA-01438: value larger than specified precision allowed for this column
iam opening a form,inserting data in empty columns of an existing record and with out saving iam calling another form.Inserting and updating data in the called form and again coming back to old form and then saving the form.
while doing this, when i am trying to save on the called form its giving an error
FRM-40403 CALLING FORM HAS UNAPPLIED CHANGES,SAVE NOT ALLOWED.
I have a multi record control block (basically a text item displaying 6 records) where user enters values and I want to process the values using pre-insert trigger.
I want to read value in each record and then do some tasks using a pre-insert trigger before I commit the values. To navigate between the records I was using first_record, next_record, clear_record built-ins but it gives errors like "40737-illegalrestricted procedure next_record in pre-insert trigger".
