Oracle Forms, Reports and Graphics

Forms 10g Tutorial
http://fdtool.free.fr/articles/tutoforms10g/tutoforms10g.htm

Change Button Lable Dynomically
https://forums.oracle.com/thread/2278106

Matrix form in forms 10g


FRM-40654 Record has been updated by another user. Re-query to see change
Solution...

I have faced this issue, also Google it but almost all possible solution was not applicable with my situation. Now i have solved my problem.

I have created a form with three different database blocks. When i update or insert any data i face the above error. Although there were no other users to perform DML statement on this form.
  1. I have changed all post-insert and post-update triggers of each block.
  2. I have placed all post-insert and post-update statements in pre-insert and pre-update triggers i.e. those statements which insert or update the other block data not database.
EXAMPLE
Suppose a form contains Block1, Block2, and Block3. All these blocks have master - detail relations.
Any statement in post-insert or post-update trigger of Block1 change the data of Block2 or Block3 not database then the form would raise frm-40654 error at the time of commit or updating the form so that statement should be moved in pre-insert or pre-update trigger instead of post-insert or post-update.

Hope my above experience would help others. If so please comments.

Serial Number in Oracle Forms 6i
Solution...

You can use :SYSTEM.TRIGGER_RECORD in POST-QUERY to assign its value to a display item in your block. If you are to create records in this block, then you will also need to put it in WHEN-CREATE-RECORD or some similar trigger depending on what scenario you need to cover.

There is :SYSTEM.CURSOR_RECORD (not :SYSTEM.CURRENT_RECORD) but it may not give you the required output
as it represents the record's current physical order in the block's list of records and you may not be navigating to all records.




Manipulating a stacked canvas pro grammatically 





Displaying a Stacked Canvas To programmatically display a stacked canvas in the 

window to which it is assigned, you can use the SHOW_VIEW built-in procedure. 

(Note that a stacked canvas' Visible property is set to No by default.)





Moving a Stacked Canvas To move the stacked canvas to a different location on 

the underlying content canvas, use the built-in procedure SET_VIEW_PROPERTY 

(which changes the canvas' Viewport Position property). 

Note that this will not change theregion of the canvas that is visible in the 

view.





Increasing the Dimensions of the View To increase the dimensions of a stacked 

canvas' view (so that more of the canvas is visible), use the built-in procedure

SETVIEW_PROPERTY. Note that increasing the of the view does not change the X,Y 

coordinates of its upper-left corneron the stacked canvas, nor does it change 

the position of the view itself relative to the underlying content canvas. 








Scrolling a Stacked Canvas To scroll a stacked canvas so that a different region

of the canvas is visible, use the built-in procedure SCROLL_VIEW (which moves 

the view relative to its canvas).





Examples 

/* Example 1: These 2 procedure calls both display the stacked
** canvas STACK_IT: */ 
SHOW_VIEW('stack_it'); 
SET_VIEW_PROPERTY('stack_it', visible, property_true); 

/* Example 2: This procedure call scrolls the view of the stacked 



** canvas STACK_ITto X,Y coordinates 12 and 0: */ 
SCROLL_VIEW('stack_it', 12, 0); 

/* Example 3: This procedure call changes the position of the 



** stacked canvas STACK_IT to X,Y coordinates 12 and 20 on the 



** underlying content canvas: */ 
SET_VIEW_PROPERTY('stack_it', display_position, 12, 20); 

/* Example 4: This procedure call increases the width of the view, 



** thereby making more of the canvas visible: */ 
SET_VIEW_PROPERTY('stack_it', width, 22);



HOW TO CODE THE AUTOMATIC TIME AND DATE IN FORMS 6i
You can use timer in forms 6i to display a clock.

Method:

Create a Display Item. Data Type is Char.

In When-New-Form-Instance Trigger, write this code:

DECLARE 
CurrTime TIMER;
OneSecond CONSTANT NUMBER := 1000;
BEGIN 
 CurrTime := CREATE_TIMER('CURRTIME',OneSecond,REPEAT);
END;

 In When-TImer-Expired Trigger, write this code

 DECLARE
ExpTimer VARCHAR2(40) := Get_Application_Property(TIMER_NAME);
vTime Varchar2(30) := :SYSTEM.CURRENT_DATETIME;
BEGIN
 IF ExpTimer = 'CURRTIME' THEN 
 :Your_Block_Nmae.Item_Name := to_char(sysdate,'HH24:MMI:SS'); END IF;
END;

 Note:

 Using timers in form builder can kill the performance of the machine if the machine has not good configuration.

 I'll recommend at least Pentium4 with more than 3GHZ Intel Processor and 512 MB ram.

 Search Words : timer, timer in forms6i, clock in forms6i

Installation of Oracle Designer10g Repository on Oracle Database 10gR2


--Minimum Setting of spfile

     compatible = 9.0.0 # for an Oracle9i database
     compatible = 8.1.7 # for an Oracle8i database
     max_enabled_roles = 30
     sort_area_size = 262144
     sort_area_retained_size = 65536

     hash_area_size = 1048576
     optimizer_index_caching = 50
     optimizer_index_cost_adj = 25
     shared_pool_size = 32000000

     db_block_buffers        # comment out on an Oracle9i database
     db_block_buffers = 2000 # on an Oracle8i database
     open_cursors = 3000
     processes = 100
     db_file_multiblock_read_count=16 # for a 4K Oracle block size
     db_file_multiblock_read_count=32 # for a 2K Oracle block size
     db_file_multiblock_read_count=8  # for a 8K Oracle block

connect sys/password@SIDname as sysdba

SQL> alter system set max_enabled_roles = 30 scope = spfile;

System altered.

SQL> alter system set sort_area_size = 262144 scope = spfile;

System altered.

SQL> alter system set sort_area_retained_size = 65536 scope = spfile;

System altered.

SQL>  alter system set hash_area_size = 1048576 scope = spfile;

System altered.

SQL> alter system set optimizer_index_caching = 50 scope = spfile;

System altered.

SQL> alter system set optimizer_index_cost_adj = 25 scope = spfile;

System altered.

SQL> alter system set shared_pool_size = 32000000 scope = spfile;

System altered.

SQL> alter system set db_block_buffers = 2000 scope = spfile;

System altered.

SQL> alter system set open_cursors = 3000 scope = spfile;

System altered.

SQL> alter system set processes = 100 scope = spfile;

System altered.

SQL> alter system set db_file_multiblock_read_count=16 scope = spfile;

System altered.

SQL>  alter system set db_file_multiblock_read_count=32 scope = spfile;

System altered.

SQL>  alter system set db_file_multiblock_read_count=8 scope = spfile;

System altered.

SQL> select file_name, tablespace_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF
USERS

C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF
SYSAUX

C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF
UNDOTBS1


FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF
SYSTEM

C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF
EXAMPLE

SQL> create tablespace constant_grow_indexes datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\cons
tant_grow_indexes_01.dbf' size 4000k default storage (initial 100k next 100k minextents 1 maxextents UNLIMITED pctincrease 0);

Tablespace created.

SQL> create tablespace constant_grow_TABLES datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\const
ant_grow_tables_01.dbf' size 15000k default storage (initial 100k next 100k minextents 1 maxextents
UNLIMITED pctincrease 0);

Tablespace created.

SQL> create tablespace dependency_indexes datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\depende
ncy_indexes_01.dbf' size 5600k default storage (initial 100k next 100k minextents 1 maxextents UNLIM
ITED pctincrease 0);

