ORACLE SQL: GETTING THE COUNT OF ROWS RETRIEVED IN SQLPLUS (WITHOUT USING FEEDBACK OPTION)

#--Below is a way we can display the total row count in the end of the sqlplus script output using Aggregate function over the entire partition

SET TERMOUT OFF
SET FEEDBACK OFF
SET SERVEROUTPUT ON
SET VERIFY OFF
SET PAGESIZE 0
COLUMN empno FORMAT 99999
COLUMN ename FORMAT a10
COLUMN ROW_COUNT NEW_VALUE TOTAL_ROWS NOPRINT
spool C:\Users\Bhaskar\Blog\Blog\example_output.txt REPLACE
SELECT 0 "ROW_COUNT" FROM DUAL:
PROMPT EMPNO|ENAME|JOB|DEPTNO
PROMPT
SELECT empno, ename, job, deptno,count(*) over () "ROW_COUNT"
FROM emp;
PROMPT
SELECT 'Row Count:('||TO_CHAR(SYSDATE,'DD-MM-YYYY')||'): '||&TOTAL_ROWS
FROM DUAL;
SPOOL OFF
CLEAR COLUMNS

#--Below is the way we can display the total rowcount in the end of the script using simply rownum

SET TERMOUT OFF 

SET FEEDBACK OFF

SET SERVEROUTPUT ON

SET VERIFY OFF

SET PAGESIZE 0

COLUMN empno FORMAT 99999

COLUMN ename FORMAT a10

COLUMN ROW_COUNT NEW_VALUE TOTAL_ROWS NOPRINT 

spool C:\Users\Bhaskar\Blog\Blog\example_output.txt REPLACE

SELECT 0 "ROW_COUNT" FROM DUAL:

PROMPT EMPNO|ENAME|JOB|DEPTNO

PROMPT

SELECT empno, ename, job, deptno,ROWNUM "ROW_COUNT"

FROM emp;

PROMPT

SELECT 'Row Count:('||TO_CHAR(SYSDATE,'DD-MM-YYYY')||'): '||&TOTAL_ROWS 

FROM DUAL;

SPOOL OFF

CLEAR COLUMNS

NOTE:

#--1). In the script above, the TOTAL_ROWS variable is used to hold the value of the column ROW_COUNT

#--2). The initial count is set to 0 by using the first SELECT query

#--3). Observe that the value in the variable TOTAL_ROWS is referenced using "&" in the finale SELECT clause

PASSING A TABLE OF RECORDS AS PARAMETER TO A ORACLE PROCEDURE

--Creating the table type to pass as parameter(inside a package)

CREATE OR REPLACE PACKAGE package_test

IS
  
     --> Declaring a RECORD type with our datatypes 
     TYPE typeEmpRec IS RECORD (ID EMP.EMPNO%TYPE, EMP.ENAME%TYPE); 

     -->a table of type record(to pass mutiple records of above type)
     TYPE typeEmpTab IS TABLE OF typeEmpRec;   

     --> Proc to fill the collection type to send as parameter                  PROCEDURE getEmpRecords;

     --> Proc that accepts the collection as parameter                          PROCEDURE fetchRecords(emp_table typeEmpTab); 
  
END package_test;

 
CREATE OR REPLACE PACKAGE BODY package_test
IS
     employees_tab typeEmpTab;
     PROCEDURE getEmpRecords
     IS
     BEGIN
         --populate the collection
         SELECT EMPNO, ENAME BULK COLLECT INTO 
         employees_tab
         FROM EMP;
         --and pass as parameter to below procedure
         fetchRecords(employees_tab);
     END getEmpRecords;

     PROCEDURE fetchRecords(emp_table in typeEmpTab)
     IS
     BEGIN
         -- perform all you want here...
        FOR I IN EMP_TABLE.FIRST..EMP_TABLE.LAST LOOP
            DBMS_OUTPUT.PUT_LINE ('Employee ID: '||emp_table(i).ID||                                       '  And Name: '||emp_table(i).NAME);

        END LOOP;
     END fetchRecords;
  
END package_test;


--To Test the above
BEGIN
    package_test.getEmpRecords;
END;

whatever procdure/function we define in package specification .. we need to define in package body. Variables need not be

In the package specification we have 2 procedures declared:

create or replace package package_temp is

         procedure proc1;

         procedure proc2;

         v_temp    varchar2(10);

end package_temp;

But, in the package body only proc1 is defined:

create or replace package body package_temp is

         procedure proc1 is

         begin

               dbms_output.put_line ('proc1 ');

         end proc1;

end package_temp;

This fails with compilation error:

  PLS-00323: subprogram or cursor 'proc2' is declared in a package specification and must be defined in the package body

This can be observed from user_errors table:

　

select *

from user_errors

where name like 'package_temp ';

The package body will be in INVALID state:

select *

from user_objects

where object_name like 'package_temp';

To correct the same, we need to define the proc2 as well:

create or replace package body package_temp is

           procedure proc1 is

           begin

               dbms_output.put_line ('proc1');

           end proc1;

           procedure proc2 is

           begin

                  dbms_output.put_line ('proc2');

           end proc2;

 end package_temp;

An example of Index by varchar2 and fetching values corresponding to it

DECLARE

  TYPE typeParamRec IS RECORD (param param_tbl.param_name%TYPE,

                               value param_tbl.param_value%TYPE);

                             

  TYPE typeParamTab IS TABLE OF typeParamRec

                      INDEX BY VARCHAR2(5);

  Paramtab typeParamTab;

BEGIN

  SELECT param_name, TO_NUMBER(param_value)*id

  INTO Paramtab('High').param, Paramtab('High').value

  FROM  param_tbl

  WHERE risk_class = 'High';

  SELECT param_name, TO_NUMBER(param_value)*id

  INTO Paramtab('Low').param, Paramtab('Low').value

  FROM  param_tbl

  WHERE risk_class = 'Low';

  DBMS_OUTPUT.PUT_LINE

          ('High & '||Paramtab('High').param  ||' & '||Paramtab('High').value);

  DBMS_OUTPUT.PUT_LINE

          ('Low & '||Paramtab('Low').param  ||' & '||Paramtab('Low').value);

END;

Analyze and Commit

Oracle database implicitly commits the current transaction before and after every DDL statement.

ANALYZE information on a table, index or a cluster also comes as part of DDL.

Suppose you have updated a table and havenot COMMITed the changes. Now in the same transaction, if you perform ANALYZE operation, the entire transaction, which includes theses changes, gets COMMITed