karthick -- Thanks for the question regarding "Execute SQL Select statement stored in a table.", version 10.2.0.4.0

Submitted on 8-Sep-2009 3:37 Central time zone

Tom's latest followup | Bookmark | Bottom

Last updated 7-Oct-2009 15:21

You Asked

Hi Tom,

I have done the following

create table sql_statement(sqlid integer, sqlstmt clob, isselect varchar2(1))

begin
insert into sql_statement(sqlid, sqlstmt, isselect) values(1, 'insert into dept(deptno, 
dname) values(:1,:2)', 'N');
insert into sql_statement(sqlid, sqlstmt, isselect) values(2, 'delete from dept where 
deptno = :1', 'N');
insert into sql_statement(sqlid, sqlstmt, isselect) values(3, 'select deptno, deptname 
from dept where loc in (:1,:2)', 'Y');
end;

create or replace procedure sql_execute(
                                 pSqlID in integer,
                                 pBindValue in varchar2,
                                 pRefCursor out sys_refcursor,
                                 pIsError out boolean,
                                 pErrorMsg out varchar2
                                       )
as
   lSqlListIdx integer default 1;
   lStrPos     integer default 1;
   lSqlStmt    sql_statement.sqlstmt%type;
   lIsSelect   sql_statement.isselect%type;
   lSqlCursor  integer;
   lSqlOut     integer;
   lSqlList    dbms_sql.varchar2a;
   lBindId     varchar2(10);
   lBindType   varchar2(1);
   lBindValue  varchar2(4000);
   lBindCnt    integer;

begin
   pIsError := false;
   pErrorMsg := '';

   begin
      select sqlstmt, isselect into lSqlStmt, lIsSelect
        from sql_statement
       where sqlid = pSqlID;
   exception
      when no_data_found then
         pIsError := true;
         pErrorMsg := 'Invalid SQL ID ' || pSqlID;
   end;

   if pIsError = false
   then 
      lSqlCursor := dbms_sql.open_cursor;

      loop
         lSqlList(lSqlListIdx) := dbms_lob.substr(lSqlStmt, 32767, lStrPos);
         exit when lSqlList(lSqlListIdx) is null;
         lStrPos := lStrPos+32767;
         lSqlListIdx := lSqlListIdx+1;
      end loop;

      dbms_sql.parse(lSqlCursor, lSqlList, 1, lSqlListIdx, null, dbms_sql.native);
      
      lBindCnt := (length(pBindValue) - length(replace(pBindValue,'#')))+1;

      for i in (select level no, regexp_substr(pBindValue, '[^#]+', 1, level) str
                  from dual
                connect by level <= lBindCnt)
      loop
         lBindId    := substr(i.str, 1, instr(i.str,'^',1,1)-1);
         lBindType  := substr(i.str, instr(i.str,'^',1,1)+1, 
instr(i.str,'^',1,2)-(instr(i.str,'^',1,1)+1));
         lBindValue := substr(i.str, instr(i.str,'^',1,2)+1);

         case 
            when lBindType = 'N' then
               dbms_sql.bind_variable(lSqlCursor, lBindId, to_number(lBindValue));
            when lBindType = 'D' then
               dbms_sql.bind_variable(lSqlCursor, lBindId, to_date(lBindValue));
            when lBindType = 'C' then
               dbms_sql.bind_variable(lSqlCursor, lBindId, lBindValue);
         end case;
      end loop;

      lSqlOut := dbms_sql.execute(lSqlCursor);
      dbms_sql.close_cursor(lSqlCursor);
   end if;
end;

Basically sql_statement is a table which has DML statements (insert, update, delete and select)

Procedure sql_execute executes the statements stored in the table by passing the sqlid and the bind values.

Bind value is passed as follows.

'BindId^BindType^BindValue # BindId^BindType^BindValue'

With the current procedure iam able to process insert, update and delete statements sucessfully. Here is an example

declare
  lRc Sys_Refcursor;
  lIsErr boolean;
  lErrMsg varchar2(100);
begin
  sql_execute(1,'1^N^50#2^C^MarketAnalysis',lRc, lIsErr, lErrMsg);
end;
/

select * from dept
/

declare
  lRc Sys_Refcursor;
  lIsErr boolean;
  lErrMsg varchar2(100);
begin
  sql_execute(2,'1^N^50',lRc, lIsErr, lErrMsg);
end;
/

select * from dept
/

But not sure how to execute the sql select statement and return the value in Refcursor. Unfortunately iam in 10g so cant make use of the cool new 11g feature of dbms_sql.to_refcursor.

So any way to achieve it with dbms_sql or with execute immediate or anything else?

The objective here is to store SQL Statements in a Table(a sql repository) and just call the required SQL from the application using the sqlid using the sql_execute procedure.

and we said...

ugh, what an ugly "design". I can see your life will be full of performance issues, strange 'bugs', and other unpleasant side effects. I mean, it all looks "so cool", but it'll be a nightmare to maintain and enhance.

Without 11g, you will have to use

open refcursor for sql_statement;

but then you have the issue of binds, which will be intractable. You'd have to know the binds at compile time, but you have hidden all of your sql in a magic generic table - so you cannot possibly know your binds at compile time.

I would suggest you discard all of this code (I am DEAD SERIOUS) and start over. This is a bad idea from the get go. Or at least do me a favor and do not use plsql (you make it look like a good database implementation, but it isn't)

...
The objective here is to store SQL Statements in a Table(a sql repository) and just call the required SQL from the application using the sqlid using the sql_execute procedure. ...

CHANGE YOUR OBJECTIVE.

How about this for a good objective:

the objective here is to store sql statement in a plsql routine (a sql repository, you call a procedure and we run sql) and just call the required sql form the applicatoin using the STORED PROCEDURE

sorry, can I think of hacks to get you going? yes, application contexts come to mind - a fixed number of binds comes to mind. Am I going to work them out? No - it is the wrong way to approach a database application.

Reviews

September 8, 2009 - 7am Central time zone