Single Row Function and Group Function

Oracle Built in Functions

There are two types of functions in Oracle.
1) Single Row Functions: Single row or Scalar functions return a value for every row that is processed in a query.
2) Group Functions: These functions group the rows of data based on the values returned by the query. This is discussed in SQL GROUP Functions. The group functions are used to calculate aggregate values like total or average, which return just one total or one average value after processing a group of rows.

There are four types of single row functions. They are:
1) Numeric Functions: These are functions that accept numeric input and return numeric values.
2) Character or Text Functions: These are functions that accept character input and can return both character and number values.
3) Date Functions: These are functions that take values that are of datatype DATE as input and return values of datatype DATE, except for the MONTHS_BETWEEN function, which returns a number.
4) Conversion Functions: These are functions that help us to convert a value in one form to another form. For Example: a null value into an actual value, or a value from one datatype to another datatype like NVL, TO_CHAR, TO_NUMBER, TO_DATE etc.

You can combine more than one function together in an expression. This is known as nesting of functions.

What is a DUAL Table in Oracle?
This is a single row and single column dummy table provided by oracle. This is used to perform mathematical calculations without using a table.

Select * from DUAL

Output:

DUMMY
——-
X

Select 777 * 888 from Dual

Output:

777 * 888
———
689976

1) Numeric Functions:

Numeric functions are used to perform operations on numbers. They accept numeric values as input and return numeric values as output. Few of the Numeric functions are:

Function Name	Return Value
ABS (x)	Absolute value of the number ‘x‘
CEIL (x)	Integer value that is Greater than or equal to the number ‘x‘
FLOOR (x)	Integer value that is Less than or equal to the number ‘x‘
TRUNC (x, y)	Truncates value of number ‘x‘ up to ‘y‘ decimal places
ROUND (x, y)	Rounded off value of the number ‘x‘ up to the number ‘y‘ decimal places

The following examples explains the usage of the above numeric functions

Function Name	Examples	Return Value
ABS (x)	ABS (1) ABS (-1)	1 -1
CEIL (x)	CEIL (2.83) CEIL (2.49) CEIL (-1.6)	3 3 -1
FLOOR (x)	FLOOR (2.83) FLOOR (2.49) FLOOR (-1.6)	2 2 -2
TRUNC (x, y)	ROUND (125.456, 1) ROUND (125.456, 0) ROUND (124.456, -1)	125.4 125 120
ROUND (x, y)	TRUNC (140.234, 2) TRUNC (-54, 1) TRUNC (5.7) TRUNC (142, -1)	140.23 54 5 140

These functions can be used on database columns.

For Example: Let’s consider the product table used in sql joins. We can use ROUND to round off the unit_price to the nearest integer, if any product has prices in fraction.

SELECT ROUND (unit_price) FROM product;

2) Character or Text Functions:

Character or text functions are used to manipulate text strings. They accept strings or characters as input and can return both character and number values as output.

Few of the character or text functions are as given below:

Function Name	Return Value
LOWER (string_value)	All the letters in ‘string_value’ is converted to lowercase.
UPPER (string_value)	All the letters in ‘string_value’ is converted to uppercase.
INITCAP (string_value)	All the letters in ‘string_value’ is converted to mixed case.
LTRIM (string_value, trim_text)	All occurrences of ‘trim_text’ is removed from the left of ‘string_value’.
RTRIM (string_value, trim_text)	All occurrences of ‘trim_text’ is removed from the right of ‘string_value’ .
TRIM (trim_text FROM string_value)	All occurrences of ‘trim_text’ from the left and right of ‘string_value’ , ‘trim_text’ can also be only one character long .
SUBSTR (string_value, m, n)	Returns ‘n’ number of characters from ‘string_value’ starting from the ‘m’ position.
LENGTH (string_value)	Number of characters in ‘string_value’ in returned.
LPAD (string_value, n, pad_value)	Returns ‘string_value’ left-padded with ‘pad_value’ . The length of the whole string will be of ‘n’ characters.
RPAD (string_value, n, pad_value)	Returns ‘string_value’ right-padded with ‘pad_value’ . The length of the whole string will be of ‘n’ characters.

For Example, we can use the above UPPER() text function with the column value as follows.

SELECT UPPER (product_name) FROM product;

The following examples explains the usage of the above character or text functions

Function Name	Examples	Return Value
LOWER(string_value)	LOWER(‘Good Morning’)	good morning
UPPER(string_value)	UPPER(‘Good Morning’)	GOOD MORNING
INITCAP(string_value)	INITCAP(‘GOOD MORNING’)	Good Morning
LTRIM(string_value, trim_text)	LTRIM (‘Good Morning’, ‘Good)	Morning
RTRIM (string_value, trim_text)	RTRIM (‘Good Morning’, ‘ Morning’)	Good
TRIM (trim_text FROM string_value)	TRIM (‘o’ FROM ‘Good Morning’)	Gd Mrning
SUBSTR (string_value, m, n)	SUBSTR (‘Good Morning’, 6, 7)	Morning
LENGTH (string_value)	LENGTH (‘Good Morning’)	12
LPAD (string_value, n, pad_value)	LPAD (‘Good’, 6, ‘*’)	**Good
RPAD (string_value, n, pad_value)	RPAD (‘Good’, 6, ‘*’)	Good**

3) Date Functions:

These are functions that take values that are of datatype DATE as input and return values of datatypes DATE, except for the MONTHS_BETWEEN function, which returns a number as output.

Few date functions are as given below.

Function Name	Return Value
ADD_MONTHS (date, n)	Returns a date value after adding ‘n’ months to the date ‘x’.
MONTHS_BETWEEN (x1, x2)	Returns the number of months between dates x1 and x2.
ROUND (x, date_format)	Returns the date ‘x’ rounded off to the nearest century, year, month, date, hour, minute, or second as specified by the ‘date_format’.
TRUNC (x, date_format)	Returns the date ‘x’ lesser than or equal to the nearest century, year, month, date, hour, minute, or second as specified by the ‘date_format’.
NEXT_DAY (x, week_day)	Returns the next date of the ‘week_day’ on or after the date ‘x’ occurs.
LAST_DAY (x)	It is used to determine the number of days remaining in a month from the date ‘x’ specified.
SYSDATE	Returns the systems current date and time.
NEW_TIME (x, zone1, zone2)	Returns the date and time in zone2 if date ‘x’ represents the time in zone1.

The below table provides the examples for the above functions

Function Name	Examples	Return Value
ADD_MONTHS ( )	ADD_MONTHS (’16-Sep-81′, 3)	16-Dec-81
MONTHS_BETWEEN( )	MONTHS_BETWEEN (’16-Sep-81′, ’16-Dec-81′)	3
NEXT_DAY( )	NEXT_DAY (’01-Jun-08′, ‘Wednesday’)	04-JUN-08
LAST_DAY( )	LAST_DAY (’01-Jun-08′)	30-Jun-08
NEW_TIME( )	NEW_TIME (’01-Jun-08′, ‘IST’, ‘EST’)	31-May-08

4) Conversion Functions:

These are functions that help us to convert a value in one form to another form. For Ex: a null value into an actual value, or a value from one datatype to another datatype like NVL, TO_CHAR, TO_NUMBER, TO_DATE.

Few of the conversion functions available in oracle are:

Function Name	Return Value
TO_CHAR (x [,y])	Converts Numeric and Date values to a character string value. It cannot be used for calculations since it is a string value.
TO_DATE (x [, date_format])	Converts a valid Numeric and Character values to a Date value. Date is formatted to the format specified by ‘date_format’.
NVL (x, y)	If ‘x’ is NULL, replace it with ‘y’. ‘x’ and ‘y’ must be of the same datatype.
DECODE (a, b, c, d, e, default_value)	Checks the value of ‘a’, if a = b, then returns ‘c’. If a = d, then returns ‘e’. Else, returns default_value.

The below table provides the examples for the above functions

Function Name	Examples	Return Value
TO_CHAR ()	TO_CHAR (3000, ‘$9999’) TO_CHAR (SYSDATE, ‘Day, Month YYYY’)	$3000 Monday, June 2008
TO_DATE ()	TO_DATE (’01-Jun-08′)	01-Jun-08
NVL ()	NVL (null, 1)	1

how to use DBMS_SCHEDULER in PL/SQL

Step1- Create 1 procedure in database.

CREATE OR REPLACE PROCEDURE XXX_UPD_ASSEST_PRC (P_ASSET_NUMBER VARCHAR2)
AS
CURSOR cur_loc_update
IS
SELECT a.asset_number,
fa.asset_id,
‘IND CORP BOOK’ book_type_code,
a.from_site location_from,
a.to_site location_to,
fdh.UNITS_ASSIGNED units,
a.segment7,
a.segment1,
a.segment3,
a.from_segment3 from_segment3,
a.flag
FROM xxbackup.XXX_fa_loc_update2809 a,
apps.fa_distribution_history fdh,
apps.fa_additions_b fa,
apps.fa_locations fl
WHERE 1 = 1
AND a.asset_number = fa.asset_number
AND fa.asset_id = fdh.asset_id
AND fdh.location_id = fl.location_id
AND DATE_INEFFECTIVE IS NULL
AND fl.segment1 = a.from_site
and a.asset_number = P_ASSET_NUMBER;

CURSOR cur_unit_trans (
p_asset_id IN NUMBER,
p_book_type_code IN VARCHAR2,
p_location_from IN VARCHAR2,
p_location_to IN VARCHAR2,
p_units IN NUMBER,
p_segment7 IN VARCHAR2,
p_segment1 IN VARCHAR2,
p_from_segment3 IN VARCHAR2,
p_segment3 IN VARCHAR2)
IS
SELECT asset_id,
book_type_code,
distribution_id,
units_assigned,
code_combination_id dist_code_combination_id,
dist_account,
location_id,
site_id,
delta_units,
source_type
FROM (SELECT fdh.asset_id asset_id,
fdh.book_type_code,
distribution_id,
units_assigned,
fdh.code_combination_id,
gcc.concatenated_segments dist_account,
fl.location_id,
fl.segment1 site_id,
TO_NUMBER (p_units) * (-1) delta_units,
‘FA’ source_type
FROM apps.fa_distribution_history fdh,
apps.fa_locations fl,
apps.fa_additions_b fa,
apps.gl_code_combinations_kfv gcc
WHERE 1 = 1
AND fdh.asset_id = p_asset_id
AND book_type_code = p_book_type_code
AND date_ineffective IS NULL
AND fa.asset_id = fdh.asset_id
AND fdh.location_id = fl.location_id
AND fdh.code_combination_id = gcc.code_combination_id
AND fl.segment3 = p_from_segment3
and fl.segment1= p_location_from
UNION ALL
SELECT p_asset_id asset_id,
p_book_type_code book_type_code,
NULL,
0,
(apps.fnd_flex_ext.get_ccid (
‘SQLGL’,
‘GL#’,
101,
TRUNC (SYSDATE),
(SELECT NVL (RTRIM (LTRIM (p_segment1)),
gcci.segment1)
|| ‘.’
|| gcci.segment2
|| ‘.’
|| gcci.segment3
|| ‘.’
|| gcci.segment4
|| ‘.’
|| (p_location_to)
|| ‘.’
|| gcci.segment6
|| ‘.’
|| NVL ( (p_segment7), gcci.segment7)
FROM apps.gl_code_combinations gcci,
apps.fa_distribution_history fdhi
WHERE 1 = 1
AND fdhi.code_combination_id =
gcci.code_combination_id
AND fdhi.asset_id = p_asset_id
AND fdhi.book_type_code = p_book_type_code
AND fdhi.date_ineffective IS NULL
AND ROWNUM = 1))),
(SELECT NVL (p_segment1, gcci.segment1)
|| ‘.’
|| gcci.segment2
|| ‘.’
|| gcci.segment3
|| ‘.’
|| gcci.segment4
|| ‘.’
|| p_location_to
|| ‘.’
|| gcci.segment6
|| ‘.’
|| NVL (p_segment7, gcci.segment7)
— added nvl
FROM apps.gl_code_combinations gcci,
apps.fa_distribution_history fdhi
WHERE 1 = 1
AND fdhi.code_combination_id =
gcci.code_combination_id
AND fdhi.asset_id = p_asset_id
AND fdhi.book_type_code = p_book_type_code
AND fdhi.date_ineffective IS NULL
AND ROWNUM = 1),
(SELECT location_id
FROM apps.fa_locations fl
WHERE 1=1
AND fl.segment1 = p_location_to
AND fl.segment3= p_segment3
AND enabled_flag = ‘Y’
AND TRUNC (SYSDATE) BETWEEN NVL (
start_date_active,
TRUNC (SYSDATE))
AND NVL (
end_date_active,
TRUNC (SYSDATE)
+ 1)),
p_location_to site_id,
TO_NUMBER (p_units) delta_units,
‘New’ source_type
FROM apps.fa_locations fa
WHERE 1 = 1
AND fa.segment1 = p_location_to
AND fa.segment3= p_segment3
AND NVL (fa.end_date_active, TRUNC (SYSDATE) + 1) >=
TRUNC (SYSDATE)
AND NVL (fa.enabled_flag, ‘Y’) = ‘Y’);

