l_path_value../usr/tmp Invalid File Operation : ORA-29283: invalid file operation ORA-06512: at "SYS.UTL_FILE", line 536 ORA-29283: invalid file operation in oracle apps

The log file shows an error like below:

Entered into Begin.
l_path_value../usr/tmp
Invalid File Operation : ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation

This is not due to the permission like read/write in the directory path /usr/tmp/
It is due to from one login say sysadmin submitted the program and generates the .csv or .txt file in /usr/tmp/ directory. The file will get saved in the directory. when one more time needs to submit from another user login then the error will throw in log file 

Entered into Begin.
l_path_value../usr/tmp
Invalid File Operation : ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation

At first needs to delete the .csv file in the /usr/tmp/ directory and then from another login can able to submit the program and generate the .csv file. Because my 2 test instances are commonly having /usr/tmp/ directory so from one test instance the file first generted then while running from another test instance the csv file won't get replaced the existing file permission denied will come through putty also we can't able to delete or replace the file from another login credentials.

There is another way one way is to delete the file and generate the file or
Needs to give permissions for the file in the directory by chmod 777 filename or winscp
so that from any login submitted the program it will replace the old file as of now it will be generated in read mode only.

Query to find the customer standard details in order management->customer->standard form

Query-

SELECT   role_acct.account_number, hzp.party_name, hl.address1, hl.address2,
         hl.city, hl.state, hl.postal_code, cont_party.person_last_name,
         cont_party.person_first_name,
         DECODE (hcp.phone_line_type,
                 'GEN', hcp.phone_area_code || hcp.phone_number,
                 NULL
                ) phone_number,
         hcp.phone_extension,
         DECODE (hcp.phone_line_type,
                 'FAX', hcp.phone_area_code || hcp.phone_number,
                 NULL
                ) fax_number,
         cont.orig_system_reference
    FROM hz_cust_account_roles cont,
         hz_parties cont_party,
         hz_relationships cont_rel,
         hz_org_contacts cont_org,
         hz_parties cont_rel_party,
         ar_lookups l,
         ar_lookups l1,
         hz_contact_restrictions cont_res,
         hz_person_language per_lang,
         hz_contact_points cont_point,
         hz_cust_account_roles acct_role,
         hz_parties party,
         hz_parties rel_party,
         hz_relationships rel,
         hz_org_contacts org_cont,
         hz_cust_accounts role_acct,
         hz_cust_accounts cont_role_acct,
         hz_contact_points hcp,
         hz_parties hzp,
         hz_party_sites hps,
         hz_cust_acct_sites_all hcas,
         hz_locations hl
   WHERE cont_org.title = l.lookup_code(+)
     AND l.lookup_type(+) = 'CONTACT_TITLE'
     AND cont_org.job_title_code = l1.lookup_code(+)
     AND l1.lookup_type(+) = 'RESPONSIBILITY'
     AND cont.cust_account_role_id = acct_role.cust_account_role_id
     AND cont.party_id = cont_rel.party_id
     AND cont.role_type = 'CONTACT'
     AND cont_org.party_relationship_id = cont_rel.relationship_id
     AND cont_rel.subject_id = cont_party.party_id
     AND cont_rel.party_id = cont_rel_party.party_id
     AND acct_role.party_id = rel.party_id
     AND acct_role.role_type = 'CONTACT'
     AND org_cont.party_relationship_id = rel.relationship_id
     AND rel.subject_id = party.party_id
     AND rel_party.party_id = rel.party_id
     AND party.party_id = per_lang.party_id(+)
     AND per_lang.native_language(+) = 'Y'
     AND cont_point.owner_table_id(+) = rel_party.party_id
     AND cont_point.contact_point_type(+) = 'EMAIL'
     AND cont_point.primary_flag(+) = 'Y'
     AND party.party_id = cont_res.subject_id(+)
     AND cont_res.subject_table(+) = 'HZ_PARTIES'
     AND cont.cust_account_id = cont_role_acct.cust_account_id
     AND cont_role_acct.party_id = cont_rel.object_id
     AND acct_role.cust_account_id = role_acct.cust_account_id
     AND role_acct.party_id = rel.object_id
     AND rel_party.party_id = hcp.owner_table_id(+)
     AND hzp.party_id = role_acct.party_id
     AND role_acct.party_id = hps.party_id
     AND hps.party_site_id = hcas.party_site_id
     AND hl.location_id = hps.location_id
     AND cont_org.party_site_id = hcas.party_site_id
     AND role_acct.account_number='10508'
     AND cont_point.email_address like 'DOCLINKCUSTOMERCARE@QLOGIC.COM'
