In Oracle forms when we have two or more blocks and there is a requirement to join them or make a relation between blocks then there are certain types of relation properties available. You will find below the explanation of Master-Detail relation properties and the triggers and procedures names which Oracle forms creates automatically:

1. Master-Detail Relation (Triggers/Procedures/Properties)

(i) Isolated: - Masters Can be deleted when Child is existing
Triggers
On Populate details (created at block level)
On Clear Details (created at form level)
Procedures
Check Package Failure
Clear all master Detail
Query Master Detail
(ii) Non- Isolated: - Masters Cannot be deleted when Child is existing.
Triggers
On Populate details (created at block level)
On Check Delete master (created at block level)
On Clear Details (created at form level)
Procedures
Check Package Failure
Clear all master Detail
Query Master Detail
(iii) Cascading: - Child Record Automatically Deleted when Masters is deleted.
Triggers
On Populate details (created at block level)
Pre Delete (created at block level)
On Clear Details (created at form level)
Procedures
Check Package Failure
Clear all master Detail
Query Master Detail
---

There is also a property settings in relation to set the query behavior of relation blocks. The following is the property settings and their meanings:

2.Various Block Co-ordination Properties

The various Block Coordination Properties are
a) Immediate
    Default Setting. The Detail records are shown when the Master Record are shown.
b) Deffered with Auto Query
    Oracle Forms defer fetching the detail records until the operator navigates to the detail block.
c) Deferred with No Auto Query
    The operator must navigate to the detail block and explicitly execute a query
---

A single canvas in Oracle forms can be converted into four types and each type has is its different purpose. The following types of Canvases available in Oracle Forms:

3. Types of Canvases (Stacked/Content Difference)