Tablespace created.

SQL> create tablespace dependency_tables datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\dependen
cy_tables_01.dbf' size 9000k default storage (initial 100k next 100k minextents 1 maxextents UNLIMIT
ED pctincrease 0);

Tablespace created.

SQL> create tablespace diagram_indexes datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\diagram_in
dexes_01.dbf' size 1000k default storage (initial 100k next 100k minextents 1 maxextents UNLIMITED p
ctincrease 0);

Tablespace created.

SQL> create tablespace diagram_tables datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\diagram_tab
les_01.dbf' size 2000k default storage (initial 100k next 100k minextents 1 maxextents UNLIMITED pct
increase 0);

Tablespace created.

SQL> create tablespace lob_data datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\lob_data_01.dbf'
size 1200k default storage (initial 100k next 100k minextents 1 maxextents UNLIMITED pctincrease 0);


Tablespace created.

SQL> create tablespace rapid_grow_indexes datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\rapid_g
row_indexes_01.dbf' size 250000k default storage (initial 100k next 100k minextents 1 maxextents UNL
IMITED pctincrease 0);

Tablespace created.

SQL> create tablespace rapid_grow_tables datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\rapid_gr
ow_tables_01.dbf' size 500000k default storage (initial 100k next 100k minextents 1 maxextents UNLIM
ITED pctincrease 0);

Tablespace created.

SQL> create tablespace system_meta_indexes datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\system
_meta_indexes_01.dbf' size 20000k default storage (initial 100k next 100k minextents 1 maxextents UN
LIMITED pctincrease 0);

Tablespace created.

SQL> create tablespace system_meta_tables datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\system_
meta_tables_01.dbf' size 30000k default storage (initial 100k next 100k minextents 1 maxextents UNLI
MITED pctincrease 0);

Tablespace created.

SQL> create tablespace temporary_indexes datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\temporar
y_indexes_01.dbf' size 5000k default storage (initial 100k next 100k minextents 1 maxextents UNLIMIT
ED pctincrease 0);

Tablespace created.

SQL> create tablespace temporary_tables datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\temporary
_tables_01.dbf' size 9000k default storage (initial 100k next 100k minextents 1 maxextents UNLIMITED
 pctincrease 0);

Tablespace created.

SQL> create tablespace version_indexes datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\version_in
dexes_01.dbf' size 9000k default storage (initial 100k next 100k minextents 1 maxextents UNLIMITED p
ctincrease 0);

Tablespace created.

SQL> create tablespace version_tables datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\version_tab
les_01.dbf' size 12000k default storage (initial 100k next 100k minextents 1 maxextents UNLIMITED pc
tincrease 0);

Tablespace created.

Make sure you ave plenty of space in your system tablespace

SQL> select tablespace_name, sum(bytes)/1024/1024 from dba_free_space
  2  group by tablespace_name;

TABLESPACE_NAME                SUM(BYTES)/1024/1024
------------------------------ --------------------
SYSTEM_META_TABLES                       29.1484375
UNDOTBS1                                    28.1875
SYSAUX                                       8.1875
CONSTANT_GROW_INDEXES                     3.7578125
DIAGRAM_INDEXES                            .9140625
CONSTANT_GROW_TABLES                     14.5234375
USERS                                        1.8125
DEPENDENCY_TABLES                         8.6328125
SYSTEM_META_INDEXES                      19.3984375
TEMPORARY_INDEXES                         4.7734375
TEMPORARY_TABLES                          8.6328125

