Home » Other » Test » test - dynmic sql execution
test - dynmic sql execution [message #526385] Mon, 10 October 2011 15:38
ora1980
Messages: 251
Registered: May 2008
Senior Member


PROCEDURE p_check (vi_sid   IN     INTEGER,
                              other_sid      IN     INTEGER DEFAULT 0,
                              vi_grp_id      IN     INTEGER,
                              vi_alt_flag    IN     VARCHAR2,
                              vi_err_str     IN OUT VARCHAR2)
IS
   TYPE type_error IS TABLE OF code_table%ROWTYPE;

   vtype_error   type_error;
BEGIN
  
   v_err_string := vi_err_str;
   v_temp_string := vi_err_str;

   IF vi_grp_id > 0
   THEN
      p_init (vi_sid, other_sid); -- all columns init to null

      IF other_sid > 0
      THEN
         v_source := 'othersource';
      ELSE
         v_source := 'mysource';
      END IF; -- find out source 



      IF v_source = 'othersource'
      THEN
         SELECT *
           BULK COLLECT INTO vtype_error
           FROM code_table
          WHERE     err_group = vi_grp_id
                AND (alt_flag IS NULL OR alt_flag = vi_alt_flag)
                AND NVL (s_flag, '*') <> 'GO';
      ELSE
         IF vi_alt_flag = 'N'   ---  Non-alt run -- Execute all NON alt errors
         THEN
            SELECT *
              BULK COLLECT INTO vtype_error
              FROM code_table
             WHERE     err_group = vi_grp_id
                   AND (alt_flag IS NULL OR alt_flag = vi_alt_flag)
                   AND NVL (s_flag, '*') <> 'LM';
         
         ELSE       --- mysource alt run -- Execute all NON alt and alt errors
            SELECT *
              BULK COLLECT INTO vtype_error
              FROM code_table
             WHERE err_group = vi_grp_id AND NVL (s_flag, '*') <> 'LM';
         END IF;
      END IF;



      IF vtype_error.COUNT > 0
      THEN
         FOR i IN vtype_error.FIRST .. vtype_error.COUNT
         LOOP
            v_fun_name := SUBSTR (vtype_error (i).ora_function, 1, 50);
            DBMS_OUTPUT.put_line ('v_fun_name: ' || v_fun_name);
            ---Do execute immediate
            DBMS_OUTPUT.put_line ('i : ' || i);
            v_sql :=
               'select ' || vtype_error (i).ora_function || '  from dual';

            EXECUTE IMMEDIATE v_sql
               INTO vi_err_str
               USING vtype_error (i).gov_err_code, v_source, v_err_string; 



            IF LENGTH (vi_err_str) <> NVL (LENGTH (v_err_string), 0)
            THEN
               DBMS_OUTPUT.put_line ('Log it ..........');
               p_log (
                  vi_sid,
                  pkg_excep.recname.lnum,
                  vtype_error (i).err_code,
                  'Set Error code: ' || vtype_error (i).err_code,
                  (CASE
                      WHEN v_source = 'othersource'
                      THEN
                         'C'
                      WHEN (v_source = 'mysource'
                            AND NVL (pkg_excep.recname.h_flag, 'N') = 'Y')
                      THEN
                         vtype_error (i).mysource_err_1
                      WHEN (v_source = 'mysource'
                            AND NVL (pkg_excep.recname.h_flag, 'N') = 'N')
                      THEN
                         vtype_error (i).mysource_err_2
                      ELSE
                         NULL
                   END));
            END IF;

            v_err_string := vi_err_str;
         END LOOP;

	 --***---

  ora_function column value from code_table from above:
	
	 pkg_excep.fname (:v_errcode,:v_data_src,:v_err_str)


Example function code :

	 	FUNCTION fname (
		vi_err_code 	  IN VARCHAR2,
		vi_data_src   IN VARCHAR2,
		vi_err_str 	  IN VARCHAR2 DEFAULT NULL)
		RETURN VARCHAR2
	IS
		v_debt	NUMBER := NULL;
	BEGIN
		IF (pkg_excep.recname.column1 IS NULL
			 OR pkg_excep.recname.column2 IS NULL)
			OR (pkg_excep.recname.column1 <
					 pkg_excep.recname.column2)
		THEN
			RETURN fun1 (vi_err_str, vi_err_code);
		ELSE
			RETURN vi_err_str;
		END IF;
	END fname;

Previous Topic: Re: Quote practice
Next Topic: test - log errors in log table
Goto Forum:
  


Current Time: Fri Mar 29 10:18:08 CDT 2024