Frm 40505 ошибка

Рис.
4.1.
Ограниченная и неограниченная выборка

Рис.
4.2.
Окно Display Error

Error in Updating Recrods

Hi All,
I have made ODBC Connection for SQL Server7. When i try to update or delete any record from Forms 6i then i got following errors.
FRM-40512:Oracle error unable to issue SAVEPOINT command.
OCA-30021:error preparing/executing SQL statement.
Its emergency plz help me. 

There is a form-level property called Savepoint Mode (turned on by default) that you can turn off to run against non-Oracle databases. This property must be set programmatically:
SET_FORM_PROPERTY('formname',SAVEPOINT_MODE, PROPERTY_FALSE);

Hi Pamela
First thanks to reply me.
I changed the savepoint mode. But OCA-30021 is still occuring and when i update any value by quering record then following message occurs
"The record has been updated by another user pleas query and retry"
Best regards
Hassan 

Is any code that you've written being executed when you update? If so, see if this reproduces on a simple form with no code. If it doesn't, perhaps there is something in the code that SQL Server doesn't understand, such as directly calling a stored procedure or using the NVL function. Using EXEC_SQL may resolve such problems -- see bug 1150980 and Note 196941.1 for a couple of examples. 

Hi all.
I didnt write any code yet in my form.
Only create a tabular form without any coding.
And there is another problem when i execute query direcly with F8 then all columns values are displayed properly. But when i use F7 and enter any valid value for a particular column then ##### or ***** print for some columns.
Please Help me.

need help with forms

hello,
i am using forms 9i. trying to access database as well as insert and update at runtime. i am getting this error at run time. i tried in google but no details. can any one help me with this.
FRM 40735 WHEN-BUTTON-PRESSED trigger raised unhandled exception ORA-01400
thank in advance.

There is an error in the PL/SQL code within your WHEN-BUTTON-PRESSED trigger.
Without the code, we can't do anything for you. 

The error indicated that one or more required fields is NULL. This could be because Forms is trying to explicitly load a NULL into the field (e.g., the user left the field blank or the item is not visible on the form) or because the data block doesn't contain the field at all, so the insert statement that Forms creates doesn't refer to the field, in which case Oracle inserts an implicit NULL.
Hope this helps. 

thanks every one. yes you were right. there was a small mistake in my tables and code. it really helped me find the bug.
thanks a lot again.

standard.commit

Hi all,
I have header info and under this I have a tab canvas. I have separate buttons save/restore in the header and the tab pages.
After entering new values in the header [actually i am using execute_query to fetch header info from the table. some fields of this table are there in the tab pages], if I give standard.commit, record is not getting saved. what should I give?
If I use commit_form, runtime failure occurs and url is getting closed.
any help please
Regards
Priya 

try doing commit_form or do_key('commit'); 

No use. not working 

well have you tried using post to test whether the record is saved or not? 

Hopefully ur Datablock is
1) a database block .
2) has its parameter update_allowed as yes.
Rest of the parameters of block & item are set properly. 

I have missed one <Not null> column in Insert statement. That was causing this problem. but need to know whey this error was not thrown? If I use standard.commit, nothing happens.
If I use commit_form form url is shutting down..
Regards
Priya 

Hi ,
Try debuging your form and check what values have at each moment the variables , e.t.c...
Simon 

I have missed one <Not null> column in Insert statement. That was causing this >problem. but need to know whey this error was not thrown?If there is a problem while inserting through database block u wont be getting the error on screen as on-insert trigger is triggered implicitly .If u want to trap the error overload on-insert & explicitly write insertion code , trapping the exception .
Declare
<variable>
Begin
Insert into <table_name>
exception
when other then
message(sqlerrm);pause;
raise form_trigger_failure;
end; 

hello,
I have given explicit insert statement and exception handlers. 

What was the result when u used explicit insert statement & handled exception in
(using sqlerrm) on-insert trigger ?U should be able to trap the Database error like
cannot add null value in not null columns. 

You would have been able to see the error by going to the show errors menu. 

Hi,
C.... When I gave explicit statements, I have not received any error msg [pl go thro one of my previous post on this topic]. This is my doubt why I have not received DB error messages.
Anyway, the problem is solved now after giving proper value for that NOT NULL column.
thanks to everybody for ur inputs.
Regards
Priya 