TABLESPACE_NAME                SUM(BYTES)/1024/1024
------------------------------ --------------------
SYSTEM                                       7.3125
EXAMPLE                                      22.625
DIAGRAM_TABLES                             1.828125
LOB_DATA                                   1.015625
RAPID_GROW_TABLES                        488.210938
VERSION_INDEXES                           8.6328125
DEPENDENCY_INDEXES                        5.3828125
RAPID_GROW_INDEXES                       244.054688
VERSION_TABLES                            11.578125

20 rows selected.

SQL> alter tablespace system add datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL10G\system03.dbf' si
ze 140m;

Tablespace altered.

SQL> create user owner identified by owner default tablespace users
  2  temporary tablespace temp;

User created.

SQL> @ C:\DevSuiteHome_1\repadm61\UTL\CKROROLE.SQL;

Dropping role CK_ORACLE_REPOS_OWNER if it already exists
DROP ROLE CK_ORACLE_REPOS_OWNER
          *
ERROR at line 1:
ORA-01919: role 'CK_ORACLE_REPOS_OWNER' does not exist



Creating role CK_ORACLE_REPOS_OWNER

Role created.


Grant succeeded.


Grant succeeded.

***********************************************************************************
* --------------------------------IMPORTANT NOTE:-------------------------------- *
***********************************************************************************

The following Privileges Must be additionally granted to the REPOSITORY OWNER:

Syntax, from within SQL*Plus as the SYS user type (or use cut/edit/paste):
GRANT EXECUTE ON DBMS_RLS TO <repository_owner>;
GRANT EXECUTE ON DBMS_LOCK TO <repository_owner>;
GRANT EXECUTE ON DBMS_PIPE TO <repository_owner>;
GRANT CREATE TABLE TO <repository_owner>;
GRANT CREATE VIEW TO <repository_owner>;
GRANT CREATE PROCEDURE TO <repository_owner>;
GRANT CREATE SYNONYM TO <repository_owner>;
GRANT CREATE SEQUENCE TO <repository_owner>;
GRANT SELECT ON sys.v_$nls_parameters TO <repository_owner> WITH GRANT OPTION;
GRANT SELECT on V_$PARAMETER TO <repository_owner>;
GRANT SELECT ON dba_rollback_segs TO <repository_owner>;
GRANT SELECT ON dba_segments TO <repository_owner>;
GRANT CREATE ANY SYNONYM TO <repository_owner>;
GRANT DROP ANY SYNONYM TO <repository_owner>;

***********************************************************************************

IMPORTANT:
If you want to implement Subordinate User Access using Public Synonyms you MUST
also grant the following to the REPOSITORY OWNER::

Syntax, from within SQL*Plus as the SYS user type (or use cut/edit/paste):
GRANT CREATE PUBLIC SYNONYM TO <repository_owner>;
GRANT DROP PUBLIC SYNONYM TO <repository_owner>;

***********************************************************************************

The following Privileges Must be granted to SUBORDINATE USERS:

Syntax, from within SQL*Plus as the SYS user type (or use cut/edit/paste):
GRANT CONNECT, RESOURCE TO <SUBORDINATE_USER>;

IMPORTANT:
Some subordinate users may need additional privileges depending upon what utilities
they are running (e.g Import/Export from the RON)
You do not need to grant these now unless you know already that certain subordinates
users will need to be able to run these utilities

Syntax, from within SQL*Plus as the SYS user type (or use cut/edit/paste):
GRANT CREATE TABLE TO <SUBORDINATE_USER>;
GRANT CREATE VIEW TO <SUBORDINATE_USER>;
GRANT CREATE PROCEDURE TO <SUBORDINATE_USER>;
GRANT CREATE SYNONYM TO <SUBORDINATE_USER>;
GRANT CREATE SEQUENCE TO <SUBORDINATE_USER>;
GRANT SELECT ON dba_rollback_segs TO <SUBORDINATE_USER>;
GRANT SELECT ON dba_segments TO <SUBORDINATE_USER>;

***********************************************************************************

GRANT EXECUTE ON DBMS_LOCK TO owner;
GRANT EXECUTE ON DBMS_PIPE TO owner;
GRANT CREATE TABLE TO owner;
GRANT CREATE VIEW TO owner;
GRANT CREATE PROCEDURE TO owner;
GRANT CREATE SYNONYM TO owner;
GRANT CREATE SEQUENCE TO owner;
GRANT SELECT ON v_$nls_parameters TO owner WITH GRANT OPTION;
GRANT SELECT on sys.V_$PARAMETER TO owner;
GRANT SELECT ON dba_rollback_segs TO owner;
GRANT SELECT ON dba_segments TO owner;
GRANT CREATE ANY SYNONYM TO owner;
GRANT DROP ANY SYNONYM TO owner;
GRANT CREATE PUBLIC SYNONYM TO owner;
GRANT DROP PUBLIC SYNONYM TO owner;
GRANT ck_oracle_repos_owner to owner;
GRANT CONNECT, RESOURCE TO owner;

SQL>  create user user65 identified by user65 default tablespace users
  2  temporary tablespace temp;

User created.

SQL> GRANT CONNECT, RESOURCE TO user65;

Grant succeeded.

SQL> GRANT CREATE TABLE TO user65;

Grant succeeded.

