Oracle Forms: COPY built-in

Description
Copies a value from one item or variable into another item or global variable.  Use specifically to write a
value into an item that is referenced through the NAME_IN built-in.  COPY exists for two reasons:
•  You cannot use standard PL/SQL syntax to set a referenced item equal to a value.
•  You might intend to programmatically place characters such as relational operators in NUMBER
and DATE fields while a form is in Enter Query mode.

Syntax
PROCEDURE COPY
  (source       VARCHAR2,
   destination  VARCHAR2);
Built-in Type   unrestricted procedure
Enter Query Mode  yes
Parameters
source The source is a literal value.
destinatioThe destination can be either a text item or another global variable.
Usage Notes
•  When using COPY with date values, the format defined in the BUILTIN_DATE_FORMAT
property will be used if the DATE_FORMAT_COMPATIBILITY_MODE property  is set to 5.0.  If
this property is set to 4.5 COPY will  expect date strings to be formatted using the default American
format.
•  To use a text item as the source reference, you can use the following code:
COPY(NAME_IN(source), destination);
COPY restrictions
No validation is performed on a value copied to a text item.  However, for all other types of items,
standard validation checks are performed on the copied value.
COPY examples
Example 1
/* 
** Built-in:  COPY
** Example:   Force a wildcard search on the EmpNo item during
**            query.
** trigger:   Pre-Query
*/
DECLARE
  cur_val VARCHAR2(40);
BEGIN
51
  /*
  ** Get the value of EMP.EMPNO as a string
  */
  cur_val := Name_In(’Emp.Empno’);
  /*
  ** Add a percent to the end of the string.
  */
  cur_val := cur_val || ’%’;
  /*
  ** Copy the new value back into the item so Form Builder
  ** will use it as a query criterion.
  */
  Copy( cur_val, ’Emp.Empno’ );
END;

Example 2
/* 
** Built-in:  COPY
** Example:   Set the value of a global variable whose name is
**            dynamically constructed.
*/
DECLARE
  global_var_name  VARCHAR2(80);
BEGIN
  IF :Selection.Choice = 5 THEN
    global_var_name := ’Storage_1’;
  ELSE
    global_var_name := ’Storage_2’;
  END IF;
  /*
  ** Use the name in the ’global_var_name’ variable as the
  ** name of the global variable in which to copy the
  ** current ’Yes’ value.
  */
  COPY( ’Yes’, ’GLOBAL.’||global_var_name );
END;

FRM-18103:fail to initialize the development Environment

In order for the Builder to start correctly you will need to unset the ORACLE_HOME and change the PATH so that references to the iDS environment are listed first. So, for example:

Path=C:\DevSuiteHome_1\BIN; . . . <ALL YOUR OTHER PATHS HERE> . . . ;C:\product\11.2.0\dbhome_1\BIN

Multiple Columns Array in PL/SQL

Example Only:


Declare
  Cursor Employee_Id_Cur Is
    Select empno, ename, job, sal, deptno
      From emp;
  Type Employeearraytype Is Table Of Employee_Id_Cur%Rowtype;
  Employeearray Employeearraytype;
Begin
  Open Employee_Id_Cur;
  Loop
    Fetch Employee_Id_Cur Bulk Collect
      Into Employeearray;
    Exit When Employee_Id_Cur%Notfound;
  End Loop;
  Close Employee_Id_Cur;
      For j In Employeearray.First .. Employeearray.Last Loop      
      DBMS_OUTPUT.PUT_LINE(Employeearray(j).empno);
      DBMS_OUTPUT.PUT_LINE(Employeearray(j).ename);
      DBMS_OUTPUT.PUT_LINE(Employeearray(j).job);
      DBMS_OUTPUT.PUT_LINE(Employeearray(j).sal);
      DBMS_OUTPUT.PUT_LINE(Employeearray(j).deptno);    
      End Loop;
End;
/

Random Numbers & Strings in Oracle

Generating random numbers and strings in Oracle

Do you know how to auto generate random numbers or strings in Oracle? Generating random numbers is required when there is a need to create a lot of data for testing purposes, or when we simply need to use a number to temporarily tag a process. It may also be necessary to generate random password strings of a fixed size--a very common requirement for websites that create and maintain logins for users.
Whatever the need, the fact is that Oracle provides us with a random number generator. This option is faster than writing your own random generation logic in PL/SQL as Oracle's internal processing logic is used. In addition, it can also be used to generate both character and alphanumeric strings.