Hi,
Ok so far from ur discussion it seems that,
1. Ur data block is not a data base block,
2. The field on form that maps to the not null field in table is not made mandatory on form,
3. You are not handling any exception handling for explicit Insert that u r executing
Usually you should make a filed MANDATORY that maps on to NOT NULL field in table, this avoids the unnecessary transactions from oracle forms to oracle database in return from your application server to databse server. This will avoid from user being directed till database and then told that he has entered something wrong or incomplete.
You should handle exception for any DML statement that you are using on form... if you would have handled this exception then there wont have been any problem you would have come to know immediately where the problem is.
Final and most important - a direct answer to ur final question -
It seems that you have set system.message_level some where in your code and forgot to reset it and thats why you are not getting the error you are talking about.
Thanks & Regards,
Avinash,
Pune.
India

Cannot query records here

Help, ... Is there a way to look up the meaning of Message such as "FRM-40360: Cannot query records here." ??? 

You can use the online help documentation to search from the error number.
FRM-40360: Cannot query records here.
Cause:  You attempted to query a block that does not allow queries.
Action:  Do not attempt to query this block.
You may be trying to perform a query on a non-database block.
You may have a database block that doesn't allow queries.
You don't have at least one item in a database block that allows queries. 

So, ... assuming that ... I "don't have at least one item in a database block that allows queries." How can I trap that error? Please tell me specifically ... for example ...
Begin
... Select Statement ...
Exception
When No_Data_Found Then Null; ---This already there but ... does not work
End;
Thanks. 

You are getting a Forms error message from a data block. It is not an error from your programmed pl/sql procedure.
To trap Forms errors, use the on-error tigger. There are quite a few examples in this forum if you search for "on-error", or look at the example in the online help of Forms builder. 

the error comes definitely from your EXECUTE_QUERY on the block.
Press SHIFT-F1, when you see the error. Then you get detailled information about the error 

create key-entqry and key-exeqry triggers for that block, set the execution hierarchy to 'override' and code any message you want in the trigger. 

what kind of problem should the KEY-triggers solve ? 

If the users are prevented from executing a query in that block then they will not see the error message. I'm assuming this error comes from user action rather than a command coded into the form, which would be simple to solve. 

ok, that's a way.
But: I think, that he needs a query else he wouldn't wrote so detailled above it. So he has to give us a hint with the SHIFT-F1 after the query-error 

I didn't read the question properly :-(

Need Code for ‘When-Button-Pressed’ Trigger in Oracle Form

Greetings All,
I need programming code as soon as possible for 'When-Button-Pressed' Trigger for a data block push button item in Oracle Form to allow users to return to a previous search result screen without having to enter parameters and redo search again.
Could you please share with me the programming code for a trigger when a push button is pressed to allow users to return to a previous screen with 1.) parameters set and 2.) query search results saved, if any, in a previous screen?
BTW, we are still on Oracle Form 6i with Database 10G.
Thanks in advance for any/all the help you might assist on this,
Jinlan
-- 

Hi,
You can use GET_BLOCK_PROPERTY('block_name', LAST_QUERY). This function returns the SQL statement of the last query in the specified block.
Hope can help
Mariella 

Mariella,
Thanks for the information. I forgot to mention the progrmming code that the search inquiry and parameters used for the search are in a form that is separated from the calling form. GET_BLOCK_PROPERTY('block_name', LAST_QUERY) probably works within one form, but I am not sure if it will work between forms.
Thanks again,
Jinlan
-- 

You can use a global variabile to save the result of GET_BLOCK_PROPERTY('block_name', LAST_QUERY), something like :global.lst_qry_frmx := GET_BLOCK_PROPERTY('block_name', LAST_QUERY);
Global variable works between forms, the only way to erase a global variable is calling the erase built-in. You can test if a global variable is been created using the default_value buil-in: if the global.variable exists it do nothing, if it doesn't exists the procedure create it with the default value. 

Mariella,
Thanks for the great information, and I will try your suggestion as soon as I get a chance. Sorry for the late response on this as I have been assigned to and need to complete a higher priority task since last Friday, 05/08/09. :(
p.s. I will share my code here in the near future (i.e. if I make it to work.) :)
Thanks again,
Jinlan
-- 

Greetings All,
It turns out that my coding problems to allow users to return to the form with a previous search result and parameters saved from a different form were resolved in the following thread (posted on Jul 24, 2009 1:18 PM):
Problem with Call_Form to Return to the Previous Screen with Saved Results
The solution is to add the following programming code:
CALL_FORM('<called_Form_name>', HIDE,NO_REPLACE, NO_QUERY_ONLY) in the "When-Button-Pressed" Trigger for the 'PB_Return_2_Called_form' item ; in Form B
EXIT_FORM in the When-Button-Pressed Trigger for the 'PB_Return_2_Calling_Form' item
Thanks,
Jinlan
--

Similar Messages