SQL> GRANT CREATE VIEW TO user65;

Grant succeeded.

SQL> GRANT CREATE PROCEDURE TO user65;

Grant succeeded.

SQL> GRANT CREATE SYNONYM TO user65;

Grant succeeded.

SQL> GRANT CREATE SEQUENCE TO user65;

Grant succeeded.

SQL> GRANT SELECT ON dba_rollback_segs TO user65;

Grant succeeded.

SQL> GRANT SELECT ON dba_segments TO user65;

Grant succeeded.

SQL> grant create public synonym to owner;

Grant succeeded.

SQL> grant drop public synonym to owner;

Grant succeeded.

SQL> grant ck_oracle_repos_owner to owner;

Grant succeeded.

SQL> grant connect, resource to owner;

Grant succeeded.

Oracle Forms 10g
ORA-01017-invalid username/password while running on Oracle 11g
I recently installed Oracle11g on Windows to work with Oracle developer Forms 10g everything works fine but when i run the form in browser from builder i got error

ORA-01017 Invalid username/password logon denied

i was confused what to do but problem solved...

In Oracle 11g a new parameter called SEC_CASE_SENSITIVE_LOGON which is defaults to TRUE for case sensitive passwords. My SCOTT password was "tiger" is small case and Forms 10g was taking it as "TIGER" on runtime.

Solution 1:
- Connect as sys and change the parameter to false.
alter system set SEC_CASE_SENSITIVE_LOGON = FALSE;
Solution 2:
- Connect as DBA and change the password of SCOTT to LETTER case.
alter user scott identified by TIGER;

Managing Oracle Forms Menu Modules

Link1
Link2

Oracle Forms Material

Link1

Calendar 
Link1

Tooltip Text
I'm trying to run this procedure on WHEN-NEW-RECORD-INSTANCE trigger. It works, but only if I click in the record AND moving mouse in the next record (multiple records).
DECLARE
     mem tab.att%TYPE;
BEGIN
SELECT att INTO mem
FROM tab
WHERE att = :block.att;
SET_ITEM_PROPERTY('block.att', TOOLTIP_TEXT, mem);
END;

java-bean-solution...
Open JDeveloper (you can use the version installed with DS 10G), create a new project and add the Forms-Library to it (Project-properties->Library->Add)

Create a new java-class in your project (File->New->General->Java Class) name it TooltipItem and copy the following code to it 
package forms;
import oracle.forms.ui.VTextField;
import oracle.forms.properties.ID;

public class TooltipItem extends VTextField
{
  public boolean setProperty(ID property, Object value)
  {
    if (property==ID.VALUE) 
    {
      try 
      {
        setToolTipValue((String)value);
      } catch (Exception ex) 
      {
        ex.printStackTrace();
      }
    }
    // TODO:  Override this oracle.forms.ui.VTextField method
    return super.setProperty(property, value);
  }
}
Add a deployment profile to your project (File->New->General->Deploment Profiles->JAR-File). Create the jar-archive using this profile (context-menu->Delpoy to jar-file)

Place your jar-file in a directory accessible to the oas (e.g. $ORACLE_HOME/forms/java) and the jar-file to your formsweb.cfg either in archive_jini= or in archive, depending on what plugin you're using.

In forms set the implementation class for your item to forms.TooltipItem




















FRM-40654: Record has been updated by another user. Re-query to see change.
Not an easy one.

Check whether you have any triggers server-side that are working on the tables in your form. Most likely, the same items that are updated within your forms are also handled in a trigger server-side.

One way to fix this is to set the update and insert properties for these items to NO in your block if they are handled in the trigger.
(On the block, set the property value of "DML Returning Value" to "Yes")

Another one is to check the DML property for the block.

Another approach is to use a timer. After you update, insert your form, requery the block from the WHEN-TIMER-EXPIRED. However, this is not going to be liked by your users (lag time).

I would prefer you go through your form and the triggers and see what is happening after you insert, update, commit.
Error - could not reserve record (2 tries). Keep trying?
In fact, automatic locks only occur when you update or delete a row. A query will NEVER lock a row(except you specify an FOR UPDATE). If you have a lock after some execute_query in forms, There is some wrong code in your POST-QUERY-trigger.
https://community.oracle.com/thread/324077

This has to be a classic Oracle forms problem.

I would like to replace that useless message by a more user friendly message when a user tries to update a record which is already locked by another user. I found the following solution in here using the on-lock trigger:

Declare

is_locked boolean := false;

dummy VARCHAR2(2);

Begin

Begin

Select null INTO dummy from mytable

where rowid = :myblock.rowid

for update nowait;

exception when Others then

If sqlcode = -00054 Then

is_locked := True;

End If;

end;

IF is_locked then

Showmessage('Another user is updating this record. Try again later.');

raise form_trigger_failure;

End if;

End;

However, when I add that code, it seems to change the behavior of the locking process. For instance, If the record is locked by user A, user B gets the expected "Another user is updating..." as long as the record lock is active.

When the user A commit the changes, user B is then able to change the data when he should have get the "Record has been updated by another user. Please re-query" message. Therefore, user A loses it's changes when user B does a commit.

Any ideas why or any suggestions ?

 Final Solution
  1. Don't update records of one table in two blocks. OR
  2. Check all post-query triggers in each data block and delete the statement which update/insert data in text item of a table's column in query mode.
  3. Don't make text item of non database item. It should be display item only i.e. cityid and cityname cityid is a database item and cityname is non database item. City LOV must be attached with cityid instead of cityname.

