Using Oracle EXPAND_SQL_TEXT

Database tables are not static entities; besides the usual insert/update/delete events, occasional DDL can be executed to add columns, drop columns or add needed constraints or indexes. The first two items can create problems with stored procedures, packages, functions and possibly triggers by changing the number of columns that need to be processed when explicit variables are used. If the programmer used a record variable (as shown below) then no issues are likely to be seen:


SPLEEBO @ gwankus > 
SPLEEBO @ gwankus > --
SPLEEBO @ gwankus > -- Code to display all employee information
SPLEEBO @ gwankus > --
SPLEEBO @ gwankus > -- This should succeed
SPLEEBO @ gwankus > --
SPLEEBO @ gwankus > declare
  2  	     cursor get_emp_info is
  3  	     select * From emp;
  4  begin
  5  	     for emp_rec in get_emp_info loop
  6  		     dbms_output.put_line(emp_rec.empno||' '||emp_rec.ename||' '||emp_rec.job||' '||emp_rec.mgr||' '||emp_rec.hiredate||' '||emp_rec.sal||' '||emp_rec.comm||' '||emp_rec.deptno);
  7  	     end loop;
  8  end;
  9  /
7369 SMITH CLERK 7902 17-DEC-80 800  20                                         
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30                                  
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30                                   
7566 JONES MANAGER 7839 02-APR-81 2975  20                                      
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30                                
7698 BLAKE MANAGER 7839 01-MAY-81 2850  30                                      
7782 CLARK MANAGER 7839 09-JUN-81 2450  10                                      
7788 SPLEEBO ANALYST 7566 09-DEC-82 3000  20                                      
7839 KING PRESIDENT  17-NOV-81 5000  10                                         
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30                                   
...
6100 MILLER CLERK 7782 23-SEP-97 1300  10                                       
SPLEEBO @ gwankus > 

If explicit variables are used, then it’s likely the code will fail because of the change. It can be disconcerting to see the following in an error message:


PLS-00394: wrong number of values in the INTO list of a FETCH statement 

Of course investigation will reveal an ‘alter table …’ statement was executed prior to the failure or a describe on the affected table will report a different number of columns than were present when the code was originally written. Once this fact is known, fixing the problem is up to the developer who wrote it. They will have choices exist on how to go about making such changes.

Looking at an example using explicitly coded variables and an added column, let’s explore using a procedure from the DBMS_UTILITY package, EXPAND_SQL_TEXT, to generate a complete column list from the modified table to use as a reference for code changes. First, the original code and the error it generates:


SPLEEBO @ gwankus > 
SPLEEBO @ gwankus > --
SPLEEBO @ gwankus > -- Code to display all employee information
SPLEEBO @ gwankus > --
SPLEEBO @ gwankus > -- Fails because not enough variables are declared
SPLEEBO @ gwankus > -- and populated
SPLEEBO @ gwankus > --
SPLEEBO @ gwankus > declare
  2  	     v_empno emp.empno%type;
  3  	     v_ename emp.ename%type;
  4  	     v_job   emp.job%type;
  5  	     v_mgr   emp.mgr%type;
  6  	     v_hiredate      emp.hiredate%type;
  7  	     v_sal   emp.sal%type;
  8  	     v_comm  emp.comm%type;
  9  	     v_deptno	     emp.deptno%type;
 10  
 11  	     cursor get_emp_info is
 12  	     select * From emp;
 13  begin
 14  	     open get_emp_info;
 15  	     loop
 16  		     fetch get_emp_info into v_empno, v_ename, v_job, v_mgr, v_hiredate, v_sal, v_comm, v_deptno;
 17  		     exit when get_emp_info%notfound;
 18  		     dbms_output.put_line(v_empno||' '||v_ename||' '||v_job||' '||v_mgr||' '||v_hiredate||'  '||v_sal||' '||v_comm||' '||v_deptno);
 19  	     end loop;
 20  end;
 21  /
		fetch get_emp_info into v_empno, v_ename, v_job, v_mgr, v_hiredate, v_sal, v_comm, v_deptno;
		*