(i) Content Canvas (Default Canvas) [A content canvas is the required on each window you create]
(ii) Stack Canvas  [you can display more then one stack canvas in a window at the same time]
(iii) Tab Type Window [In Tab canvas that have tab pages and have one or more then tab page]
(iv) Toolbar Canvas [A toolbar canvas often is used to create Toolbar Windows. There are two type of Toolbar window.
a. Horizontal Toolbar Canvas: - Horizontal Toolbar canvases are displayed at the top of the window, Just Under the Main Menu Bar.
b. Vertical Toolbar Canvas: - While vertical Toolbar are displayed along the Left Edge of the window.



Yes, Oracle can have a primary key table containing duplicate values in particular field, but it doesn't mean that it will allow further duplicate values but it means that it will ignore the existing duplicate values while creating the primary key on that field.

Suppose you have a requirement that to keep existing table as is with the data but further you don't want to allow duplicate values in a particular field(s). 

So here is the solution, see the example below:

-- Create a table or use existing (which is containing duplicate values):

Create Table EmpDup (
ecode number(4),
ename varchar2(50),
sal number)
/
-- Create non unique index:

Create Index Indx_empdup on EmpDup(ecode);
-- Insert duplicate values:
Insert into EmpDup Values (1,'abc',5000);

Insert into EmpDup Values (1,'xyz', 9000);

Insert into EmpDup Values (2,'xyznew', 9000);

Commit;
-- Add primary key constraint, but with disable and keep index clause, it is necessary:

Alter Table EmpDup add constraint pk_ecode primary key(ecode)  disable keep index;
-- Now enable the constraint with novalidate clause to ignore the existing duplicate values:
Alter Table EmpDup modify constraint pk_ecode enable novalidate ;
-- Now try to insert duplicate values and it will not allow:

Insert into EmpDup Values (2,'xyznew2', 9000);
-- No Select the rows to check the data:

Select * from Empdup;



Suppose you have a requirement that a particular table in Oracle can not have records greater than 10 and also there is a requirement to handle this issue without trigger.

Then there is only one way left from my point of view is Check Constraint for a particular field, for this case you must have a field to control or you have to create it a new one.

You can control easily on a number field if getting inserted eg. any id field where a unique id is getting saved on every record commit, but if not then you have to create it.

Example Table:

create table max_10_rec
(
srlno number(2) primary key check(srlno between 1 and 10),
ename varchar2(30)
)
/

In above example the srlno field will handle the max insertion of 10 records, because it has a primary key and a check constraint for checking the value from 1 to 10.



http://www.movieseekers.net
"Download high quality movies for free.... Both hollywood and bollywood movies are available... Multiple download links for error free downloads... No ads or survey... Regularly updated !! "

http://www.le25casablanca.net
Blog about luxury living, travel, and food and drinks in Casablanca, Morocco.

http://masterofrhythm.blogspot.com
Rythm of life which can be any in your life such as rhythm of music, study, games, emotions, feelings, driving, all activities.

http://free-medical-transcription-resources.blogspot.in/
Free resource for guiding aspiring candidates and job seekers to bring more awareness about Medical Transcription Training and Career Opportunities available for those who complete Medical Transcription Training. You will find useful links of existing MT Companies, Training Institutes, and other useful information relevant to MT profession.

komputoo.blogspot.com
Blog Pecinta Komputer dan Teknologi

http://exadatacertification.blogspot.com/
This site is provide guideline to clear Exadata comcept as well as certification. Also it's include all the features of Exadata with real time demonstration.

http://www.higherneeds.com/
Higher Needs, Bangalore is an online informative e-commerce website for exploring luxury items which aims to seek the finer things in commodity for you to purchase. Being a connoisseur, one know that acquring taste is not easy but always desired and we give you one destination where you can acquire luxury in its finest form.

http://www.zeddes.com/
Get 100% FREE Likes, Subscribers, Followers, Views, Circles and Hits. Start Promoting Right Now, get free bonus and invite all your friends to join in! Dozens of social networks included.  
Tech Gator delivers the Pc tricks & latest news updates related to Cyber Crime, vulnerability, Security breach, Cyber Security and Penetration testing tools on one click and keep you aware from the Latest Online Threats. 

Blog about Oracle ADF, Jdeveloper and Java. Oracle ADF is a J2EE based framework used to develop large scale web based enterprise application.

Narula's Builders is best and reliable Real Estate agent in Indirapuram. Our another dream project Shri Vrinda Build Estate is also going to launch soon. Mr. Jitender Narula, the founder and the Director of Narulas Property, Goa, Marine Resort, Narulas Builders & Developers and Shri Vrinda Build Estates Pvt. Ltd.

A platform where I share my Oracle experiences and learning’s. It’s also to rumble about my feelings on various oracle related stuff.

Sadguru technologies is an institute par excellence where it’s online courses are designed keeping in view of individual’s, employees working in MNC’s and corporate located globally but desirous of being trained remotely.

You Gets is World's Best Local Search Engine provides information on all types of Businesses and offers to promote these businesses across the globe.




Download free customized software for Hotel Room Booking and Billing system. This is very simple and user friendly software, with this software you can create all the rooms and banquet halls your hotel have, you can do room bookings, generate KOTs and finally you can generate the bill on guest check out.

Download HotelSoftwareSetup.rar from Google Drive with the following link:
HotelSoftwareSetup.rar (Google Drive)

Or you can download it from Dropbox with the following link:

HotelSoftwareSetup.rar (Dropbox)

Also providing the link for run time Dlls, if required then you can download from this link Vfp_Dlls.zip. You can download this zip file and after extracting copy all the dlls into \Windows\System32 folder.

If you experience any problem related to installation or running the application please comment on this page, so that I can solve the problem.

Below are the screen shots for Hotel Software:







Chain

Chain is the first level under the Company organizational hierarchy described in my previous blog An Overview of Organizational Hierarchy in Oracle RMS. To create a chain go to the main menu, select Action > Organizational Hierarchy, the Organization Hierarchy Main Form window will open.

1. Select Chain on the tree structure and then click on New.
2. Click Add.
3  For Chain field enter a unique number.
4. For Description field, enter the name for the chain.
5. For Manager field, enter the name of the manager who is responsible for the chain.
6. For Currency field, enter the currency code, or click the LOV button and select the currency.
7. Click OK to save the changes.

Area

Area is the second lever under the Company. To create an area go to the main menu, select Action > Organizational Hierarchy. The Organization Hierarchy Main Form will open.

1. Select Area on the tree structure and click New.
2. Click Add.
3. For Area field, enter a unique number.
4. For Description field, enter the name for the area.
5. For Manager field, enter the name of the manager who is responsible for the area.
6. For Currency field, enter the currency code, or you can click the LOV button to select the currency.
7. For Chain field, enter the ID of the chain that is associated with the area, or you can click the LOV button to select the chain.
8. Click OK to save the changes.

Region

Region is the third level under the Company. To create a region go to the main menu, select Action > Organizational Hierarchy and Organization Hierarchy Main Form will open.

1. Select Region on the tree structure and then click New.
2. Click Add.
3. For Region field enter a unique number.
4. For Description field, enter the name for the region.
5. For Currency field, enter the currency code, or you can click the LOV button to select the currency.
6. For Manager field, enter the name of the manager who is responsible for the region.
7. For Area field, enter the ID of the area that is associated with the region, or click the LOV button and select the area.
8. Click OK to save the changes.


The organizational hierarchy creation in Oracle RMS allows you to build the relationships that are necessary in order to maintain the operational structure of a company.

The following hierarchy levels are used in Oracle RMS:

Company

The highest organizational and merchandise unit level defined in RMS. Only one company can be defined.

Chain

The first level under the company level in the organizational hierarchy. The chain hierarchy level is based on the requirements of the company and a chain can be used to group many store formats, concepts, and locations within the organization.

Area

The second level under the company level in the organizational hierarchy. Area is used to define a geographical group within the organization chain. An area can belong to only one chain.

Region

The third level under the company level in the organizational hierarchy. A region can be used to group locations within an area. A region can belong to only one area.

District

The fourth level under the company level in the organizational hierarchy. District is used to group locations within a region. A district can belong to only one region.

Channel

You create channels to associate it with a location when you create or edit a store or virtual warehouse.


Download free software for Courier & Cargo / Logistic services. A very good software for small to midsize Courier companies to handle day to day consignments they receive from their clients. Software is having comprehensive reports for tracking and billing purposes.
Download CourierProSetup.rar from Google Drive with the following link:
Also providing the link for run time Dlls, if required then you can download from this link Vfp_Dlls.zip. You can download this zip file and after extracting copy all the dlls into \Windows\System32 folder.

Use this software and if you satisfied with the software and want to make it more customized then you can buy this software including source code with the following link:
If you experience any problem related to installation or running the application please comment on this page, so that I can solve the problem.

The following options are available in the software:

1    Outbound Party Creation (Clients)
2    Inbound Party Creation (Couier Associates)
3    Consignment Entry
4    POD updation
5    Final Billing
6    Voucher Entry
7    Cash Billing
8    Party Ledger and Related Reports
Follow to get notifications for CourierPro Software




You can also use records that are based on %ROWTYPE declarations against the table to which the insert  and update are made, or on an explicit record TYPE that is compatible with the structure of the table.

Record Based Insert Example:

DECLARE
my_book books%ROWTYPE;
BEGIN
my_book.isbn := '1-123456-123-1';
my_book.title := 'Fox Infotech Blogs';
my_book.summary := 'Fox Examples';
my_book.author := 'V. Kapoor';
my_book.page_count := 300;
INSERT INTO books VALUES my_book;
END;
/
Notice that you do not include parentheses around the record specifier. If you use this format:

INSERT INTO books VALUES (my_book); -- With parentheses, INVALID!

then you will get an ORA-00947: not enough values exception, since the program is expecting a separate expression for each column in the table.

Record Based Update Example:

You can also perform updates of an entire row using a record. The following example updates a row in the books table with a %ROWTYPE record. Notice that I use the keyword ROW to indicate that I am updating the entire row with a record:

DECLARE
my_book books%ROWTYPE;
BEGIN
my_book.isbn := '1-123456-123-1';
my_book.title := 'Fox Infotech Blogs';
my_book.summary := 'Fox Examples';
my_book.author := 'V. Kapoor';
my_book.page_count := 300;

UPDATE books
SET ROW = my_book
WHERE isbn = my_book.isbn;
END;
/
There are some restrictions on record-based updates:
You must update an entire row with the ROW syntax. You cannot update a subset of columns (although this may be supported in future releases). Any fields whose values are left NULL will result in a NULL value assigned to the corresponding column.

You cannot perform an update using a subquery. 

And, in case you are wondering, you cannot create a table column called ROW.


The CONTINUE statement in loops exits the current iteration of a loop unconditionally and transfers control to the next iteration of either the current loop or an enclosing labeled loop.
In below example, the CONTINUE statement inside the basic LOOP statement transfers control unconditionally to the next iteration of the current loop.

DECLARE
   x   NUMBER := 0;
BEGIN
   LOOP                      -- After CONTINUE statement, control resumes here
      DBMS_OUTPUT.PUT_LINE ('Inside loop: x = ' || TO_CHAR (x));
      x := x + 1;

      IF x < 3  -- instead this if end if condition for continue you can use also CONTINUE When x < 3;
      THEN
         CONTINUE;
      END IF;

      DBMS_OUTPUT.PUT_LINE (
         'Inside loop, after CONTINUE: x = ' || TO_CHAR (x));
      EXIT WHEN x = 5;
   END LOOP;

   DBMS_OUTPUT.PUT_LINE (' After loop: x = ' || TO_CHAR (x));
END;
/
The output of the above block should come something like this:

Inside loop: x = 0
Inside loop: x = 1
Inside loop: x = 2
Inside loop, after CONTINUE: x = 3
Inside loop: x = 3
Inside loop, after CONTINUE: x = 4
Inside loop: x = 4
Inside loop, after CONTINUE: x = 5
After loop: x = 5

Yes... Now you can free download and run this vb.net based utility to backup Oracle Archive Logs. I developed this utility in 2011. This utility is developed to copy archive logs automatically from its default location to another location whether it is on FTP or locally. 

The functionality of this software is to copy automatically an archive log to location you specified in settings and this automatic process occurs whenever a new archive log creation starts then previous archive log get freed and then it copies that freed archive log to the destination you specified and maintains the log for successful or unsuccessful transfer. The unsuccessful transfers can be manually transfer from this utility.

It is very useful utility to backup archive logs for standby database purposes or any others purposes. Oracle Database must be in Archive Log mode to run this utility. 

I am providing below the step by step configuration with screen shots, have a look:

1.   First download the self extractor from this link Click Here
2.   Extract the files to any folder and run the Setup.exe.
3.   It will run the executable automatically or run from the menu, the following screen will appear.


4.   Then click the left most that yellow gear icon button for settings and specify the settings.


5.  Specify file transfer settings.


6.   Specify the database settings.


7.   Now it is done, after specifying all settings the program will run at the specified interval and manage the archive logs.

Note: The application must be run always for successful transportation of archive logs, you can run in background by setting Minimize to Tray icon in status bar.



A cursor acts logically as a pointer into a result set. You can move the cursor through the result set, processing each row, until you determine you are at the end of the result set. There are three types of syntax associated with cursors: creating the cursor, fetching with the cursor, and closing the cursor. In addition, there are a number of attributes of a cursor you can use in your logical comparisons. The following are the types of Cursors in Oracle:

Explicit Cursors

Explicit Cursors are cursors that you declare and use.

Implicit Cursors

PL/SQL allows you to include SQL statements, including SELECT statements, as a part of your code without declaring a cursor, that is called an implicit cursor.

Ref Cursors

A cursor references a result set. The REF CURSOR allows you to pass a cursor reference from one PL/SQL program unit to another. In other words, it allows you to create a variable that will receive a cursor and enable access to its result set, but in this blog I am giving examples for only Explicit and Implicit Cursors, I will give example for Ref Cursors and Dynamic Cursor in another blog.

An example of Explicit Cursor:

DECLARE
   nemployeeid   NUMBER;
   dstartdate    DATE;
   denddate      DATE;
   sjobid        VARCHAR2 (20);

   -- declare cursor
   CURSOR curjob
   IS
      SELECT employee_id,
             start_date,
             end_date,
             job_id
        FROM hr.job_history;
BEGIN
   OPEN curjob;

   LOOP
      FETCH curjob
      INTO nemployeeid, dstartdate, denddate, sjobid;

      EXIT WHEN curjob%NOTFOUND;
      DBMS_OUTPUT.put_line(   'Employee '
                           || nemployeeid
                           || 'had job '
                           || sjobid
                           || ' for '
                           || (denddate - dstartdate)
                           || ' days.');
   END LOOP;

   CLOSE curjob;
END;
/
Same example is given below for explicit cursor but with For Loop, the For Loop cursors are more smart as there is no need to declare variables to fetch values in them and no need to open or close or to check whether the pointer is at end of the cursor. Here is the example:

DECLARE
   CURSOR curjob
   IS
      SELECT employee_id,
             start_date,
             end_date,
             job_id
        FROM hr.job_history;
BEGIN
   FOR jh_rec IN curjob
   LOOP
      DBMS_OUTPUT.put_line(   '‘Employee '
                           || jh_rec.employee_id
                           || ' had job '
                           || jh_rec.job_id
                           || ' for '
                           || (  jh_rec.end_date
                               - jh_rec.start_date
                               || ' days.'));
   END LOOP;
END;
/

An Implicit Cursor example:

DECLARE
   nempno   NUMBER;

   CURSOR curjob
   IS
      SELECT employee_id,
             start_date,
             end_date,
             job_id
        FROM hr.job_history;
BEGIN
  -- below sql query is the type of Implicit Cursor
   SELECT COUNT ( * ) INTO nempno FROM hr.job_history;

   DBMS_OUTPUT.put_line (
      'There are ' || nempno || ' employee history records.');

   FOR jh_rec IN curjob
   LOOP
      DBMS_OUTPUT.put_line(   '‘Employee '
                           || jh_rec.employee_id
                           || ' had job '
                           || jh_rec.job_id
                           || ' for '
                           || (  jh_rec.end_date
                               - jh_rec.start_date
                               || ' days.'));
   END LOOP;
END;
/


Use Bulk Collect to retrieve multiple rows of data in a single fetch operation. Multiple rows of data in a single fetch reduce the number of network round-trips and improve performance.

The following example describes job listed in the JOB_HISTORY table was the job the employee had but also the job the employee went to after the job listed in the JOB_HISTORY table.

CREATE OR REPLACE PROCEDURE Promotion_Rev
IS
   old_job          hr.job_history.job_id%TYPE;
   new_job          hr.job_history.job_id%TYPE;
   nincr            NUMBER;

   CURSOR cselectjob
   IS
        SELECT employee_id,
               start_date,
               end_date,
               job_id
          FROM hr.job_history
      ORDER BY employee_id, start_date;

   TYPE jh_rec IS RECORD (
      employee_id   hr.job_history.employee_id%TYPE,
      start_date    hr.job_history.start_date%TYPE,
      end_date      hr.job_history.end_date%TYPE,
      job_id        hr.job_history.job_id%TYPE
   );

   TYPE jh_table IS TABLE OF jh_rec
                       INDEX BY PLS_INTEGER;

   jh_table_array   jh_table;
BEGIN
   OPEN cselectjob;

   FETCH cselectjob
   BULK COLLECT INTO jh_table_array;

   CLOSE cselectjob;

   FOR counter IN jh_table_array.FIRST .. jh_table_array.LAST
   LOOP
      IF counter = jh_table_array.LAST
      THEN
         nincr := 0;
      ELSE
         nincr := 1;
      END IF;

      old_job := jh_table_array (counter).job_id;

      IF jh_table_array (counter).employee_id =
            jh_table_array (counter + nincr).employee_id
      THEN
         new_job := jh_table_array (counter + nincr).job_id;
      ELSE
         SELECT job_id
           INTO new_job
           FROM hr.employees
          WHERE hr.employees.employee_id =
                   jh_table_array (counter).employee_id;
      END IF;

      DBMS_OUTPUT.put_line(   'Employee '
                           || jh_table_array (counter).employee_id
                           || ' had job '
                           || old_job
                           || ' for '
                           || (jh_table_array (counter).end_date
                               - jh_table_array (counter).start_date)
                           || ' days and moved to job '
                           || new_job
                           || '.');
   END LOOP;
END;
/

Execute the following procedure:

set serveroutput on;
BEGIN
   promotion_rev;
END;
/
The output should come something like this:

Employee 101 had job AC_ACCOUNT for 1497 days and moved to job AC_MGR.
Employee 101 had job AC_MGR for 1234 days and moved to job AD_VP.
Employee 102 had job IT_PROG for 2018 days and moved to job AD_VP.
Employee 114 had job ST_CLERK for 647 days and moved to job PU_MAN.
Employee 122 had job ST_CLERK for 364 days and moved to job ST_MAN.
Employee 176 had job SA_REP for 282 days and moved to job SA_MAN.
Employee 176 had job SA_MAN for 364 days and moved to job SA_REP.
Employee 200 had job AD_ASST for 2100 days and moved to job AC_ACCOUNT.
Employee 200 had job AC_ACCOUNT for 1644 days and moved to job AD_ASST.
Employee 201 had job MK_REP for 1401 days and moved to job MK_REP.
PL/SQL procedure successfully completed.



Profiles must first be created by the DBA using the CREATE PROFILE command. Profiles are used for two purposes, as a means of controlling resources used by an account and for enforcing password and other authentication rules. Here is an example of password and login control:

Create Profile crp_security_profile 
  Limit 
    failed_login_attempts 5
    password_lock_time 1
    password_life_time 90
   password_grace_time 3
   password_reuse_max 5
   password_reuse_time 365
   password_verify_function func_crp_pwd_vfy;

Most organizations will not necessarily implement all of the available password rules as shown in this exhaustive example. This command says that any user who has been assigned the crp_security_profile will have his or her account locked after five successive failed attempts to log in and that the account will remain locked for 24 hours unless explicitly unlocked by the DBA. Any password can be used for no longer than 90 days, but the user will receive the advance warnings for three days before the actual password expriation. A user's password can not be used until five other password changes have been made, and a password cant be reused within 365 days.

Finally, that profile specifies that all passwords will be validated using a verification function, named func_crp_pwd_vfy. This function must be created by the privileged user SYS in order to be used in profile.

Create or Replace Function func_crp_pwd_vfy
(in_username in varchar2,
in_new_password in varchar2,
in_old_password in varchar2)
return boolean
as
pwd_okay boolean;
begin
-- you can wright your own validation...
if in_new_password = in_username then
  raise_application_error(-20001, 'Password may not be user name.');
end if;
return true;
end func_crp_pwd_vfy;
/



A bigfile tablespace can have only one datafile. I will describe a few details about how a bigfile tablespace can be resize. Most of the parameters are available for changing the tablespace's data file, such as the maximum size, whether it can extend at all, and the size of the extents are now modifiable at the tablespace level. Let's start with a bigfile tablespace created as follows:

Create bigfile tablespace bigtblspc 
datafile '/u05/oradata/bigtblspc.dbf' size 300m
autoextend on next 50m maxsize unlimited
extent management local
segment space management auto;

Operations that are valid only at the datafile level with smallfile tablespaces can be used with bigfile tablespaces at the tablespace level:

SQL> alter tablespace bigtblspc
       2       resize 1000m;
Tablespace altered.

Although using alter database with the datafile specification for the bigtblspc tablespace will work, the advantage of the alter tablespace syntax is obvious: you don't have to or need to know where the datafile is stored. As you might suspect, trying to change datafile parameters at the tablespace level with smallfile tablespaces is not allowed.

If a bigfile tablespace runs out of space because its single datafile cannot extend on the disk, you need to relocate the datafile to another volume.


These errors occurred usually due to forms connection problem or some internal problem, the solution is, close the form, re-connect to the database and then re-open the form.

FRM-10001: Internal Error: TOS.
Cause:  Internal system error.
Action:  If the problem persists, contact Oracle Support Services.

FRM-10002: Unable to save the current module.
Cause:  Internal system error.
Action:  If the problem persists, contact Oracle Support Services.

FRM-10003: Unable to retrieve the module from the file.
Cause:  Internal system error.
Action:  If the problem persists, contact Oracle Support Services.


This program connects to ORACLE, declares and opens a cursor, fetches the names, salaries, and commissions of all sales people, displays the results, then closes the cursor. 
The following shows a query that uses an explicit cursor, selecting data into a host structure:

#include <stdio.h>
#include <sqlca.h>

#define UNAME_LEN      20 
#define PWD_LEN        40 
 
/*
 * Use the precompiler typedef'ing capability to create
 * null-terminated strings for the authentication host
 * variables. (This isn't really necessary--plain char *'s
 * does work as well. This is just for illustration.)
 */
typedef char asciiz[PWD_LEN]; 

EXEC SQL TYPE asciiz IS STRING(PWD_LEN) REFERENCE; 
asciiz     username; 
asciiz     password; 

struct emp_info 
{ 
    asciiz     emp_name; 
    float      salary; 
    float      commission; 
}; 


/* Declare function to handle unrecoverable errors. */ 
void sql_error(); 


main() 
{ 
    struct emp_info *emp_rec_ptr; 

/* Allocate memory for emp_info struct. */ 
    if ((emp_rec_ptr = 
        (struct emp_info *) malloc(sizeof(struct emp_info))) == 0)
    { 
        fprintf(stderr, "Memory allocation error.\n"); 
        exit(1); 
    } 
 
/* Connect to ORACLE. */ 
    strcpy(username, "SCOTT"); 
    strcpy(password, "TIGER"); 
 
    EXEC SQL WHENEVER SQLERROR DO sql_error("ORACLE error--");
 
    EXEC SQL CONNECT :username IDENTIFIED BY :password; 
    printf("\nConnected to ORACLE as user: %s\n", username); 
 
/* Declare the cursor. All static SQL explicit cursors
 * contain SELECT commands. 'salespeople' is a SQL identifier,
 * not a (C) host variable.
 */
    EXEC SQL DECLARE salespeople CURSOR FOR 
        SELECT ENAME, SAL, COMM 
            FROM EMP 
            WHERE JOB LIKE 'SALES%'; 
 
/* Open the cursor. */
    EXEC SQL OPEN salespeople; 
 
/* Get ready to print results. */
    printf("\n\nThe company's salespeople are--\n\n");
    printf("Salesperson   Salary   Commission\n"); 
    printf("-----------   ------   ----------\n"); 
 
/* Loop, fetching all salesperson's statistics.
 * Cause the program to break the loop when no more
 * data can be retrieved on the cursor.
 */
    EXEC SQL WHENEVER NOT FOUND DO break; 

    for (;;) 
    { 
        EXEC SQL FETCH salespeople INTO :emp_rec_ptr; 
        printf("%-11s%9.2f%13.2f\n", emp_rec_ptr->emp_name, 
                emp_rec_ptr->salary, emp_rec_ptr->commission); 
    } 
 
/* Close the cursor. */
    EXEC SQL CLOSE salespeople; 
 
    printf("\nArrivederci.\n\n");

    EXEC SQL COMMIT WORK RELEASE; 
    exit(0); 
} 



void 
sql_error(msg) 
char *msg;
{ 
    char err_msg[512];
    int buf_len, msg_len;

    EXEC SQL WHENEVER SQLERROR CONTINUE;

    printf("\n%s\n", msg);

/* Call sqlglm() to get the complete text of the
 * error message.
 */
    buf_len = sizeof (err_msg);
    sqlglm(err_msg, &buf_len, &msg_len);
    printf("%.*s\n", msg_len, err_msg);

    EXEC SQL ROLLBACK RELEASE;
    exit(1);
} 

The following example program connects to Oracle, then loops, prompting the user for an employee number. It queries the database for the employee's name, salary, and commission, displays the information, and then continues the loop. The information is returned to a host structure. There is also a parallel indicator structure to signal whether any of the output values SELECTed might be NULL.
Precompile example programs using the precompiler option MODE=ORACLE.

/*
* example1.pc * * Prompts the user for an employee number, * then queries the emp table for the employee's * name, salary and commission. Uses indicator * variables (in an indicator struct) to determine * if the commission is NULL. * */ #include <stdio.h> #include <string.h> /* Define constants for VARCHAR lengths. */ #define UNAME_LEN 20 #define PWD_LEN 40 /* Declare variables. No declare section is needed if MODE=ORACLE.*/ VARCHAR username[UNAME_LEN]; /* VARCHAR is an Oracle-supplied struct */ varchar password[PWD_LEN]; /* varchar can be in lower case also. */ /* Define a host structure for the output values of a SELECT statement. */ struct { VARCHAR emp_name[UNAME_LEN]; float salary; float commission; } emprec; /* Define an indicator struct to correspond to the host output struct. */ struct { short emp_name_ind; short sal_ind; short comm_ind; } emprec_ind; /* Input host variable. */ int emp_number; int total_queried; /* Include the SQL Communications Area. You can use #include or EXEC SQL INCLUDE. */ #include <sqlca.h> /* Declare error handling function. */ void sql_error(); main() { char temp_char[32]; /* Connect to ORACLE-- * Copy the username into the VARCHAR. */ strncpy((char *) username.arr, "SCOTT", UNAME_LEN); /* Set the length component of the VARCHAR. */ username.len = strlen((char *) username.arr); /* Copy the password. */ strncpy((char *) password.arr, "TIGER", PWD_LEN); password.len = strlen((char *) password.arr); /* Register sql_error() as the error handler. */ EXEC SQL WHENEVER SQLERROR DO sql_error("ORACLE error--\n"); /* Connect to ORACLE. Program will call sql_error() * if an error occurs when connecting to the default database. */ EXEC SQL CONNECT :username IDENTIFIED BY :password; printf("\nConnected to ORACLE as user: %s\n", username.arr); /* Loop, selecting individual employee's results */ total_queried = 0; for (;;) { /* Break out of the inner loop when a * 1403 ("No data found") condition occurs. */ EXEC SQL WHENEVER NOT FOUND DO break; for (;;) { emp_number = 0; printf("\nEnter employee number (0 to quit): "); gets(temp_char); emp_number = atoi(temp_char); if (emp_number == 0) break; EXEC SQL SELECT ename, sal, NVL(comm, 0) INTO :emprec INDICATOR :emprec_ind FROM EMP WHERE EMPNO = :emp_number; /* Print data. */ printf("\n\nEmployee\tSalary\t\tCommission\n"); printf("--------\t------\t\t----------\n"); /* Null-terminate the output string data. */ emprec.emp_name.arr[emprec.emp_name.len] = '\0'; printf("%-8s\t%6.2f\t\t", emprec.emp_name.arr, emprec.salary); if (emprec_ind.comm_ind == -1) printf("NULL\n"); else printf("%6.2f\n", emprec.commission); total_queried++; } /* end inner for (;;) */ if (emp_number == 0) break; printf("\nNot a valid employee number - try again.\n"); } /* end outer for(;;) */ printf("\n\nTotal rows returned was %d.\n", total_queried); printf("\nG'day.\n\n\n"); /* Disconnect from ORACLE. */ EXEC SQL COMMIT WORK RELEASE; exit(0); } void sql_error(msg) char *msg; { char err_msg[128]; int buf_len, msg_len; EXEC SQL WHENEVER SQLERROR CONTINUE; printf("\n%s\n", msg); buf_len = sizeof (err_msg); sqlglm(err_msg, &buf_len, &msg_len); if (msg_len > buf_len) msg_len = buf_len; printf("%.*s\n", msg_len, err_msg); EXEC SQL ROLLBACK RELEASE; exit(1); }


You may need to update a table's particular fields or all fields if their values are null only else there is no need to update any field. Below is the example procedure for the same purpose written on HR.Employees table:

CREATE OR REPLACE PROCEDURE HR.UpdateEmpIfNull (
   PN_EMPLOYEE_ID    IN     NUMBER,
   PV_FIRST_NAME     IN     VARCHAR2,
   PV_LAST_NAME      IN     VARCHAR2,
   PV_EMAIL          IN     VARCHAR2,
   PV_PHONE_NUMBER   IN     VARCHAR2,
   PD_HIRE_DATE      IN     DATE,
   PV_JOB_iD         IN     VARCHAR2,
   PN_SALARY         IN     NUMBER,
   PV_Success           OUT VARCHAR2)
IS
   n_length    NUMBER;
   n_length2   NUMBER;
BEGIN
   SELECT LENGTH(   FIRST_NAME
                 || LAST_NAME
                 || EMAIL
                 || PHONE_NUMBER
                 || HIRE_DATE
                 || JOB_ID
                 || SALARY)
     INTO n_length
     FROM HR.EMPLOYEES
    WHERE EMPLOYEE_ID = PN_EMPLOYEE_ID;

   UPDATE HR.EMPLOYEES
      SET FIRST_NAME = DECODE (FIRST_NAME, NULL, PV_FIRST_NAME, FIRST_NAME),
          LAST_NAME = DECODE (LAST_NAME, NULL, pv_LAST_NAME, LAST_NAME),
          EMAIL = DECODE (EMAIL, NULL, pv_EMAIL, EMAIL),
          PHONE_NUMBER =
             DECODE (PHONE_NUMBER, NULL, pv_PHONE_NUMBER, PHONE_NUMBER),
          HIRE_DATE = DECODE (HIRE_DATE, NULL, pD_HIRE_DATE, HIRE_DATE),
          JOB_ID = DECODE (JOB_ID, NULL, pV_JOB_ID, JOB_ID),
          SALARY = DECODE (SALARY, NULL, pN_SALARY, SALARY)
    WHERE EMPLOYEE_ID = PN_EMPLOYEE_ID;

/* Using below query to determine if the update was successful, we can not use here sql%rowcount or sql%found to determine because if employee id is correct then it will always show a successful update but we need to determine if any null field value has updated or not */

   SELECT LENGTH(   FIRST_NAME
                 || LAST_NAME
                 || EMAIL
                 || PHONE_NUMBER
                 || HIRE_DATE
                 || JOB_ID
                 || SALARY)
     INTO n_length2
     FROM HR.EMPLOYEES
    WHERE EMPLOYEE_ID = PN_EMPLOYEE_ID;

   IF n_length2 > n_length
   THEN
      --- Save Changes.
      COMMIT;

      Pv_Success := 'Y';
   ELSE
      ROLLBACK;
      pv_success := 'N';
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      Pv_Success := 'N';
      ROLLBACK;
END;
/

You may be performing routine for maintenance, such as moving an index to a different tablespace and before you do so, you want to verify the current storage settings. You can use the DBMS_METADATA package to display the DDL required to recreate an index. If you’re using SQL*Plus, set the LONG variable to a value large enough to display all the output. Here is an example:

SQL> set long 10000
SQL> select dbms_metadata.get_ddl('INDEX','SUPP_IDX1') from dual;

Here is a partial listing of the output:

CREATE INDEX "MV_MAINT"."SUPP_IDX1" ON "MV_MAINT"."SUPP" ("SUPP_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 INVISIBLE COMPUTE STATISTICS

To show all index DDL for a user, run this query:

SQL> select dbms_metadata.get_ddl('INDEX',index_name) from user_indexes;

You can also display the DDL for a particular user. You must provide as input to the GET_DDL function the object type, object name, and schema; example:
select
dbms_metadata.get_ddl(object_type=>'INDEX', name=>'SUPP_IDX1', schema=>'INV')
from dual;



More Topics

V. Kapoor

{picture#https://3.bp.blogspot.com/-q3wCssWUHo8/ViDFfCpPEuI/AAAAAAAAD0M/MHPvgnl3T3U/s1600/authpic.JPG} Vinish Kapoor is a Software Consultant, currently working in a reputed IT company in Noida, India and doing blogging on Oracle Forms, SQL and PLSQL topics. He is an author of an eBook Oracle Forms Recipes, which is available on Google play. {facebook#https://www.facebook.com/foxinfotech2014} {twitter#https://twitter.com/foxinfotech} {google#https://plus.google.com/u/0/+VKapoor/posts}
Fox Infotech. Powered by Blogger.