Oracle Database

Using 'RETURN_RESULT' WITH Clause in procedure/Function in Oracle 12c Oracle 12c allows a procedure to return a list of rows by using RETURN_RESULT procedure of DBMS_SQL package. Rather than defining explicit reference cursor out parameters, the RETURN_RESULT procedure in the DBMS_SQL package allows you to pass them out implicitly. The following procedure returns list of rows from SALARY table as return value from a procedure. CREATE OR REPLACE PROCEDURE Get_salary AS Salary_cursor SYS_REFCURSOR; BEGIN    OPEN salary_cursor FOR    SELECT * from pay_payment_master;    DBMS_SQL.RETURN_RESULT(salary_cursor); END; You can call the procedure and find the list of rows from the procedure as follows: EXECUTE Get_salary Functions in the WITH Clause: The declaration section of the WITH clause can be used to define PL/SQL functions, as shown below. WITH   FUNCTION  expert(EMPLOYMENT_DATE date) RETURN NUMBER IS  ...

DBA interview Question and Answer Part 2 I have configured the RMAN with Recovery window of 3 days but on my backup destination only one days archive log is visible while 3 days database backup is available there why? I go through the issue by checking the backup details using the list command. I found there is already 3 days database as well as archivelog backup list is available. Also the backup is in Recoverable backup. Thus it is clear due to any reason the backup is not stored on Backup place. Connect rman target database with catalog List backup Summary; List Archivelog All; List Backup Recoverable; When I check the db_recovery_dest_size , it is 5 GB and our flash-recovery area is almost full because of that it will automatically delete archive logs from backup location. When I increase the db_recovery_dest_size then it is working fine. If one or all of control file is get corrupted and you are unable to start database then how can you perform recovery? If ...

How to prevent table from Altering If you need to secure your table from any kind of DDL actions then following example will help you to understand actually what happens when locks are disabled on the TABLE. SQL> alter table temp_payroll disable table lock; Table altered. Now no one can drop as well as truncate the table as table locks are disabling. SQL> drop table temp_payroll; drop table temp_payroll ORA-00069: cannot acquire lock — table locks disabled for temp_payroll SQL> truncate table temp_payroll; truncate table temp_payroll ORA-00069: cannot acquire lock — table locks disabled for temp_payroll Also you are not able to modify and drop the column but you are able to add the column. SQL> alter table temp_payroll modify amount Number (10,2); ORA-00069: cannot acquire lock — table locks disabled for temp_payroll SQL> alter table temp_payroll drop column Amount Number (10,2) alter table temp_payroll drop column Amount Number (10,2) ORA...

To Extract Value in Oracle Report from exiting another Table Create one place holder column of the related type value then write the select statement into the formula column. For Example: Display the Supplier_name from Existing another table: GL_Master Item Name: CP_SUPPLIER_NAME Item Type: Place holder Data type: Char 100 Item Name: CF_CALC Item Type: Formula Column Data type: Number – 10 PL/SQL Formula: function CF_CALCFormula return Number is begin SELECT UPPER(M_DESC) into   :CP_SUPPLIER_NAME FROM GL_MASTER WHERE GL_MASTER.VEN_NUMBER = :SUPP_CODE; RETURN 0; end; To Extract Check flag and List item value in Oracle Report Create a place holder column to store the value and then write the code into formula column. IF :FMCG_FLAG = 1 THEN       :CP_FMCG := 'X'; else       :CP_FMCG := ' '; END IF; IF :NON_FOOD = 1 THEN       :CP_NON_FOOD := 'X';...

How to Convert NetTotal Amount in words from digit in oracle Report Use the Summary column to find the sum of the details report amount then later use the formula column to convert this NetTotal amount into words. Create Summary Column CS_1 Number 14,2 Function: Sum Source : Amount (base table column) Reset at: Block_name Through this way you are able to find the sum of the details column Amount. Now to convert Amount in words from digit, Create a formula column function CF_AMOUNT_IN_WORDSFormula return Char is vRiyals NUMBER; VHalala NUMBER; begin   VRIYALS := TRUNC(:cs_1);   VHalala := TRUNC((:CS_1 - VRIYALS)*100);     IF VRIYALS > 0 THEN   IF VHALALA > 0 THEN    RETURN('SAR '||NumberInWords(VRIYALS)|| ' & ' || NumberInWords(VHalala)||' Halala Only');   ELSE    return('SAR '||NumberInWords(VRIYALS)||' Only');   END IF;   ELSE   IF VHALALA > 0 THEN    RETURN('SAR...

DBA interview Question and Answer What is basic difference between V$ view to GV$ or V$ and V_$ view? The V_$ (V$ is the public synonym for V_$ views) view are called dynamic performance views . They are continuously updated while a database is open in use and their contents related primary to performance. Select owner , object_name , object_type  from dba_objects where object_name like '%SESSION'    and   object_name like 'V%' ; OWNER OBJECT_NAME    OBJECT_TYPE -----  ----------- ------------- SYS    V_$HS_SESSION VIEW SYS    V_$LOGMNR_SESSION    VIEW SYS    V_$PX_SESSION VIEW SYS    V_$SESSION    VIEW Where as GV$ views are called Global dynamic performance view and retrieve information about all started instance accessing one RAC database in contrast with dynamic performance views which retrieves information about local i...