6 Replies Latest reply: Dec 28, 2012 7:02 AM by APC RSS

    Identify columns after fetching cursor

    611408
      Hi,
      I have a scenario in which iam reading the columns into a cursor and during fetch I want to apply some condition against each column in the cursor.

      Eg:
      curcor c1 is Select col1,col2,col3 from test;
      rec1 c1%rowtype;

      begin

      open c1;

      loop
      fetch c1 into rec1;
      exit when c1%NOTFOUND;

      if rec1.col1=1234

      Here instead of using column name can I know that this is the first column in the cursor and apply multiple validations and similarly to next column and so on.


      Regards
      Satya
        • 1. Re: Identify columns after fetching cursor
          ranit B
          Eg:
          curcor c1 is Select col1,col2,col3 from test;
          rec1 c1%rowtype;

          begin

          open c1;

          loop
          fetch c1 into rec1;
          exit when c1%NOTFOUND;

          if rec1.col1=1234

          Here instead of using column name can I know that this is the first column in the cursor and apply multiple validations and similarly to next column and so on.
          I'm not sure_ on this... but this is what i understood -
          Try the FOR LOOP...
          DECLARE
          cursor c1
          is 
          select col1, col2, col3 from test;
          
          BEGIN
          FOR i in c1
          LOOP
            if(i.col1 >0) then /* do any validation */
              ...
            end if;
          
            if(i.col2 <> 0) then  /* do any validation */
              ...
            end if;
          
            if(i.col3 IS NULL) then  /* do any validation */
              ...
            end if;
          
          END LOOP
          
          END;
          • 2. Re: Identify columns after fetching cursor
            611408
            For each row if any of the column doesnt satisfy the validation that complete row has to be captured with the column which is mis-matching
            • 3. Re: Identify columns after fetching cursor
              ranit B
              user608405 wrote:
              For each row if any of the column doesnt satisfy the validation that complete row has to be captured with the column which is mis-matching
              What do you mean by 'captured' ??
              There might have multiple columns which doesn't satisfy the condition. In that case, what to do?

              Can you please give some inputs and some example like - what kind of validations are you trying to perform?
              • 4. Re: Identify columns after fetching cursor
                vijayrsehgal-Oracle
                your condition does not depend on the column position and will depend on the column itself, so why bother on which position the column is returned. While comparing the column to the condition you know which part of the comparison is failing, so you can take necessary action.
                • 5. Re: Identify columns after fetching cursor
                  BluShadow
                  If you want to reference columns by position then you either need to be using a ref cursor via a 3rd party developement language like .NET etc. or you need to use the DBMS_SQL package within PL/SQL.

                  Example of using DBMS_SQL package (from my standard library of examples - in this case something that takes a query and outputs the data as CSV)...

                  As sys user:
                  CREATE OR REPLACE DIRECTORY TEST_DIR AS '\tmp\myfiles'
                  /
                  GRANT READ, WRITE ON DIRECTORY TEST_DIR TO myuser
                  /
                  As myuser:
                  CREATE OR REPLACE PROCEDURE run_query(p_sql IN VARCHAR2
                                                       ,p_dir IN VARCHAR2
                                                       ,p_header_file IN VARCHAR2
                                                       ,p_data_file IN VARCHAR2 := NULL) IS
                    v_finaltxt  VARCHAR2(4000);
                    v_v_val     VARCHAR2(4000);
                    v_n_val     NUMBER;
                    v_d_val     DATE;
                    v_ret       NUMBER;
                    c           NUMBER;
                    d           NUMBER;
                    col_cnt     INTEGER;
                    f           BOOLEAN;
                    rec_tab     DBMS_SQL.DESC_TAB;
                    col_num     NUMBER;
                    v_fh        UTL_FILE.FILE_TYPE;
                    v_samefile  BOOLEAN := (NVL(p_data_file,p_header_file) = p_header_file);
                  BEGIN
                    c := DBMS_SQL.OPEN_CURSOR;
                    DBMS_SQL.PARSE(c, p_sql, DBMS_SQL.NATIVE);
                    d := DBMS_SQL.EXECUTE(c);
                    DBMS_SQL.DESCRIBE_COLUMNS(c, col_cnt, rec_tab);
                    FOR j in 1..col_cnt
                    LOOP
                      CASE rec_tab(j).col_type
                        WHEN 1 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_v_val,2000);
                        WHEN 2 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_n_val);
                        WHEN 12 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_d_val);
                      ELSE
                        DBMS_SQL.DEFINE_COLUMN(c,j,v_v_val,2000);
                      END CASE;
                    END LOOP;
                    -- This part outputs the HEADER
                    v_fh := UTL_FILE.FOPEN(upper(p_dir),p_header_file,'w',32767);
                    FOR j in 1..col_cnt
                    LOOP
                      v_finaltxt := ltrim(v_finaltxt||','||lower(rec_tab(j).col_name),',');
                    END LOOP;
                    --  DBMS_OUTPUT.PUT_LINE(v_finaltxt);
                    UTL_FILE.PUT_LINE(v_fh, v_finaltxt);
                    IF NOT v_samefile THEN
                      UTL_FILE.FCLOSE(v_fh);
                    END IF;
                    --
                    -- This part outputs the DATA
                    IF NOT v_samefile THEN
                      v_fh := UTL_FILE.FOPEN(upper(p_dir),p_data_file,'w',32767);
                    END IF;
                    LOOP
                      v_ret := DBMS_SQL.FETCH_ROWS(c);
                      EXIT WHEN v_ret = 0;
                      v_finaltxt := NULL;
                      FOR j in 1..col_cnt
                      LOOP
                        CASE rec_tab(j).col_type
                          WHEN 1 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_v_val);
                                      v_finaltxt := ltrim(v_finaltxt||',"'||v_v_val||'"',',');
                          WHEN 2 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_n_val);
                                      v_finaltxt := ltrim(v_finaltxt||','||v_n_val,',');
                          WHEN 12 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_d_val);
                                      v_finaltxt := ltrim(v_finaltxt||','||to_char(v_d_val,'DD/MM/YYYY HH24:MI:SS'),',');
                        ELSE
                          DBMS_SQL.COLUMN_VALUE(c,j,v_v_val);
                          v_finaltxt := ltrim(v_finaltxt||',"'||v_v_val||'"',',');
                        END CASE;
                      END LOOP;
                    --  DBMS_OUTPUT.PUT_LINE(v_finaltxt);
                      UTL_FILE.PUT_LINE(v_fh, v_finaltxt);
                    END LOOP;
                    UTL_FILE.FCLOSE(v_fh);
                    DBMS_SQL.CLOSE_CURSOR(c);
                  END;
                  This allows for the header row and the data to be written to seperate files if required.

                  e.g.
                  SQL> exec run_query('select * from emp','TEST_DIR','output.txt');
                   
                  PL/SQL procedure successfully completed.
                  Output.txt file contains:
                  empno,ename,job,mgr,hiredate,sal,comm,deptno
                  7369,"SMITH","CLERK",7902,17/12/1980 00:00:00,800,,20
                  7499,"ALLEN","SALESMAN",7698,20/02/1981 00:00:00,1600,300,30
                  7521,"WARD","SALESMAN",7698,22/02/1981 00:00:00,1250,500,30
                  7566,"JONES","MANAGER",7839,02/04/1981 00:00:00,2975,,20
                  7654,"MARTIN","SALESMAN",7698,28/09/1981 00:00:00,1250,1400,30
                  7698,"BLAKE","MANAGER",7839,01/05/1981 00:00:00,2850,,30
                  7782,"CLARK","MANAGER",7839,09/06/1981 00:00:00,2450,,10
                  7788,"SCOTT","ANALYST",7566,19/04/1987 00:00:00,3000,,20
                  7839,"KING","PRESIDENT",,17/11/1981 00:00:00,5000,,10
                  7844,"TURNER","SALESMAN",7698,08/09/1981 00:00:00,1500,0,30
                  7876,"ADAMS","CLERK",7788,23/05/1987 00:00:00,1100,,20
                  7900,"JAMES","CLERK",7698,03/12/1981 00:00:00,950,,30
                  7902,"FORD","ANALYST",7566,03/12/1981 00:00:00,3000,,20
                  7934,"MILLER","CLERK",7782,23/01/1982 00:00:00,1300,,10
                  The procedure allows for the header and data to go to seperate files if required. Just specifying the "header" filename will put the header and data in the one file.

                  Adapt to output different datatypes and styles are required.
                  • 6. Re: Identify columns after fetching cursor
                    APC
                    user608405 wrote:
                    Here instead of using column name can I know that this is the first column in the cursor and apply multiple validations and similarly to next column and so on.
                    No. PL/SQL is not Java and does not support Reflection or a similar mechanism for interrogating itself.

                    The DBMS_SQL package does have the ability to access metadata about cursors. So perhaps you could use dynamic SQL; although this is an extreme route.

                    If you're just worried about the duplicated typing, put all the validations into a private procedure and call it for each column in the projection:
                    rec1 c1%rowtype;
                    
                      procedure generic_validation (p_col in varchar2(128)
                      is 
                      begin
                         if p_col is null
                         or p_col = '1234'
                         then
                            -- do whatever e.g. 
                            raise_application_error(-20000, 'generic_validation failed!');
                         end if;
                      end generic_validation;
                    
                    begin
                    
                      open c1;
                    
                      loop
                        fetch c1 into rec1;
                        exit when c1%NOTFOUND; 
                    
                           generic_validation (rec1.col1);
                           generic_validation (rec1.col2);
                           generic_validation (rec1.col3);
                    
                       ...
                    Why does PL/SQL make this sort of thing so difficult? because Pl/SQL is a language for proceduralizing database operations. Needing to run the same set of operations against a number of different columns is usually the sign of a poor (i.e. de-nomalised) data model. Which isn't to say that maybe PL/SQL should be extended to handle these areas but it's well down the prioritised To Do list.


                    Cheers, APC