ORDER BY role_acct.account_number, cont.cust_account_role_id;

To find the concurrent request program submitted through backend query in oracle apps r12

select * from apps.fnd_concurrent_requests
where concurrent_program_id in (select concurrent_program_id from apps.fnd_concurrent_programs where concurrent_program_name like 'XX_O2C_RAXINV')
order by request_id desc;

From the above query take the requested by and query below with user id to see the user name

select * from apps.fnd_user where user_id=1005316;

Take the responsibility id from above query and search below to find the responsibility name.

select * from apps.fnd_responsibility_vl where responsibility_id=54314;

The argument 1 to argument 15 from first query shows the parameters that are from querying the concurrent program name in system administrator ->program name-> parameters not while submitting passing parameters. Refer that with while submitting parameter names

PRAGMA in oracle

PRAGMA: 

To create exceptions with standard oracle errors by using the PRAGMA EXCEPTION_INIT function are called non- predefined exceptions.

PRAGMA( also called as pseudoinstructions) is the keyword that signifies that the statement is a compiler directive, which is not processed when the pl/sql block is executed.Rather it directs the pl/sql compiler to interpret all occurences of the exception name within the block as the associated oracle server error number.

SYNTAX:

declare 

exception EXCEPTION;

PRAGMA EXCEPTION_INIT(exception,error_number);

error_number is the standard oracle server error number

Non pre defined error i.e user defined error

To trap oracle server error number -01400

("Cannot insert null");

set serveroutput on

declare

insert_excep exception;

PRAGMA EXCEPTION_INIT

(insert_excep,-01400);

begin

insert into departments

(department_id,department_name) values (280,null);

EXCEPTION

WHEN insert_excep THEN

DBMS_OUTPUT.PUT_LINE('Insert Operation Failed');

DBMS_OUTPUT.PUT_LINE(SQLERRM);

END;

/

where department_name is not null column.

Cursors,parameter cursors

Cursors: A cursor is like a pointer in c. It holds the address location instead of item.

The cursor is of 2 types

a) implicit cursor
b) explicit cursor

• Implicit cursors:

Declared and managed by pl/sql for all dml and pl/sql select statements.

• Explicit cursors:

For queries return more than one row,explicit cursors are declared and managed by the programmer and manipulated through specific statements in the block's executable actions.

Explicit cursor attributes:

Attribute	Type	Description
%ISOPEN	Boolean	Evaluates to true if the cursor is open
%NOTFOUND	Boolean	Evaluates to true if the recent fetch does not return a row
%FOUND	Boolean	Evaluates to true if the recent fetch returns a row
%ROWCOUNT	Number	Evaluates to the total number of rows

ex-

Begin

open emp_cursor;

loop

fetch emp_cursor into empno,ename;

EXIT WHEN emp_cursor%ROWCOUNT > 10 OR  emp_cursor%NOTFOUND;

end loop;

close emp_cursor;

end;

PARAMETER CURSORS: 

We can pass values inside the cursor by using a parameterized cursors.

ex-

set serveroutput on

declare

dept_id number;

lname varchar2(15);

 cursor emp_cursor ( deptno NUMBER ) IS

select employee_id,last_name

from employees

where department_id=deptno;

BEGIN

open emp_cursor(10);

...

close emp_cursor;

open emp_cursor(20);

...

close emp_cursor;

...

end;

Difference between stored procedure and functions in oracle

Stored Procedures are pre-compile objects which are compiled for first time and its compiled format is saved which executes (compiled code) whenever it is called. But Function is compiled and executed every time when it is called. For more about stored procedure and function refer the articles Different types of Stored Procedure and Different types of Function.

Basic Difference

1. Function must return a value but in Stored Procedure it is optional( Procedure can return zero or n values).
2. Functions can have only input parameters for it whereas Procedures can have input/output parameters .
3. Functions can be called from Procedure whereas Procedures cannot be called from Function.

Advance Difference

1. Procedure allows SELECT as well as DML(INSERT/UPDATE/DELETE) statement in it whereas Function allows only SELECT statement in it.
2. Procedures can not be utilized in a SELECT statement whereas Function can be embedded in a SELECT statement.
3. Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section whereas Function can be.
4. The most important feature of stored procedures over function is to retention and reuse the execution plan while in case of function it will be compiled every time.
5. Functions that return tables can be treated as another rowset. This can be used in JOINs with other tables.
6. Inline Function can be though of as views that take parameters and can be used in JOINs and other Rowset operations.
7. Exception can be handled by try-catch block in a Procedure whereas try-catch block cannot be used in a Function.
8. We can go for Transaction Management in Procedure whereas we can't go in Function.