DBMS_RANDOM package

The DBMS_RANDOM package will generate random data in character, numeric or alphanumeric formats. The size and the range from which to pickup the random values can also be specified. This package is created by the script dbmsrand.sql available in the <ORACLE_HOME>/rdbms/admin directory.
The following functions present in the package can be used to serve the purpose of generating random numbers and strings. RANDOM - generate random numbers.
VALUE - generate random numbers from the range provided. The range will be taken as 0-1 if none is provided.
STRING - generate strings in upper case, lower case or alphanumeric format.

• The first parameter takes the string type to be generated, the following values can be provided in upper or lower case.
• U - Upper case
• L - Lower case
• A - Alphanumeric
• X - Alphanumeric with upper case alphabets.
• P - Printable characters only.Providing any other character will return the output in upper case only.
  The size of the string should also be provided as the second parameter.

Oracle documentation says that it is necessary to initialize the package before using the random number generator. Oracle by default initializes the package with the seed value as the current user name, current time down to the second and the current session id.
INITIALIZE - Initialize the package to proceed with the number generation.
Provide a number (seed) as input to the routine.
SEED - Used to change the seed value. It is used in the internal algorithm to generate values. Setting this will
generate the random numbers in an order that will be similar in multiple sessions. Refer to the example below.
TERMINATE - Close the process of random number generation.

Examples:

Below are some examples of using the package.
E.g.: Generating a random number (positive or negative)

SQL> select dbms_random.random from dual;

       RANDOM
_____________
   1393936551

E.g.: Generating a random number between 0 and 1.

SQL> select dbms_random.value from dual;

        VALUE
_____________
            1

E.g.: Generating a random number from a range, between 1 to 1000.

SQL> select dbms_random.value(1,1000) num from dual;

          NUM
_____________
          611

E.g.: Generating a 12 digit random number.

SQL> select dbms_random.value(100000000000, 999999999999) num from dual;

          NUM
_____________
 175055628780

E.g.: Generating an upper case string of 20 characters

SQL> select dbms_random.string('U', 20) str from dual;

STR
_______________________
VUOQOSTLHCKIPIADIZTD

E.g.: Generating a lower case string of 20 characters

SQL> select dbms_random.string('L', 20) str from dual;

STR
____________________
xpoovuspmehvcptdtzcz

E.g.: Generating an alphanumeric string of 20 characters. There is a bug in Oracle 8i that results in special (non-alphanumeric) characters such as ']' in the string. This is resolved in Oracle 9i.

SQL> select dbms_random.string('A', 20) str from dual;

STR
__________________
sTjERojjL^OlTaIc]PLB

E.g.: Generating an upper case alphanumeric string of 20 characters

SQL> select dbms_random.string('X', 20) str from dual;

STR
________________________
SQ3E3B3NRBIP:GOGAKSC

E.g.: Generating a string of printable 20 characters. This will output a string of all characters that could possibly be printed.

SQL> select dbms_random.string('P', 20) str from dual;

