Forum Stats

  • 3,733,256 Users
  • 2,246,740 Discussions
  • 7,856,637 Comments

Discussions

ORA-00903 and Invalid Table Name errors

Eslam_Elbyaly
Eslam_Elbyaly Member Posts: 4,163 Silver Trophy
edited December 2014 in Forms

hi , i am using forms 6i ,

i created a form with a block based on a ( From Clause ) ,

the query is :

SELECT I.ITEM_ID , S.ITEM_ENG_NAME , S.ITEM_ARABIC_NAME , SUM(I.QUANTITY) QTY , I.MAX_TO_MIN_FACTOR , I.MIN_UNIT ,
I.PACKET_SELL_PRICE
FROM ITEM_DETAIL I , STOCK S WHERE I.ITEM_ID = S.ITEM_ID 
GROUP BY I.ITEM_ID , S.ITEM_ENG_NAME , S.ITEM_ARABIC_NAME , I.MAX_TO_MIN_FACTOR , I.MIN_UNIT , I.PACKET_SELL_PRICE
ORDER BY S.ITEM_ENG_NAME

the query runs successfully in sql developer ,

when i try to change a value on any updatable item , i get the above errors ( unable to reserve records for update or delete , and when display error , i get invalid table name ) .

?

Answers

  • Amatu Allah Neveen Ebrahim
    Amatu Allah Neveen Ebrahim Member Posts: 5,081 Gold Trophy
    edited November 2014

    Hi salute-Salem,

    Pls make sure that you are connected to the relevant database user  or the right database alias in forms; my guess you have more than user or ...  with the same tables' name.

    or check the spelling of the data block property > TABLE NAME (Correctly).


    Amatu Allah.

    Eslam_Elbyaly
  • Eslam_Elbyaly
    Eslam_Elbyaly Member Posts: 4,163 Silver Trophy

    thanks Amatu Allah , but all of this are ok .

    there is no problem with data block name , or the user i am connecting to .

  • Amatu Allah Neveen Ebrahim
    Amatu Allah Neveen Ebrahim Member Posts: 5,081 Gold Trophy

    What privileges the user have?

    may be insufficient privilege!

    Eslam_Elbyaly
  • Zlatko Sirotic
    Zlatko Sirotic Member Posts: 2,131 Gold Trophy

    In your module, Query Data Source Type is From clause query.

    But, what is DML Data Target Type - Table, Procedure or Trasactional Triggers?

    If you are using Trasactional Triggers, do you have ON-LOCK trigger?

    Regards,

    Zlatko

    Eslam_Elbyaly
  • Eslam_Elbyaly
    Eslam_Elbyaly Member Posts: 4,163 Silver Trophy
    what is DML Data Target Type
    

    it is the default value (Table) , i think the property is asking about the object on which the DML take place .


    i am working with tables , but the dml " data target name " property was empty , i tried assigning " item_detail , stock " to it , but vainly ,

    i tried assigning only " item_detail " , and also was vainly .


    am i missing something ? i used the same approach " From Clause " in another form , and the same thing happened ?


    i am using " Scott " Schema .


    Eslam_Elbyaly
  • Zlatko Sirotic
    Zlatko Sirotic Member Posts: 2,131 Gold Trophy

    If you want to update only one table, it's easy:

    - DML Data Target Type = Table

    - DML Data Target Name = table_name.

    If you want to update multiple tables, then you have to use Transactional Triggers (ON-% triggers)

    or (in my opinion better) database view and INSTEAD-OF database trigger.

    Then:

    - DML Data Target Type = Table (although actually view)

    - DML Data Target Name = view_name.

    Here is midnight.

    Good night,

    Zlatko

    Eslam_Elbyaly
  • Eslam_Elbyaly
    Eslam_Elbyaly Member Posts: 4,163 Silver Trophy

    i wrote a post , i do not know if it erased or what ,

    if i change the property of any item " database item " to "No" , then i can change the value in the item without seeing any error ,

    but off course , the "select statement " of the "From Clause " does not return something in the non-database items .

    how to handle this ?

  • Zlatko Sirotic
    Zlatko Sirotic Member Posts: 2,131 Gold Trophy

    Leave the properties "Database Item" and "Query Allowed" = "Yes",

    and change the properties "Insert Allowed" and "Update Allowed" to "No".

    Regards,

    Zlatko

  • Eslam_Elbyaly
    Eslam_Elbyaly Member Posts: 4,163 Silver Trophy
    Leave the properties "Database Item" and "Query Allowed" = "Yes",
    

    These are the default values for my items , and i did not change it .

    and change the properties "Insert Allowed" and "Update Allowed" to "No".
    

    i tried , and i faced " the field is protected against update " error ,  although i do not know how to set " Update Allowed " to "No" when i want to update the items .

  • HamidHelal
    HamidHelal Member Posts: 3,524 Gold Trophy

    fc.jpg

    Can you tell us, how you want to update data from your FORMS ?

    Hamid

    Eslam_Elbyaly
  • Eslam_Elbyaly
    Eslam_Elbyaly Member Posts: 4,163 Silver Trophy

    i do not want to update data directly , i want to create an On-Update trigger and write the update statement in it .

    is this conflicts with "From Clause " ? i do not know what do they mean when they said that "From Clause does not allow DML " ?

    do they mean that i can not change the value in the items ? or what ?

  • Zlatko Sirotic
    Zlatko Sirotic Member Posts: 2,131 Gold Trophy

    > i want to create an On-Update trigger and write the update statement in it.

    This is ok.

    > i do not know what do they mean when they said that "From Clause does not allow DML" ?

    This means that From Clause can not be DML Data Target Type.

    DML Data Target Type must be:

    - table, view (without INSTEAD-OF trigger) or procedure, if you are updating only one table;

    - transactional triggers, view (with INSTEAD-OF trigger) or procedure, if you are updating multiple tables.

    Regards,

    Zlatko

    Eslam_Elbyaly
  • Eslam_Elbyaly
    Eslam_Elbyaly Member Posts: 4,163 Silver Trophy

    is not there any solution ? did anybody try it ?

    i stopped developing the form , i can not deal with it , and it is not one form .

    please try it and tell me what is wrong .

    thank you

  • Amatu Allah Neveen Ebrahim
    Amatu Allah Neveen Ebrahim Member Posts: 5,081 Gold Trophy
    edited November 2014

    Hi

    Yesterday, i solved a problem like yours - same table error name- the reason was unexpected by me offcourse.

    He was watching an oracle form lesson, but the newbie developer didn't recognize what...

    FIRST:

    =====

    --Connect as System user ------------------------------------
    CONNECT <USER-NAME>/<PASSWORD>@&lt;DATABASE NAME>;
    --Create user query: ---------------------------
    CREATE USER <USER NAME> IDENTIFIED BY <PASSWORD>;
    --Provide roles -------------------
    GRANT CONNECT,RESOURCE,DBA TO <USER NAME>;
    
    

    SECOND

    =======

    --Provide privileges
    GRANT CREATE SESSION GRANT ANY PRIVILEGE TO <USER NAME>;
    GRANT UNLIMITED TABLESPACE TO <USER NAME>;
    --Provide access to tables.
    GRANT SELECT,UPDATE,INSERT ON <TABLE NAME> TO <USER NAME>;
    
    

    Finally:

    ======

    connect to the previous user created & Run the script
    
    

    Very well done...

    My guess is you did miss one step or more, pls start over once again with a new db user, etc.

    let's see what's up & never give up.

    Amatu Allah.

    Eslam_Elbyaly
  • Eslam_Elbyaly
    Eslam_Elbyaly Member Posts: 4,163 Silver Trophy

    unfortunately , i have a problem connecting as system user .

    2- is not DBA privilege provide these ones :

    GRANT SELECT,UPDATE,INSERT ON <TABLE NAME> TO <USER NAME>;

    connect privilege .

    lastly ,

    let's see what's up & never give up.
    

    is this poetry or what ?

  • Zlatko Sirotic
    Zlatko Sirotic Member Posts: 2,131 Gold Trophy

    I think that your problem has nothing with database rights.

    If you want to update multiple tables (in Forms), you must use ON-UPDATE transactional trigger (or database view and INSTEAD-OF trigger).

    Did you write ON-UPDATE trigger?

    Which tables you want to update? Show us their structure.

    Regards,

    Zlatko

    Eslam_Elbyaly
  • CraigB
    CraigB Member Posts: 5,746
    edited November 2014
    salute-Salem wrote:
    ...when i try to change a value on any updatable item , i get the above errors ( unable to reserve records for update or delete , and when display error , i get invalid table name ) .
    
    

    What changes are you making?  Please show us examples of the changes.  Since you are using a From Clause Query block, I think the error you are getting is self-explanatory.  Whatever the changes you are making to the core SQL is, they are not valid.  Again, please show us what changes you are making.

    One of the troubleshooting steps I like to use when working with From Clause Query blocks (FCQ) is to add a temporary multi-line text item to the form and display the value of the :SYSTEM.LAST_QUERY variable in this field.  This way I can look at the query that being executed by the Form and I can copy-n-paste it to TOAD/SQL Developer to run it and see what is wrong...


    Craig...

    Eslam_Elbyaly
  • Eslam_Elbyaly
    Eslam_Elbyaly Member Posts: 4,163 Silver Trophy

    What i mean with " changing " is :

    the field has data ( a number for example ) , i delete this number with the keyboard and try to write another one ,

    i can not delete it , or write anything .

  • Eslam_Elbyaly
    Eslam_Elbyaly Member Posts: 4,163 Silver Trophy

    Last thing i figured out is that " From Clause " is only for displaying data not for editing ,

    i do not know if i am totally right or not , but that what i faced till now after many tries .

    thanks so much . 

    Eslam_Elbyaly
This discussion has been closed.