Same error as above
Finding and Fixing ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

Recently we ran into an error Oracle error which says


Caused By: Error executing SQL ALTER TABLE ***_ALLOCATION ADD REGION_ID NUMBER(38):
Caused By: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

The most common reason for this are either 'SELECT FOR UPDATE ' or some uncommitted INSERT statements.


Combining the information I got from several Google searches, I ended up with this sql


SELECT O.OBJECT_NAME, S.SID, S.SERIAL#, P.SPID, S.PROGRAM,SQ.SQL_FULLTEXT, S.LOGON_TIME FROM V$LOCKED_OBJECT L, DBA_OBJECTS O, V$SESSION S, V$PROCESS P, V$SQL SQ WHERE L.OBJECT_ID = O.OBJECT_ID AND L.SESSION_ID = S.SID AND S.PADDR = P.ADDR AND S.SQL_ADDRESS = SQ.ADDRESS;


This gave me info about the sql and the table which it has locked and the logon time.


OBJECT_NAME
SID
SERIAL#
SPID
PROGRAM
LOGON_TIME
SQL_FULLTEXT

TABLE_USER
953
40807
9179
JDBC Thin Client
26-Jul-12
INSERT INTO TABLE_USER VALUES (BLAH…BLAH)

TABLE _USER
953
40807
9179
JDBC Thin Client
26-Jul-12
INSERT INTO TABLE_USER VALUES (BLAH…BLAH)

TABLE _USER
953
40807
9179
JDBC Thin Client
26-Jul-12
INSERT INTO TABLE_USER VALUES (BLAH…BLAH)



The info on the user name and the machine name can also be obtained from the table V$SESSION. Just add S.USERNAME and S.MACHINE to the above sql.


We have 3 options to fix this error

Kill the DB session and get the tables unlocked
Kill the application which holds this particular session(sql connection)
The ideal solution is to get to the actual process(application) to debug/fix the issue



1. Killing the DB session
To kill the DB session execute the sql
alter system kill session 'sid,serial#'
In my case it will be
alter system kill session '953,40807'
2. Killing the Application which holds the session
Get the column value of MACHINE from the table V$SESSION and search for the running processes in that machine.

SELECT O.OBJECT_NAME, S.SID, S.SERIAL#, P.SPID, S.PROGRAM,S.USERNAME,S.MACHINE,S.PORT ,S.LOGON_TIME,SQ.SQL_FULLTEXT FROM V$LOCKED_OBJECT L, DBA_OBJECTS O, V$SESSION S, V$PROCESS P, V$SQL SQ WHERE L.OBJECT_ID = O.OBJECT_ID AND L.SESSION_ID = S.SID AND S.PADDR = P.ADDR AND S.SQL_ADDRESS = SQ.ADDRESS;

In my case I just had a look and I knew who the culprit was from the LOGON_TIME from the sql results


[bash]$ ps aux | grep java


user 30745 0.0 3.3 677336 63172 ? Sl Jul26 3:26 /usr/local/jdk1.6.0_14/jre/bin/java -jar /opt/ea/nova/jenkins/jobs/project/workspace/target/surefire/surefirebooter5649151821229923783.jar /opt/ea/nova/jenkins/jobs/ project /workspace/target/surefire/surefire1933027872679766101tmp /opt/ea/nova/jenkins/jobs/ project/workspace/target/surefire/surefire5320833548219373656tmp


Now that we got to the process, we can easily debug the problem (or just kill the process :P) I did a
kill -15 30745
and puff!!!! Problem solved!!!! Yay!!
3. Getting to the Actual Process on the application server


The better way to find the process is to get the V$SESSION.PORT from the above sql and find the process listening on that port.


SELECT O.OBJECT_NAME, S.SID, S.SERIAL#, P.SPID, S.PROGRAM,S.USERNAME,S.MACHINE,S.PORT ,S.LOGON_TIME,SQ.SQL_FULLTEXT FROM V$LOCKED_OBJECT L, DBA_OBJECTS O, V$SESSION S, V$PROCESS P, V$SQL SQ WHERE L.OBJECT_ID = O.OBJECT_ID AND L.SESSION_ID = S.SID AND S.PADDR = P.ADDR AND S.SQL_ADDRESS = SQ.ADDRESS;


To get to the process listening on the port, execute the below command.For me, the port number was 34465


netstat -ap | grep 34465


which gave me


tcp 0 0 machine.name:34465 oracle.db.com:ncube-lm ESTABLISHED 4030/java

Bingo!!! We got the process id 4030. Debug or Kill; Your Choice!!!

SYSTEM.MESSAGE_LEVEL
There are 25 Levels of Message in an Oracle Forms.
How much cretical error or message is
0 -- all errors or message
1
2
.
.
.
25 -- don't shows any

If we don't want to show any error or message to the user then we assign 25 to message level as 
:SYSTEM.MESSAGE_LEVEL := 25;
Now, system will not show any message error to the user.

If i wanna show all the errors then assign it 0. 
:SYSTEM.MESSAGE_LEVEL := 0;
Final Solution 
You could create a KEY-EXIT-Trigger and put something like, but this would throw away any changes.
EXIT_FORM(NO_VALIDATE); 

















Trap FORM EXIT 'Do you want to save changes'


You can use the below mentioned code to suppress that message at form level.


 


When_New_Form_Instance Trigger