STR
___________________
*Yw>IKzsj\uI8K[IQPag

E.g.: Example for calling the dbms_random package and setting the seed for generating the same set of random numbers in different sessions. Please note that the same random numbers are generated in different sessions. Though I have found this to work on most accounts, in some cases, the first number generated was different in different sessions and the remaining were same. I recommend not using this option in any of production code until it is properly document by Oracle.

jaJA>declare
  2     l_num    number;
  3  begin
  4    l_num := dbms_random.random;
  5    dbms_output.put_line(l_num);
  6    dbms_random.seed('amar testing 67890');
  7    l_num := dbms_random.random;
  8    dbms_output.put_line(l_num);
  9  end;
 10  /
483791552
478774329

PL/SQL procedure successfully completed.

Conclusion

DBMS_RANDOM is a good utility and will find its way into lot of development projects, especially web based ones. However, this Package is not exhaustively documented. One should not use it just for the sake of it being there. Make sure that there is a true requirement or a necessity of random values before making use of this package. If you already have a custom code meant for the same purpose, check out the benefits that are available when using this package compared to your application.

Article Reference:
https://www.databasejournal.com/features/oracle/article.php/3341051/Generating-random-numbers-and-strings-in-Oracle.htm

PL/SQL Developer » Changing background color for different connections

Are you Looking for a way to set a different background color in the command window for a specific connection?


Is your intention is to have some sort of visual cue when you are connecting to the production database to prevent accidents?



Solution:

Yes! You can found the feature in:

Tools --> Preferences --> Appearance tab --> change the Connection Indicator settings

Oracle SQL: How to subtract 2 dates in oracle to get the result in hour and minute

Objective: To subtract 2 dates and represent the result in hour and minute in one decimal figure.

START_TIME          END_TIME            (END_TIME-START_TIME)*24    
------------------- ------------------- ------------------------      
21-06-2011 14:00:00 21-06-2011 16:55:00  2.9166667      
21-06-2011 07:00:00 21-06-2011 16:50:00  9.8333333      
21-06-2011 07:20:00 21-06-2011 16:30:00  9.1666667  

Required result (end_time-start_time) as below.

16:55- 14:00 = 2.55      
16:50-07:00 = 9.5      
16:30-7:20 = 9.1 and so on.   

Solution:

SQL> select start_date
     , end_date
     , (24 * extract(day from (end_date - start_date) day(9) to second))
      + extract(hour from (end_date - start_date) day(9) to second)
      + ((1/100) * extract(minute from (end_date - start_date) day(9) to second)) as "HOUR_MINUTE"
 from table;
/

START_DATE          END_DATE            HOUR.MINUTE
------------------- ------------------- -----------
21-06-2011 14:00:00 21-06-2011 16:55:00        2.55
21-06-2011 07:00:00 21-06-2011 16:50:00         9.5
21-06-2011 07:20:00 21-06-2011 16:30:00         9.1

Change Oracle Forms MDI Background Image and Splash Screen

Use existing virtual directories.
For. e.g. use the 
virtual directory /forms/html/ 
that maps to <ora10g-home>/tools/web/html

for 10g put your images in the directory: <ora10g-home>/tools/web/html

for 12c put your images in the directory:
Oracle\Middleware\Oracle_Home\user_projects\domains\base_domain\servers\WLS_FORMS\tmp\_WL_user\formsapp_12.2.1\pgdzbo\war\html

and in your formsweb.cfg assign parameters as follows

splashScreen=/forms/html/<your-file>.gif or .jpg
background=/forms/html/<your-bg-file>.gif or .jpg

You can also check the virtual directory by using the URL
<your-server-name>/forms/html/<image-name>

Make sure that logo, background and splash screen image name must be mentioned in same case in formsweb.cfg file as the source image filename in Oracle 12c Forms

To set password limit Unlimited in Oracle Database

Sometimes for a test user in oracle , we often get this error :-

SQL> conn scott/tiger
ERROR:
ORA-28002: the password will expire in 7 days
Connected

We want that as its a test user, its password never expire. We have to make a change in the profile associated with this user.

SQL> select profile from dba_users where username='SCOTT';

PROFILE
------------------------------------------------------------------------------------------
PROFILE



SQL> select * from dba_profiles where profile='PROFILE' and RESOURCE_NAME='PASSWORD_LIFE_TIME';


PROFILE RESOURCE_NAME RESOURCE_TYPE LIMIT
------------------------- ------------------------------ ------------------------ ------------------------------
PROFILE PASSWORD_LIFE_TIME PASSWORD 10


we need to change the limit of password_life_time parameter to unlimited.


SQL> alter profile PROFILE limit PASSWORD_LIFE_TIME unlimited;


Profile altered.


SQL> select * from dba_profiles where profile='PROFILE' and RESOURCE_NAME='PASSWORD_LIFE_TIME';


PROFILE RESOURCE_NAME RESOURCE_TYPE LIMIT
------------------------- ------------------------------ ------------------------ ------------------------------
PROFILE PASSWORD_LIFE_TIME PASSWORD UNLIMITED


Check that the expirary date column is null of desired user in DBA_USERS view


SQL> select USERNAME,ACCOUNT_STATUS,EXPIRY_DATE,PROFILE from dba_users where USERNAME='SCOTT';


USERNAME ACCOUNT_STATUS EXPIRY_DATE PROFILE
------------------------------ ------------------------- ------------------ -------------------------
SCOTT OPEN PROFILE


Similarly if we want failed login attempts has to be unlimited for a test user then alter the profile associated with the test user :-


SQL> alter profile default limit FAILED_LOGIN_ATTEMPTS unlimited;


Profile altered


I hope this article helped you.

Oracle Case sensitive password - SEC_CASE_SENSITIVE_LOGON

From Oracle 11 passwords are configured as case sensitive by default. Oracle has provided SEC_CASE_SENSITIVE_LOGON initialization parameters to enables or disables password case sensitivity in the database. Value of SEC_CASE_SENSITIVE_LOGON can be between True or False where TRUE means Database logon passwords are case sensitive and FALSE means Database logon passwords are not case sensitive.

SEC_CASE_SENSITIVE_LOGON parameter can be directly modified by ALTER SYSTEM command, and comes in effect without bouncing the instance.

Reference: Oracle Documentation

Lets check following example to understand SEC_CASE_SENSITIVE_LOGON parameter better.

Step 1: Check value of SEC_CASE_SENSITIVE_LOGON parameter

C:\Users\nimish.garg>sqlplus sys/sys as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sun Jun 14 19:58:28 2015
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Release 11.2.0.3.0 - Production


SQL> show parameter sec_case_sensitive_logon;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon             boolean     TRUE

Step 2: Lets create a user and grant required privileges

SQL> create user testuser identified by MyPassword;
User created.

SQL> grant create session to testuser;
Grant succeeded.

Step 3: Lets try to connect user with case sensitive password, it should work.

SQL> conn testuser/MyPassword
Connected.

Step 4: Lets try to connect user with password in Lower Case

SQL> conn / as sysdba
Connected.

SQL> conn testuser/mypassword
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

Oh, we failed to login because Password is configured as case sensitive and we ignored the case .

Step 5: Lets configure Case Sensitivity off for User Name and Password

SQL> conn / as sysdba
Connected.

SQL> show parameter sec_case_sensitive_logon;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon             boolean     TRUE

SQL> alter system set sec_case_sensitive_logon=false scope=both;
System altered.

SQL> show parameter sec_case_sensitive_logon
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon             boolean     FALSE

Step 6: Lets try to login again with password in lower-case. We do not need to bounce the instance to make the effect of sec_case_sensitive_logon parameter value.

SQL> conn testuser/mypassword
Connected.

WOW, It worked. Also We need to remember that even when case sensitive passwords are not enabled, Oracle retains the original case of the password so that it can be used if case sensitivity is enabled later.

Hope you have enjoyed reading this post. Looking forward for your valuable feedback.

Oracle Forms: Direct Print JavaBean

Feature Overview

Overview: 

• Using a JavaBean to get back print features lost in Oracle Reports  when converting to web.
• Send file directly to printer using link

I. Problem One:

Point Of Sale (POS) clients is slow to print.

After converting reports from client/server to the web environment, OraRRP utility was used to print reports directly to client’s default printer, without being prompted with a print dialog. Even though OraRRP works as advertised, it does not meet the speed required for a POS terminal.

Solution:

In my experience with DirectPrint JavaBean, the time to print a receipt reduced from 12 – 14 seconds to 2 - 3 seconds. Also, DirectPrint JavaBean is downloaded and cached to the client machine when client accessed form that calls for DirectPrint JavaBean.

II. Problem Two:

OraRRP utility does not allow printing to named client printer.

Because of architectural change from client/server to web, knowledge of the client printers was lost since Report Server resides on the middle tier where knowledge of printers is loaded.

Solution:

With DirectPrint JavaBean, programmers can send PDF report output to named client printer. This feature can also be used to send a single report to multiple client printers that are either local or on the network. The exact printer name is required.

Conclusion:

DirectPrint JavaBean can be used to restore some print features lost when converting reports from a client/server to web environment.

Demo Installation

Purpose: Instruction to install DirectPrint JavaBean Demo.

Software Needed: Oracle 10g Developer Suite - 10.1.2.

Tested: I have tested DirectPrint JavaBean using Microsoft Internet Explorer 11 with Java plugin-1.6.0_27.

Files Needed:

DirectPrintDemo.fmb – A form that demos the DirectPrint JavaBean features.

DirectPrint.java – Java Source

PDFBox-0.7.3.jar

·         Available at http://www.pdfbox.org

·         Used with permission.

FontBox-0.1.0.jar

·         Available at http://www.fontbox.org

·         Used with permission.

Installation:

1. Open copy DirectPrintDemo.fmx into location listed in FORMS_PATH.
2. Download the PDFBox-0.7.3.jar and FontBox-0.1.0 .jar

·         Both are available at the URLs listed below respectively.

·         http://www.pdfbox.org

·         http://www.fontbox.org

3. Extract the DirectPrintDemo.zip to a temporary location
4. Create a project in Oracle jdeveloper and add the DirectPrint.java to the project
5. Add pdfbox.jar and fontbox as libraries to the project.
6. Use the JAR deployment profile to create the DirectPrint.jar
7. Sign all jars, DirectPrint.jar and PDFBox-0.7.3.jar and FontBox-0.1.0 .jar

·         Both are available at the URLs listed below respectively.

·         http://www.pdfbox.org

·         http://www.fontbox.org

8. Copy all signed jars to the %ORACLE_HOME%\forms\java 

·         Note: all signed jars need to be added to the archive parameter.

9. Add configuration section to the formsweb.cfg to match your environment.

[directprint]

width=650

height=500

separateFrame=True

lookandfeel=Generic

serverApp=default

splashScreen=no

form=DIRECTPRINTDEMO.fmx

envFile=default.env

userid=username/password@database

workingDirectory=CHANGETHISVALUE

archive=frmall.jar,DirectPrint.jar,PDFBox-0.7.3.jar,FontBox-0.1.0.jar

10. Start local forms OC4J engine and the directprint config.

·         http://localhost/forms/frmservlet?config=directprint

11. Screen shot.

12. For additional information see the javadoc included the downloaded DirectPrintDemo.zip.

Loading Fonts: Use the SET_TTF_FROM_FS and SET_TTF_FROM_URL to load other fonts that are listed in the PDF.

Click to download complete package.

Click to downlod DirectPrint.jar File

Click to downlod fontbox-0.1.0.jar File

Click to downlod pdfbox-0.7.3.jar File

Click to Download Form Source File

Generate PDF on server using command line keywords

In this article, I show you that how we can generate PDF File from oracle forms using command line keywords. For this, we need to understand what are command line keywords and their purpose. I already posted detail information about command line keyword click to view the page. Also view, what is rwrun and how it works? 

Now first create simple report based on EMP table with DEPT parameter in order to filter report department-wise.

Now create module with control block "B" having one list item :DEPT, one text item :FNAME, one button to view report PB_VU_REP and one button for generating report in PDF format PB_GEN_REPORT.
As mentioned in below images.

Now create record group in order to populate Department list.
For e.g.

SELECT DNAME||' - '||TO_CHAR(DEPTNO),TO_CHAR(DEPTNO) FROM DEPT

After creating record group, create a new trigger on form level to populate the list.

DECLARE
LST NUMBER:=0;
BEGIN
LST := POPULATE_GROUP('DEPTS');
CLEAR_LIST('B.DEPT'); 
POPULATE_LIST('B.DEPT', 'DEPTS'); 
END;

Now create trigger WHEN-BUTTON-PRESSED on view report button.

If :B.Dept is Null Then
Message('Please select department from list');
Message('Please select department from list');
Raise Form_Trigger_Failure;
End If;

WEB.SHOW_DOCUMENT ('http://localhost/reports/rwservlet?destype=cache&desformat=pdf'
||'&userid=SCOTT/TIGER@DBWARE'
||'&report=D:\SAMPLE\SAMPLE.RDF&DEPT='||:B.DEPT);

Now create trigger WHEN-BUTTON-PRESSED on generate report button.

If :B.Dept is Null Then
Message('Please select department from list');
Message('Please select department from list');
Raise Form_Trigger_Failure;
End If;
If :B.FName is Null Then
Message('Please mention file name');
Message('Please mention file name');
Raise Form_Trigger_Failure;
End If;

host('rwrun server=<your_report_server> report=D:\Sample\Sample.rdf userid=scott/tiger@dbware desformat=pdf DESTYPE=file DESNAME=D:\Sample\'||:B.FName||'.pdf dept='||:B.Dept);
 
If Not Form_success then
Message('Error- '||Error_Text);
Message('Error- '||Error_Text);
 Raise Form_Trigger_Failure;
Else
Message('Report Created on D:\Sample\'||:B.FName);
Message('Report Created on D:\Sample\'||:B.FName);
End If;


Now compile and run the module.

Click to download complete sample package.
Note:
1) Sample package coding is set on D:\ drive. If you are using it, must be extracted in D:\ drive
2) Change report server name in generate report's button trigger.