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.

Oracle Application: Using the Command Line

A.1 Using the Command Line

An Oracle Reports command on the command line generally has the following form:

component_name keyword=value, keyword=value, ...

where each keyword=value pair is called a command line option.

Keywords must be specified and can be used in any order following the component name.

A.1.1 General Usage Notes

• No spaces should be placed before or after the equal sign of an option.
• Separate options with one or more spaces; do not use commas to separate options.
• Values may be in single or double quotes. The effect of single or double quotes is operating system-specific.
• The keyword= part of all options is not case-sensitive. The value portion may be case-sensitive, depending on your operating system.
• To pass a single quote from the command line, you must enter two quotes (one quote as an escape and one as the actual quote). For example:

  rwrun REPORT=myrep DESTYPE=file DESNAME=run.out BATCH=yes p_value="Roy''s Batch Report"
  

• Full pathnames are supported for all file references (for example, DESNAME=/revenues/q1/nwsales). If you do not specify the full path name, the Oracle Reports file searching method is used to find the file. If you do not specify a path for a keyword value that includes a file name, the Reports Server will try to find the file from the REPORTS_PATH environment variable.
• All file names and paths specified in the client command line refer to files and directories on the server machine, except for any file specified for the following command line keywords:
  • CMDFILE=filename. In this case, the CMDFILE specified is read and appended to the original command line (of which CMDFILE is a part) before being sent to the Reports Server. The runtime engine does not reread the command file
  • DESNAME=filename DESTYPE=LOCALFILE. In this case, DESNAME refers to files on the client machine.

A.1.2 Rules

• Values entered on the Runtime Parameter Form override those entered on the command line. For example, if you specify rwrun on the command line with COPIES=1, but in the Runtime Parameter Form, specify COPIES=2, then two copies of the report are generated.
• Values entered on the command line override those specified in command files. For example, if you specify rwrun on the command line with COPIES=1and CMDFILE=RUNONE (a command file), but the command file RUNONE, includes rwrun COPIES=2, only one copy of the report is generated.
• You can specify values for DESTYPE, DESNAME, DESFORMAT, ORIENTATION, and COPIES in a number of different places. The following list shows the decreasing order of precedence for the places where you specify these values:
  1. Print Job dialog box
  2. Runtime Parameter Form
  3. Runtime Parameters/Settings tab of Preferences dialog box
  4. Keywords on the command line
  5. Values specified in the report definition
  6. Choose Printer dialog box

Oracle Application Server: RWRUN

RWRUN

(Command Line Keyword)
rwrun (Reports Runtime) runs a report by starting its own in-process server (not to be confused with the default in-process Reports Server), which runs in the same JVM as the rwrun process.

The configuration file for this in-process server is rwbuilder.conf and trace files are saved in the rep_machinename-rwbuilder directory.

The rwrun command runs a report using the OracleAS Reports Services in-process server. When you run a .rep file, the PL/SQL is already compiled and will not be recompiled. If you are running an .rdf file, the PL/SQL is automatically recompiled, if necessary. It becomes necessary if the report wasn't compiled and saved from Reports Builder or the platform or version on which you were running the report is incompatible with the platform on which it was last compiled and saved.

Keywords used with rwrun

The brackets surrounding each keyword in this list are there to create a separation between keywords and has no other significance.

rwrun [ACCESSIBLE] [ARRAYSIZE] [AUTHID] [AUTOCOMMIT] [BCC] [BLANKPAGES]

[BUFFERS] [CACHELOB] [CC] [CELLWRAPPER] [CMDFILE] [COLLATE] [COPIES] [CUSTOMIZE]

[DATEFORMATMASK] [DELIMITED_HDR] [DELIMITER] [DESFORMAT] [DESNAME] [DESTINATION]

[DESTYPE] [DISTRIBUTE] [EXPRESS_SERVER] [FROM] [LONGCHUNK] [MODE]

[MODULE|REPORT] [NONBLOCKSQL] [NOTIFYFAILURE][NOTIFYSUCCESS][OLAP_CON]

[ONFAILURE] [ONSUCCESS] [ORIENTATION] [OUTPUTIMAGEFORMAT] [PAGESIZE]

[PAGESTREAM] [PDFCOMP] [PDFEMBED] [PRINTJOB] [READONLY] [RECURSIVE_LOAD]

[REPLYTO] [REPORT|MODULE] [ROLE] [RUNDEBUG] [SAVE_RDF]

[SQLTRACE][SUBJECT][SUPPRESSLAYOUT][TRACEFILE] [TRACEMODE] [TRACEOPTS] [USERID]

rwrun (Reports Runtime) runs a report by starting its own in-process server (not to be confused with the default in-process Reports Server), which runs in the same JVM as the rwrun process.

The configuration file for this in-process server is rwbuilder.conf and trace files are saved in the rep_machinename-rwbuilder directory.

Examples

Example 1: 

Customizing a report

rwrun userid=scott/tiger@mydb report=emp.rdf CUSTOMIZE=empcustomize.xml destype=file desformat=pdf desname=emp.pdf

Example 2:

Sending report output to a file

rwrun report=test.rdf userid=scott/tiger@mydb desformat=pdf DESTYPE=file DESNAME=c:\mydir\test.pdf

Example 3: 

Sending report output to a printer

rwrun report=test.rdf userid=scott/tiger@mydb DESTYPE=printer DESNAME=myprinter

Example 4: 

Sending report output through e-mail

rwrun report=test.rdf userid=scott/tiger@mydb desformat=pdf DESTYPE=mail DESNAME="emp1@comp.com, emp2@comp.com" cc="emp3@comp.com" bcc="mgr@comp.com" replyto="me@comp.com" from="me@comp.com"

Example 5: 

Sending report output to WebDAV (any WebDAV server or Oracle Portal WebDAV)

rwrun report=test.rdf userid=scott/tiger@mydb desformat=htmlcss DESTYPE=webdav "DESNAME"="http://myusername:mypassword@mywebdavserv.com/mydir/test.html"

Example 6: 

Sending report output to Oracle Portal

rwrun report=test.rdf userid=scott/tiger@mydb DESTYPE=oracleportal desformat=PDF PAGEGROUP=mypagegrp OUTPUTPAGE=reports_output ITEMTITLE=pushtoportal STATUSPAGE=result

Example 7: 

Sending XML PDS report output to a file

rwrun report=myxmlpdstest.rdf destype=file desformat=PDF desname=c:\mydir\my.pdf

Example 8: 

Sending JDBC PDS report output to a file

rwrun report=myjdbcpdstest.rdf destype=file desformat=PDF desname=c:\mydir\myxml.pdf P_JDBCPDS=sybuser/sybpwd@server1.mydomain.com:1300

Example 9: 

Distributing report output to multiple destinations

rwrun report=test.rdf userid=scott/tiger@mydb DISTRIBUTE=yes DESTINATION=c:\mydistribute.xml

Example 10: 

Using a secured Reports Server

rwrun report=test.rdf userid=scott/tiger@mydb desformat=pdf destype=file desname=test.pdf AUTHID=myadmin/myadmin

Example 11: 

Running a report with e-mail notification

rwrun report=test.rdf userid=scott/tiger@mydb destype=file desformat=pdf desname=test.pdf NOTIFYSUCCESS="emp@comp.com" NOTIFYFAILURE=admin@comp.com

How to execute rwrun with user parameters?

Following is the example to execute rwrun with user parameter:

rwrun REPORT=C:\report1.rdf DESTYPE=FILE DESNAME=C:\report1_MMDDYYYY.pdf DESFORMAT=pdf USERID=username/passwd@database para1=<value> para2=<value>