ERROR at line 16:
ORA-06550: line 16, column 3: 
PLS-00394: wrong number of values in the INTO list of a FETCH statement 
ORA-06550: line 16, column 3: 
PL/SQL: SQL Statement ignored 


SPLEEBO @ gwankus > 
SPLEEBO @ gwankus > 

Create a copy of the original code (to preserve it should it be needed) and use DBMS_UTILITY.EXPAND_SQL_TEXT to generate, as a comment, the expanded results of a ‘select * from …” query against the modified table. The procedure requires a CLOB variable be declared to hold the results of the procedure call and also requires the specific ‘select *’ query to operate on. The code shown below generates this output as a comment and can be reused by editing the table name in the supplied query:


SPLEEBO @ gwankus > --
SPLEEBO @ gwankus >-- Expand the 'select *' query to see all
SPLEEBO @ gwankus >-- of the returned columns
SPLEEBO @ gwankus >--
SPLEEBO @ gwankus >-- Add the output to the failing script
SPLEEBO @ gwankus >-- to facilitate corrective edits
SPLEEBO @ gwankus >--
SPLEEBO @ gwankus >spool new_query.sql
SPLEEBO @ gwankus >declare
  2    l_clob clob;
  3  begin
  4    dbms_utility.expand_sql_text (
  5      input_sql_text  => 'select * from emp',
  6      output_sql_text => l_clob
  7    );
  8
  9    dbms_output.put_line('/*');
 10    dbms_output.put_line(lower(l_clob));
 11    dbms_output.put_line('*/');
 12  end;
 13  /

/*                                                                              
select "a1"."empno" "empno","a1"."ename" "ename","a1"."job" "job","a1"."mgr"    
"mgr","a1"."hiredate" "hiredate","a1"."sal" "sal","a1"."comm"                   
"comm","a1"."deptno" "deptno","a1"."term_dt" "term_dt" from "scott"."emp" "a1"  
*/                                                                              

PL/SQL procedure successfully completed.

SPLEEBO @ gwankus > spool off

Create a working copy and append the output of the above query to it. Then, open the modified file in the editor of choice to make the necessary changes:


SPLEEBO @ gwankus > --
SPLEEBO @ gwankus > -- Copy the original script to preserve code
SPLEEBO @ gwankus > --
SPLEEBO @ gwankus > !cp emp_info_pl_orig.sql emp_info_pl.sql

SPLEEBO @ gwankus > --
SPLEEBO @ gwankus > -- Add the output generated above as a comment
SPLEEBO @ gwankus > -- for reference purposes
SPLEEBO @ gwankus > --
SPLEEBO @ gwankus > !cat new_query.sql >> emp_info_pl.sql

SPLEEBO @ gwankus > 
SPLEEBO @ gwankus > --
SPLEEBO @ gwankus > -- Edit the script copy to fix the issue by
SPLEEBO @ gwankus > -- adding the necessary variable declaration
SPLEEBO @ gwankus > -- and editing the code to populate it
SPLEEBO @ gwankus > --
SPLEEBO @ gwankus > !vi emp_info_pl.sql

declare
        v_empno emp.empno%type;
        v_ename emp.ename%type;
        v_job   emp.job%type;
        v_mgr   emp.mgr%type;
        v_hiredate      emp.hiredate%type;
        v_sal   emp.sal%type;
        v_comm  emp.comm%type;
        v_deptno        emp.deptno%type;
        v_term_dt        emp.term_dt%type;

        cursor get_emp_info is
        select * From emp;
