Home » SQL & PL/SQL » SQL & PL/SQL » Repeated static SQL sections in PL/SQL packages (Oracle 9i/10g)
Repeated static SQL sections in PL/SQL packages [message #377583] Tue, 23 December 2008 10:49 Go to next message
dandormont
Messages: 12
Registered: April 2006
Junior Member
For my application (Java mostly) I am using stored procedures with static SQL to take advantage of the security and performance benefits that offers. But I find I end up with a lot of repeated code that looks like:
create or replace package body mypack is
procedure oneway(myid in integer, mycur out sys_refcursor) is 
begin
   open mycur for select col_2, col_3, col_4, col_5 from my_table where col_1 = myid
   and <some_condition>;
end oneway;

procedure anotherway(myid in integer, mycur out sys_refcursor) is 
begin
   open mycur for select col_2, col_3, col_4, col_5 from my_table where col_1 = myid
   and <some_other_condition>;
end anotherway;
end mypack;

Assume that some_condition and some_other_condition differ meaningfully (different columns, different subselects) such that the difference cannot be bridged with bind variables.

Now if I want to add col_6 to my select clause I have to do it in all the procedures manually. What can I do to simplify this task? Is there some way to generate the packages from stubs and take care of the whole compilation process (I currently use Eclipse for my app development and PL/SQL developer for PL/SQL). thanks,
Re: Repeated static SQL sections in PL/SQL packages [message #377592 is a reply to message #377583] Tue, 23 December 2008 12:41 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Simple code generator
-- run from sqlplus
set echo off
set serveroutput on size 1000000
declare
  l_tab varchar2(30);
begin
  for i in (select table_name from user_tables where table_name like '%8%') loop
    dbms_output.put_line('create or replace view v_'||i.table_name||' as (select');
    for j in (select column_name, decode(column_id, 1, null, ',') comma
                from user_tab_columns 
               where column_name != 'SSN'
                 and table_name = i.table_name
               order by column_id) loop
      dbms_output.put_line(j.comma||j.column_name);
    end loop;
    dbms_output.put_line('from '||i.table_name||');');
  end loop;
end;
/


Your ref cursor can be completely dynamic, so you can keep a single template statement and modify as needed for your different cases. Bear in mind that if it's completely dynamic, your compilation won't verify existence of the table you want to access at runtime like your example will...
create table t1 (col1 varchar2(10), col2 varchar2(10));
insert into t1 values ('FirstCol', 'SecondCol');

CREATE OR REPLACE PACKAGE ref_cur_tst
IS
   -- weak ref cursor
   TYPE t_cur IS REF CURSOR; 

   PROCEDURE get_t1 (cv_cur IN OUT t_cur, p_col IN varchar2);
END;
/
CREATE OR REPLACE PACKAGE BODY ref_cur_tst
AS
   PROCEDURE get_t1 (cv_cur IN OUT t_cur, p_col IN varchar2)
   IS
   BEGIN
      OPEN cv_cur FOR 'SELECT '||p_col||' FROM t1';
   END;
END;
/


set serveroutput on;

DECLARE
   a       varchar2(10);
   cv_c1   ref_cur_tst.t_cur;
   p_col  varchar2(20) := 'COL1';
BEGIN
   ref_cur_tst.get_t1 (cv_c1, p_col);

   LOOP
      FETCH cv_c1 INTO a;
      EXIT WHEN cv_c1%NOTFOUND;
      DBMS_OUTPUT.put_line (a);
   END LOOP;
   CLOSE cv_c1;
END;
/


A bit off the topic, but it's easy to add a compile time dependency to the table you want to reference:
v_compile_chk my_table.col1%type;

[Updated on: Tue, 23 December 2008 12:45]

Report message to a moderator

Re: Repeated static SQL sections in PL/SQL packages [message #377593 is a reply to message #377592] Tue, 23 December 2008 12:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
OPEN cv_cur FOR 'SELECT '||p_col||' FROM t1';


Now Google for SQL INJECTION.

Regards
Michel
Re: Repeated static SQL sections in PL/SQL packages [message #377596 is a reply to message #377592] Tue, 23 December 2008 14:30 Go to previous messageGo to next message
dandormont
Messages: 12
Registered: April 2006
Junior Member
Using PL/SQL itself to generate views is an interesting idea. So I guess I could regenerate these views every time I need to make the kind of change I'm talking about here, and then query these views howerver I want.

The second idea is not bad, but as Michel said there's a risk of SQL injection (though this can be mitigated by making sure that part of the input only comes from the program itself, not from the user). Even then though, we lose one main advantage of static SQL: having the query precompiled, execution plan prepared etc. So I'd rather avoid that.
Re: Repeated static SQL sections in PL/SQL packages [message #377611 is a reply to message #377596] Tue, 23 December 2008 21:21 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Well, I'm not suggesting that views are a solution - that snippet just shows how to build a simple code generator.

SQL injection is something to be aware of, but I agree that if the input is programatically controlled you are fine.

Just because the SQL is dynamic, doesnt mean it's inefficient. The first time you run a query it will be parsed but then will be cached. When you compile pl/sql, it's not cached ready to run (I'm pretty sure of that). Bind variables (rather than literals in the sql) help to ensure that subsequent executions (with different values) is still efficient.
Previous Topic: WITH clause | How to use it?
Next Topic: Help with PLSQL code for searching
Goto Forum:
  


Current Time: Sun Dec 04 23:08:20 CST 2016

Total time taken to generate the page: 0.09163 seconds