Posts Tagged ‘Oracle’

« Older Entries
Newer Entries »

ORACLE :How to Configure Net8 in Non-RAC Database

February 17th, 2013, posted in Oracle
Share

To access the database from other than the system in which database installed you need to configure oracle supplies Net8 for this purpose. It is a common interface to client application that needs to connect to the oracle database. It consists of the three files: tnsname.ora, listener.ora, sqlnet.ora.

Steps or Process:
1.      Edit the tnsname.ora file and add the new service name for use by the client software.
2.      Edit the listener.ora file add an entry for another database instance.
3.      Use the ping command to verify connectivity to the Host.
4.      Use the tnsping command to verify the connectivity to the Net8 listener.
5.      Start/Stop the Net8 listener to reload newly added DB instance.
6.      Connect to a remote database over the network.

tnsname.ora:
The normal location for this file is D:oracleora92networkadmin. This file is located on both client and server. If you make configuration changes on the server ensure you can connect to the database through the listener if you are logged on to the server. If you make configuration change on the client ensure you can connect from your client workstation to the database through the listener running on the server.
Add the following entry in your tnsname.ora file and change the value in the bracket according to your environment.
RMAN.AL-SADHAN.COM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = rmanbackup)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = RMAN)
)
)

Note: You can put either system name or directly static IP (192.168. 0.1) as HOST.
C:tnsping rman
Use the above command to verify the connectivity on windows environment.

listener.ora:
The normal location for this file is D:oracleora92networkadmin. This file is client side file (typically on remote PC). The client uses thistnsname.ora file to obtain connection details from the desired database. Add the following entry in your listener.ora file and changed the value in bracket as per your environment.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = D:oracleora92)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = RMAN)
(ORACLE_HOME = D:oracleora92)
(SID_NAME = RMAN)
)
)

Note: Provide the unique global name as you specify at the time of database creation. You can change it later with your own domain name.

SQL>ALTER DATABASE rename global_name to rman.al-sadhan.com;
C:lsnrctl
Lsnrctl>stop listener
Lsnrctl>start listener
Lsnrctl>reload listener
You can use above command to bounce the listener.

sqlnet.ora:
The normal location for this file is D:oracleora92networkadmin. The sqlnet.ora file is the profile configuration file, and it resides on the client machines and the database server. The sqnet.ora is text file that contain basic configuration details used by the SQL*Net.
NAMES.DEFAULT_DOMAIN = al-sadhan.com
SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)
trace_level_client = OFF
sqlnet.expire_time = 30
names.default_domain:  If the net service name does not have a domain specified, this parameter value is appended to the service name.

names_directory_path: This parameter specifies the order of naming methods used when a client attempts a connection to a database. Possible values include: LDAP; TNSNAMES; HOSTNAME; ONAMES; and EZCONNECT.
sqlnet.expire_time:  This parameter, set on the server, enables dead connection detection.  After the specified time interval, expressed in minutes, the server checks to see if the client is still connected. If the client is not still connected, the server process exits.

sqlnet.authentication_services: This parameter is used to enable one or more authentication services.  There is no default setting, so if authentication has been installed, it this parameter should be set to either NONE for no authentication methods or ALL for all authentication methods.

trace_level_client: This parameter enables tracing unless it is set to OFF or 0, which is the default.  Use the following values to set tracing levels: USER (4); ADMIN (10); and SUPPORT (16).

*********************************************************************************************************************
Note : Please not do make backups before using these queries and also confirm them yourself or by aother means as
 well.
*********************************************************************************************************************
Share
read more no comments

ORACLE : How to Check Only One Item In a Check Box ?

February 7th, 2013, posted in Oracle Queries
Share

Here i will share a tricky PL/SQL code with you.
If you want to use Check Box in oracle forms and want to restrict end user to select only one check box, how will you handle this ?

Just customize the following code and you will get the result :

declare
l_current_record number;
l_last_record    number;
begin
l_current_record := :system.trigger_record;

last_record;
l_last_record := :system.cursor_record;

first_record;

for i in 1 .. l_last_record loop
if :system.cursor_record <> l_current_record then
:SM_ACT_FLAG := 0;
end if;
next_record;
end loop;

go_record(l_current_record);
end;

*********************************************************************************************************************
Note : Please not do make backups before using these queries and also confirm them yourself or by aother means as
 well.
