GL Tables which stores parent and child segments information
FND_FLEX_VALUES and
FND_FLEX_VALUE_HIERARCHIES tables.
_ALL tables in Oracle Apps
_ALL tables in Oracle Apps?
_ALL tables in oracle applications give the info about multiple organizations info about these tables.
Difference between data conversion and data migration
What is the difference between data conversion and data migration?
Data Migration is upgrading from one version to another version fro e.g. from 11.5.9 to 11.5.10.. Data Conversion is receiving data from a legacy system or receiving data from another module ( e.g. PA), validating it and converting it to the target module using the standard import program.
Key Words Used in Oracle
Key Words Used in Oracle
The Key words that are used in Oracle are ::
a) Commiting :: A transaction is said to be commited when the transaction makes permanent changes resulting from the SQL statements.
b) Rollback :: A transaction that retracts any of the changes resulting from SQL statements in Transaction.
c) SavePoint :: For long transactions that contain many SQL statements, intermediate markers or savepoints are declared. Savepoints can be used to divide a transactino into smaller points.
d) Rolling Forward :: Process of applying redo log during recovery is called rolling forward.
e) Cursor :: A cursor is a handle ( name or a pointer) for the memory associated with a specific stament. A cursor is basically an area allocated by Oracle for executing the Sql Statement. Oracle uses an implicit cursor statement for Single row query and Uses Explcit cursor for a multi row query.
f) System Global Area(SGA) :: The SGA is a shared memory region allocated by the Oracle that contains Data and control information for one Oracle Instance.It consists of Database Buffer Cache and Redo log Buffer.
g) Program Global Area (PGA) :: The PGA is a memory buffer that contains data and control information for server process.
g) Database Buffer Cache :: Databese Buffer of SGA stores the most recently used blocks of datatbase data.The set of database buffers in an instance is called Database Buffer Cache.
h) Redo log Buffer :: Redo log Buffer of SGA stores all the redo log entries.
i) Redo Log Files :: Redo log files are set of files that protect altered database data in memory that has not been written to Data Files. They are basically used for backup when a database crashes.
j) Process :: A Process is a 'thread of control' or mechansim in Operating System that executes series of steps.
AutoInvoice Overview in Receivables
AutoInvoice is a program that can be used to import and validate transaction data from other financial systems from which one can create invoices, debit memos, credit memos, and on-account credits. It rejects transactions with invalid information to insure the integrity of the data. This fits well with in Oracle ERP or to integrate with any third party application.
Top 10 reasons for using Auto Invoice
1. Powerful Interface Tool
2. Supports Oracle & Non-Oracle Systems
3. Import Large Amount of Data
4. Calculate or Import Tax
5. Group Lines & Invoices
6. Online Error Correction
7 .Lines Validation
8. Derive GL Date
9 .Import Flex fields
10.Import or Derive Accounting Info
What is inside AutoInvoice
AutoInvoice is a program set consists of 3 main programs. Each program will have unique nature of work to do and they are called internally except Purge program whose execution is derived on the setup otherwise ready to execute stand alone.
Master (RAXMTR)
Import (RAXTRX)
Purge (RAXDEL)
1. Auto Invoice Master program RAXMTR
Selects and marks records in the interface tables to be processed based on the parameters the user entered and then calls the AutoInvoice Import program. Auto Invoice Master program has no report output.
•Gathers statistics, it means it gathers the stats on interface tables and set the stats on certain indices on interface tables
•Marks interface records for processing by marking request_id
•Submits multiple workers for Parallel Processing by creating instances for request.
2. Auto Invoice Import Program
Validates the selected record and creates transaction if it passes validation. Any record that fails validation is left in the interface table with an error code. Depending on the setup, related records may be rejected as well. This program has an output file called Auto Invoice Execution report, which you can view by clicking the View Report button in the Requests window.Working of Auto invoice , Validates data, Inserts records, Deletes interface data Only when system option purge set to ‘Y’
3. Auto Invoice Purge Program
Deletes records from the interface tables. If you set the Purge Interface Table system option to No in Define System Option window, Auto Invoice does not delete processed records from the interface tables after each run,and we must submit Auto Invoice Purge Program periodically to clean up the interface tables. This program only deletes transaction lines that have been successfully imported.Deletes all rows where interface_status =‘P’
• Ra_interface_lines
• Ra_interface_distributions
• Ra_interface_salescredits
Oracle Receivable’s Auto Invoice program will be used to import and validate Invoices. A custom feeder program is required to transfer data from the Advantage extract files and populate the Auto Invoice interface tables (RA_INTERFACE_LINES_ALL and RA_INTERFACE_DISTRIBUTIONS_ALL).If there is need to run populate sales credit into RA_INTERFACE_SALESCREDITS_ALL table. When run, AutoInvoice produces the AutoInvoice Execution Report and the AutoInvoice Validation Report. Any entries which failed validation can be reviewed in Oracle Receivables’ AutoInvoice Interface Exceptions window. Depending on the error, changes may need to be made in Receivables, the feeder program or the imported records in the interface tables.
How Autoinvoice Execution works
Normally, Auto Invoice can be divided into three major phases, Pre-grouping: here the validates all of the line level data takes place, Grouping: groups lines based on the grouping rules and validates header level data, Transfer :validates information that exists in Receivables tables
What happen when AutoInvoice run?
Once the Auto invoice Program gets called, the following activity takes place is part of execution process. This can be analyzed by debug options.Line, accounting, and sales credit information for each line populates 3 interface tables
Lines are ordered and grouped
Tax is calculated
GL date is determined
GL accounts are assigned using Auto Accounting
Tax, freight, commitments, and credit memos are linked to transaction lines
All transactions are batched
Validated lines are used to create the transaction
How Data is flowing?
Select, insert and update and delete take place on certain tables once it is logged out.
Selects
– RA_INTERFACE_LINES_ALL
– RA_INTERFACE_DISTRIBUTIONS_ALL
– RA_INTERFACE_SALESCREDITS_ALL
Updates/Insert
– RA_INTERFACE_ERRORS_ALL
– RA_CUSTOMER_TRX_ALL
– RA_CUSTOMER_TRX_LINES_ALL
– AR_PAYMENT_SCHEDULES_ALL
– AR_RECEIVABLE_APPLICATIONS_ALL
Inserts
– RA_INTERFACE_ERRORS_ALL
AutoInvoice Exception Handling
Records that fail validation are called ‘Exceptions’. Exceptions stay in Interface Tables which is RA_INTERFACE_ERRORS_ALL. Errors can be corrected in the Exception Handling window. Once corrections are made, Auto invoice must be resubmitted. Records that pass validation get transferred to Receivables tables
AutoInvoice Exception Handling Windows
-Interface Exception window displays exception messages associated with all invalid records
-Interface Lines window displays records that fail validation, provides an error message and can be used to correct the errors
-The Line Errors windows displays errors associated with a specific line, and can only be opened from Interface Lines window
-Interface Exceptions window displays Interface Id, Exception Type, Error Message and Invalid Value associated to the error
-Data cannot be edited in this window, but error can be viewed and corrected by clicking the Details button
-Error Message and Column name with invalid data are displayed in the Message column, and the invalid value that needs to
In R12 what is MO_GLOBAL.INIT
Purpose of mo_global.init :-
It will check if new Multi Org Security Profile is set, to decide if new Security Profile method will be used.
If the new MO security profile is set, then mo_global.init inserts one record, for each Organization in Org Hierarchy, in table mo_glob_org_access_tmp
When & from where is mo_global.init called ?
This package procedure will be called as soon as you login or as soon as you switch responsibility. Just like
FND_GLOBAL.INITIALIZE is called. It is safe to assume that Oracle will invoke MO_GLOBAL.INIT after FND_GLOBAL.INITIALIZE
From SQL*Plus, you can simulate login to a specific responsibility by calling
a.Call FND_GLOBAL.INITIALIZE
This will set your responsibility id, user_id etc
b. call MO_GLOBAL.INIT
This will read the MO profile option values for your responsibility/user, and will initialize the Multi Org Access.
Introduction to LINUX
Introduction to LINUX
Typical Installation of Linux is as simple as installing windows operating systems. Custom installation requires some expertise in understanding the Linux directory structure. Linux always takes the unformatted partition to load operating system
Directory structure
/
root bin etc usr home dev lib boot .. d01
‘/’ is the starting point, under this various default, user-defined subdirectories like d01 in which oracle applications is created
Logging to Linux server using telnet will take the user to user’s home directory.Eg. For user1 the home directory is /home/user1
Using Telnet
Telnet provides user support for the Telnet protocol, a remote access protocol you can use to log on to a remote computer , network device, or private TCP/IP network.
• To open Telnet, click Start, click Run, and then type telnet. You can also type telnet at the
command prompt.
• Using Telnet, you can make a remote connection.
• To use Telnet, you must have the TCP/IP protocol installed and configured on your computer and you must have a user account established on a remote host.
• To display help for Telnet, type help at the command prompt.
Linux Commands
ls - list directory contents
ls [OPTION]... [FILE]...
-a displays all the files including starting with .
-A displays all the files excluding implicit “.” “..”
-t sort by modification time
-r reverse order
-R recursively displays the files , subdirectories and it’s contents
-s print the size of each file, in blocks
-S sort by file size
-l displays long format
example: when you use ls -ls
4 -rw-r--r-- 1 sdaggupa users 803 Apr 1 10:10 test.c
4 drwxr-xr-x 3 sdaggupa users 4096 Apr 7 11:00 conv
[number of operating system blocks; permissions (d- directory,- file); unknown; username; usergroup; size in bytes; month; date; time; file or directory name]
clear – Clears the screen
more -
mkdir - Create the DIRECTORY(ies), if they do not already exist.
mkdir [OPTION] DIRECTORY...
-m, --mode=MODE set permission mode (as in chmod), not rwxrwxrwx - umask
-p, --parents no error if existing, make parent directories as needed
cd - change directory
cd [ or or ] relative path : path from the working directory absolute path : complete path from “/” virtual path : path defined in the current environment
cd -changes to user’s home directory
cp - copy files and directories
cp [OPTION]... SOURCE DEST
cp [OPTION]... SOURCE... DIRECTORY
OPTION -R copies the source (including subdirectories and its contents) recursively.
-u copy only when the file is newer than the destination, and when destination file is missing.
mv - move (rename) files
mv [OPTION]... SOURCE DEST
mv [OPTION]... SOURCE... DIRECTORY
Rename SOURCE to DEST, or move SOURCE(s) to DIRECTORY.
-f, --force never prompt before overwriting
-i, --interactive prompt before overwriteNote: Practice well before using otherwise you may not get back your file some times
rm - remove files or directories
rm [OPTION]... FILE...
This manual page documents the GNU version of rm. rm removes each specified file. By default, it does not remove directories.
-f, --force ignore nonexistent files, never prompt
-i, --interactive prompt before any removal
-r, -R, --recursive remove the contents of directories recursively
rmdir - remove empty directory
chmod - change file access permissions
chmod [OPTION]... MODE[,MODE]... FILE...
-R to change the mode recursively if it is directory
ex: $chmod 755 test.cafter executing the above command test.c will have rwx permissions for the user, r-x permissions for group, r-x permissions for Other users
chgrp - chgrp - change group ownership
chgrp [OPTION]... GROUP FILE...
-R to change the group ownership recursively if it is Directory
Triggers concepts
Data Dictionary Views Related
To DDL Triggers trigger$
dba_triggers all_triggers user_triggers
System Privileges Related To Table Triggers create trigger
create any trigger
administer database trigger
alter any trigger
drop any trigger
Table Trigger Firing Options -- before constraints are applied
BEFORE INSERT
BEFORE UPDATE
BEFORE DELETE
-- after constraints are applied
AFTER INSERT
AFTER UPDATE
AFTER DELETE
Transaction Model Oracle transactions are atomic. No commit or rollback are allowed in a trigger.
Maximum trigger size 32K - but you can call procedures and function in triggers to perform processing
Create Statement Level Triggers (the default)
Statement Level Trigger With A Single Action CREATE OR REPLACE TRIGGER
[]
[OR OR ]
ON
[FOLLOWS ]
DECLARE
BEGIN
EXCEPTION
END ;
/
CREATE TABLE orders (
somecolumn VARCHAR2(20),
numbercol NUMBER(10),
datecol DATE);
CREATE OR REPLACE TRIGGER statement_level
BEFORE UPDATE
ON orders
DECLARE
vMsg VARCHAR2(30) := 'Statement Level Trigger Fired';
BEGIN
dbms_output.put_line(vMsg);
END statement_level;
/
set serveroutput on
INSERT INTO orders (somecolumn) VALUES ('ABC');
UPDATE orders SET somecolumn = 'XYZ';
Statement Level Trigger With Multiple Actions
CREATE OR REPLACE TRIGGER statement_level
AFTER INSERT OR UPDATE OR DELETE
ON orders
DECLARE
vMsg VARCHAR2(30) := 'Statement Level Trigger Fired';
BEGIN
IF INSERTING THEN
dbms_output.put_line(vMsg || ' When Inserting');
ELSIF UPDATING THEN
dbms_output.put_line(vMsg || ' When Updating');
ELSIF DELETING THEN
dbms_output.put_line(vMsg || ' When Deleting');
END IF;
END statement_level;
/
set serveroutput on
INSERT INTO orders (somecolumn) VALUES ('ABC');
UPDATE orders SET somecolumn = 'DEF' WHERE ROWNUM = 1;
DELETE FROM orders WHERE ROWNUM = 1;
Create Row Level Triggers
Note: AFTER row triggers create less UNDO than BEFORE row triggers so use AFTER when possible.
Row Level Trigger ... most common usage to provide a surrogate key from a sequence CREATE OR REPLACE TRIGGER
[FOLLOWS ]
[]
OR OR
[OF ]
ON
REFERENCING NEW AS OLD AS PARENT AS
FOR EACH ROW
DECLARE
BEGIN
EXCEPTION
END ;
/
CREATE TABLE t (
rid NUMBER(5),
col VARCHAR2(3));
ALTER TABLE t
ADD CONSTRAINT pk_t
PRIMARY KEY (rid)
USING INDEX;
CREATE SEQUENCE seq_t;
CREATE OR REPLACE TRIGGER row_level
BEFORE INSERT
ON t
FOR EACH ROW
BEGIN
SELECT seq_t.NEXTVAL
INTO:NEW.rid
FROM dual;
dbms_output.put_line(:NEW.rid);
END row_level;
/
INSERT INTO t (col) VALUES ('A');
INSERT INTO t (col) VALUES ('B');
INSERT INTO t (col) VALUES ('C');
SELECT * FROM t;
Row Level Trigger With A Single Action
CREATE OR REPLACE TRIGGER row_level
BEFORE UPDATE
ON orders
FOR EACH ROW
DECLARE
vMsg VARCHAR2(30) := 'Row Level Trigger Fired';
BEGIN
dbms_output.put_line(vMsg);
END row_level;
/
set serveroutput on
INSERT INTO orders (somecolumn) VALUES ('ABC');
INSERT INTO orders (somecolumn) VALUES ('ABC');
INSERT INTO orders (somecolumn) VALUES ('ABC');
SELECT * FROM orders;
UPDATE orders SET somecolumn = 'XYZ';
Row Level Trigger With Multiple Actions
CREATE OR REPLACE TRIGGER statement_level
AFTER INSERT OR UPDATE OR DELETE
ON orders
FOR EACH ROW
DECLARE
vMsg VARCHAR2(30) := 'Row Level Trigger Fired';
BEGIN
IF INSERTING THEN
dbms_output.put_line(vMsg || ' On Insert');
ELSIF UPDATING THEN
dbms_output.put_line(vMsg || ' On Update');
ELSIF DELETING THEN
dbms_output.put_line(vMsg || ' On Delete');
END IF;
END statement_level;
/
set serveroutput on
INSERT INTO orders (somecolumn) VALUES ('ABC');
UPDATE orders
SET somecolumn = 'ZZT';
DELETE FROM orders WHERE rownum < 4;
Row Level Trigger With OF Clause
CREATE OR REPLACE TRIGGER of_clause
BEFORE UPDATE
OF numbercol
ON orders
FOR EACH ROW
DECLARE
vMsg VARCHAR2(40) := 'Update Will Change numbercol Column';
BEGIN
dbms_output.put_line(vMsg);
END of_clause;
/
set serveroutput on
UPDATE orders
SET numbercol = 8;
=========
FND_FLEX_VALUES and
FND_FLEX_VALUE_HIERARCHIES tables.
_ALL tables in Oracle Apps
_ALL tables in Oracle Apps?
_ALL tables in oracle applications give the info about multiple organizations info about these tables.
Difference between data conversion and data migration
What is the difference between data conversion and data migration?
Data Migration is upgrading from one version to another version fro e.g. from 11.5.9 to 11.5.10.. Data Conversion is receiving data from a legacy system or receiving data from another module ( e.g. PA), validating it and converting it to the target module using the standard import program.
Key Words Used in Oracle
Key Words Used in Oracle
The Key words that are used in Oracle are ::
a) Commiting :: A transaction is said to be commited when the transaction makes permanent changes resulting from the SQL statements.
b) Rollback :: A transaction that retracts any of the changes resulting from SQL statements in Transaction.
c) SavePoint :: For long transactions that contain many SQL statements, intermediate markers or savepoints are declared. Savepoints can be used to divide a transactino into smaller points.
d) Rolling Forward :: Process of applying redo log during recovery is called rolling forward.
e) Cursor :: A cursor is a handle ( name or a pointer) for the memory associated with a specific stament. A cursor is basically an area allocated by Oracle for executing the Sql Statement. Oracle uses an implicit cursor statement for Single row query and Uses Explcit cursor for a multi row query.
f) System Global Area(SGA) :: The SGA is a shared memory region allocated by the Oracle that contains Data and control information for one Oracle Instance.It consists of Database Buffer Cache and Redo log Buffer.
g) Program Global Area (PGA) :: The PGA is a memory buffer that contains data and control information for server process.
g) Database Buffer Cache :: Databese Buffer of SGA stores the most recently used blocks of datatbase data.The set of database buffers in an instance is called Database Buffer Cache.
h) Redo log Buffer :: Redo log Buffer of SGA stores all the redo log entries.
i) Redo Log Files :: Redo log files are set of files that protect altered database data in memory that has not been written to Data Files. They are basically used for backup when a database crashes.
j) Process :: A Process is a 'thread of control' or mechansim in Operating System that executes series of steps.
AutoInvoice Overview in Receivables
AutoInvoice is a program that can be used to import and validate transaction data from other financial systems from which one can create invoices, debit memos, credit memos, and on-account credits. It rejects transactions with invalid information to insure the integrity of the data. This fits well with in Oracle ERP or to integrate with any third party application.
Top 10 reasons for using Auto Invoice
1. Powerful Interface Tool
2. Supports Oracle & Non-Oracle Systems
3. Import Large Amount of Data
4. Calculate or Import Tax
5. Group Lines & Invoices
6. Online Error Correction
7 .Lines Validation
8. Derive GL Date
9 .Import Flex fields
10.Import or Derive Accounting Info
What is inside AutoInvoice
AutoInvoice is a program set consists of 3 main programs. Each program will have unique nature of work to do and they are called internally except Purge program whose execution is derived on the setup otherwise ready to execute stand alone.
Master (RAXMTR)
Import (RAXTRX)
Purge (RAXDEL)
1. Auto Invoice Master program RAXMTR
Selects and marks records in the interface tables to be processed based on the parameters the user entered and then calls the AutoInvoice Import program. Auto Invoice Master program has no report output.
•Gathers statistics, it means it gathers the stats on interface tables and set the stats on certain indices on interface tables
•Marks interface records for processing by marking request_id
•Submits multiple workers for Parallel Processing by creating instances for request.
2. Auto Invoice Import Program
Validates the selected record and creates transaction if it passes validation. Any record that fails validation is left in the interface table with an error code. Depending on the setup, related records may be rejected as well. This program has an output file called Auto Invoice Execution report, which you can view by clicking the View Report button in the Requests window.Working of Auto invoice , Validates data, Inserts records, Deletes interface data Only when system option purge set to ‘Y’
3. Auto Invoice Purge Program
Deletes records from the interface tables. If you set the Purge Interface Table system option to No in Define System Option window, Auto Invoice does not delete processed records from the interface tables after each run,and we must submit Auto Invoice Purge Program periodically to clean up the interface tables. This program only deletes transaction lines that have been successfully imported.Deletes all rows where interface_status =‘P’
• Ra_interface_lines
• Ra_interface_distributions
• Ra_interface_salescredits
Oracle Receivable’s Auto Invoice program will be used to import and validate Invoices. A custom feeder program is required to transfer data from the Advantage extract files and populate the Auto Invoice interface tables (RA_INTERFACE_LINES_ALL and RA_INTERFACE_DISTRIBUTIONS_ALL).If there is need to run populate sales credit into RA_INTERFACE_SALESCREDITS_ALL table. When run, AutoInvoice produces the AutoInvoice Execution Report and the AutoInvoice Validation Report. Any entries which failed validation can be reviewed in Oracle Receivables’ AutoInvoice Interface Exceptions window. Depending on the error, changes may need to be made in Receivables, the feeder program or the imported records in the interface tables.
How Autoinvoice Execution works
Normally, Auto Invoice can be divided into three major phases, Pre-grouping: here the validates all of the line level data takes place, Grouping: groups lines based on the grouping rules and validates header level data, Transfer :validates information that exists in Receivables tables
What happen when AutoInvoice run?
Once the Auto invoice Program gets called, the following activity takes place is part of execution process. This can be analyzed by debug options.Line, accounting, and sales credit information for each line populates 3 interface tables
Lines are ordered and grouped
Tax is calculated
GL date is determined
GL accounts are assigned using Auto Accounting
Tax, freight, commitments, and credit memos are linked to transaction lines
All transactions are batched
Validated lines are used to create the transaction
How Data is flowing?
Select, insert and update and delete take place on certain tables once it is logged out.
Selects
– RA_INTERFACE_LINES_ALL
– RA_INTERFACE_DISTRIBUTIONS_ALL
– RA_INTERFACE_SALESCREDITS_ALL
Updates/Insert
– RA_INTERFACE_ERRORS_ALL
– RA_CUSTOMER_TRX_ALL
– RA_CUSTOMER_TRX_LINES_ALL
– AR_PAYMENT_SCHEDULES_ALL
– AR_RECEIVABLE_APPLICATIONS_ALL
Inserts
– RA_INTERFACE_ERRORS_ALL
AutoInvoice Exception Handling
Records that fail validation are called ‘Exceptions’. Exceptions stay in Interface Tables which is RA_INTERFACE_ERRORS_ALL. Errors can be corrected in the Exception Handling window. Once corrections are made, Auto invoice must be resubmitted. Records that pass validation get transferred to Receivables tables
AutoInvoice Exception Handling Windows
-Interface Exception window displays exception messages associated with all invalid records
-Interface Lines window displays records that fail validation, provides an error message and can be used to correct the errors
-The Line Errors windows displays errors associated with a specific line, and can only be opened from Interface Lines window
-Interface Exceptions window displays Interface Id, Exception Type, Error Message and Invalid Value associated to the error
-Data cannot be edited in this window, but error can be viewed and corrected by clicking the Details button
-Error Message and Column name with invalid data are displayed in the Message column, and the invalid value that needs to
In R12 what is MO_GLOBAL.INIT
Purpose of mo_global.init :-
It will check if new Multi Org Security Profile is set, to decide if new Security Profile method will be used.
If the new MO security profile is set, then mo_global.init inserts one record, for each Organization in Org Hierarchy, in table mo_glob_org_access_tmp
When & from where is mo_global.init called ?
This package procedure will be called as soon as you login or as soon as you switch responsibility. Just like
FND_GLOBAL.INITIALIZE is called. It is safe to assume that Oracle will invoke MO_GLOBAL.INIT after FND_GLOBAL.INITIALIZE
From SQL*Plus, you can simulate login to a specific responsibility by calling
a.Call FND_GLOBAL.INITIALIZE
This will set your responsibility id, user_id etc
b. call MO_GLOBAL.INIT
This will read the MO profile option values for your responsibility/user, and will initialize the Multi Org Access.
Introduction to LINUX
Introduction to LINUX
Typical Installation of Linux is as simple as installing windows operating systems. Custom installation requires some expertise in understanding the Linux directory structure. Linux always takes the unformatted partition to load operating system
Directory structure
/
root bin etc usr home dev lib boot .. d01
‘/’ is the starting point, under this various default, user-defined subdirectories like d01 in which oracle applications is created
Logging to Linux server using telnet will take the user to user’s home directory.Eg. For user1 the home directory is /home/user1
Using Telnet
Telnet provides user support for the Telnet protocol, a remote access protocol you can use to log on to a remote computer , network device, or private TCP/IP network.
• To open Telnet, click Start, click Run, and then type telnet. You can also type telnet at the
command prompt.
• Using Telnet, you can make a remote connection.
• To use Telnet, you must have the TCP/IP protocol installed and configured on your computer and you must have a user account established on a remote host.
• To display help for Telnet, type help at the command prompt.
Linux Commands
ls - list directory contents
ls [OPTION]... [FILE]...
-a displays all the files including starting with .
-A displays all the files excluding implicit “.” “..”
-t sort by modification time
-r reverse order
-R recursively displays the files , subdirectories and it’s contents
-s print the size of each file, in blocks
-S sort by file size
-l displays long format
example: when you use ls -ls
4 -rw-r--r-- 1 sdaggupa users 803 Apr 1 10:10 test.c
4 drwxr-xr-x 3 sdaggupa users 4096 Apr 7 11:00 conv
[number of operating system blocks; permissions (d- directory,- file); unknown; username; usergroup; size in bytes; month; date; time; file or directory name]
clear – Clears the screen
more -
mkdir - Create the DIRECTORY(ies), if they do not already exist.
mkdir [OPTION] DIRECTORY...
-m, --mode=MODE set permission mode (as in chmod), not rwxrwxrwx - umask
-p, --parents no error if existing, make parent directories as needed
cd - change directory
cd [ or or ] relative path : path from the working directory absolute path : complete path from “/” virtual path : path defined in the current environment
cd -changes to user’s home directory
cp - copy files and directories
cp [OPTION]... SOURCE DEST
cp [OPTION]... SOURCE... DIRECTORY
OPTION -R copies the source (including subdirectories and its contents) recursively.
-u copy only when the file is newer than the destination, and when destination file is missing.
mv - move (rename) files
mv [OPTION]... SOURCE DEST
mv [OPTION]... SOURCE... DIRECTORY
Rename SOURCE to DEST, or move SOURCE(s) to DIRECTORY.
-f, --force never prompt before overwriting
-i, --interactive prompt before overwriteNote: Practice well before using otherwise you may not get back your file some times
rm - remove files or directories
rm [OPTION]... FILE...
This manual page documents the GNU version of rm. rm removes each specified file. By default, it does not remove directories.
-f, --force ignore nonexistent files, never prompt
-i, --interactive prompt before any removal
-r, -R, --recursive remove the contents of directories recursively
rmdir - remove empty directory
chmod - change file access permissions
chmod [OPTION]... MODE[,MODE]... FILE...
-R to change the mode recursively if it is directory
ex: $chmod 755 test.cafter executing the above command test.c will have rwx permissions for the user, r-x permissions for group, r-x permissions for Other users
chgrp - chgrp - change group ownership
chgrp [OPTION]... GROUP FILE...
-R to change the group ownership recursively if it is Directory
Triggers concepts
Data Dictionary Views Related
To DDL Triggers trigger$
dba_triggers all_triggers user_triggers
System Privileges Related To Table Triggers create trigger
create any trigger
administer database trigger
alter any trigger
drop any trigger
Table Trigger Firing Options -- before constraints are applied
BEFORE INSERT
BEFORE UPDATE
BEFORE DELETE
-- after constraints are applied
AFTER INSERT
AFTER UPDATE
AFTER DELETE
Transaction Model Oracle transactions are atomic. No commit or rollback are allowed in a trigger.
Maximum trigger size 32K - but you can call procedures and function in triggers to perform processing
Create Statement Level Triggers (the default)
Statement Level Trigger With A Single Action CREATE OR REPLACE TRIGGER
[]
[OR OR ]
ON
[FOLLOWS ]
DECLARE
BEGIN
EXCEPTION
END ;
/
CREATE TABLE orders (
somecolumn VARCHAR2(20),
numbercol NUMBER(10),
datecol DATE);
CREATE OR REPLACE TRIGGER statement_level
BEFORE UPDATE
ON orders
DECLARE
vMsg VARCHAR2(30) := 'Statement Level Trigger Fired';
BEGIN
dbms_output.put_line(vMsg);
END statement_level;
/
set serveroutput on
INSERT INTO orders (somecolumn) VALUES ('ABC');
UPDATE orders SET somecolumn = 'XYZ';
Statement Level Trigger With Multiple Actions
CREATE OR REPLACE TRIGGER statement_level
AFTER INSERT OR UPDATE OR DELETE
ON orders
DECLARE
vMsg VARCHAR2(30) := 'Statement Level Trigger Fired';
BEGIN
IF INSERTING THEN
dbms_output.put_line(vMsg || ' When Inserting');
ELSIF UPDATING THEN
dbms_output.put_line(vMsg || ' When Updating');
ELSIF DELETING THEN
dbms_output.put_line(vMsg || ' When Deleting');
END IF;
END statement_level;
/
set serveroutput on
INSERT INTO orders (somecolumn) VALUES ('ABC');
UPDATE orders SET somecolumn = 'DEF' WHERE ROWNUM = 1;
DELETE FROM orders WHERE ROWNUM = 1;
Create Row Level Triggers
Note: AFTER row triggers create less UNDO than BEFORE row triggers so use AFTER when possible.
Row Level Trigger ... most common usage to provide a surrogate key from a sequence CREATE OR REPLACE TRIGGER
[FOLLOWS ]
[]
OR OR
[OF ]
ON
REFERENCING NEW AS OLD AS PARENT AS
FOR EACH ROW
DECLARE
BEGIN
EXCEPTION
END ;
/
CREATE TABLE t (
rid NUMBER(5),
col VARCHAR2(3));
ALTER TABLE t
ADD CONSTRAINT pk_t
PRIMARY KEY (rid)
USING INDEX;
CREATE SEQUENCE seq_t;
CREATE OR REPLACE TRIGGER row_level
BEFORE INSERT
ON t
FOR EACH ROW
BEGIN
SELECT seq_t.NEXTVAL
INTO:NEW.rid
FROM dual;
dbms_output.put_line(:NEW.rid);
END row_level;
/
INSERT INTO t (col) VALUES ('A');
INSERT INTO t (col) VALUES ('B');
INSERT INTO t (col) VALUES ('C');
SELECT * FROM t;
Row Level Trigger With A Single Action
CREATE OR REPLACE TRIGGER row_level
BEFORE UPDATE
ON orders
FOR EACH ROW
DECLARE
vMsg VARCHAR2(30) := 'Row Level Trigger Fired';
BEGIN
dbms_output.put_line(vMsg);
END row_level;
/
set serveroutput on
INSERT INTO orders (somecolumn) VALUES ('ABC');
INSERT INTO orders (somecolumn) VALUES ('ABC');
INSERT INTO orders (somecolumn) VALUES ('ABC');
SELECT * FROM orders;
UPDATE orders SET somecolumn = 'XYZ';
Row Level Trigger With Multiple Actions
CREATE OR REPLACE TRIGGER statement_level
AFTER INSERT OR UPDATE OR DELETE
ON orders
FOR EACH ROW
DECLARE
vMsg VARCHAR2(30) := 'Row Level Trigger Fired';
BEGIN
IF INSERTING THEN
dbms_output.put_line(vMsg || ' On Insert');
ELSIF UPDATING THEN
dbms_output.put_line(vMsg || ' On Update');
ELSIF DELETING THEN
dbms_output.put_line(vMsg || ' On Delete');
END IF;
END statement_level;
/
set serveroutput on
INSERT INTO orders (somecolumn) VALUES ('ABC');
UPDATE orders
SET somecolumn = 'ZZT';
DELETE FROM orders WHERE rownum < 4;
Row Level Trigger With OF Clause
CREATE OR REPLACE TRIGGER of_clause
BEFORE UPDATE
OF numbercol
ON orders
FOR EACH ROW
DECLARE
vMsg VARCHAR2(40) := 'Update Will Change numbercol Column';
BEGIN
dbms_output.put_line(vMsg);
END of_clause;
/
set serveroutput on
UPDATE orders
SET numbercol = 8;
=========
No comments:
Post a Comment