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;

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

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
/