********************************************************************************************************************
Share
read more no comments

ORACLE : How to call a report in Forms 10g ?

February 2nd, 2013, posted in Oracle
Share

Lets talk about version 10g Developer Suite.

In development environment you need to run report server manually but at Application Server it’s not needed.

How can we run report server manually ?
Just go to start menu >> run and type
rwserver SERVER=myserver
 
Where myserver is the server name.
Now reports server runs.
Make changes(Report Name) on following code and  try this in a Button trigger :
DECLARE
v_repid REPORT_OBJECT;
v_rep VARCHAR2(100);
v_rep_status VARCHAR2(100);
v_param VARCHAR2(200) := NULL;
v_valor VARCHAR2(200);
v_url VARCHAR2(2000);
v_repserver varchar2(20) := 'myserver';
v_report varchar2(100) := 'D:REPORT_NAME.REP';
v_PARAMETRO varchar2(100) := '';
BEGIN
v_repid := FIND_REPORT_OBJECT('MYREPORT'); -- report is an element from object navigator report
SET_REPORT_OBJECT_PROPERTY(v_repid, REPORT_FILENAME, v_report);
SET_REPORT_OBJECT_PROPERTY(v_repid, REPORT_EXECUTION_MODE, BATCH);
SET_REPORT_OBJECT_PROPERTY(v_repid, REPORT_COMM_MODE, SYNCHRONOUS);
SET_REPORT_OBJECT_PROPERTY(v_repid, REPORT_DESTYPE, cache);
SET_REPORT_OBJECT_PROPERTY(v_repid, REPORT_DESFORMAT, 'pdf' );
SET_REPORT_OBJECT_PROPERTY(v_repid, REPORT_DESNAME, v_report);
SET_REPORT_OBJECT_PROPERTY(v_repid, REPORT_SERVER, v_repserver);
SET_REPORT_OBJECT_PROPERTY(v_repid, REPORT_OTHER, 'paramform=no '||v_PARAMETRO);
v_rep := RUN_REPORT_OBJECT(v_repid);
v_rep_status := REPORT_OBJECT_STATUS(v_rep);
WHILE v_rep_status IN ('RUNNING','OPENING_REPORT','ENQUEUED') LOOP
v_rep_status := REPORT_OBJECT_STATUS(v_rep);
END LOOP;
IF v_rep_status = 'FINISHED' THEN
message(v_rep);
message(v_rep);
WEB.SHOW_DOCUMENT(v_url||'/reports/rwservlet/getjobid'||
SUBSTR(v_rep, INSTR(v_rep,'_', -1)+1)||'?'||'server='||v_repserver, '_blank');
END IF;
END;


*****************************************************************************************************************
Note : Please not do make backups before using these queries and also confirm them yourself or by aother means as
 well.
*****************************************************************************************************************

***
Share
read more no comments

ORACLE : How to Add or Remove List Text Anywhere from List Item?

January 30th, 2013, posted in Oracle
Share

Here we will talk a trick of Oracle List Item for Forms Version 10g and 6i.

If you want to remove list text and values from a oracle forms list item.
How will you do this ?
When there is no remove icons and Backspace and Delete key doesn’t work, both just lest blank spaces.

Try the following shortcut keys:

  • “Ctrl + Shift + >” – add list element.
  • “Ctrl + Shift + <” – remove list element.
    • Share
    read more 1 comment

    ORACLE : How to Show / Get, Year Month and Date between two date ?

    January 28th, 2013, posted in Oracle Queries
    Share

    Use bellow sql to show year month and date :

    SELECT    TRUNC (MONTHS_BETWEEN (:END_DATE, :START_DATE) / 12) as YEARS,
    MOD(TRUNC (MONTHS_BETWEEN (:END_DATE, :START_DATE)), 12) as MONTHS,
    (  TO_DATE (:END_DATE)- ADD_MONTHS (:START_DATE,TRUNC (MONTHS_BETWEEN (:END_DATE, :START_DATE))))  as Date
    FROM DUAL;

    *********************************************************************************************************************
Note : Please not do make backups before using these queries and also confirm them yourself or by aother means as
 well.
*********************************************************************************************************************
    Share
    read more 6 comments
    « Older Entries
    Newer Entries »