l_return_status VARCHAR2 (1);
l_msg_count NUMBER;
l_mesg_count NUMBER;
l_calling_fn VARCHAR2 (30);
l_msg_data VARCHAR2 (2000);
l_tbl_ct NUMBER;
l_trans_rec apps.fa_api_types.trans_rec_type;
l_asset_hdr_rec apps.fa_api_types.asset_hdr_rec_type;
l_asset_dist_tbl apps.fa_api_types.asset_dist_tbl_type;
l_mesg VARCHAR2 (2000) := NULL;
l_ccid VARCHAR2 (2000);
p_ret_msg VARCHAR2 (2000) := NULL;
— l_asset_id Number;

BEGIN

DBMS_output.put_line(‘Block execution :’|| to_char(sysdate,’DD-MON-YYYY HH24:MI:SS’));

FOR i_loc_update IN cur_loc_update
LOOP
DBMS_output.put_line(‘For ASSET_NUMBER :’);

DBMS_output.put_line(i_loc_update.asset_number);    
 
 l_tbl_ct := 1;
 
DBMS_output.put_line('Before CCID :'|| to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
 
 BEGIN
    SELECT (apps.fnd_flex_ext.get_ccid (
               'SQLGL',
               'GL#',
               101,
               TRUNC (SYSDATE),
               (SELECT    NVL (i_loc_update.segment1, gcci.segment1)
                       || '.'
                       || gcci.segment2
                       || '.'
                       || gcci.segment3
                       || '.'
                       || gcci.segment4
                       || '.'
                       || i_loc_update.segment3
                       || '.'
                       || gcci.segment6
                       || '.'
                       || NVL (i_loc_update.segment7, gcci.segment7)
                  FROM apps.gl_code_combinations gcci,
                       apps.fa_distribution_history fdhi
                 WHERE     1 = 1
                       AND fdhi.code_combination_id =
                              gcci.code_combination_id
                       AND fdhi.asset_id = i_loc_update.asset_id
                       AND fdhi.book_type_code =
                              i_loc_update.book_type_code
                       AND fdhi.date_ineffective IS NULL
                       AND ROWNUM = 1)))
              ccid
      INTO l_ccid
      FROM DUAL;
 EXCEPTION
    WHEN OTHERS
    THEN
       apps.fnd_file.put_line (apps.fnd_file.LOG, 'l_ccid' || l_ccid);
 END;
 
  DBMS_output.put_line('After CCID :'|| to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
 
 FOR trans_rec IN cur_unit_trans (i_loc_update.asset_id,
                                  i_loc_update.book_type_code,
                                  i_loc_update.location_from,
                                  i_loc_update.location_to,
                                  i_loc_update.units,
                                  i_loc_update.segment7,
                                  i_loc_update.segment1,
                                  i_loc_update.from_segment3,
                                  i_loc_update.segment3)
 LOOP
 
  DBMS_output.put_line('Inside trans loop and before if conditions  :'|| to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
    l_return_status := NULL;
    l_asset_hdr_rec.asset_id := trans_rec.asset_id;
    l_asset_hdr_rec.book_type_code := trans_rec.book_type_code;
    l_asset_dist_tbl (l_tbl_ct).distribution_id :=
       trans_rec.distribution_id;
    l_asset_dist_tbl (l_tbl_ct).distribution_id :=
       trans_rec.distribution_id;
 
    IF (trans_rec.distribution_id IS NOT NULL)
    THEN
       l_asset_dist_tbl (l_tbl_ct).transaction_units :=
          trans_rec.delta_units;
       l_asset_dist_tbl (l_tbl_ct).expense_ccid :=
          trans_rec.dist_code_combination_id;
       l_asset_dist_tbl (l_tbl_ct).location_ccid := trans_rec.location_id;
       l_asset_dist_tbl (l_tbl_ct).assigned_to := NULL;
    ELSE
       l_asset_dist_tbl (l_tbl_ct).transaction_units :=
          trans_rec.delta_units;
       l_asset_dist_tbl (l_tbl_ct).units_assigned :=
          trans_rec.delta_units;
       l_asset_dist_tbl (l_tbl_ct).expense_ccid :=
          trans_rec.dist_code_combination_id;
       l_asset_dist_tbl (l_tbl_ct).location_ccid := trans_rec.location_id;
       l_asset_dist_tbl (l_tbl_ct).assigned_to := NULL;
    END IF;
 
 
    l_tbl_ct := l_tbl_ct + 1;
    l_return_status := NULL;
    l_msg_count := NULL;
    l_msg_data := NULL;
 END LOOP;
 
  DBMS_output.put_line('After trans end loop :'|| to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
 
   DBMS_output.put_line('Before API call :'|| to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
 
 
 apps.fa_transfer_pub.do_transfer (
    p_api_version        => 1.0,
    p_init_msg_list      => apps.fnd_api.g_true,
    p_commit             => apps.fnd_api.g_false,
    p_validation_level   => apps.fnd_api.g_valid_level_full,
    p_calling_fn         => l_calling_fn,
    x_return_status      => l_return_status,
    x_msg_count          => l_msg_count,
    x_msg_data           => l_msg_data,
    px_trans_rec         => l_trans_rec,
    px_asset_hdr_rec     => l_asset_hdr_rec,
    px_asset_dist_tbl    => l_asset_dist_tbl);
 
DBMS_output.put_line('After API call :'|| to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
DBMS_output.put_line('l_return_status ::'|| l_return_status);
 --COMMIT;
 IF (NVL (l_return_status, 'X') <> apps.fnd_api.g_ret_sts_success)
 THEN
    l_mesg_count := apps.fnd_msg_pub.count_msg;
 
    IF l_mesg_count > 0
    THEN
       l_mesg :=
             CHR (10)
          || SUBSTR (
                apps.fnd_msg_pub.get (apps.fnd_msg_pub.g_first,
                                      apps.fnd_api.g_false),
                1,
                250);
 
 
 
       FOR i IN 1 .. (l_mesg_count - 1)
       LOOP
          l_mesg :=
             SUBSTR (
                apps.fnd_msg_pub.get (apps.fnd_msg_pub.g_next,
                                      apps.fnd_api.g_false),
                1,
                250);
       END LOOP;
 
       apps.fnd_msg_pub.delete_msg ();
    END IF;
 
    -----------------------------------end---------------------------------------------------------------------
    apps.fnd_msg_pub.count_and_get (p_count   => l_msg_count,
                                    p_data    => l_msg_data);
 
    p_ret_msg :=
       'Error while asset transfer - apps.fa_transfer_pub.do_transfer';
    DBMS_OUTPUT.put_line ('p_ret_msg ==> ' || p_ret_msg);
 
 ELSE
    p_ret_msg := NULL;
 
 END IF;

END LOOP;

COMMIT;
EXCEPTION
WHEN OTHERS
THEN
p_ret_msg := ‘Error while asset transfer.’ || SQLERRM;

1	`DBMS_OUTPUT.put_line ('p_ret_msg ==> ' \|\| p_ret_msg);`

END;
/

Step2-

DECLARE
CURSOR cur_loc_update
IS
SELECT DISTINCT a.asset_number
FROM xxbackup.XXX_fa_loc_update2809 a,
apps.fa_distribution_history fdh,
apps.fa_additions_b fa,
apps.fa_locations fl
WHERE 1 = 1
AND a.asset_number = fa.asset_number
AND fa.asset_id = fdh.asset_id
AND fdh.location_id = fl.location_id
AND DATE_INEFFECTIVE IS NULL
AND fl.segment1 = a.from_site;

BEGIN

DBMS_SCHEDULER.CREATE_PROGRAM(
    PROGRAM_NAME => 'XXX_FA_UPD_ASSET_PROGRAM'
    ,PROGRAM_TYPE => 'STORED_PROCEDURE'
    ,PROGRAM_ACTION => 'XXX_UPD_ASSEST_PRC'
    ,NUMBER_OF_ARGUMENTS => 1
    ,ENABLED =>  FALSE
    ,COMMENTS => 'my test program'
);
 
DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT(
    PROGRAM_NAME => 'XXX_FA_UPD_ASSET_PROGRAM',
    argument_name     => 'P_ASSET_NUMBER',
    argument_position => 1,
    argument_type     => 'VARCHAR2',
    default_value     => ''
);
 
dbms_scheduler.enable (name => 'XXX_FA_UPD_ASSET_PROGRAM');

FOR i_loc_update IN cur_loc_update
LOOP

   DBMS_output.put_line('For ASSET_NUMBER :'|| i_loc_update.ASSET_NUMBER);
 
     DBMS_SCHEDULER.CREATE_JOB (
    job_name => 'MY_TEST_JOB_'||i_loc_update.ASSET_NUMBER||'_5',
    PROGRAM_NAME => 'XXX_FA_UPD_ASSET_PROGRAM'
);
 
dbms_scheduler.set_job_argument_value(
    job_name          => 'MY_TEST_JOB_'||i_loc_update.ASSET_NUMBER||'_5',
    argument_position => 1,
    argument_value    => i_loc_update.ASSET_NUMBER);
 
 
DBMS_SCHEDULER.RUN_JOB(
    JOB_NAME            => 'MY_TEST_JOB_'||i_loc_update.ASSET_NUMBER||'_5',
    USE_CURRENT_SESSION => FALSE);
 
END LOOP;

END;

Register Oracle Views from One Instance to Another Instance

PLSQL SCRIPT TO REGISTER ORACLE VIEWS FROM ONE INSTANCE TO ANOTHER INSTANCE AUTOMATICALLY IN ORACLE APPS

In this post, I will share you one method in which you can extract the Oracle Views registration details in few seconds using this below script.

As an example, you are working on some project in development instance and you have created around 40 views for this and now you want to register these 40 views in other instance and it’s a time-consuming activity.

By this below query, you just need to execute this below script in test instance with proper values as per your requirement and that will create one .sql file in you server will all the 40 Views registration details.

Copy this file from server and put this file in your system and then open sql plus with targeted instance where you want to register these Views and execute this file in sql plus for that instance and your Views will be register there in few seconds.

For Views

declare

a1 LONG;

b LONG;

C1 LONG;

TEST_FILE Utl_File.File_Type;

FILE_NAME varchar2(400):=’VIEW_NAME _REGISTRATION_FILE’;

BEGIN

TEST_FILE := Utl_File.FOPEN(‘ODPDIR’,FILE_NAME||’.txt’,’W’,32767); — ODPDIR is the Directory

FOR j in ( select OBJECT_NAME from dba_objects

where object_type=’VIEW’

AND OBJECT_NAME like ‘XX_%AP%’)

loop

for i IN (SELECT COLUMN_NAME FROM DBA_TAB_COLUMNS

WHERE TABLE_NAME=j.object_name

ORDER BY COLUMN_ID)

loop

a1:=a1||i.COLUMN_NAME||’,’;

end loop;

C1:=NULL;

SELECT TEXT INTO C1 FROM DBA_VIEWS

WHERE VIEW_NAME=j.object_name;

b:=’create or replace view ‘||j.object_name||’R’||'(‘||rtrim(a1,’,’)||’) AS ‘||C1||’;’||chr(10);

a1:=null;

Utl_File.FOPEN(‘ECX_UTL_XSLT_DIR_OBJ’,FILE_NAME||’.txt’,’W’,32767);

–Utl_File.PUT_LINE(TEST_FILE ,b);

Utl_File.PUT_LINE(FILE => TEST_FILE,

buffer => b,

autoflush => TRUE);

–UTL_FILE.NEW_LINE(TEST_FILE ,1);

B:=NULL;

–dbms_output.put_line(‘1’);

end loop;

if Utl_File.is_open(TEST_FILE) then

Utl_File.FCLOSE(TEST_FILE);

END IF;

EXCEPTION WHEN NO_DATA_FOUND THEN

NULL;

dbms_output.put_line(sqlerrm);

WHEN OTHERS THEN

dbms_output.put_line(sqlerrm);

Comparison and Logical Condition in SQL

Comparison Condition:-

Comparison conditions are used in conditions that compare one expression to another value or expression. These operator are used in WHERE condition.

Comparison Operator:-

1. Equal to (=):-

SELECT *

FROM EMPLOYEES

WHERE EMPLOYEE_ID = 100;

2. GREATER THAN (>):-

SELECT *

FROM EMPLOYEES

WHERE SALARY > 4000;

3. LESS THAN (<):-

SELECT *

FROM EMPLOYEES

WHERE SALARY < 5000;

4. GREATER THAT OR EQUAL TO (>=)

SELECT *

FROM EMPLOYEES

WHERE SALARY >= 4000;

5. LESS THAN OR EQUAL TO (<=):-

SELECT *

FROM EMPLOYEES

WHERE SALARY <= 5000;

6. NOT EQUAL TO (<>):-

SELECT *

FROM EMPLOYEES

WHERE DEPARTMENT_ID <> 50;

Others Comparison Operator:-

1. BETWEEN .. AND ..:- Between two value(include)

SELECT *

FROM EMPLOYEES

WHERE DEPARTMENT_ID BETWEEN (200 AND 300);

2. IN:- Match any of the list of values

SELECT *

FROM EMPLOYEES

WHERE EMPLOYEE_ID IN(101, 110, 200, 220, 109);

3. LIKE:- Match a character pattern.

SELECT *

FROM EMPLOYEES

WHERE LAST_NAME LIKE ‘%A%’;

4. IS NULL:- Is a null value.

SELECT *

FROM EMPLOYEES

WHERE COMMISSION_PCT IS NULL;

Logical Condition:-

1. AND:- If both compare condition are TRUE then it return TRUE.

SELECT *

FROM EMPLOYEES

WHERE SALARY < 20000

AND DEPARTMENT_ID = 20;

2. OR:- If any one compare condition is TRUE then it return TRUE.

SELECT *

FROM EMPLOYEES

WHERE SALARY < 20000

OR DEPARTMENT_ID = 20;

3. NOT:- If condition is false then it return TRUE.

SELECT *

FROM EMPLOYEES

WHERE DEPARTMENT_ID NOT IN(10,50);

Sql, PL/SQL Special Query

Ques:-

TEAM RESULT

A WIN

B WIN

A LOSS

A WIN

B DRAW

C WIN

C LOSS

D LOSS

E DRAW

EXPECTED OUTPUT

TEAM WIN LOSS DRAW

A 2 1 0

B 1 0 1

C 1 1 0

D 0 1 0

E 0 0 1

ANS:-

select TEAM,SUM(CASE RESULT when ‘WIN’ then 1

else 0 end) WIN

,SUM(CASE RESULT when ‘LOSS’ then 1

else 0 end) LOSS

,SUM(CASE RESULT when ‘DRAW’ then 1

else 0 end) DRAW

,SUM((CASE RESULT when ‘WIN’ then 1 else 0 end)+(CASE RESULT when ‘LOSS’ then 1 else 0 end)+(CASE RESULT when ‘DRAW’ then 1 else 0 end)) TOTAL

from xxc05_test1

GROUP BY TEAM

order by 1

———————————————————————-

Ques:- Display the salary of each and every employee in 1991,1992 and 1993.

(hint: use ‘DECODE’ function)

Zigzag

NAME YEAR ATM_NO

————- ———– —————–

JOHN 1991 1000

JOHN 1992 2000

JOHN 1993 3000

JACK 1991 1500

JACK 1992 1200

JACK 1993 1340

MARY 1991 1250

MARY 1992 2323

MARY 1993 8700

EXPECTED OUTPUT

YEAR JOHN JACK MARY

———— ———- ———- ———-

1991 1000 1500 1250

1992 2000 1200 2323

1993 3000 1340 8700

Ans:-

SELECT YEAR,SUM(DECODE(NAME,’JOHN’,ATM_NO,0)) JOHN

,SUM(DECODE(NAME,’JACK’,ATM_NO,0)) JACK

,SUM(DECODE(NAME,’MERRY’,ATM_NO,0)) MERRY

FROM XXC05_TEST2

GROUP BY YEAR

ORDER BY 1

———————————————————————-

Ques:-

Write a Query to display each letter of the world “Happy” in a separate row.

(Hint: use ‘SUBSTRING and CONNECT BY LEVEL’)

Answer table should be

Output

result

—

Ans:-

select substr(‘HAPPY’,LEVEL,1) FROM DUAL

CONNECT BY LEVEL<=LENGTH(‘HAPPY’);

———————————————————————-

Ques:-

My table have some records

NAME ACT_NO

SAJAL 1234567890

AMAL 2345678901

NAVAL 9876542211

—

but i want to print Like thet

******7890

******8901

******2211

Ans:-

SELECT LPAD(SUBSTR(ACT_NO,LENGTH(ACT_NO)-3),LENGTH(ACT_NO),’*’) FROM DUAL

———————————————————————-

Ques:- Convert number to cahr

Ans:-

select to_char(to_date(999999,’j’),’jsp’) from dual;

range is 1 to 5373484

———————————————————————-

Ques:-

Find Nth Highest Salary

Ans:-

select * from

EMPloyees x

where &no=(

select count(*)

from EMPloyees y

where y.employee_id>=x.employee_id

)

———————————————————————-

Ques:- Count the no of Male and Female Candidate in a table.

Ans:-

select SUM(case sex when ‘MALE’ then 1

end )”MALE”

,SUM(case sex when ‘FEMALE’ then 1

end)”FEMALE”

from XXC05_TEST3;

———————————————————————-

Ques:- how to insert a column in a table which is already existing in other table

First you add column name in second table then

Run that query.

DECLARE

BEGIN

FOR I IN (SELECT ID,DEPARTMENT_NAME FROM XXC05_T1)

LOOP

UPDATE XXC05_T2 SET NAME = I.DEPARTMENT_NAME

WHERE ID = I.ID;

END LOOP;

COMMIT;

END;

———————————————————————-

QUES:- HOW TO PRINT MAX,MIN,AVG,SUM OF SALARY AND PRINT ALL COLUMN NAME.

ANS:-

SELECT E.*,MAX(SALARY) OVER() MAX_SALARY ,MIN(SALARY) OVER() MIN_SALARY,ROUND(AVG(SALARY) OVER()) AVG_SALARY,SUM(SALARY) OVER() SUM_SALARY FROM EMPLOYEES E

SELECT

e.*,

(SELECT MAX(salary)

FROM Employees) MAXSAL,

(SELECT SUM(salary)

FROM Employees) SUMSAL

FROM Employees e;

———————————————————————-

Ques:-

HI FRND HOW TO REPLACE

col

a*b@c#d

o/p

a,b,c,d

ANS:-

select ‘a*b@c#d’,regexp_replace(‘a*b@c#d’, ‘[^0-9a-zA-Z]’, ‘,’ ) from dual;

SELECT translate(‘a*b@c#d’,’*@#’,’,,,’) FROM dual

select replace (replace (replace (‘a*b@c#d’,’*’,’,’),’@’,’,’),’#’,’,’) N from dual

———————————————————————-

Ques:- Without use rowid delete duplicate record.

ANS:-

select *

from (select e.empno, rank() over(order by e.deptno desc) rk

from scott.emp e)

———————————————————————-

Ques:- How to find greated, smallest number

Table is

c1 c2 c3

1 4 10

2 5 15

10 20 28

Ouput is 28,1

ANS:-

SELECT MAX(GREATEST(C1,C2,C3)) FROM T1;

SELECT GREATEST(MAX(C1),MAX(C2), MAX(C3)) FROM T1

SELECT LEAST(MIN(C1),MIN(C2),MIN(C3)) FROM T1;

SELECT MIN(LEAST(C1,C2,C3)) FROM T1;

———————————————————————-

QUES:- hi…frd….suppose a number is 2642.how we get output 14 (2+6+4+2) by the use of sql.

ANS:- select sum(s) total from(

select substr(num,level,1) s from(select &g num from dual)

connect by level<=length(num))

SELECT SUBSTR(7654,1,1)+SUBSTR(7654,2,1)+SUBSTR(7654,3,1)+SUBSTR(7654,4,1) FROM DUAL

———————————————————————-

QUES:- HOW TO CHACK STRING IS PALINDROM OR NOT

DECLARE

LEN NUMBER;

PALSTR VARCHAR2(20) := ‘&PALSTR’;

CHKSTR VARCHAR2(20);

BEGIN

LEN := LENGTH(PALSTR);

FOR I IN REVERSE 1..LEN LOOP

CHKSTR := CHKSTR||SUBSTR(PALSTR,I,1);

END LOOP;

IF CHKSTR = PALSTR THEN

DBMS_OUTPUT.PUT_LINE(PALSTR||’ IS A PALINDROME’);

ELSE

DBMS_OUTPUT.PUT_LINE(PALSTR||’ IS NOT A PALINDROME’);

END IF;

END;

———————————————————————-

DIFFERENCE BETWEEN DATASET AND DATATABLE

DataSet:- DataSet is the Collection of DataTables

DataSet can Fetch multiple TablesRows at a time

In DataSet DataTable objects can be related to each other like(primary key, forign key, unique key etc)

DataTable:- DataTable is single datbase table

DataTable Fetch data from only one table.

As DataTable is a single database table, so there is no Data relation object in it.

———————————————————————-

QUES:- How to find Those record in which and special symbol.

SELECT * FROM T11

WHERE REGEXP_LIKE(NAME,’%’);

SELECT * FROM T11

WHERE NAME LIKE ‘%\%%’ ESCAPE ‘\’;

———————————————————————-

QUES:- Given String is 11223344556677 or give it at run time

output is:- 11**********77

Ans:-

SELECT RPAD(SUBSTR(&A,1,2),LENGTH(&&A)-2,’*’)||SUBSTR(&&A,-2,2) FROM DUAL

———————————————————————-

Ques:- In table data is

1 SAJAL MALE

2 AMAL MALE

3 MEENA FEMALE

4 SONAM FEMALE

5 RAHUL MALE

6 SONIA FEMALE

7 NANCY FEMALE

8 NAVAL MALE

My requirement is print record alternative of Gender

1 SAJAL MALE

3 MEENA FEMALE

2 AMAL MALE

4 SONAM FEMALE

5 RAHUL MALE

6 SONIA FEMALE

8 NAVAL MALE

7 NANCY FEMALE

ANS:-

select ROW_NUM,id,name,gender from (

select (rownum*2) ROW_NUM,id,name,gender from XXC05_GENDER

where gender = ‘M’

union

select (rownum*2-1) ROW_NUM,id,name,gender from XXC05_GENDER

where gender = ‘F’

)

order by ROW_NUM

———————————————————————-

QUES:- Print 1 to 100 number in sql

ANS:-

Select Rownum

From dual

Connect By Rownum <= 100

SELECT LEVEL

FROM DUAL

CONNECT BY LEVEL <= 100;

How to check grants on database objects in oracle

SELECT * FROM ALL_TAB_PRIVS
WHERE TABLE_NAME LIKE ‘TABLE_OR_PACKAGE_OR_PROCEDURE_OR_FUNCTION’;

Trigger Example

Trigger:- Trigger is a pl/sql block or procedure that implicitly execute when some event occur.

Application Trigger:- Fire whenever an event occurs with a particular application.
Database Trigger:- Fire whenever a data event(such as DML) or system event(such as logon or shutdown) occur on a schema or database

Trigger Timing
For table:- Before, After
For View:- Instead of

Trigger Event:-
Insert, Update or Delete

Trigger Name:-
On Table, View

Trigger Type:-
Row Level
Statement Level

Trigger Body:-
What Action perform

CREATE OR REPLACE TRIGGER XXC05_TRIGGER001
BEFORE INSERT ON XXC05_TRIGGER_TEST
BEGIN
IF TO_CHAR(SYSDATE,’DY’) = ‘SAT’
THEN
RAISE_APPLICATION_ERROR(-20500,’YOU NOT INSERT DATA INTO XXC05_TRIGGER_TEST TABLE’);
END IF;
END XXC05_TRIGGER_TEST;

CREATE OR REPLACE TRIGGER XXC05_TRIGGER001
BEFORE INSERT OR UPDATE OR DELETE ON XXC05_TRIGGER_TEST
BEGIN
IF TO_CHAR(SYSDATE,’DY’) = ‘SAT’
THEN
IF INSERTING
THEN
RAISE_APPLICATION_ERROR(-20500,’YOU NOT INSERT DATA INTO XXC05_TRIGGER_TEST TABLE’);
ELSIF UPDATING
THEN
RAISE_APPLICATION_ERROR(-20501,’YOU NOT UPDATE DATA INTO XXC05_TRIGGER_TEST TABLE’);
ELSIF DELETING
THEN
RAISE_APPLICATION_ERROR(-20502,’YOU NOT DELETE DATA INTO XXC05_TRIGGER_TEST TABLE’);
END IF;
END IF;
END XXC05_TRIGGER_TEST;

CREATE OR REPLACE TRIGGER XXC05_TRIGGER001
BEFORE INSERT OR UPDATE OR DELETE ON XXC05_TRIGGER_TEST
FOR EACH ROW
BEGIN
IF TO_CHAR(SYSDATE,’DY’) = ‘SAT’
THEN
IF INSERTING
THEN
RAISE_APPLICATION_ERROR(-20500,’YOU NOT INSERT DATA INTO XXC05_TRIGGER_TEST TABLE’);
ELSIF UPDATING
THEN
RAISE_APPLICATION_ERROR(-20501,’YOU NOT UPDATE DATA INTO XXC05_TRIGGER_TEST TABLE’);
ELSIF DELETING
THEN
RAISE_APPLICATION_ERROR(-20502,’YOU NOT DELETE DATA INTO XXC05_TRIGGER_TEST TABLE’);
END IF;
END IF;
END XXC05_TRIGGER_TEST;

WHERE CURRENT OF & FOR UPDATE

The WHERE CURRENCT OF clause in an UPDATE or DELETE statement states that most recent row fetched from the table should be update or deleted. We must declare the cursor with the CURRENCT OF clause to use this feature.

Oracle provide the FOR UPDATE clause in SQL syntax to allow the developer to lock a set of Oracle rows for the duration of transactions.

The syntax of using the WHERE CURRENT OF clause is UPDATE OR DELETE statement.

Syntax:-

WHERE[CURRENT OF cursor_name | search_condition]

Example:-

DECLARE
CURSOR C1 IS
SELECT EMPLOYEE_ID,LAST_NAME
FROM EMPLOYEES
WHERE COMMISSION_PCT IS NULL
FOR UPDATE OF COMMISSION_PCT;
BEGIN
FOR I IN C1
LOOP
UPDATE EMPLOYEES
SET COMMISSION_PCT= 0.19
WHERE CURRENT OF C1;
END LOOP;
END;

Ref Cursor with example

Ref Cursor:-

Ref Cursor is a dynamic cursor in Oracle. We can change cursor definition as run time but it define and declare once.

Two type of Ref Cursor

Week Ref Cursor:- Week ref cursor never return a value.

Strong Ref Cursor:- Strong ref cursor must return a value.

Example of week ref cursor
Example 1-

DECLARE
TYPE XXC_REF_CUR IS REF CURSOR;
XXC_REF_CUR_C1 XXC_REF_CUR;
EMP_ID EMPLOYEES.EMPLOYEE_ID%TYPE;
L_NAME EMPLOYEES.LAST_NAME%TYPE;
BEGIN
OPEN XXC_REF_CUR_C1 FOR
SELECT EMPLOYEE_ID,LAST_NAME
FROM EMPLOYEES;
LOOP
FETCH XXC_REF_CUR_C1 INTO EMP_ID,L_NAME;
EXIT WHEN XXC_REF_CUR_C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(‘EMPLOYEE_ID IS ‘||EMP_ID||’ and ‘||’LAST_NAME IS ‘||L_NAME);
END LOOP;
CLOSE XXC_REF_CUR_C1;
OPEN XXC_REF_CUR_C1 FOR
SELECT DEPARTMENT_ID,DEPARTMENT_NAME
FROM DEPARTMENTS;
LOOP
FETCH XXC_REF_CUR_C1 INTO DEPT_ID,DEPT_NAME;
EXIT WHEN XXC_REF_CUR_C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(‘DEPARTMENT ID IS ‘||DEPT_ID||’ and ‘||’DEPARTMENT NAME IS ‘||DEPT_NAME);
END LOOP;
CLOSE XXC_REF_CUR_C1;
END;

Example 2-

DECLARE
TYPE XXC_REF_CUR IS REF CURSOR;
XXC_REF_CUR_C1 XXC_REF_CUR;
EMP_REC EMPLOYEES%ROWTYPE;
DEPT_REC DEPARTMENTS%ROWTYPE;
BEGIN
OPEN XXC_REF_CUR_C1 FOR
SELECT *
FROM EMPLOYEES;
LOOP
FETCH XXC_REF_CUR_C1 INTO EMP_REC;
EXIT WHEN XXC_REF_CUR_C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(‘EMPLOYEE_ID IS ‘||EMP_REC.EMPLOYEE_ID||’ and ‘||’LAST_NAME IS ‘||EMP_REC.LAST_NAME);
END LOOP;
CLOSE XXC_REF_CUR_C1;
OPEN XXC_REF_CUR_C1 FOR
SELECT *
FROM DEPARTMENTS;
LOOP
FETCH XXC_REF_CUR_C1 INTO DEPT_REC;
EXIT WHEN XXC_REF_CUR_C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(‘DEPARTMENT ID IS ‘||DEPT_REC.DEPARTMENT_ID||’ and ‘||’DEPARTMENT NAME IS ‘||DEPT_REC.DEPARTMENT_NAME);
END LOOP;
CLOSE XXC_REF_CUR_C1;
END;

Example of Strong ref cursor

DECLARE
TYPE XXC_EMP_REF_CUR IS REF CURSOR RETURN EMPLOYEES%ROWTYPE;
XXC_EMP_REF_CUR_C1 XXC_EMP_REF_CUR;
TYPE XXC_DEPT_REF_CUR IS REF CURSOR RETURN DEPARTMENTS%ROWTYPE;
XXC_DEPT_REF_CUR_C1 XXC_DEPT_REF_CUR;
EMP_REC EMPLOYEES%ROWTYPE;
DEPT_REC DEPARTMENTS%ROWTYPE;
BEGIN
OPEN XXC_EMP_REF_CUR_C1 FOR
SELECT *
FROM EMPLOYEES;
LOOP
FETCH XXC_EMP_REF_CUR_C1 INTO EMP_REC;
EXIT WHEN XXC_EMP_REF_CUR_C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(‘EMPLOYEE_ID IS ‘||EMP_REC.EMPLOYEE_ID||’ and ‘||’LAST_NAME IS ‘||EMP_REC.LAST_NAME);
END LOOP;
CLOSE XXC_EMP_REF_CUR_C1;
OPEN XXC_DEPT_REF_CUR_C1 FOR
SELECT *
FROM DEPARTMENTS;
LOOP
FETCH XXC_DEPT_REF_CUR_C1 INTO DEPT_REC;
EXIT WHEN XXC_DEPT_REF_CUR_C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(‘DEPARTMENT ID IS ‘||DEPT_REC.DEPARTMENT_ID||’ and ‘||’DEPARTMENT NAME IS ‘||DEPT_REC.DEPARTMENT_NAME);
END LOOP;
CLOSE XXC_DEPT_REF_CUR_C1;
END;

SYS Ref Cursor:-

DECLARE
XXC_EMP_REF_CUR SYS_REFCURSOR;
EMP_REC EMPLOYEES%ROWTYPE;
BEGIN
OPEN XXC_EMP_REF_CUR FOR SELECT * FROM EMPLOYEES;
LOOP
FETCH XXC_EMP_REF_CUR INTO EMP_REC;
EXIT WHEN XXC_EMP_REF_CUR%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(EMP_REC.EMPLOYEE_ID||CHR(9)||EMP_REC.LAST_NAME||CHR(9)||EMP_REC.HIRE_DATE);
END LOOP;
END;

————————- normal corsor ———————
declare
cursor c1 is select * from employees;
v1 employees%rowtype;
begin
open c1;
for i in 1..3
loop
fetch c1 into v1;
dbms_output.put_line(v1.employee_id);
end loop;
close c1;
end;

—————– week ref cursor ——————–
declare
type week_ref_cursor is ref cursor;
wrf week_ref_cursor;
v1 employees%rowtype;
v2 departments%rowtype;
begin
open wrf for select * from employees;
loop
fetch wrf into v1;
exit when wrf%notfound;
dbms_output.put_line(v1.employee_id||chr(9)||v1.last_name||chr(9)||v1.salary);
end loop;
close wrf;
open wrf for select * from departments;
loop
fetch wrf into v2;
exit when wrf%notfound;
dbms_output.put_line(v2.department_id||chr(9)||v2.department_name);
end loop;
close wrf;
end;

——————- strong ref cursor ————–

declare
type strong_ref_cursor is ref cursor return employees%rowtype;
src strong_ref_cursor;
v1 employees%rowtype;
begin
open src for select * from employees;
loop
fetch src into v1;
exit when src%notfound;
dbms_output.put_line(v1.employee_id);
end loop;
close src;
open src for select * from employees;
loop
fetch src into v1;
exit when src%notfound;
dbms_output.put_line(v1.employee_id||’ ‘||v1.last_name);
end loop;
close src;
end;

***************************************REF CURSOR *******************************

declare
emp_id number;
emp_name varchar2(20);
type test_cur is ref cursor;
v_ref test_cur;
begin
if(no=1)then
open v_ref for select employee_id from employees where rownum<9;
for i in 1..9
loop
fetch v_ref into emp_id;
dbms_output.put_line((emp_id));
end loop;
close v_ref;
elsif(no=2)then
open v_ref for select department_id from employees where rownum<9;
for i in 1..9
loop
fetch v_ref into emp_id;
dbms_output.put_line((emp_id));
end loop;
close v_ref;
else
open v_ref for select last_name from employees where rownum<9;
for i in 1..9
loop
fetch v_ref into emp_name;
dbms_output.put_line((emp_name));
end loop;
close v_ref;
end;
/

***************************************************** PARAMETERIZED CURSOR *************************************

declare
cursor c1(no number)is select employee_id from employees where employee_id<no;
id number;
begin
open c1(198);
for i in 1..10
loop
fetch c1 into id;
dbms_output.put_line(id);
end loop;
close c1;
end;

declare
type p1 is ref cursor;
t1 p1;
v1 varchar2(10);
begin
open t1 for select employee_id from employees;
for i in 1..10
loop
fetch t1 into v1;
dbms_output.put_line(v1);
end loop;
open t1 for select last_name from employees;
for i in 1..10
loop
fetch t1 into v1;
dbms_output.put_line(v1);
end loop;
end;
/

create or replace procedure p1 is
type ref_cursor is ref cursor;
ref1 ref_cursor;
v1 employees%rowtype;
begin
open ref1 for select * from employees;
for i in 1..10
loop
fetch ref1 into v1;
dbms_output.put_line(v1.employee_id||chr(9)||v1.last_name);
end loop;
end;
/

create or replace procedure p1(no number) is
type ref_cursor is ref cursor;
ref1 ref_cursor;
v1 employees%rowtype;
begin
if no=10 then
open ref1 for select * from employees;
for i in 1..10
loop
fetch ref1 into v1;
dbms_output.put_line(v1.employee_id||chr(9)||v1.last_name||chr(9)||v1.salary);
end loop;
elsif no=20 then
open ref1 for select * from employees;
for i in 1..10
loop
fetch ref1 into v1;
dbms_output.put_line(v1.salary||chr(9)||v1.manager_id);
end loop;
else
open ref1 for select * from employees;
for i in 1..10
loop
fetch ref1 into v1;
dbms_output.put_line(v1.manager_id);
end loop;
end if;
end;
/

create or replace procedure p1(no number) is
type ref_cursor is ref cursor;
ref1 ref_cursor;
type v1_table is table of employees%rowtype;
v1 v1_table;
begin
if no=10 then
open ref1 for select * from employees;
for i in 1..10
loop
fetch ref1 bulk collect into v1 limit 10;
for i in v1.first..v1.last
loop
dbms_output.put_line(v1(i).employee_id||chr(9)||v1(i).last_name||chr(9)||v1(i).salary);
end loop;
dbms_output.put_line(chr(10)||v1.count||chr(10));
end loop;
elsif no=20 then
open ref1 for select * from employees;
for i in 1..10
loop
fetch ref1 bulk collect into v1 limit 10;
for i in v1.first..v1.last
loop
dbms_output.put_line(v1(i).manager_id||chr(9)||v1(i).first_name||chr(9)||v1(i).salary);
end loop;
dbms_output.put_line(chr(10)||v1.count||chr(10));
end loop;
else
open ref1 for select * from employees;
for i in 1..10
loop
fetch ref1 bulk collect into v1 limit 10;
for i in v1.first..v1.last
loop
dbms_output.put_line(v1(i).manager_id);
end loop;
dbms_output.put_line(chr(10)||v1.count||chr(10));
end loop;
end if;
end;
/

Thanks

Sajal Agarwal

Set operators with examples

Set Operator:-
Set Operators combine the result of two or more component queries into a single result. Queries containing set operators are called compound queries.

1. UNION
2. MINUS
3. UNION ALL
4. INTERSECT

Table EMP

ID   NAME   SALARY
1   AMAL   80000
2   SAJAL   20000
3   SEEMA   50000
4   SUSHIL   90000

Table EMP_DETAIL

ID   NAME   SALARY
1   AMAL   80000
2   NAVAL   10000
3   SEEMA   50000
4   ANKIT   60000
5   AYUSH   30000

UNION:-
All distinct rows selected by either query. In UNION operator data is sorted in ascending order according to first column of first query.

Example:-
SELECT ID,NAME,SALARY
FROM EMP
union
SELECT ID,NAME,SALARY
FROM EMP_DETAIL;

Output:-

ID   NAME   SALARY
1   AMAL   80000
2   SAJAL   20000
2   NAVAL   10000
3   SEEMA   50000
4   SUSHIL   90000
4   ANKIT   60000
5   AYUSH   30000

MINUS:-
All distinct rows that are selected by the first SELECT statement and not selected in the second SELECT statement.

Example:-
SELECT ID,NAME,SALARY
FROM EMP
minus
SELECT ID,NAME,SALARY
FROM EMP_DETAIL;

Output:-

ID   NAME   SALARY
2   SAJAL   20000
4   SUSHIL   90000

UNION ALL:-
All rows selected by either queries, including all duplicates.

Example:-
SELECT ID,NAME,SALARY
FROM EMP
union all
SELECT ID,NAME,SALARY
FROM EMP_DETAIL;

Output:-

ID   NAME   SALARY
1   AMAL   80000
2   SAJAL   20000
3   SEEMA   50000
4   SUSHIL   90000
1   AMAL   80000
2   NAVAL   10000
3   SEEMA   50000
4   ANKIT   60000
5   AYUSH   30000

INTERSECT:-
All distinct rows selected by both queries.

Example:-
SELECT ID,NAME,SALARY
FROM EMP
intersect
SELECT ID,NAME,SALARY
FROM EMP_DETAIL;

Output:-

ID   NAME   SALARY
1   AMAL   80000
3   SEEMA   50000

What is Sequence and examples

Sequence:-
Sequence is a database object, it use to generate a numeric value only.

SYNTAX:-

CREATE SEQUENCE sequence_name
[INCREAMENT BY n]
[{START WITH n}]
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{NOCYCLE | CYCLE}]
[{NOCACHE | CACHE n}]

INCREAMENT BY n:- Specifies the interval between sequence of number(n is interger).By default in sequence number increment by 1.

START WITH n:- Specific the first sequence of number.By default in sequence number start with 1.

MAXVALUE n:- Specifies the maximum value sequence can be generate.

NOMAXVALUE:- Specifies a maximum value is 10^27.

MINVALUE n:- Specific the minimum value.

NOMINVALUE:- Specifies a minimum value is -10^26.

CYCLE:- Specifies whether the sequence continue to generate value after reaching maximum or minimum.

NOCYCLE:- After reaching maximum or minimum, sequence may not generate value and it give error. NOCYCLE is default option.

CACHE n | NOCACHE:- :- Specifies how many value the oracle preallocates and keeps in memory.(BY default it is 20).

NEXTVAL:- It returns the next value of the sequence.

CURVAL:- It returns the current value of the sequence.

Example:-

CREATE SEQUENCE XXC05_SEQ
START WITH 1
INCREMENT BY 1
MAXVALUE 100
MINVALUE 1
NOCYCLE
NOCACHE;

Modify Sequence:- We can modify sequence.

Syntax:-

ALTER SEQUENCE sequence_name
INCREAMENT BY n
MAXVALUE n
MINVALUE n
CYCLE | NOCYCLE
CACHE | NOCACHE;

Note:- we can not modify starting value of sequence. Maximum value cannot less current value.

Example:-

ALTER SEQUENCE XXC05_SEQ
INCREAMENT BY 10
MAXVALUE 1000
MINVALUE -1000
CYCLE
CACHE;

Drop Sequence:- We can drop a sequence.

Syntax:-

DROP SEQUENCE sequence_name;

Example:-

DROP SEQUENCE XXC05_SEQ;

—————————————————–

SYNONYM:-
Synonym is the another or alternate name of the database object.
We can create synonym of TABLE, VIEW, SEQUENCE, INDEX, PROCEDURE, FUNCTION, TRIGGER, PACKAGE etc.

Syntax:-

CREATE [PUBLIC] S
SYNONYM synonym_name
FOR object_name;

Example:-

CREATE SYNONYM XXC05_SEQ_SYN
FOR XXC05_SEQ;

Note:- XXC05_SEQ is the name of sequence.

Drop Synonym:-

DROP SYNONYM synonym_name;

Example:-

DROP SYNONYM XXC05_SEQ_SYN;

——————————————————-

Control File:-

The LOAD DATA statement is required at the beginning of the control file.

INFILE * specifies that the data is found in the control file and not in an external file.

The INTO TABLE statement is required to identify the table to be loaded (DEPT) into. By default, SQL*Loader requires the table to be empty before it inserts any records.

FIELDS TERMINATED BY specifies that the data is terminated by commas, but may also be enclosed by quotation marks.
Data types for all fields default to CHAR.

Specifies that the names of columns to load are enclosed in parentheses. Since no datatype is specified, the default is a character of length 255.

BEGINDATA specifies the beginning of the data.

put control file in that path———> /u02/oracle/visappl/wip/11.5.0/bin

Attach those ctl file in concurrent program

Program name———-> ANY CONCURRENT PROGRAM NAME

Execute that command:-

sqlldr user=apps/apps@vis control= control_file_name.ctl

Example:-

We create a control file XXC05_CONTROL_EMP.ctl

sqlldr user=apps/apps@vis control=XXC05_CONTROL_EMP.ctl

Note:- When we execute sqlloder command 3 more file created i.e log file, bad file, discard file with the same name of ctl file name.

Log File:- In that file we see the log details of control file. File extension is “file_name.log”.

Bad File:- Which date have logical error (for example:- datatype mismatch, length, etc) goes in that file. File extension is “file_name.bad”.

Discard File:- Which data are not satisfy in when condition goes in that file.

******************************************** 1 ****************************

LOad data
infile *
INSERT/APPEND/DELETE/TRUNCATE
into table XXC05_EMP
when dept_name=’HR’
fields terminated by ‘ ‘
(emp_id position(1:2),last_name position(4:5),salary position(7:9),dept_name position(11:12))
into table XXC04_emp2
when dept_name=’FF’
(emp_id position(1:2),dept_name position(11:12))
begindata
10 AA 200 HR
20 BB 300 HR
30 CC 400 MG
40 DD 500 FF

******************************************** 2 ****************************

Option (skip=1)
load data
infile *
insert into table XXC05_EMPLOYEES
fields terminated by ‘ ‘
(emp_id,last_name,dept_name FILLER POSITION(1),salary)
begindata
10 HR 200
20 GG 300
30 AA 400

******************************************** 3 ****************************

load data
infile *
insert into table XXC05_EMPLOYEES
fields terminated by ‘,’
(emp_id constant 100,last_name)
begindata
HR
HR
AA
BB

******************************************** 4 ****************************

load data
infile *
insert into table XXC05_EMPLOYEES
when (1:1)=’H’
fields terminated by ‘,’
(emp_id constant 100,last_name)
begindata
HR
HR
AA
BB

******************************************** 5 ****************************

load data
infile *
replace into table XXC05_EMPLOYEES
when (1:1)!=’H’
fields terminated by ‘,’
(emp_id constant 100,last_name,dept_name,rec_no recnum)
begindata
H,A
HR,B
AA,C
BB,D

******************************************** 6 ****************************

options(skip=2)– skip first 2 lines from the top
LOAD DATA
INFILE *
INTO TABLE image_table
REPLACE
FIELDS TERMINATED BY ‘,’
(
image_id INTEGER(5),
file_name CHAR(30),
image_data LOBFILE (file_name) TERMINATED BY EOF
)
BEGINDATA
001,image1.gif
002,image2.jpg
003,image3.jpg

******************************************** 7 ****************************

LOAD DATA
INFILE ‘/u02/oracle/visappl/wip/11.5.0/data/XXC05_DATA_FILE’
TRUNCATE
INTO TABLE XXC05_EMPLOYEES
FIELDS TERMINATED BY “,” trailing nullcols
(
c1,
field2 BOUNDFILLER,
field3 BOUNDFILLER,
field4 BOUNDFILLER,
field5 BOUNDFILLER,
c2 “:field2 || :field3”,
c3 “:field4 + :field5”
)

******************************************** 8 ****************************

LOAD DATA
INFILE *
INSERT
INTO TABLE XXC05_EMPLOYEES
(
LAST_NAME position(1:7) CHAR “UPPER(:LAST_NAME)”,
FIRST_NAME position(8:15) CHAR “LOWER(:FIRST_NAME)”
)

BEGINDATA
Locke Phil
Gorman Tim

————————————————————————–

Multiple Insert Statement:-
In multiple INSERT statement, you insert data into more then one tables as a part of single DML statement.

Type of Multiple Insert Statement:

1. Unconditional INSERT
2. Conditional INSERT
3. Conditional FIRST INSERT
4. Pivoting INSERT

Syntax:-

INSERT [ALL] [CONDITION_INSERT_CLAUSE]
[insert_into_clause values_clause ] (subquery);

=> Conditional_insert_clause

[ALL] [FIRST]
[WHEN condition THEN] [insert_into_clause values_clause]
[else] [insert_into_clause values_clause];

Unconditional INSERT ALL:-
This INSERT statement is referred to as as unconditional INSERT because no further restriction is applied to the rows that are retrieved by SELECT statement.

Example:-

INSERT ALL
INTO XXC05_EMP1(EMP_ID, L_NAME, SAL)
INTO XXC05_EMP2(EMP_ID, F_NAME, MGR_ID)
SELECT EMPLOYEE_ID EMP_ID,
LAST_NAME L_NAME,
FIRST_NAME F_NAME,
SALARY SAL,
MANAGER_ID MGR_ID
FROM EMPLOYEES;

Note:- Insert data into XXC05_EMP1, XXC05_EMP2 tables by a single SELECT statement.

Conditional INSERT ALL:-
This INSERT statement is referred to as as conditional INSERT because a further restriction is applied to the rows that are retrieved by SELECT statement.

Example:-

INSERT ALL
WHEN EMPLOYEE_ID 200
THEN
INTO XXC05_EMP2(EMP_ID, F_NAME, MGR_ID)
VALUE(EMPLOYEE_ID, FIRST_NAME, MANAGER_ID)
SELECT EMPLOYEE_ID EMP_ID,
LAST_NAME L_NAME,
FIRST_NAME F_NAME,
SALARY SAL,
MANAGER_ID MGR_ID
FROM EMPLOYEES;

Conditional FIRST INSERT:-
This INSERT statement is referred to as as conditional FIRST INSERT because a further restriction is applied to the rows that are retrieved by SELECT statement. In that statement first true condition find and insert data in those table, rest of conditions or statement are switched.

Example:-

INSERT FIRST
WHEN EMPLOYEE_ID <= 24000
THEN
INTO employee1(EMPLOYEE_ID, LAST_NAME, SALARY)
WHEN EMPLOYEE_ID = 2388
THEN
INTO employee2(EMPLOYEE_ID, LAST_NAME, SALARY)
WHEN EMPLOYEE_ID =0
THEN
INTO employee4(EMPLOYEE_ID, LAST_NAME, SALARY)
SELECT EMPLOYEE_ID,LAST_NAME,SALARY
FROM EMPLOYEES;

Note:- In above example First condition is true so data insert into only EMPLOYEE1 table.It do not check any other conditions.

Example:-

INSERT FIRST
WHEN EMPLOYEE_ID <= -24000
THEN
INTO employee1(EMPLOYEE_ID, LAST_NAME, SALARY)
WHEN EMPLOYEE_ID = -2388
THENINTO employee2(EMPLOYEE_ID, LAST_NAME, SALARY)
WHEN EMPLOYEE_ID =0
THEN
INTO employee4(EMPLOYEE_ID, LAST_NAME, SALARY)
SELECT EMPLOYEE_ID,LAST_NAME,SALARY
FROM EMPLOYEES;

Note:- In above example THIRD condition is true so data insert into only EMPLOYEE3 table. It do not check FORTH condition.

Pivoting INSERT:-
Using pivoting INSERT, convert the set of sales records from nonrelational database table to relational format.

SAVE EXCEPTIONS with example

SAVE EXCEPTIONS:-

Since version 9i it is possible to do bulk DML using FORALL and use the SAVE EXCEPTIONS clause. It makes sure that all invalid rows – the exceptions – are saved into the sql%bulk_exceptions array, while all valid rows are still processed. This array stores a record for each invalid row, containing an ERROR_INDEX which is the iteration number during the FORALL statement and an ERROR_CODE which corresponds with the Oracle error code.

DECLARE
USER_EXCE EXCEPTION;
PRAGMA EXCEPTION_INIT(USER_EXCE,-24381);
TYPE T1 IS TABLE OF ALL_OBJECTS%ROWTYPE;
T2 T1;
CURSOR C1 IS SELECT * FROM ALL_OBJECTS WHERE ROWNUM<=1000;
BEGIN
OPEN C1;
LOOP
FETCH C1 BULK COLLECT INTO T2 LIMIT 100;
FORALL I IN 1..T2.COUNT SAVE EXCEPTIONS
INSERT INTO XXC05_OBJECT VALUES T2(I);
EXIT WHEN T2.COUNT=0;
END LOOP;
EXCEPTION
WHEN USER_EXCE
THEN
FOR I IN 1..SQL%BULK_EXCEPTIONS.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE(SQL%BULK_EXCEPTIONS(I).ERROR_CODE);
END LOOP;
END;

DECLARE
TYPE T1 IS TABLE OF ALL_OBJECTS%ROWTYPE;
T2 T1;
CURSOR C1 IS SELECT * FROM ALL_OBJECTS WHERE ROWNUM<=1000;
BEGIN
OPEN C1;
LOOP
FETCH C1 BULK COLLECT INTO T2 LIMIT 100;
FORALL I IN 1..T2.COUNT SAVE EXCEPTIONS
INSERT INTO XXC05_OBJECT VALUES T2(I);
EXIT WHEN T2.COUNT=0;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
FOR I IN 1..SQL%BULK_EXCEPTIONS.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE(SQL%BULK_EXCEPTIONS(I).ERROR_CODE);
END LOOP;
END;

What is REF-Cursor with example

Ref Cursor:-

Ref Cursor is a dynamic cursor in Oracle. We can change cursor definition as run time but it define and declare once.

Two type of Ref Cursor

Week Ref Cursor:- Week ref cursor never return a value.

Strong Ref Cursor:- Strong ref cursor must return a value.

Example of week ref cursor
Example 1-

Example 2-

Example of Strong ref cursor

SYS Ref Cursor:-

Predefined Exception In Oracle

ACCESS_INTO_NULL        ORA-06530
CASE_NOT_FOUND          ORA-06592
COLLECTION_IS_NULL      ORA-06531
CURSOR_ALREADY_OPEN     ORA-06511
DUP_VAL_ON_INDEX       ORA-00001
INVALID_CURSOR         ORA-01001
INVALID_NUMBER         ORA-01722
LOGIN_DENIED           ORA-01017
NO_DATA_FOUND         ORA-01403
NOT_LOGGED_ON         ORA-01012
PROGRAM_ERROR         ORA-06501
ROWTYPE_MISMATCH       ORA-06504
SELF_IS_NULL           ORA-30625
STORAGE_ERROR         ORA-06500
SUBSCRIPT_BEYOND_COUNT ORA-06533
SUBSCRIPT_OUTSIDE_LIMIT ORA-06532
SYS_INVALID_ROWID     ORA-01410
TIMEOUT_ON_RESOURCE    ORA-00051
TOO_MANY_ROWS         ORA-01422
VALUE_ERROR            ORA-06502
ZERO_DIVIDE            ORA-01476

Example of Pragmas in Oracle PLSQL

——————————- PRAGMA SERIALLY_REUSABLE ———-

create or replace package XXC05_TEST_PACKAGES
is
PRAGMA SERIALLY_REUSABLE;
id number:=10;
end XXC05_TEST_PACKAGES;

—–1

begin
XXC05_TEST_PACKAGES.id:=20;
dbms_output.put_line(XXC05_TEST_PACKAGES.id);
end;

—-2

begin
XXC05_TEST_PACKAGES.id:=50;
dbms_output.put_line(XXC05_TEST_PACKAGES.id);
end;

—-3

begin
dbms_output.put_line(XXC05_TEST_PACKAGES.id);
end;

When we Execute 1 then give 20.
When we execute 3 then give 10;
when we execute 2 then give 50;
When we execute 3 then give 10;

——————————————— PRAGMA AUTONOMOUS_TRANSACTION ——————————————-

In function, If we perform DML then we cant call in select.
But if we use PRAGMA AUTONOMOUS_TRANSACTION in function then we call these function in select;

create or replace function XXC05_FUN002
return number
is
PRAGMA AUTONOMOUS_TRANSACTION;
emp_id number;
begin
insert into XXC05_TEST
values(60,’F’);
commit;
select id
into emp_id
from XXC05_TEST
where id=60;
return (emp_id);
end XXC05_FUN002;

select XXC05_FUN002 from XXC05_TEST;

CREATE OR REPLACE TRIGGER XXC05_PRAGMA_AT_EXAMPLE
BEFORE INSERT OR UPDATE ON XXC05_TEST
FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO XXC05_TEST1 VALUES(100,’ABC’);
COMMIT;
END XXC05_PRAGMA_AT_EXAMPLE;

INSERT INTO XXC05_TEST VALUES(100,’SAJAL’);

—————————————— PRAGMA EXCEPTION_INIT —————————

create or replace function XXC05_PRAGMA_INIT(emp_date in varchar2)
return date
is
e_date date;
begin
e_date:= to_date(emp_date,’YYYY-MON-DD’);
return e_date;
end XXC05_PRAGMA_INIT;

select XXC05_PRAGMA_INIT(‘sajal’) from dual;

——– Solution

create or replace function XXC05_PRAGMA_INIT(emp_date in varchar2)
return date
is
data_exception exception;
pragma exception_init(data_exception,-1841);
e_date date;
begin
e_date:= to_date(emp_date,’YYYY-MON-DD’);
return e_date;
exception
when data_exception
then
dbms_output.put_line(‘Exception created by Sajal’);
return null;
end XXC05_PRAGMA_INIT;

select XXC05_PRAGMA_INIT(‘sajal’) from dual;

Example 2——–

create or replace function XXC05_TOO_MANY_ROW_EXCEP(d_id number)
return number
is
cus_execp_hdl exception;
pragma exception_init(cus_execp_hdl,-1422);
emp_id number;
begin
select employee_id
into emp_id
from employees
where department_id= d_id;
return(emp_id);
exception
when cus_execp_hdl
then
dbms_output.put_line(‘Custom Error’);
return null;
end XXC05_TOO_MANY_ROW_EXCEP;

select XXC05_TOO_MANY_ROW_EXCEP(20) from dual;

——————————————- PRGMA RESTRICT_REFERENCES

WNDS:- Write no database State;
RNDS:- Read no database State;
WNPS:- Write no package State;
RNPS:- Read no package State;

If we use “trust” then effect of WNDS/RNDS/WNPS/RNPS is nor reflected;

create or replace package XXC05_PRAGMA_RES_REF
IS
PROCEDURE P1;
PROCEDURE P2;
pragma restrict_references(P1,’RNDS’,’TRUST’);
pragma restrict_references(P2,’RNPS’,’TRUST’);
END XXC05_PRAGMA_RES_REF;

create or replace package BODY XXC05_PRAGMA_RES_REF
IS
PROCEDURE P1
IS
DEP_ID NUMBER;
BEGIN
SELECT DEPARTMENT_ID
INTO DEP_ID
FROM EMPLOYEES
WHERE EMPLOYEE_ID=100;
DBMS_OUTPUT.PUT_LINE(DEP_ID);
END P1;
PROCEDURE P2
IS
DEP_ID NUMBER;
EMP_ID NUMBER:=10;
BEGIN
DBMS_OUTPUT.PUT_LINE(EMP_ID);
END P2;
END XXC05_PRAGMA_RES_REF;

Pattern Printing in Oracle SQL

1 ————

*
**
***
****
*****

DECLARE
P_STAR VARCHAR2(5);
BEGIN
FOR I IN 1..5
LOOP
P_STAR := P_STAR||’*’;
DBMS_OUTPUT.PUT_LINE(P_STAR);
END LOOP;
END;

DECLARE
STAR VARCHAR2(5):=’*’;
P_STAR VARCHAR2(5);
BEGIN
FOR I IN 1..5
LOOP
P_STAR := LPAD(STAR,I,STAR);
DBMS_OUTPUT.PUT_LINE(P_STAR);
END LOOP;
END;

2 ————————————–

&
&*
&*&
&*&*
&*&*&

DECLARE
P_STAR VARCHAR2(5);
BEGIN
FOR I IN 1..5
LOOP
IF MOD(I,2)=0
THEN
P_STAR := P_STAR||’*’;
DBMS_OUTPUT.PUT_LINE(P_STAR);
ELSE
P_STAR:= P_STAR||’&’;
DBMS_OUTPUT.PUT_LINE(P_STAR);
END IF;
END LOOP;
END;

3 —————————————

****
****
***
**
*

DECLARE
P_STAR VARCHAR2(5):= ‘*****’;
S_STAR VARCHAR2(5);
BEGIN
FOR I IN 1..5
LOOP
S_STAR := SUBSTR(P_STAR,I);
DBMS_OUTPUT.PUT_LINE(S_STAR);
END LOOP;
END;

4 ———————————

*
**
***
****
*****
****
***
**
*

DECLARE
V_STAR VARCHAR2(1):= ‘*’;
P_STAR VARCHAR2(5);
S_STAR VARCHAR2(5);
BEGIN
FOR I IN 1..5
LOOP
P_STAR:= LPAD(V_STAR,I,V_STAR);
DBMS_OUTPUT.PUT_LINE(P_STAR);
END LOOP;
FOR I IN 1..5
LOOP
S_STAR := SUBSTR(P_STAR,I+1);
DBMS_OUTPUT.PUT_LINE(S_STAR);
END LOOP;
END;

5 —————————————————-

*****
****
***
**
*
*
**
***
****
*****

DECLARE
V_STAR VARCHAR2(5):= ‘*****’;
P_STAR VARCHAR2(5);
S_STAR VARCHAR2(5);
BEGIN
FOR I IN 1..5
LOOP
P_STAR := SUBSTR(V_STAR,I);
DBMS_OUTPUT.PUT_LINE(P_STAR);
END LOOP;
FOR I IN 1..5
LOOP
S_STAR := LPAD(P_STAR,I,P_STAR);
DBMS_OUTPUT.PUT_LINE(S_STAR);
END LOOP;
END;

6 ——————————————

*****
****
***
**
*
**
***
****
*****

DECLARE
V_STAR VARCHAR2(5):= ‘*****’;
P_STAR VARCHAR2(5);
S_STAR VARCHAR2(5);
BEGIN
FOR I IN 1..5
LOOP
P_STAR := SUBSTR(V_STAR,I);
DBMS_OUTPUT.PUT_LINE(P_STAR);
END LOOP;
FOR I IN 1..4
LOOP
P_STAR := P_STAR||’*’;
DBMS_OUTPUT.PUT_LINE(P_STAR);
END LOOP;
END;

7 ——————————

*****
****
***
**
*

DECLARE
SPACE VARCHAR2(1):= ‘ ‘;
V_STAR VARCHAR2(5):= ‘*****’;
P_STAR VARCHAR(5);
BEGIN
FOR I IN 1..5
LOOP
P_STAR:= LPAD(SUBSTR(V_STAR,I),5,SPACE);
DBMS_OUTPUT.PUT_LINE(P_STAR);
END LOOP;
END;

8 ——————————
*
**
***
****
*****

DECLARE
STAR VARCHAR2(1):= ‘*’;
SPACE VARCHAR2(1):= ‘ ‘;
P_NO VARCHAR2(5);
P_STAR VARCHAR2(5);
BEGIN
FOR I IN 1..5
LOOP
P_STAR:= P_STAR||STAR;
FOR J IN 1..5
LOOP
P_NO:= LPAD(P_STAR,5,SPACE);
END LOOP;
DBMS_OUTPUT.PUT_LINE(P_NO);
END LOOP;
END;

9 ——————————
*****
****
***
**
*
*
**
***
****
*****

DECLARE
SPACE VARCHAR2(1):= ‘ ‘;
V_STAR VARCHAR2(5):= ‘*****’;
P_STAR VARCHAR(5);
S_STAR VARCHAR(5);
BEGIN
FOR I IN 1..5
LOOP
P_STAR:= LPAD(SUBSTR(V_STAR,I),5,SPACE);
DBMS_OUTPUT.PUT_LINE(P_STAR);
END LOOP;
FOR J IN 1..5
LOOP
S_STAR:= RPAD(SUBSTR(P_STAR,J),5,’*’);
DBMS_OUTPUT.PUT_LINE(S_STAR);
END LOOP;
END;

10 ——————————

*****
****
***
**
*
**
***
****
*****

DECLARE
SPACE VARCHAR2(1):= ‘ ‘;
V_STAR VARCHAR2(5):= ‘*****’;
P_STAR VARCHAR(5);
S_STAR VARCHAR(5);
BEGIN
FOR I IN 1..5
LOOP
P_STAR:= LPAD(SUBSTR(V_STAR,I),5,SPACE);
DBMS_OUTPUT.PUT_LINE(P_STAR);
END LOOP;
FOR J IN 1..5
LOOP
S_STAR:= RPAD(SUBSTR(P_STAR,J+1),5,’*’);
DBMS_OUTPUT.PUT_LINE(S_STAR);
END LOOP;
END;

11 ——————————

**** ****
***   ***
**     **
*       *

12 ——————————

*        *
**      **
***    ***
**** ****
**********

13 ——————————

**** ****
***    ***
**      **
*        *
**      **
***    ***
**** ****

DECLARE
STAR VARCHAR2(1):= ‘*’;
SPACE VARCHAR2(1):= ‘ ‘;
P_PRINT VARCHAR2(9);
S_SPACE VARCHAR2(9);
P_SPACE VARCHAR2(9);
BEGIN
FOR I IN 1..4
LOOP
P_SPACE:= S_SPACE;
S_SPACE:= S_SPACE||SPACE;
P_PRINT:= RPAD(LPAD(S_SPACE,5,STAR)||P_SPACE,9,STAR);
DBMS_OUTPUT.PUT_LINE(P_PRINT);
END LOOP;
FOR I IN 1..3
LOOP
S_SPACE:= P_SPACE;
P_SPACE:= SUBSTR(P_SPACE,1,4-(I+1));
P_PRINT:= RPAD(LPAD(S_SPACE,5,STAR)||P_SPACE,9,STAR);
DBMS_OUTPUT.PUT_LINE(P_PRINT);
END LOOP;
END;

14 ———————————-

*
***
*****
*******
*********

DECLARE
STAR VARCHAR2(1):= ‘*’;
SPACE VARCHAR2(1):= ‘ ‘;
P_PRINT VARCHAR2(9);
S_STAR VARCHAR2(9);
P_STAR VARCHAR2(9);
BEGIN
FOR I IN 1..5
LOOP
P_STAR:= S_STAR;
S_STAR:= S_STAR||STAR;
P_PRINT:= RPAD(LPAD(S_STAR,5,SPACE)||P_STAR,9,SPACE);
DBMS_OUTPUT.PUT_LINE(P_PRINT);
END LOOP;
END;

Oracle PL/SQL Package Example

Oracle Package Example

create or replace package XXC05_PACK
is
procedure XXC05_PROCD1(id number);
end XXC05_PACK;

create or replace package body XXC05_PACK
is
procedure XXC05_PROCD1(id number)
is
l_name varchar2(100);
begin
select last_name into l_name
from employees
where employee_id=id;
dbms_output.put_line(‘EMP ID’||chr(9)||id);
end;
end XXC05_PACK;

declare
begin
XXC05_PACK.XXC05_PROCD1(100);
end;

———————————————————————————

create or replace package XXC05_global_varr
is
id number:=100;
name varchar2(200):=’SAJAL’;
end XXC05_global_varr;

execute dbms_output.put_line(‘EMP ID’||chr(9)||XXC05_global_varr.id);

execute dbms_output.put_line(‘EMP NAME’||chr(9)||XXC05_global_varr.name);

————————————————————————————

create or replace package XXC05_PAC1
is
procedure XXC05_PRO1(id number);
procedure XXC05_PRO1(name varchar2);
procedure XXC05_PRO1(id number,name varchar2);
end;

create or replace package body XXC05_PAC1
is
procedure XXC05_PRO1(id number)
is
emp_id number;
begin
select employee_id into emp_id
from employees
where employee_id = id;
dbms_output.put_line(‘EMPLOYEE_ID’||chr(9)||emp_id);
end;
procedure XXC05_PRO1(name varchar2)
is
emp_id number;
begin
select employee_id into emp_id
from employees
where last_name = name;
dbms_output.put_line(‘EMPLOYEE_ID’||chr(9)||emp_id);
end;
procedure XXC05_PRO1(id number,name varchar2)
is
emp_id number;
begin
select employee_id into emp_id
from employees
where employee_id = id
and last_name = name;
dbms_output.put_line(‘EMPLOYEE_ID’||chr(9)||emp_id);
end;
end XXC05_PAC1;

————————————————————————————————-

create or replace package XXC05_PAC1
is
procedure XXC05_PRO1(id number);
procedure XXC05_PRO2(name varchar2);
end;

create or replace package body XXC05_PAC1
is
procedure XXC05_PRO1(id number)
is
emp_id number;
l_name varchar2(100);
begin
select employee_id,last_name into emp_id,l_name
from employees
where employee_id = id;
dbms_output.put_line(‘EMPLOYEE_ID’||chr(9)||emp_id);
dbms_output.put_line(‘LAST NAME’||chr(9)||l_name);
XXC05_PRO2(l_name);
end;
procedure XXC05_PRO2(name varchar2)
is
emp_id number;
begin
select employee_id into emp_id
from employees
where last_name = name;
dbms_output.put_line(‘EMPLOYEE_ID’||chr(9)||emp_id);
end;
end XXC05_PAC1;

——————————————————————————-

create or replace package XXC05_PACKA1
is
cursor c1 is
select employee_id,last_name
from employees;
procedure XXC05_id;
procedure XXC05_name;
end XXC05_PACKA1;

create or replace package body XXC05_PACKA1
is
procedure XXC05_id
is
id number;
name varchar2(200);
begin
open c1;
fetch c1 into id,name;
for i in 1..10
loop
dbms_output.put_line(‘EMPLPOYEE_ID’||chr(9)||id);
dbms_output.put_line(‘EMPLPOYEE_name’||chr(9)||name);
end loop;
end XXC05_id;
procedure XXC05_name
is
id number;
name varchar2(200);
begin
open c1;
fetch c1 into id,name;
for i in 1..10
loop
dbms_output.put_line(‘ID’||chr(9)||id);
dbms_output.put_line(‘NAME’||chr(9)||name);
end loop;
end XXC05_name;
end XXC05_PACKA1;

drop package package_name;

ADVANTAGES: –
1. EASY TO MAINTENANCE
2. COLLECTION OF SUBPROGRAMS
3. OVERLOADING
4. BETTER FERFORMANCE
5. EASIER APPLICATION DESIGN

Script to find Oracle API’s of any module

Following script and get all the packages related to API in Oracle applications, from which you can select APIs that pertain to AP. You can change the name like to PA or AR and can check for different modules

select substr(a.OWNER,1,20)
, substr(a.NAME,1,30)
, substr(a.TYPE,1,20)
, substr(u.status,1,10) Stat
, u.last_ddl_time
, substr(text,1,80) Description
from dba_source a, dba_objects u
WHERE 2=2
and u.object_name = a.name
and a.text like ‘%Header%’
and a.type = u.object_type
and a.name like ‘PA_%API%’
order by
a.owner, a.name;

What is collection and its attributea with example.

Collection:-
Collection is an Ordered group of elements, all of the same type.

Attributes of Collection

1. FIRST
2. LAST
3. COUNT
4. DELETE
5. EXTAND
6. TRIM
7. NEXT
8. EXIST
9. PRIOR
10. LIMIT

Types of Collection

1. Varray
2. Nested Table
3. PL/SQL Table or Associate Array

Varray:-
Varray stands of variable size array.Varray can be stored in the column of table.

Syntax of Varray:- Type type_name is VARRAY(length) of data_type;

Examples of Varray

DECLARE
TYPE L_VAR IS VARRAY(10) OF VARCHAR2(3);
L_VAR1 L_VAR;
BEGIN
L_VAR1:=L_VAR(‘A’,’B’,’C’,’D’,’E’,’F’,’G’,’H’);
FOR I IN 1..8
LOOP
DBMS_OUTPUT.PUT_LINE(L_VAR1(I));
END LOOP;
END;

DECLARE
TYPE L_VAR IS VARRAY(10) OF VARCHAR2(3);
L_VAR1 L_VAR;
BEGIN
L_VAR1:=L_VAR(‘A’,’B’,’C’,’D’,’E’,’F’,’G’,’H’);
FOR I IN L_VAR1.FIRST..L_VAR1.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE(L_VAR1(I));
END LOOP;
END;

DECLARE
TYPE L_VAR IS VARRAY(10) OF VARCHAR2(10);
L_VAR1 L_VAR:= L_VAR();
BEGIN
L_VAR1.EXTEND;
L_VAR1(1):=’HELLO’;
L_VAR1.EXTEND;
L_VAR1(2):= ‘ORACLE’;
L_VAR1.EXTEND;
L_VAR1(3):=’JAVA’;
L_VAR1.EXTEND;
L_VAR1(4):= ‘OAF’;
FOR I IN 1..4
LOOP
DBMS_OUTPUT.PUT_LINE(L_VAR1(I));
END LOOP;
END;

DECLARE
CURSOR C1 IS
SELECT PO_HEADER_ID
FROM PO_HEADERS_ALL
WHERE ROWNUM<=100;
TYPE L_HDR IS VARRAY(100) OF PO_HEADERS_ALL.PO_HEADER_ID%TYPE;
L_HDR1 L_HDR:= L_HDR();
COUNTER NUMBER:=0;
BEGIN
FOR I IN C1
LOOP
COUNTER:= COUNTER+1;
L_HDR1.EXTEND;
L_HDR1(COUNTER):= I.PO_HEADER_ID;
DBMS_OUTPUT.PUT_LINE('PO_HEADER_ID IS '||L_HDR1(COUNTER));
END LOOP;
END;

DECLARE
CURSOR C1 IS
SELECT PO_HEADER_ID
FROM PO_HEADERS_ALL
WHERE ROWNUM<=100;
TYPE L_HDR IS VARRAY(100) OF PO_HEADERS_ALL.PO_HEADER_ID%TYPE;
L_HDR1 L_HDR:=L_HDR();
COUNTER NUMBER:=0;
BEGIN
OPEN C1;
FOR I IN 1..100
LOOP
COUNTER:= COUNTER+1;
L_HDR1.EXTEND;
FETCH C1 INTO L_HDR1(COUNTER);
DBMS_OUTPUT.PUT_LINE('PO_HEADER_ID IS '||L_HDR1(COUNTER));
END LOOP;
END;

Nested Table:-
Nested table is like a one-dimensional array.

Syntax of Nested Table:- TYPE type_name IS TABLE OF data_type;

Example of Nested Table

DECLARE
TYPE L_TAB IS TABLE OF NUMBER;
L_TAB1 L_TAB;
BEGIN
L_TAB1:= L_TAB(1,2,3,4,5);
FOR I IN 1..5
LOOP
DBMS_OUTPUT.PUT_LINE(L_TAB1(I));
END LOOP;
END;

DECLARE
TYPE L_TAB IS TABLE OF NUMBER;
L_TAB1 L_TAB;
BEGIN
L_TAB1:= L_TAB(1,2,3,4,5);
FOR I IN L_TAB.FIRST..L_TAB1.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(L_TAB1(I));
END LOOP;
END;

DECLARE
CURSOR C_HDR IS
SELECT PO_HEADER_ID
FROM PO_HEADERS_ALL
WHERE ROWNUM<=100;
TYPE L_TAB_HDR IS TABLE OF PO_HEADERS_ALL.PO_HEADER_ID%TYPE;
L_HDR L_TAB_HDR:= L_TAB_HDR();
COUNTER NUMBER:=0;
BEGIN
FOR I IN C_HDR
LOOP
COUNTER:= COUNTER+1;
L_HDR.EXTEND;
L_HDR(COUNTER):= I.PO_HEADER_ID;
DBMS_OUTPUT.PUT_LINE(L_HDR(COUNTER));
END LOOP;
END;

DECLARE
CURSOR C_HDR IS
SELECT PO_HEADER_ID
FROM PO_HEADERS_ALL
WHERE ROWNUM<=100;
TYPE L_TAB_HDR IS TABLE OF PO_HEADERS_ALL.PO_HEADER_ID%TYPE;
L_HDR L_TAB_HDR:= L_TAB_HDR();
COUNTER NUMBER:=0;
BEGIN
OPEN C_HDR;
FOR I IN 1..100
LOOP
COUNTER:= COUNTER+1;
L_HDR.EXTEND;
FETCH C_HDR INTO L_HDR(COUNTER);
DBMS_OUTPUT.PUT_LINE(L_HDR(COUNTER));
END LOOP;
END;

DECLARE
CURSOR C_HDR IS
SELECT *
FROM PO_HEADERS_ALL
WHERE ROWNUM<=100;
TYPE L_TAB_HDR IS TABLE OF PO_HEADERS_ALL%ROWTYPE;
L_HDR L_TAB_HDR:= L_TAB_HDR();
COUNTER NUMBER:=0;
BEGIN
OPEN C_HDR;
FOR I IN 1..100
LOOP
COUNTER:= COUNTER+1;
L_HDR.EXTEND;
FETCH C_HDR INTO L_HDR(COUNTER);
DBMS_OUTPUT.PUT_LINE('HEADER ID'||CHR(9)||L_HDR(COUNTER).PO_HEADER_ID||CHR(9)||'PO NUMBER'||CHR(9)||L_HDR(COUNTER).SEGMENT1);
END LOOP;
END;

PL/SQL Table:-
PL/SQL Table helps you moves bulk data. They can store column or rows of Oracle Data.

Syntax:- TYPE type_name is TABLE OF data_type
INDEX BY NUMBER/VARCHAR2/PLS_INTRGER/BINARY_INTEGER;

Example:-

DECLARE
TYPE L_TAB IS TABLE OF NUMBER
INDEX BY PLS_INTEGER;
L_TAB1 L_TAB;
BEGIN
L_TAB1(1):=1;
L_TAB1(2):=2;
L_TAB1(3):=3;
L_TAB1(4):=4;
L_TAB1(5):=5;
FOR I IN L_TAB1.FIRST..L_TAB1.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(L_TAB1(I));
END LOOP;
END;

DECLARE
CURSOR C_HDR IS
SELECT PO_HEADER_ID,SEGMENT1
FROM PO_HEADERS_ALL
WHERE ROWNUM<=100;
TYPE L_TAB_HDR IS TABLE OF PO_HEADERS_ALL.SEGMENT1%TYPE
INDEX BY PLS_INTEGER;
L_HDR L_TAB_HDR;
COUNTER NUMBER:=0;
BEGIN
FOR I IN C_HDR
LOOP
COUNTER:= COUNTER+1;
L_HDR(COUNTER):= I.SEGMENT1;
DBMS_OUTPUT.PUT_LINE(L_HDR(COUNTER));
END LOOP;
END;

DECLARE
CURSOR C_HDR IS
SELECT PO_HEADER_ID,SEGMENT1
FROM PO_HEADERS_ALL
WHERE ROWNUM<=100;
TYPE L_TAB_HDR IS TABLE OF PO_HEADERS_ALL.SEGMENT1%TYPE
INDEX BY PLS_INTEGER;
L_HDR L_TAB_HDR;
COUNTER NUMBER:=0;
BEGIN
FOR I IN C_HDR
LOOP
COUNTER:= COUNTER+1;
L_HDR(COUNTER):= I.SEGMENT1;
END LOOP;
FOR J IN 1..COUNTER
LOOP
DBMS_OUTPUT.PUT_LINE(L_HDR(J));
END LOOP;
END;

———————————————–

DECLARE
TYPE L_EMP_REC IS RECORD(
L_EMP_ID NUMBER,
L_NAME VARCHAR2(20),
L_SALARY NUMBER);
TYPE L_TAB IS TABLE OF L_EMP_REC;
L_TAB1 L_TAB;
BEGIN
SELECT EMPLOYEE_ID,LAST_NAME,SALARY
BULK COLLECT INTO L_TAB1
FROM EMPLOYEES;
FOR I IN L_TAB1.FIRST..L_TAB1.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE(L_TAB1(I).L_EMP_ID||CHR(9)||L_TAB1(I).L_NAME);
END LOOP;
END;

DECLARE
TYPE L_EMP_REC IS RECORD(L_EMP_ID NUMBER,
L_NAME VARCHAR2(20),
L_DEPT_ID NUMBER,
L_SAL NUMBER,
L_HIRE_DATE DATE
);
TYPE L_TAB IS TABLE OF L_EMP_REC;
L_TAB1 L_TAB;
CURSOR C1 IS
SELECT EMPLOYEE_ID,
LAST_NAME,
DEPARTMENT_ID,
SALARY,
HIRE_DATE
FROM EMPLOYEES;
BEGIN
OPEN C1;
FETCH C1 BULK COLLECT INTO L_TAB1;
FOR I IN L_TAB1.FIRST..L_TAB1.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE(L_TAB1(I).L_EMP_ID||CHR(9)||L_TAB1(I).L_NAME);
END LOOP;
END;

————————————

Package:-
Package is the collection of database objects i.e. Procedure, Function, Anomous block.

Advantages:-
1) Modularity
2) Security
3) Easy to Maintenance
4) Function overLoading

Syntax:-
CREATE OR REPLACE PACKAGE package_name
IS
PROCEDURE PROC1;
……
FUNCTION FUN1
RETURN NUMBER;
………
END package_name;

CREATE OR REPLACE PACKAGE BODY package_name
IS
PROCEDURE PROC1
IS
BEGIN
STATEMENT;
END PROC1;
FUNCTION FUN1
RETURN NUMBER
IS
BEGIN
STATEMENT;
RETURN value;
END FUN1;
END package_name;

How to print calendar from SQL Query

SELECT LPAD( MONTH, 20-(20-LENGTH(MONTH))/2 ) MONTH,”Sun”, “Mon”, “Tue”,
“Wed”, “Thu”, “Fri”, “Sat”
FROM (SELECT TO_CHAR(dt,’fmMonthfm YYYY’) MONTH,TO_CHAR(dt+1,’iw’) week,
MAX(DECODE(TO_CHAR(dt,’d’),’1′,LPAD(TO_CHAR(dt,’fmdd’),2))) “Sun”,
MAX(DECODE(TO_CHAR(dt,’d’),’2′,LPAD(TO_CHAR(dt,’fmdd’),2))) “Mon”,
MAX(DECODE(TO_CHAR(dt,’d’),’3′,LPAD(TO_CHAR(dt,’fmdd’),2))) “Tue”,
MAX(DECODE(TO_CHAR(dt,’d’),’4′,LPAD(TO_CHAR(dt,’fmdd’),2))) “Wed”,
MAX(DECODE(TO_CHAR(dt,’d’),’5′,LPAD(TO_CHAR(dt,’fmdd’),2))) “Thu”,
MAX(DECODE(TO_CHAR(dt,’d’),’6′,LPAD(TO_CHAR(dt,’fmdd’),2))) “Fri”,
MAX(DECODE(TO_CHAR(dt,’d’),’7′,LPAD(TO_CHAR(dt,’fmdd’),2))) “Sat”
FROM ( SELECT TRUNC(SYSDATE,’y’)-1+ROWNUM dt
FROM all_objects
WHERE ROWNUM <= ADD_MONTHS(TRUNC(SYSDATE,'y'),12) – TRUNC(SYSDATE,'y'))
GROUP BY TO_CHAR(dt,'fmMonthfm YYYY'), TO_CHAR( dt+1, 'iw' ))
ORDER BY TO_DATE( MONTH, 'Month YYYY' ), TO_NUMBER(week);

How to hide our code in Oracle?

How to hide your code?

Step 1:- First we create DATA Object.

Step 2:- Write that code in particular file for ex:- file_name.sql

Step 3:- Save that file in particular location in which you want.

Step 4:- Open cmd prompt, Run that command from the path where you put those file.

wrap iname= file_name.sql (press enter) ****(please write same statement)

Step 5:- Run that command in SQL*plus
@patah\file_name.plb;

Step 6:- Execute your object.

Note:- If you want to saw code of that object then it is hide.

How to call different data base object from another data base obejct in oracle

************************************Call procedure within trigger*******************************************************

create or replace procedure p_1 is
begin
dbms_output.put_line(‘SAJAL’);
end;

create or replace trigger t_1
before insert on saj
for each row
begin
p_1;
end;

************************************Call function within trigger*******************************************************

create or replace function f_1(no number) return number is
sal number;
begin
select salary into sal from saj where id=no;
return(no);
end;

create or replace trigger t_1
before insert on saj
for each row
begin
if f_1(1)>2 then
dbms_output.put_line(‘aaaa’);
end if;
end;

create or replace trigger t_1
before insert on saj
for each row
declare
l number;
begin
l:=f_1(1);
dbms_output.put_line(l);
end;

**********************************Call function(return) within trigger***************************************

create or replace function f_1(saj_id number)return number is
sal number;
begin
select salary into sal from saj where id=saj_id;
return(sal);
end;

create or replace trigger t_1
before insert on saj
for each row
declare
v_1 number;
begin
v_1:=f_1(1);
dbms_output.put_line(v_1);
end;

**********************************Call function(return) within procedure***************************************

create or replace function f_1(saj_id number)return number is
sal number;
begin
select salary into sal from saj where id=saj_id;
return(sal);
end;

create or replace procedure p_1 is
l number:=10;
begin
l:=f_1(1);
dbms_output.put_line(l);
end;

**********************************Call procedure within function***************************************

create or replace procedure p_1 is
l number:=10;
begin
dbms_output.put_line(l);
end;
/

create or replace function f_1 return number is
begin
p_1;
return(1);
end;

**********************************Function within function***************************************

create or replace function f1
return varchar2
is
l number;
id varchar2(20);
function inner (no number) return number
is
begin
select employee_id into id from employees
where employee_id=no;
return(1);
end;
begin
l:=inner(198);
dbms_output.put_line(l);
return(2);
end;

What is Formal, Actual, Scalar and Bind Variable in Oracle

Scalar Variable:-
It hold single variable and has no internal components.
Example:-

DECLARE
L_NAME VARCHAR2(20);
BEGIN
SELECT LAST_NAME
INTO L_NAME
FROM EMPLOYEES
WHERE EMPLOYEE_ID = 100;
DBMS_OUTPUT.PUT_LINE(L_NAME);
END;

Bind Variable:-
To reference a bind variable is PL/SQL, you must prefix its name with a colon(:).
Example:-

VARIABLE L_NAME VARCHAR2(10)
BEGIN
SELECT LAST_NAME
INTO :L_NAME
FROM EMPLOYEES
WHERE EMPLOYEE_ID = 100;
END;

PRINT L_NAME;

Formal Parameter:-
Formal parameter are variables declared in parameter list of sub program specification.
Example:-

CREATE OR REPLACE PROCEDURE XXC05_PROC(P_ID NUMBER, P_NAME VARCHAR2)….

**P_ID and P_NAME is formal parameter.

Actual Parameter:-
Actual Parameter are variable or expressions referred in parameter list of sub program call.
Example:-

DECLARE
L_ID NUMBER;
L_NAME VARCHAR2(20);
BEGIN
XXC05_PROC(L_ID,L_NAME);
END;

**L_ID and L_NAME is actual parameter.

What is Raise Application Error in oracle?

What is Raise_application_error in oracle?

Raise_application_error allows users to create custom error messages.
Syntax:-

Raise_application_error (error_number error_messages);

Here, error_number is between -20000 to -20999 because remaining numbers are for Oracle. Message can be varchar(2000)
They are most commonly used to handle unwanted and unmanageable exceptions.

Raise command is used for raise an user defined exception.

Raise_application_error is a procedrue in dbms_standard package..It will raise the user defined error message and error number.

Error number range will be from -20000 to -20999. Error msg length can be 2048 bytes..(new versions may be more than this).

——————————- predefined Exception ———————

declare
emp_id number;
begin
select employee_id into emp_id
from employees
where department_id=1000;
dbms_output.put_line(’employee_id’||emp_id);
Exception
when no_data_found
then
dbms_output.put_line(‘No such employee exist’);
when too_many_rows
then
dbms_output.put_line(‘fetch too many row, so please use cursor’);
end;

————————————— declare block use in Exception block ————————-

declare
emp_id number;
begin
select employee_id into emp_id
from employees
where department_id=1000;
dbms_output.put_line(’employee_id’||emp_id);
Exception
when no_data_found
then
declare
sal number;
begin
dbms_output.put_line(‘No such employee exist’);
select salary into sal from employees
where employee_id=100;
dbms_output.put_line(‘SAL’||sal);
exception
when no_data_found
then
dbms_output.put_line(’employee does not exits’);
end;
dbms_output.put_line(‘fetch too many row’);
when too_many_rows
then
dbms_output.put_line(‘fetch too many row, so please use cursor’);
end;
/

———————————————- Pragma exception —————–

declare
emp_exception exception;
pragma exception_init(emp_exception,100);
emp_id number;
begin
select employee_id into emp_id from employees
where employee_id=1111;
exception
when emp_exception
then
dbms_output.put_line(‘NO RECORD’);
–dbms_output.put_line(sqlcode||chr(9)||sqlerrm);
end;
/

————————- USer defined exception(raise) ———————–

declare
emp_exception exception;
begin
update employees
set employee_id=10
where employee_id=10;
IF sql%notfound
then
raise emp_exception;
end if;
exception
when emp_exception then
dbms_output.put_line(‘HELLO’);
end;
/

NALAM Oracle Apps Technical

Sunday, 14 April 2024