:system.message_level := 25;


On Key_Exit Trigger


:system.message_level := 0;


exit_form(no_validate);


 



REP-52251: Cannot get output of job ID




REP-52251: Cannot get output of job ID 







This is a
 undocumented bug. the cause is SSO turned on for one component only, 
either Forms or Reports. This bug applies to Oracle Application Server 
version 10.1.2.2







By default, the SSO is turned off for Oracle Forms and turned on for Oracle reports.







The solution suggested by Oracle is to either enable SSO for both Forms and reports or disable SSO for both forms and reports.




REPORTS_PATH limited to 1024 chars: REP-52251: Cannot get output of job ID 7204







We are deploying an application in Forms 10g Release 2, and when we run a report we get this error:









1



2





REP-52251: Cannot get output of job ID 7204



REP-51026: No output for job 7204













In the Oracle Enterprise Manager Reports Server’s Failed jobs we can see this error message:









1



2



3



4





Terminated with error:



REP-110: Unable to open file 'repenv1012.rdf'.



REP-1070: Error while opening or saving a document.



REP-0110: Unable to open file 'repenv1012.rdf'.













This error is due to the length of the REPORTS_PATH environment 
variable defined in $ORACLE_HOME/bin/reports.sh. We have a lot of 
applications deployed within one reports server, and the reports path 
length has exceeded 1024 chars.



To solve it, we have two ways:




Although the Dinamic Environment Switching is very powerfull, it 
requires adding a new parameter to the calling report, and as we do not 
develop the applications, we have opted for the second one.







Oracle Forms 10g release 2 : Demos, Tips and Techniques







http://fdtool.free.fr/articles/tutoforms10g/tutoforms10g.htm



 


PROBLEM


To change any type of default message with your own


SOLUTION


use on-message trigger on that block and put the following code in it
if MESSAGE_CODE = 40350 THEN
MESSAGE('NO records found plzz try again with another no.');
ELSE
MESSAGE(MESSAGE_TYPE||'-'||TO_CHAR(MESSAGE_CODE)||': '||ERROR_TEXT);
END IF;


If you want to suppress this message, create a form-level ON-ERROR trigger which would look like this:


if err_type = 'FRM' and err_code = '40350' then
   null;
end if; 


PROBLEM


when i do not change any values in the master block and i try to exit out of the form, getting a message "Do you want to save the changes you have made ".
SOLUTION
It sounds like you have a Post-Query (PQ) trigger that is populating items in your Form. This is the typical reason why you would see this message when you close your form and no User changes have been made. The best solution would be to move your non-base table items from your data blocks to a control block so the PQ trigger won't mark the block or record as "Changed".





 








How to use a calendar in a Form 







How to display Caleder to select Date...Forms 6I
                            
                        



 
                        



            
                    
                    



    


                    
                    

































                        



                    




Download oracle form 6i demo





-How to Use the Calendar Class Demo in your Application



1. Install the Demos that come with Oracle Forms



2. Open the Calendar.fmb situated in C:\Forms6i_demo\Win32\PMBF\6_0_8_8\Demo\Forms



3. Copy the Program Unit: Date_Chosen to your application



4. Get the Object Library: STNDRD20.OLB located in C:\Forms6i_demo\Win32\PMBF\6_0_8_8\Demo\Forms



5. Get the Attached Library: Calendar.pll located in C:\Forms6i_demo\Win32\PMBF\6_0_8_8\Demo\Forms

Select attach library in form builder -> browse -> select *.pll file -> remove path then place *.pll file in C:\orant\TOOLS\OPEN60\PLSQLLIB



6. Drag the Object Groups: Calendar from calendar.fmb to your application and copy it.



In the item to get the date, in the Key_ListVal trigger enter the following code:



date_lov.get_date(sysdate, -- initial date

's_inventory.text_item5', -- return block.item

240, -- window x position

60, -- window y position

'Start Date', -- window title

'OK', -- ok button label

'Cancel', -- cancel button label

TRUE, -- highlight weekend days

FALSE, -- autoconfirm selection

FALSE); -- autoskip after selection



In either a button when button pressed trigger or on the mouse 
double-click trigger of the text item that will get the date, enter the 
following code:



begin

go_block('s_inventory'); --this  is  the  block

go_record(to_number(:system.cursor_record));

go_item('s_inventory.Text_item5'); -- this is the item

do_key('list_values'); --this  calls  the  Key_ListVal  trigger  of  the  item

end; 







http://oracleerpraj.blogspot.com/2013/07/date-picker-calendar-for-oracle-forms.html





WebUtil: A simplified guide to understanding WebUtil



Configure WebUtil for Forms 10g R2



WebUtil: How to Read an Excel file into an Oracle Form 









Graphic Builder







Include Graphics Charts on Forms 6i

Oracle Forms Developer Graphics Builder Reference

http://web2.uwindsor.ca/courses/cs/rituch









hotel reservation system SQL query: identify when one specific room is available




Occupancy



how to suppress frm 40350 query caused no records to be retrieved

on-message trigger on block level

declare

  old_message_level number;

begin

    if MESSAGE_CODE = 40350 THEN

        --message('__________________'); Your own message

      old_message_level := :system.message_level;

      :system.message_level := 20; -- suppresses most errors

---      commit; -- action you want to do without messages or errors

      :system.message_level := old_message_level;

    end if; 

end;



Tool Tip Text Property

http://www.experts-exchange.com/Database/Oracle/Q_20345570.html 









