Oracle DataBase Installation 11g

.PL/SQL is not a stand-alone programming language; it is a tool within the Oracle programming environment. SQL* Plus is an interactive tool that allows you to type SQL and PL/SQL statements at the command prompt. These commands are then sent to the database for processing. Once the statements are processed, the results are sent back and displayed on screen.

To run PL/SQL programs you should have Oracle RBDMS Server installed in your machine which will take care of executing SQL commands. Most recent version of Oracle RDBMS is 11g. You can download a trial version of Oracle 11g from the following link:

http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html

You will have to download either 32bit or 64 bit version of the installation as per your operating system. Usually there are two files, as I have downloaded for 64 bit Windows7. You will also use similar steps on your operating system, does not matter if it is Linux or Solaris.

                         win64_11gR2_database_1of2.zip

                         win64_11gR2_database_2of2.zip

After downloading above two files, you will need to unzip them in a single directory database and under that you will find following sub-directories:

Step 1

Now let's launch Oracle Database Installer using setup file. Following is the first screen. You can provide your email ID and uncheck, check box and click Next button:

Step 2

You will have following screen, just uncheck the check box and click continue button to proceed.

Step 3

Just select first option Create and Configure Database using radio button and click Next button to proceed:

Step 4

I assume you are installing Oracle just for learning purpose and you will install it on your PC or Laptop. So select Desktop Class option and click Next button to proceed:

Step 5

Provide a location where you will install Oracle Server. Just modify Oracle Base and rest of the locations will set automatically. Second you will have to provide a password which will be used by system DBA. Once you provide required information, click Next button to proceed:

Step 6

Just click Next button to proceed:

Step 7

Now click Finish button to proceed, this will start actuall server installation.

Step 8

Just wait, until Oracle starts doing required configuration.

Step 9

Here Oracle installation will copy required configuration files, so kindly just wait and watch:

Step 10

Once everything is done, you will have following dialogue box. Just click OK button and come out.

Step 11

Once your installation is done, you will have following final window.

Final Step

If everything has been done successfully then its time to verify your installation. At your command prompt use the following command if you are using Windows:

sqlplus "/ as sysdba"

If everything is fine, you should have SQL prompt where you will write your PL/SQL commands and scripts:

Text Editor

Running large programs from command prompt may land you in inadvertently losing some of the work. So a better option is to use command files. To use the command files:

                         Type your code in a text editor, like Notepad, Notepad+, or EditPlus etc.

                         Save the file with the .sql extension in the home directory.

                         Launch SQL*Plus command prompt from the directory where you created your PL/SQL file.

                         Type @file_name at the SQL*Plus command prompt to execute your program.

If you are not using a file to execute PL/SQL scripts, then simply copy your PL/SQL code and then right click on the black window having SQL prompt and use paste option to paste complete code at the command prompt. Finally, just press enter to execute the code, if it is not already executed.

Registering New Forms in Oracle Apps 11i

Registering New Forms in Oracle Apps 11i

In this tutorial you will learn how to Register New Forms in Oracle Apps 11i , registering form functions, creating menu of functions and creating responsibilities.

Document Summary

This document describes the process of registering new forms in oracle applications.

Registering a Form

Navigation – Application Developer -> Application->Form

Click on Form and you will see following screen.

Fields

Form : Enter the file name of your form (the name of the .fmx file) without extension. Your form filename must be all uppercase, and its .fmx file must be located in your application directory structure.

Application : Enter the name of the application which will own this form.

User Form Name : This is the form name you see when selecting a form using the Functions window.

Description : Enter a suitable description for your form.

Register Form Functions

Navigation – Application Developer -> Application->Function

Click on Function and you will see following screen

Click on the form tab and you will see following screen

Fields

Function : Enter a unique function name for your function. This function name can be used while calling this program programmatically. This name is not visible to the user through other forms.

Form : Select the form name which you have registered.

Application : Select the application name for your form.

Parameters : Enter the parameters that you want to pass to your form function. E.g. Query_only.

Creating Menu of Functions

Navigation – Application Developer -> Application->Menu

Click on Menu and you will see following screen

Fields

Menu : Enter the descriptive name for the menu. This name is not visible to the user.