begin
        open get_emp_info;
        loop
                fetch get_emp_info into v_empno, v_ename, v_job, v_mgr, v_hiredate, v_sal, v_comm, v_deptno, v_term_dt;
                exit when get_emp_info%notfound;
                dbms_output.put_line(v_empno||' '||v_ename||' '||v_job||' '||v_mgr||' '||v_hiredate||' '||v_sal||' 
'||v_comm||' '||v_deptno||' '||v_term_dt); end loop; end; / /* select "a1"."empno" "empno","a1"."ename" "ename","a1"."job" "job","a1"."mgr" "mgr","a1"."hiredate" "hiredate","a1"."sal" "sal","a1"."comm" "comm","a1"."deptno" "deptno","a1"."term_dt" "term_dt" from "scott"."emp" "a1" */

Test the changes to ensure everything works as expected:


SPLEEBO @ gwankus > 
SPLEEBO @ gwankus > set head on feedback on pagesize 60
SPLEEBO @ gwankus > --
SPLEEBO @ gwankus > -- Run modified code
SPLEEBO @ gwankus > --
SPLEEBO @ gwankus > -- The anonymous block now completes
SPLEEBO @ gwankus > -- without error
SPLEEBO @ gwankus > --
SPLEEBO @ gwankus > @emp_info_pl
SPLEEBO @ gwankus > declare
  2  	     v_empno emp.empno%type;
  3  	     v_ename emp.ename%type;
  4  	     v_job   emp.job%type;
  5  	     v_mgr   emp.mgr%type;
  6  	     v_hiredate      emp.hiredate%type;
  7  	     v_sal   emp.sal%type;
  8  	     v_comm  emp.comm%type;
  9  	     v_deptno	     emp.deptno%type;
 10  	     v_term_dt	      emp.term_dt%type;
 11  
 12  	     cursor get_emp_info is
 13  	     select * From emp;
 14  begin
 15  	     open get_emp_info;
 16  	     loop
 17  		     fetch get_emp_info into v_empno, v_ename, v_job, v_mgr, v_hiredate, v_sal, v_comm, v_deptno, v_term_dt;
 18  		     exit when get_emp_info%notfound;
 19  		     dbms_output.put_line(v_empno||' '||v_ename||' '||v_job||' '||v_mgr||' '||v_hiredate||' '||v_sal||' '||v_comm||' '||v_deptno||' '||v_term_dt);
 20  	     end loop;
 21  end;
 22  /
7369 SMITH CLERK 7902 17-DEC-80 800  20 31-DEC-99                               
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 31-DEC-99                        
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 31-DEC-99                         
7566 JONES MANAGER 7839 02-APR-81 2975  20 31-DEC-99                            
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 31-DEC-99                      
7698 BLAKE MANAGER 7839 01-MAY-81 2850  30 31-DEC-99                            
7782 CLARK MANAGER 7839 09-JUN-81 2450  10 31-DEC-99                            
7788 SPLEEBO ANALYST 7566 09-DEC-82 3000  20 31-DEC-99                            
7839 KING PRESIDENT  17-NOV-81 5000  10 31-DEC-99                               
...
6100 MILLER CLERK 7782 23-SEP-97 1300  10 31-DEC-99                             

PL/SQL procedure successfully completed.

SPLEEBO @ gwankus > --

This same technique can be used on tables with a relatively large number of columns:


SPLEEBO @ gwankus > --
SPLEEBO @ gwankus > -- Let's take another example
SPLEEBO @ gwankus > --
SPLEEBO @ gwankus > -- Create a table with 21 columns
SPLEEBO @ gwankus > -- and populate it
SPLEEBO @ gwankus > --
SPLEEBO @ gwankus > @lotsa_cols
SPLEEBO @ gwankus > create table lotsacols(
  2  a1      number,
  3  a2      number,
  4  a3      number,
  5  a4      number,
  6  a5      number,
  7  a6      number,
  8  a7      number,
  9  a8      number,
 10  a9      number,
 11  a10     number,
 12  a11     number,
 13  a12     number,
 14  a13     number,
 15  a14     number,
 16  a15     number,
 17  a16     number,
 18  a17     number,
 19  a18     number,
 20  a19     number,
 21  a20     number,
 22  a21     number);

Table created.

SPLEEBO @ gwankus > 
SPLEEBO @ gwankus > begin
  2  	     for z in 1..1000 loop
  3  		     insert into lotsacols(a1,a2,a3,a4,a5,a6,a7,a8,a9,a10,a11,a12,a13,a14,a15,a16,a17,a18,a19,a20,a21)
  4  		     values(mod(z,3)+1,mod(z,13)+1,mod(z,21)+1,mod(z,34)+1,mod(z,47)+1,mod(z,53)+1,
mod(z,67)+1,mod(z,79)+1,mod(z,81)+1,mod(z,97)+1,mod(z,3)+1,mod(z,7)+1,mod(z,6)+1,mod(z,2)+1,
mod(z,9)+1,mod(z,8)+1,mod(z,101)+1,mod(z,407)+1,mod(z,313)+1,mod(z,271)+1,mod(z,133)+1); 5 end loop; 6 7 commit; 8 end; 9 / PL/SQL procedure successfully completed. SPLEEBO @ gwankus > SPLEEBO @ gwankus > SPLEEBO @ gwankus > -- SPLEEBO @ gwankus > -- Rather than do a DESC on the table SPLEEBO @ gwankus > -- use expand_sql_text to generate the SPLEEBO @ gwankus > -- column list and spool it to a file for SPLEEBO @ gwankus > -- later use SPLEEBO @ gwankus > -- SPLEEBO @ gwankus > -- Edit that file to create a working block SPLEEBO @ gwankus > -- of PL/SQL to generate results from the SPLEEBO @ gwankus > -- table data SPLEEBO @ gwankus > -- /* select "a1"."a1" "a1","a1"."a2" "a2","a1"."a3" "a3","a1"."a4" "a4","a1"."a5" "a5","a1"."a6" "a6","a1"."a7" "a7","a1"."a8" "a8","a1"."a9" "a9","a1"."a10" "a10","a1"."a11" "a11","a1"."a12" "a12","a1"."a13" "a13","a1"."a14" "a14","a1"."a15" "a15","a1"."a16" "a16","a1"."a17" "a17","a1"."a18" "a18","a1"."a19" "a19","a1"."a20" "a20","a1"."a21" "a21" from "scott"."lotsacols" "a1" */ declare cursor get_lotsa is select * From lotsacols; begin dbms_output.put_line('Your lucky LOTTO numbers are: '); for lotsa in get_lotsa loop dbms_output.put_line(lotsa.a1||' '||lotsa.a6||' '||lotsa.a7||' '||lotsa.a13||' '||lotsa.a17||' '||lotsa.a20); end loop; end; / SPLEEBO @ gwankus > -- SPLEEBO @ gwankus > -- Execute the code SPLEEBO @ gwankus > -- Your lucky LOTTO numbers are: 2 50 8 5 7 209 3 51 9 6 8 210 1 52 10 1 9 211 ... 2 53 11 2 10 212 1 32 51 1 14 179 2 33 52 2 15 180 3 34 53 3 16 181 PL/SQL procedure successfully completed.

Using EXPAND_SQL_TEXT can be easier than generating a table listing using DESC and spooling the results as it creates a smaller file that can easily be incorporated into a change procedure. Since the expanded SQL text is generated as a comment, it can remain after the edits are completed in the event further code changes are required or desired.

The choice is the developer’s to make, but it certainly seems easier to let Oracle generate usable output in a somewhat automated fashion to facilitate code edits. In the end it’s whatever the developer is comfortable with that matters most. But, it might be worth investigating using EXPAND_SQL_TEXT to put that reference information in the script being edited and possibly avoid getting lost between two screens of code. That could save editing time.

# # #

See articles by David Fitzjarrell

David Fitzjarrell
David Fitzjarrell
David Fitzjarrell has more than 20 years of administration experience with various releases of the Oracle DBMS. He has installed the Oracle software on many platforms, including UNIX, Windows and Linux, and monitored and tuned performance in those environments. He is knowledgeable in the traditional tools for performance tuning – the Oracle Wait Interface, Statspack, event 10046 and 10053 traces, tkprof, explain plan and autotrace – and has used these to great advantage at the U.S. Postal Service, American Airlines/SABRE, ConocoPhilips and SiriusXM Radio, among others, to increase throughput and improve the quality of the production system. He has also set up scripts to regularly monitor available space and set thresholds to notify DBAs of impending space shortages before they affect the production environment. These scripts generate data which can also used to trend database growth over time, aiding in capacity planning. He has used RMAN, Streams, RAC and Data Guard in Oracle installations to ensure full recoverability and failover capabilities as well as high availability, and has configured a 'cascading' set of DR databases using the primary DR databases as the source, managing the archivelog transfers manually and montoring, through scripts, the health of these secondary DR databases. He has also used ASM, ASMM and ASSM to improve performance and manage storage and shared memory.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles