Auto Generated Codes IN PLSQL

Back in 2013 when i started with PL/SQL, i was given an assignment to write a function which would return the row type for a given table, that would be used as a input parameter. Now with the advent oracle advance features and PFT we have ways to write it real quick but back then, it consumed a lot of my time and ended up writing a messy dynamic query.

Why would anyone give such requirement. Working on projects we often Encounter some task which is kind of repetitive. One of the ways to combat such Encounter, is write code which would write code for you.

In this post I would list 3 generic codes which is more or less used by most of us in PL/SQL programming.

NUMBER OF RECORDS IN DIFFERENT TABLES: Not often you would be using row counts of different tables in your project but it cab be very handy in Data migration related projects wherein you would match the no of records in all the tables of two schema.

CREATE OR REPLACE FUNCTION tabCount (
tab IN VARCHAR2, whr IN VARCHAR2 := NULL)
RETURN INTEGER
IS
retval INTEGER;
BEGIN
EXECUTE IMMEDIATE
'SELECT COUNT(*)
FROM ' ||  DBMS_ASSERT.SQL_OBJECT_NAME(tab) ||
' WHERE ' || NVL (whr, '1=1')
INTO retval;
RETURN retval;
END;

The above code creates a function for me to count the records.

FOR instance if i want to have the number of records in employee table having department =30

SELECT tabcount('employees', 'department_id =30') from  dual;
--------------------------------------------------------------
This returns 6

This code can also be very handy in data migration projects or any such which requires a records comparison between two schema.

You can use the below to get the counts of all the tables in the current schema

DECLARE
  result number;
BEGIN
   FOR i in (SELECT TABLE_NAME FROM USER_TABLES)
  LOOP
   result := tabCount(i.table_name);
  DBMS_OUTPUT.put_line(result || ' ' || i.table_name);
   end loop;
end;

Coming back to where i started lets Suppose you are asked to write functions which take the primary key of a table as input

i/p and returns row type as o/p.

for this you have to write as many functions as is the tables in your DB

FUNCTION returning rowtypes of table being passed

Let's try to do it in a more generalized way.

create or replace procedure genlookup(tab_in in varchar2,col_in in varchar2)
is
begin
dbms_output.put_line('create or replace function ' || tab_in || '_row_for(');
dbms_output.put_line(' ' || col_in||'_in  IN ' || tab_in || '.'||col_in||'%type)');
dbms_output.put_line('return '||tab_in||'%rowtype');
dbms_output.put_line('is');
dbms_output.put_line('l_return '||tab_in||'%rowtype;');
dbms_output.put_line('begin');
dbms_output.put_line('select * into l_return from ' ||tab_in);
dbms_output.put_line('where ' || col_in ||'='||col_in||'_in;');
dbms_output.put_line('return l_return;');
dbms_output.put_line('end;');
end;


begin
for i in (select ccol.table_name,ccol.column_name pkycol_name
            from user_constraints cons,user_cons_columns ccol
             where cons.owner = ccol.owner


             and cons.constraint_name = ccol.constraint_name
             and cons.table_name like '%EMP%' and cons.CONSTRAINT_TYPE = 'P')
 
  loop
   genlookup(i.table_name,i.pkycol_name);
   end loop;
   end;

Above code will automatically generate PLSQL FUNCTION For every table in your database. I have used TABLE like EMP to restrict the function to generate code for all the tables carrying EMP in my DATABASE. Try running it.

Earlier this week, i saw a generic code generation on oracle live posted by Steven Feuerstein. A man of great fame in this domain and a source of inspiration for developers like us.

He showed some ways to generate code to move to string indexed collection. I am copying his code below but for details you can visit his codes.

Generate code to move rows into a collection

CREATE OR REPLACE PROCEDURE gen_select_into_index_by2 (query_in    IN VARCHAR2, 
                                                      table_in    IN VARCHAR2, 
                                                      column_in   IN VARCHAR2) 
IS 
   c_template   VARCHAR2(32767) 
      := q'[DECLARE 
   CURSOR c IS #QUERY#; 
   
   TYPE vct IS TABLE OF c%ROWTYPE INDEX BY VARCHAR2(32767); 
 
   l_rows_by_varchar2   vct; 
   l_index VARCHAR2(32767); 
BEGIN 
   FOR rec IN (#QUERY#) 
   LOOP 
      l_rows_by_varchar2 (rec.#COLUMN#) := rec; 
   END LOOP; 
    
   CLOSE c; 
 
   /* Iterate through the string-indexed collection */ 
    
   l_index := l_rows_by_varchar2.FIRST; 
    
   WHILE l_index IS NOT NULL 
   LOOP 
      DBMS_OUTPUT.PUT_LINE (l_rows_by_varchar2(l_index).#COLUMN#); 
      l_index := l_rows_by_varchar2.NEXT (l_index); 
   END LOOP; 
END;]'; 
BEGIN 
   DBMS_OUTPUT.put_line ( 
      REPLACE ( 
         REPLACE ( 
            REPLACE (c_template, '#QUERY#', query_in), 
            '#TABLE#', table_in), 
         '#COLUMN#', column_in)); 
END;

begin 
gen_select_into_index_by2 ( 
   'select last_name, first_name, employee_id  
      from employees where department_id = 30', 'employees', 'last_name'); 
end;
-----------------------------------------------------------------
This would generate code for fetching the result into a string indexed collection.

All of the three codes above are beautiful examples of automating the code writing and reducing much of our efforts in re- writing similar codes, gosh these days i am also kind of overwhelmed with repetitive. I must find a way to do it from a code rather than creating scripts every time.

QUIZ: 

I encountered something similar years ago. Would you try ?


BEGIN


 WHILE sysdate = sysdate LOOP
 NULL;
 END LOOP;
END;
/
 
DECLARE


 x DATE;
BEGIN
 LOOP
 BEGIN
 SELECT null INTO x FROM dual WHERE sysdate =

 sysdate;
 EXCEPTION
 WHEN NO_DATA_FOUND THEN EXIT;
 END;
 END LOOP;
END;


QUESTION :  Are the loops above both finite, both infinite or is it so that one of them is finite 
and the other one infinite?


ANSWER :

The first is finite while the other is infinite.

Follow this link for Polymorphic table functions(PFT) , which can really reduce a lot of your efforts while writing these types of subprograms:

PFT