User Menu Name : The user menu name is used when a responsibility calls a menu.

Menu Type : The options in menu type include:

·  Standard - for menus that would be used in the Navigator form

·  Tab - for menus used in self-service applications tabs

·  Security - for menus that are used to aggregate functions for data security or specific function security purposes, but would not be used in the Navigator form

Seq : Enter a sequence number.

Prompt : Enter the prompt that the users will see for the menu.

Submenu : If you want another menu to be called from this menu, then enter this menu name in this field.

Function : Enter the form function name in this field.

Description : Enter a suitable description for the menu.

Grant :The Grant check box should usually be checked. Checking this box indicates that this function is automatically enabled for the user. If this is not checked then the function must be enabled using additional data security rules.

View Tree :Click on View Tree Button and you will see following screen with the full hierarchy of the menu.

Creating Responsibilities

Navigation – System Administrator -> Security->Responsibility->Define

Click on Define button and you will see following screen in front of you.

Fields

Responsibility Name : Enter the responsibility name for the new responsibility.

Application : Enter the application name you want to associate the new responsibility to.

Responsibility Key : The responsibility key is a unique identification for the responsibility. This is used in the loader programs for internal purposes.

Description : Enter a suitable description for the new responsibility.

Effective Dates : Enter the date range in which the responsibility will be active in the From and To fields.

Available From : In the available from field box, select whether you want this responsibility to be available from Oracle applications or from Oracle Self Service Web Applications or from Oracle mobile Applications.

Data Group : The data group defines the database user name that oracle applications use to connect to the database when you connect to applications using this responsibility.

Menu Name : Enter the menu name that you want to associate with this responsibility.

Web Host Name : If your Web Server resides on a different machine from your database, you must designate the host name (URL) here. Otherwise, the Web Host Name defaults to the current database host server.

Web Agent Name : Enter the PL/SQL Agent Name for the database used by this responsibility. If you do not specify an Agent Name, the responsibility defaults to the agent name current at log-on.

Request Group : The request group would define which requests the users with this responsibility can run. If no request group is assigned to this responsibility then the users with this responsibility will not be able to run requests for which he is not the owner.

Enter the ‘Request group’ name and ‘Application’ name for the request group that you want to assign to this responsibility.

Frequently Asked Questions

1. What is the significance of attaching a request group to a responsibility?

2. What is the significance of attaching a data group to a responsibility?

3. How we relate a function to a form?

4. What is a difference between submenu and function?

5. What is the significance of responsibility in oracle applications?

Courtesy: www.exforsys.com

Anonymous Blocks

Anonymous Blocks:

Anonymous blocks of code are not stored, and not named. They are executed insession

and cannot be called from another session. To execute the same code again,

you must save the anonymous block to an OS file and run it, type it in again, or

include it in a program that executes the block when needed.

You will find throughout the examples that anonymous blocks are used extensively.

Anonymous blocks are perfect for scripting, or activities that you do not wish to

repeat frequently. The following example is an anonymous block:

SET SERVEROUTPUT ON

DECLARE

v_Date TIMESTAMP;

BEGIN

SELECT systimestamp - 1/24

INTO v_Date

FROM dual;

DBMS_OUTPUT.PUT_LINE('One hour ago: '||v_Date);

END;

/

The block begins with DECLARE or BEGIN and is not stored anywhere once

Executed.

String literal customization ORA-01756: quoted string not properly terminated

String literal customization:

If you get tired of having to put two single

quotes inside of a string literal, you can use q’!…!’, with the string

Placed inside the exclamation points. This lets you use one single quote

in your string rather than requiring two. Here’s a quick example using

an anonymous block:

SET SERVEROUTPUT ON

BEGIN

DBMS_OUTPUT.PUT_LINE('Ron's');

END;

/

This returns the following error:

ORA-01756: quoted string not properly terminated

To fix it, we used to have to use two single quotes in place of the

apostrophe, as in ‘Ron’’s’.

BEGIN

DBMS_OUTPUT.put_line('Ram''a');

END;

/

10gR1 provides another alternative:

BEGIN

DBMS_OUTPUT.put_line(q'!Ram'A!');

END;

 /

This completes successfully and displays Ron’s as intended.