https://groups.google.com/forum/#!topic/oracle-plsql/1zcLW51oeno















You can use stacked canvas and put one text_box inside this
canvas.assigining value to it







here i have attached one procedure. check it and customise
it.







test on when new record instance.







PROCEDURE DO_SHOW_TOOLTIP (p_canvas
VARCHAR2 DEFAULT NULL)IS

    v_mouse_block varchar2(100) := substr(:System.Mouse_Item, 1,
instr(:System.Mouse_Item,'.')-1);

    n_rec_count number := :system.mouse_record;

    n_rec_loc number;

    n_rec_height number;    

    n_top_rec number;

    n_view_x number;

    n_view_y number;    

    n_main_width number;

    n_main_height number;

    n_canvas_x_pos number;

    n_canvas_y_pos number;

    n_item_height number := 31;

    n_item_width number:=44;      


    c_dum     varchar2(10);

    n_dum     number;

     c_txt  varchar2(4000);

BEGIN

           
go_record(:system.mouse_record);       
    

    if n_bed_mast_id is not null then

            
set_application_property(CURSOR_STYLE,win_api.getresource('HAND_PNT'));

             n_main_width :=
get_view_property('canitems',width);

             n_main_height :=
get_view_property('canitems',height);

            

            n_top_rec :=
get_block_property(v_mouse_block, top_record);

            n_rec_height :=
n_item_height ;

            n_rec_loc := (n_rec_count
- n_top_rec) * n_rec_height;





            n_view_x :=
Get_Item_Property(:system.mouse_item,X_POS)+60;

            n_view_y :=
Get_Item_Property(:system.mouse_item,Y_POS)+n_rec_loc+91;

     

          

        IF p_canvas IS NOT NULL THEN

            n_canvas_x_pos :=
get_view_property(p_canvas,VIEWPORT_X_POS);

            n_canvas_y_pos :=
get_view_property(p_canvas,VIEWPORT_Y_POS);

        ELSE

            n_canvas_x_pos := 0 ;

            n_canvas_y_pos := 0 ;

        END IF;

        n_view_x := n_view_x + n_canvas_x_pos ;

        n_view_y := n_view_y + n_canvas_y_pos ;

        

    if n_view_x+get_view_property('can_tooltip',width) >
n_main_width then 

            n_view_x :=(
n_main_width - get_view_property('can_tooltip',width))-60;

        end if;

        -- if the item is at window bottom, move
tooltip above mouse pointer

        if
(n_view_y+get_view_property('can_tooltip',height)+91) > n_main_height then

            
n_view_y := (n_view_y - (get_view_property('can_tooltip',height)) -40);

         end if;   
    

         
set_view_property('can_tooltip',VIEWPORT_x_POS,n_view_x);

         
set_view_property('can_tooltip',VIEWPORT_y_POS,n_view_y); 

               
   
copy(name_in(v_mouse_block||'.TXT_BEDS_DETAIL'),'blk_ctrl.txt_tooltip');

      if :blk_ctrl.txt_tooltip is not null then

             
show_view('can_tooltip');

             
synchronize;

      end if;

    end if;

EXCEPTION

         when others then 

               
    null;

END;



Re WHEN-VALIDATE-ITEM, that does seem to work for me - as far as it 
goes.  I am using a simple block based on the EMP table.  I have a 
procedure:







Code:


PROCEDURE set_tooltip IS
  v_text VARCHAR2(100);
BEGIN
  select ename
  into v_text
  from emp
  where empno = :emp.mgr;

  set_item_property( 'emp.mgr', TOOLTIP_TEXT, 'Manager is '||v_text );
END;



And a WHEN-VALIDATE-ITEM trigger on :EMP.MGR:





Code:


set_tooltip;



I did find that there needed to be a "default" tooltip_text value 
on the item, otherwise it only displayed the tooltip for the first 
record (even though I fired the W-V-I trigger on some other record!) - 
so I put "Don't Know" in the item property palette, and then it worked.



This, along with WHEN-NEW-RECORD-INSTANCE, might work fine for a 
single-row block, but it is unfortunately useless on a multi-row block 
because once set the tooltip applies to every row on the screen until 
you either navigate to a new record (e.g. click on an item in it) or 
update the item.  We really need that WHEN-MOUSE-ENTER trigger solution 
that Forms denies us!





Audit Connect or dba_audit_trial

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1830073957439



http://oradim.blogspot.com/2007/11/enable-database-auditing.html

Insert Images into Oracle Databas 









Finger Print in Oracle


















Sending SMS from form 6i to Mobile Device












Installation of Thermal Printer






    

  1. Install the Thermal Printer Driver
    2. 

  2. Set the page layout width = 8.5 and height = 11 and margin shout be set at 0 to 3 inches or depend upon the width of printer roll in report developer.
    3. 

  3. select start > Setting > Printer & Fax
    4. 

  4. Right Click on printer icon.
    5. 

  5. select properties.
    6. 

  6. select Printing Preferences Button.
    7. 

  7. change the selected options on Layout and Paper/Quality Table and re-select the original selected options and select the apply button.
    8. 

  8. Restart the computer.
    9. 

  9. check the printing in software.
    10. 




commit before leaving the record



You need to create a POST-RECORD trigger on your block:

Create_Timer

http://www.oracle.com/webapps/online-help/forms/10g/state?navSetId=_&navId=3&vtTopicFile=f1_help/builta_c/createti.html&vtTopicId=