• FRM-40505  Oracle Error: Unable to perform query(URGENT)

  Hi I developed a form with a control_block and table_block(based on table)
  in same Canvas.
  Based on values on control_block and pressing Find button detail block will be queried.
  Control_block ->
  textitem name «payment_type» char type
  text item name «class_code » char type
  push button «find»
  base table: —> payment_terms(termid,payment_type,class_code,other colums)
  table_block is based on above table
  Now I have written when-button-pressed trigger on find button..
  declare
  l_search varchar2(100);     
  BEGIN
  l_search := ‘payment_type=’|| :control_block .payment_type||’ AND class_code=’||:control_block .class_code ;
  SET_BLOCK_PROPERTY(‘table_block’,DEFAULT_WHERE,l_search);
  go_block(‘table_block’);
  EXECUTE_QUERY;
  EXCEPTION
       when others then
       null;
  END;
  I am getting
  FRM-40505 Oracle Error: Unable to perform query
  please help..

  You don’t need to build the default_where at run time. Just hard-code the WHERE Clause property as:
      column_x = :PARAMETER.X
  But, if for some compelling reason, you MUST do it at run time this should work:
      Set_block_property(‘MYBLOCK’,Default_where,
          ‘COLUMN_X=:PARAMETER.X’);
  Note that there are NO quotes except for first and last. If you get some sort of error when you query, you should actually see :Parameter.X replaced with :1 when you do Help, Display Error.

• FRM-40505 ORACLE error: unable to perform query

  I have a block based on a table but when I issue an execute_query in «WHEN_NEW_BLOCK_INSTANCE» I get the above error.
  I know can access this table because immediately after this I select some fields from the table into variables and display them. However when I try and assign them to the fields in the block I get «FRM:41051 You cannot create records here.»
  Can you please help.

  If I set «Insert Allowed» and Update Allowed» to yes I can assign values I’ve selected form the table to the fields but the execute_query still fails.
  By the way, I have a where clause in the property block to limit the number of rows brought back to 1

• [SOLVED] FRM-40508:ORACLE error: unable to INSERT record

  Hi all,
  I have migrated this form from 4.5 to 10g (Version 10.1.2.0.2 ). This form inserts a record into the database table when all the fields in the form are filled and a button Save is presed.
  At the time when I press the Save button, I get this error. FRM-40508:ORACLE error: unable to INSERT record
  So I went on to see the «Display Error» from help and found to be the database error, inserting into a table.
  The error message is ORA-00932: inconsistent datatypes: expected DATE got NUMBER
  The form where I press Save button has 3 date fields and I checked the properties of them and they are Date only.
  I also generated to object list report and tried to find some answer, but no use.
  Please help me in debugging this form.
  Edited by: Charan on Aug 18, 2011 4:05 PM

  I think you need to get a description of the table and compare all the «database» columns in the form with the ones in the database table to see that the types match. Somewhere there’s a mismatch. Also check the block(s) «query data source columns» and see if there’s any
  columns in there that the type does not match the table. (check the sizes of things too while you’re at it.)