Then a WHEN-TIMER-EXPIRED trigger at form level:

When-Timer-Expired

http://www.oracle.com/webapps/online-help/forms/10g/state?navSetId=_&navId=3&vtTopicFile=f1_help/trigsp_z/whentrex.html&vtTopicId=







POST-RECORD trigger:


DECLARE
  timer_id Timer;
  one_second NUMBER(5) := 1000;
BEGIN
  timer_id := CREATE_TIMER('COMMIT_TIMER', one_second, NO_REPEAT);
END; 

When-Timer-Expired form-level trigger:


If GET_APPLICATION_PROPERTY(TIMER_NAME) = 'COMMIT_TIMER' Then
  commit;
  Delete_Timer('COMMIT_TIMER'); 
End if ;  











Call Report Trigger without Parameter Form

 DECLARE  

   pl_id       paramlist;  

   x           VARCHAR2 (150);  

   y           NUMBER;  

   report_service_name  VARCHAR2 (200);

   v_acpcode number;  

 BEGIN  



   -- Create parameter List to pass report parameters through it.  

   pl_id := GET_PARAMETER_LIST ('tmpdata');  

   

   IF NOT ID_NULL (pl_id)  

   THEN  

    DESTROY_PARAMETER_LIST (pl_id);  

   END IF;  

   

   pl_id := CREATE_PARAMETER_LIST ('tmpdata');  

   

   -- Adding Parameters to parameter List  

  add_parameter(pl_id,'paramform',text_parameter,'no');

  add_parameter(pl_id,'maximize',text_parameter,'yes');  

--  add_parameter(pl_id,'family_id',text_parameter,:ben.familyid);

  add_parameter(pl_id,'rcstay_id',text_parameter,:stay.stayid);



--   ADD_PARAMETER (pl_id,'P_EMP_NO',text_parameter,'100');  

--  ADD_PARAMETER (pl_id,'P_DEPTNO',text_parameter,'10');  

   

   -- Set Report server name which uses to run report programatically  

--report_service_name := 'FRHOME1_REPORT_SERVER';

   report_service_name := :global.repsvrname;  

--   report_service_name := 'RptSvr_has-pc_asinst_1';  

--   report_service_name := 'FRHOME1_REPORT_SERVER';  

   SET_REPORT_OBJECT_PROPERTY ('reg_card',  

                 report_server,  

                 report_service_name);  



--   SET_REPORT_OBJECT_PROPERTY ('REPORT_NAME',  

--                 report_server,  

--                 report_service_name);  

   

   -- Set report output format programatically  

   -- Set spreadsheet output  

   SET_REPORT_OBJECT_PROPERTY ('reg_card',  

                 report_desformat,  

                 'PDF');  



--   SET_REPORT_OBJECT_PROPERTY ('REPORT_NAME',  

--                 report_desformat,  

--                 'spreadsheet');  

   

   --set pdf output  

   --SET_REPORT_OBJECT_PROPERTY ('REPORT_NAME', report_desformat, 'pdf');  

   

   --- Execute report ar Report Service  

   y := LENGTH (report_service_name) + 2;  

   x := RUN_REPORT_OBJECT ('reg_card', pl_id);  

   

   --Display a report in URL  

   web.show_document ('/reports/rwservlet/getjobid'|| SUBSTR (x, y)|| '?server='|| report_service_name);  

 END;



Call Report Trigger with Parameter Form

 declare  

 repid REPORT_OBJECT;  

 v_rep VARCHAR2(100);  

 rep_status VARCHAR2(20);

 rep_url varchar2(2000);

 v_username varchar2(50);

 v_password varchar2(50);

 v_database varchar2(50); 

BEGIN  

 repid := FIND_REPORT_OBJECT('final_occup2');  

 SET_REPORT_OBJECT_PROPERTY(repid, REPORT_server, :global.repsvrname);  

 SET_REPORT_OBJECT_PROPERTY(repid, REPORT_DESTYPE, CACHE);  

 SET_REPORT_OBJECT_PROPERTY(repid, REPORT_DESFORMAT, 'HTML');  

 SET_REPORT_OBJECT_PROPERTY(repid, REPORT_OTHER, 'PARAMFORM=YES');  

   

 v_rep := RUN_REPORT_OBJECT(repid);  

 rep_status := REPORT_OBJECT_STATUS(v_rep);   

 WHILE rep_status in ('RUNNING','OPENING_REPORT','ENQUEUED')  

 LOOP  

 rep_status := report_object_status(v_rep);  

 END LOOP;  

 IF rep_status = 'FINISHED' THEN  

 /*Display report in the browser*/  

 rep_url:='/reports/rwservlet?userlogin'||'&report=c:\has\FOF\final_occup2.rep'||'&desformat=htmlcss&destype=cache'||'paramform=yes';

 WEB.SHOW_DOCUMENT(rep_url,'_blank');

 ELSE   

 message('Error when displaying report in browser');   

 END IF;   



END;





Show LOV only when field is blank



 

It is very simply, 



IN When-new-item-Instance



DECLARE

V_LOV BOOLEAN;

BEGIN

IF :BLOCK_NAME.ITEM_NAME IS NULL 

THEN 

V_LOV = SHOW_LOV('LOV_NAME');

END IF;

END;



give it a try, you will get your result, if it doesn't work then try :block_name.item_name













































































No comments:















Post a Comment


















        

      









Subscribe to:
Posts (Atom)