• Frm-40502: oracle error: unable to read list of values

  Hi All,
  I am personalizing the assignment form, where we need to restrict the JOB LOV based on Organization value.
  In Forms Personalization we are creating the record group from query and attaching to Job field.
  The query is,
  SELECT DISTINCT j.NAME, DECODE (1, 2, 1, NULL) c_valid_job_flag,
  j.job_id job_id
  FROM per_jobs_v j
  WHERE j.business_group_id + 0 = :CTL_GLOBALS.BUSINESS_GROUP_ID
  AND j.date_from <= :CTL_GLOBALS.SESSION_DATE
  AND ( (j.date_to IS NULL)
  OR (j.date_to >= :CTL_GLOBALS.SESSION_DATE)
  ORDER BY j.NAME
  When we use this query, we are getting the error «frm-40502: oracle error: unable to read list of values»
  If i replace the bind variable with values we are not getting the error and its working fine.
  Replace query:
  SELECT DISTINCT j.NAME, DECODE (1, 2, 1, NULL) c_valid_job_flag,
  j.job_id job_id
  FROM per_jobs_v j
  WHERE j.business_group_id + 0 = 202
  AND j.date_from <= TRUNC(SYSDATE)
  AND ( (j.date_to IS NULL)
  OR (j.date_to >= TRUNC(SYSDATE))
  ORDER BY j.NAME
  how to use bind variables (Block.field) here? We are getting this error only when using the bind variable in the query.
  Please share your ideas.
  Thanks.

  Hi;
  What is your EBS version? There are 96 docs avaliable about similar error message. I suggest use metalink for your issue
  You can also check:
  FRM-40502: Oracle Error: Unable To Read List Of Values [ID 1161404.1]
  Frm-40502: Oracle Error: Unable To Read List Of Values. [ID 351931.1]
  FRM 40502: Oracle Error:Unable to Read List of Values [ID 179162.1]
  Regard
  Helios

• Forms Personalization — FRM-40502: ORACLE error: unable to read list of val

  Hi,
  I am using Forms Personalization to create an LOV using builtin. In the Builtin, I have put the sql query under create record group from query. In the query, when I hardcode a value, in a where condition, the LOV works fine but when I replace the hardcoded value with :block.field value, it gives the error, FRM-40502: ORACLE error: unable to read list of values. Below is the part of the sql query where I am facing this issue.
  «WHERE related_customer_id = :ORDER.INVOICE_TO_CUSTOMER_ID»
  Please get back to me if anyone has faced similar problem.
  Thanks all in advance,
  Regards,
  AN

  Please post a new thread for each issue. Tacking onto a 2 year old post is generally a bad idea.
  Forms personalization is a feature in Oracle E+Biz Suite and as such you should post your question in this forum:
  OA Framework

• About the FRM-40508 ORACLE ERROR, Unable to insert’

  Dear Friends:
  I use the Toolbar button to insert one record — do_key(‘CREATE_RECORD’) in the Toolbar_actions procedure, and then, input the value for this record, and then, click the «Save» button — commit_form; in the when_button_pressed trigger. There will be the problem FRM-40508 ORACLE ERROR, Unable to insert’ .
  Please kindly help to fix it………..

  Hi,
  See…just for a test, please give whatever u input in the form, give the same in the sql query
  like
  insert into tablename etc in sql builder and see what happens,
  This error will come,
  if input value does not meet the datatype in the table or
  if u fail to input value for not null column or
  access rights pbm(check whether you have rights to insert in to the table)
  Regards
  Priya

• «FRM-40501: Oracle error: Unable to Reserve Record For Update or Delete»

  «FRM-40501: Oracle error: Unable to Reserve Record For Update or Delete»
  as I can unblock a session in the graphical surroundings of administration of the BD 10g of Oracle

  From experience with this problem since the blocked customer form has been coded not to wait for the updating session to complete then there is likely no waiter on the system now so you cannot find the blocking session.
  What you need to do is determine what row the Form was going after then using SQLPlus issue an update on the row. If the blocking session has not yet committed then this update will wait. Now if you look for blocking sessions you will be able to find it and make a determination if the session should be killed or if someone needs to call the user and ask he or she to flip through their screens and get out of the blocking screen.
  Applications screens written not to wait on currently being updated data need to also be written to expose the information necessary to identify the row(s) in question.
  HTH — Mark D Powell —

• FRM-40509: ORACLE error: unable to UPDATE record.

  FRM-40509: ORACLE error: unable to UPDATE record.
  what can i do?

  There will always be another error which tells you what the actual problem on the database is. Use the Display message key, or message out DBMS_ERROR_TEXT

• FRM-40506: Oracle error: unable to check for record uniqueness

  Hello everyone
  I have an emergency problom. the context is:
  I have a master -detail structure in a form. first, my detail is a tabulation view, so it has severals records. Then, when i try to commit the form ii received the message <<<FRM-40506: Oracle error: unable to check for record uniqueness>>.
  i made a test by changing the detail block presentation to Form. and i could save because my detail has one record.
  Could someone has any idea how can I save my master-detail form with severals records in my detail.
  Master
  [code_p] [non_P]
  Detail
  [code_p] [product1]
  [code_p] [product2]
  [code_p] product3]
  [code_p] [productn]
  thanks!

  Error Cause:
  Processing error encountered while checking a record’s primary key items for uniqueness. The table associated with the current block of the form does not exist, or you do not have authority to access the table.
  You can review the error documentation:
  http://www.errorpro.com/oracleerrors/oraerror.php?level1=Oracle&send=Send&ecode=FRM-40506&Submit=Search

• FRM-40501 ORACLE error: unable to reserve record for update or delete when

  Hello.
  I have two tab pages and one item on each page. Second tab page item, is mirror item of the first one. I use synchronize property on the mirrored one.
  When i try to update mirrored one i get that error: FRM-40501 ORACLE error: unable to reserve record for update or delete when.
  How can i solve that?
  Thanks

  hi dejan,
  the error u r getting means that the record cannot be locked. This is ussually caused if u had locked the record from somewhere else (u or another user) or when forms cannot find corresponding column to the base table to lock the record.. U probably r facing the second situation. I’m not sure that forms can commit a change to an item that is synchronized cause synchronized items ignore their base table attribute and just copy their value from the other item.
  Why don’t u try Copy Value from Item property using <block_name>.<item_name> syntax and your item will have a value copied from the other item but u will have no problem with the db transactions. I suppose..
  hope this helps,
  teo

• FRM-40512 ORACLE ERROR UNABLE TO ISSUE SAVE POINT COMAND

  Dear Friends
  I am getting this FRM-99999 ERRORE 408 OCCURRED FRM-40655 SQL ERROR FORCED ROLLBACK CLEAR FROM AND RE-ENTER TRANACTION.
  FRM-40512 ORACLE ERROR UNABLE TO ISSUE SAVE POINT COMAND.
  I am calling the form using the following script :
  :global.command_line := ‘C:SALESNEW_ITEMS.FMX’ ;
  CALL_FORM(:global.command_line);
  go_block(‘COP_ORDER_DETAILS’);
  go_record(:global.l_n_curr_rec2);
  :COP_ORDER_DETAILS.ITEM_CODE :=:GLOBAL.ITEM_CODE_VAR;
  :COP_ORDER_DETAILS.COLOR := :GLOBAL.COLOR_CODE ;
  :COP_ORDER_DETAILS.SHAPE_CODE := :GLOBAL.SHAPE_CODE ;
  :COP_ORDER_DETAILS.DELIVERY_QUANTITY := :GLOBAL.QTY ;
  :COP_ORDER_DETAILS.VOLUME := :GLOBAL.VOLUME;
  It works fine for one record, when I try to create another record by calling the called form it gives the above messages.
  Waiting for your valuable replay.
  Best regards
  Jamil

  Hi!
  May you try to open your form, compile all pl/sql (shift-ctrl-k) and compile the form (ctrl-t).
  Take a look at triggers like pre-insert, on-insert, post-insert and key-commit.
  What’s happen there?
  I don’t know, if the default username feature in 10g is still supported.
  So may the logon ( ‘/’ …) fails and for this the login screen os shown.
  Regards

• FRM-40509: ORACLE error : Unable to update the record.

  Hi,
  I am having the following code in delete button.
  declare
       v_button  NUMBER;
  begin
    IF :CHNG_CNTRL_JOB_DTLS.SENT_DATE IS NULL THEN
                 v_button := fn_display_warning_alert( ‘Do you want to delete the version ?’);
                 IF ( v_button = alert_button1 )
                      THEN
                        message(‘before insert’);
                        insert into chng_cntrl_job_dtls_log
                        select * from chng_cntrl_job_dtls
                        where job_name = :CHNG_CNTRL_JOB_DTLS.job_name
                        and job_version_no = :CHNG_CNTRL_JOB_DTLS.job_version_no
                        and sent_date is null;
                        message(‘before delete’);
                        delete from CHNG_CNTRL_JOB_DTLS
                        where job_name = :CHNG_CNTRL_JOB_DTLS.job_name
                        and job_version_no = :CHNG_CNTRL_JOB_DTLS.job_version_no
                        and sent_date is null;
                        message(‘before commit’);
                        silent_commit;
                        go_item(‘CHNG_CNTRL_JOB_DTLS.JOB_NAME’);
                        P_DELETE_SET_PROPERTY;
                        clear_form;
                 ELSIF ( v_button = alert_button2 )
                      THEN
                        null;
              END IF;
       ELSE
              p_display_alert(‘Version ‘||:CHNG_CNTRL_JOB_DTLS.JOB_VERSION_NO||’ for the job name ‘||:CHNG_CNTRL_JOB_DTLS.JOB_NAME||’ cannot be deleted.’,’I’);
    END IF;
       exception
            when others then
            message (‘Exception in delete version button’);
            end;when i am trying to save it says » *FRM-40509: ORACLE error : Unable to update the record*.» .
  i am getting message till before commit.
  i am not able to check the error message in help as it is diabled in our form builder.
  I have checked the privileges also. they are fine.
  Please advice.
  Edited by: Sudhir on Dec 7, 2010 12:26 PM

  This error does not come from your procedure code, but from Forms itself. If you are in a database block, change something, and do a commit (either via a Forms key or in your own procedure), Forms commits the changes. For some reason this is not possible. You can see the database error via the Display Error key (often Shift-F1).
  I see in the OP that this key is disabled. Change the on-error code then:
  begin
     message(dbms_error_code||’-‘||dbms_error_text);
     raise form_trigger_failure;     
  end;     Edited by: InoL on Dec 7, 2010 8:50 AM

• Getting error Unable to perform transaction on the record.

  Hi,
  My requirement is to implement the custom attachment, and to store the data into custom lob table.
  my custom table structure is similer to that of standard fnd_lobs table and have inserted the data through EO based VO.
  Structure of custom table
  CREATE TABLE XXAPL.XXAPL_LOBS
  ATTACHMENT_ID NUMBER NOT NULL,
  FILE_NAME VARCHAR2(256 BYTE),
  FILE_CONTENT_TYPE VARCHAR2(256 BYTE) NOT NULL,
  FILE_DATA BLOB,
  UPLOAD_DATE DATE,
  EXPIRATION_DATE DATE,
  PROGRAM_NAME VARCHAR2(32 BYTE),
  PROGRAM_TAG VARCHAR2(32 BYTE),
  LANGUAGE VARCHAR2(4 BYTE) DEFAULT ( userenv ( ‘LANG’) ),
  ORACLE_CHARSET VARCHAR2(30 BYTE) DEFAULT ( substr ( userenv ( ‘LANGUAGE’) , instr ( userenv ( ‘LANGUAGE’) , ‘.’) +1 ) ),
  FILE_FORMAT VARCHAR2(10 BYTE) NOT NULL
  i have created a simple messegefileupload and submit button on my custom page and written below code on CO:
  Process Request Code:
  if(!pageContext.isBackNavigationFired(false))
  TransactionUnitHelper.startTransactionUnit(pageContext, «AttachmentCreateTxn»);
  if(!pageContext.isFormSubmission()){
  System.out.println(«In ProcessRequest of AplAttachmentCO»);
  am.invokeMethod(«initAplAttachment»);
  else
  if(!TransactionUnitHelper.isTransactionUnitInProgress(pageContext, «AttachmentCreateTxn», true))
  OADialogPage dialogPage = new OADialogPage(NAVIGATION_ERROR);
  pageContext.redirectToDialogPage(dialogPage);
  ProcessFormRequest Code:
  if (pageContext.getParameter(«Upload») != null)
  DataObject fileUploadData = (DataObject)pageContext.getNamedDataObject(«FileItem»);
  String strFileName = null;
  strFileName = pageContext.getParameter(«FileItem»);
  if(strFileName == null || «».equals(strFileName))
  throw new OAException(«Please select a File for upload»);
  fileName = strFileName;
  contentType = (String)fileUploadData.selectValue(null, «UPLOAD_FILE_MIME_TYPE»);
  BlobDomain uploadedByteStream = (BlobDomain)fileUploadData.selectValue(null, fileName);
  String strItemDescr = pageContext.getParameter(«ItemDesc»);
  OAFormValueBean bean = (OAFormValueBean)webBean.findIndexedChildRecursive(«AttachmentId»);
  String strAttachId = (String)bean.getValue(pageContext);
  System.out.println(«Attachment Id:» +strAttachId);
  int aInt = Integer.parseInt(strAttachId);
  Number numAttachId = new Number(aInt);
  Serializable[] methodParams = {fileName, contentType , uploadedByteStream , strItemDescr , numAttachId};
  Class[] methodParamTypes = {fileName.getClass(), contentType.getClass() , uploadedByteStream.getClass() , strItemDescr.getClass() , numAttachId.getClass()};
  am.invokeMethod(«setUploadFileRowData», methodParams, methodParamTypes);
  am.invokeMethod(«apply»);
  System.out.println(«Records committed in lobs table»);
  if (pageContext.getParameter(«AddAnother») != null)
  pageContext.forwardImmediatelyToCurrentPage(null,
  true, // retain AM
  OAWebBeanConstants.ADD_BREAD_CRUMB_YES);
  if (pageContext.getParameter(«cancel») != null)
  am.invokeMethod(«rollbackShipment»);
  TransactionUnitHelper.endTransactionUnit(pageContext, «AttachmentCreateTxn»);
  Code in AM:
  public void apply(){
  getTransaction().commit();
  public void initAplAttachment() {
  OAViewObject lobsvo = (OAViewObject)getAplLobsAttachVO1();
  if (!lobsvo.isPreparedForExecution())
  lobsvo.executeQuery();
  Row row = lobsvo.createRow();
  lobsvo.insertRow(row);
  row.setNewRowState(Row.STATUS_INITIALIZED);
  public void setUploadFileRowData(String fName, String fContentType, BlobDomain fileData , String fItemDescr , Number fAttachId)
  AplLobsAttachVOImpl VOImpl = (AplLobsAttachVOImpl)getAplLobsAttachVO1();
  System.out.println(«In setUploadFileRowData method»);
  System.out.println(«In setUploadFileRowData method fAttachId: «+fAttachId);
  System.out.println(«In setUploadFileRowData method fName: «+fName);
  System.out.println(«In setUploadFileRowData method fContentType: «+fContentType);
  RowSetIterator rowIter = VOImpl.createRowSetIterator(«rowIter»);
  while (rowIter.hasNext())
  AplLobsAttachVORowImpl viewRow = (AplLobsAttachVORowImpl)rowIter.next();
  viewRow.setFileContentType(fContentType);
  viewRow.setFileData(fileData);
  viewRow.setFileFormat(«IGNORE»);
  viewRow.setFileName(fName);
  rowIter.closeRowSetIterator();
  System.out.println(«setting on fndlobs done»);
  The attchemnt id is the sequence generated number, and its defaulting logic is written in EO
  public void create(AttributeList attributeList) {
  super.create(attributeList);
  OADBTransaction transaction = getOADBTransaction();
  Number attachmentId = transaction.getSequenceValue(«xxapl_po_ship_attch_s»);
  setAttachmentId(attachmentId);
  public void setAttachmentId(Number value) {
  System.out.println(«In ShipmentsEOImpl value::»+value);
  if (getAttachmentId() != null)
  System.out.println(«In AplLobsAttachEOImpl AttachmentId::»+(Number)getAttachmentId());
  throw new OAAttrValException(OAException.TYP_ENTITY_OBJECT,
  getEntityDef().getFullName(), // EO name
  getPrimaryKey(), // EO PK
  «AttachmentId», // Attribute Name
  value, // Attribute value
  «AK», // Message product short name
  «FWK_TBX_T_EMP_ID_NO_UPDATE»); // Message name
  if (value != null)
  // Attachment ID must be unique. To verify this, you must check both the
  // entity cache and the database. In this case, it’s appropriate
  // to use findByPrimaryKey() because you’re unlikely to get a match, and
  // and are therefore unlikely to pull a bunch of large objects into memory.
  // Note that findByPrimaryKey() is guaranteed to check all AplLobsAttachment.
  // First it checks the entity cache, then it checks the database.
  OADBTransaction transaction = getOADBTransaction();
  Object[] attachmentKey = {value};
  EntityDefImpl attachDefinition = AplLobsAttachEOImpl.getDefinitionObject();
  AplLobsAttachEOImpl attachment =
  (AplLobsAttachEOImpl)attachDefinition.findByPrimaryKey(transaction, new Key(attachmentKey));
  if (attachment != null)
  throw new OAAttrValException(OAException.TYP_ENTITY_OBJECT,
  getEntityDef().getFullName(), // EO name
  getPrimaryKey(), // EO PK
  «AttachmentId», // Attribute Name
  value, // Attribute value
  «AK», // Message product short name
  «FWK_TBX_T_EMP_ID_UNIQUE»); // Message name
  setAttributeInternal(ATTACHMENTID, value);
  Issue faced:
  When i run the page for the first time data gets inserted into custom table perfectly on clicking upload button,
  but when clicked on add another button on the same page (which basically redirects to the same upload page and increments the attachment id by 1)
  i am getting the below error:
  Error
  Unable to perform transaction on the record.
  Cause: The record contains stale data. The record has been modified by another user.
  Action: Cancel the transaction and re-query the record to get the new data.
  Have spent entire day to resolve this issue but no luck.
  Any help on this will be appreciated, let me know if i am going wrong anywhere.
  Thanks nd Regards
  Avinash

  Hi,
  After, inserting the values please re-execute the VO query.
  Also, try to redirect the page with no AM retension
  Thanks,
  Gaurav

• Exact fetch returns more than requested number of rows oracle error ; unable to find where exactly throwing error in the below code.

  hi i am receiving «exact fetch returns more than requested number of rows oracle error». but i am not able to locate the error in the below code. Any help would be appreciated!!!
  CREATE OR REPLACE PROCEDURE load_scene_collection_item (
  —pdname                                  VARCHAR2,
  — LOCATION TO ADD/CHANGE below!!!
  pITEM_TYPE_ID                      INTEGER,
  pSCENE_COLLECTION_ID        INTEGER,
  pCOLLECTION_ITEM_NAME     VARCHAR2,
  pCOLLECTION_ITEM_DESC      VARCHAR2,
  pDEFAULT_COORD_X      NUMBER,
  pDEFAULT_COORD_Y      NUMBER,
  pDEFAULT_COORD_Z      NUMBER,
  pDEFAULT_WIDTH            NUMBER,
  pDEFAULT_HEIGHT            NUMBER,
  pDEFAULT_ROTATION      INTEGER,
  pDEFAULT_ALPHA            INTEGER,
  pfname                                 VARCHAR2)    IS
  src_file BFILE;
  dst_file BLOB;
  lgh_file BINARY_INTEGER;
  BEGIN
  src_file := bfilename(‘BUSINESSBLOBSIMAGES’, pfname);
  — insert a NULL record to lock
  Insert into SCENE_COLLECTION_ITEM
     ( ITEM_TYPE_ID,
     SCENE_COLLECTION_ID,
     COLLECTION_ITEM_NAME,
     COLLECTION_ITEM_DESC,
      COLLECTION_ITEM_IMAGE,
     DEFAULT_COORD_X,
     DEFAULT_COORD_Y,
     DEFAULT_COORD_Z,
     DEFAULT_WIDTH,
     DEFAULT_HEIGHT,
     DEFAULT_ROTATION,
     DEFAULT_ALPHA,
      CREATE_USER,
      CREATE_DATE)
  Values    ( pITEM_TYPE_ID, pSCENE_COLLECTION_ID, pCOLLECTION_ITEM_NAME, pCOLLECTION_ITEM_DESC, EMPTY_BLOB(),   
     pDEFAULT_COORD_X,
     pDEFAULT_COORD_Y,
     pDEFAULT_COORD_Z,
     pDEFAULT_WIDTH,
     pDEFAULT_HEIGHT,
     pDEFAULT_ROTATION,
     pDEFAULT_ALPHA,  
  sys_context(‘USERENV’, ‘OS_USER’), 
  sys_extract_utc(systimestamp))
  RETURNING COLLECTION_ITEM_IMAGE INTO dst_file;
  — LOCATIONs(2) TO ADD/CHANGE above!!!
  — lock record
  SELECT COLLECTION_ITEM_IMAGE
  INTO dst_file
  FROM SCENE_COLLECTION_ITEM
  WHERE ITEM_TYPE_ID     = pITEM_TYPE_ID
  AND SCENE_COLLECTION_ID = pSCENE_COLLECTION_ID
  AND COLLECTION_ITEM_NAME= pCOLLECTION_ITEM_NAME
  AND COLLECTION_ITEM_DESC= pCOLLECTION_ITEM_DESC
  AND    pDEFAULT_COORD_X = DEFAULT_COORD_X
  AND   pDEFAULT_COORD_Y  = DEFAULT_COORD_Y
  AND   pDEFAULT_COORD_Z  = DEFAULT_COORD_Z
  AND   pDEFAULT_WIDTH    = DEFAULT_WIDTH
  AND   pDEFAULT_HEIGHT   = DEFAULT_HEIGHT
  AND   pDEFAULT_ROTATION = DEFAULT_ROTATION
  AND   pDEFAULT_ALPHA    = DEFAULT_ALPHA
  FOR UPDATE;
  — LOCATION TO ADD/CHANGE above!!!
  — open the file
  dbms_lob.fileopen(src_file, dbms_lob.file_readonly);
  — determine length
  lgh_file := dbms_lob.getlength(src_file);
  — read the file
  dbms_lob.loadfromfile(dst_file, src_file, lgh_file);
  — update the blob field
  UPDATE SCENE_COLLECTION_ITEM
  SET COLLECTION_ITEM_IMAGE = dst_file
  WHERE ITEM_TYPE_ID     = pITEM_TYPE_ID
  AND SCENE_COLLECTION_ID = pSCENE_COLLECTION_ID
  AND COLLECTION_ITEM_NAME= pCOLLECTION_ITEM_NAME
  AND COLLECTION_ITEM_DESC= pCOLLECTION_ITEM_DESC
  AND    pDEFAULT_COORD_X = DEFAULT_COORD_X
  AND   pDEFAULT_COORD_Y  = DEFAULT_COORD_Y
  AND   pDEFAULT_COORD_Z  = DEFAULT_COORD_Z
  AND   pDEFAULT_WIDTH    = DEFAULT_WIDTH
  AND   pDEFAULT_HEIGHT   = DEFAULT_HEIGHT
  AND   pDEFAULT_ROTATION = DEFAULT_ROTATION
  AND   pDEFAULT_ALPHA    = DEFAULT_ALPHA
  — LOCATION TO ADD/CHANGE above!!!
  — close file
  dbms_lob.fileclose(src_file);
  END load_scene_collection_item;
  Thanks in advance!!!!

  Hi PaulHorth,
  Thanks for the quick reply.
  Actually, i am getting  error while updating the records.
  below is the error message:
  Error starting at line 1 in command:
  exec load_scene_collection_item(3,15,’2 Lane 4way Stop’,’4 Way Stop Intersection with 2 lanes in each direction’,0,0,0,400,517,0,1,’2 Lane 4way Stop.PNG’);
  Error report:
  ORA-01422: exact fetch returns more than requested number of rows
  ORA-06512: at «DP_OWNER.LOAD_SCENE_COLLECTION_ITEM», line 55
  ORA-06512: at line 1
  01422. 00000 — «exact fetch returns more than requested number of rows»
  *Cause:    The number specified in exact fetch is less than the rows returned.
  *Action:   Rewrite the query or change number of rows requested