7 Replies Latest reply: Jul 22, 2012 3:59 PM by 896971 RSS

    Cursor Question (no data displaying)

    896971
      Good day. I am puzzled as to why this cursor is not working as expected. Any assistance would be appreciated. Version is 11g R2.
      --Test DDL
      
      create table country
      (
      country_id number not null,
      country_name varchar2(250),
      country_iso_3alpha varchar(20),
      deleteflag varchar2(2),
      char_map varchar(2),
      constraint country_pk primary key (country_id) 
      );
      
      SET DEFINE OFF;
      Insert into COUNTRY
      (COUNTRY_ID, COUNTRY_NAME, DELETEFLAG, COUNTRY_ISO_3ALPHA, CHAR_MAP)
      Values (1, 'Awesomeville', 'N', 'AWE', 'A1');
      Insert into COUNTRY
      (COUNTRY_ID, COUNTRY_NAME, DELETEFLAG, COUNTRY_ISO_3ALPHA, CHAR_MAP)
      Values (2, 'Happy Land', 'N', 'HPY', 'B1'); 
      Insert into COUNTRY
      (COUNTRY_ID, COUNTRY_NAME, DELETEFLAG, COUNTRY_ISO_3ALPHA, CHAR_MAP)
      Values (3, 'Outer Space', 'N', 'OUT', 'C1');
      Insert into COUNTRY
      (COUNTRY_ID, COUNTRY_NAME, DELETEFLAG, COUNTRY_ISO_3ALPHA, CHAR_MAP)
      Values (4, 'Taiwan', 'N', 'TWN', 'D1');
      Insert into COUNTRY
      (COUNTRY_ID, COUNTRY_NAME, DELETEFLAG, COUNTRY_ISO_3ALPHA, CHAR_MAP)
      Values (5, 'Uganda', 'N', 'UGA', 'E1');
      commit;
      --Function/Cursor
      
      create or replace function parseCountries (seqcode in varchar2)
      return varchar2 is
      var_iso_3alpha varchar2(500);
      var_counter number;
      var_relto varchar2(500);
      var_tmpVar varchar2(500);
      var_output varchar2(500);
      
      cursor cur_country is
          select country_iso_3alpha from country
          where char_map in (var_tmpVar) and deleteflag = 'N';
          
      begin
      
      var_relto := seqcode;
      
      --check to make sure it's not an empty string
      if length(var_relto)>0 then
      
      --divide by 2 because the country.char_map field is always 2 characters
          var_counter := length(var_relto)/2;
          
      --take the clumped together string of char_map and break into apostrophe and comma-delimited string
      --so it will work in the cursor's "in" section
          for i in 1 .. var_counter loop
              var_tmpVar := var_tmpVar || CHR(39) || substr(var_relto,1,2) || CHR(39);
              var_relto := substr(var_relto,3);
              if i<var_counter then
                  var_tmpVar := var_tmpVar || ',';
              end if;
          end loop;
          
          open cur_country;
          loop
          
          fetch cur_country into var_iso_3alpha;
          exit when cur_country%NOTFOUND;
          
          dbms_output.put_line('-->'||var_iso_3alpha||'<--');
          
      --produce string that should resemble this: USA/GBR/TAI/CHI/etc...
          var_output := var_output || '/' || var_iso_3alpha;
             
          end loop;
          close cur_country;
          
      else
          var_output := 'N/A';
      end if;
      
      return var_output;
      
      end parseCountries;
      /
      --Sample Query
      
      select
      'A1B1D1', parseCountries('A1B1D1'),
      '', parseCountries('')
      from dual;
      The sample query above demonstrates the problem. The expected output for the first part of the query should be 'AWE/HPY/TWN' but it's blank. It's as if the lines of code after the "exit when cur_country%NOTFOUND;" aren't being processed. That dbms_output doesn't even show up at all. It should be displaying at least "--><--" if the variables were null, but it's not even showing that.

      What am I doing wrong??

      Thank you.
        • 1. Re: Cursor Question (no data displaying)
          SomeoneElse
          SQL> set serveroutput on
          • 2. Re: Cursor Question (no data displaying)
            rp0428
            >
            What am I doing wrong??
            >
            Run this simple code in sql*plus and see if it answers your question
            SQL> begin
              2  dbms_output.put_line('test');
              3  end;
              4  /
            
            PL/SQL procedure successfully completed.
            
            SQL> set serveroutput on
            SQL> /
            test
            
            PL/SQL procedure successfully completed.
            
            SQL>
            The first execution did not display the word 'test'. Why not?

            The second execution displayed properly. Why?
            • 3. Re: Cursor Question (no data displaying)
              896971
              I appreciate your responses, but I have serveroutput on already. Please execute my SQL and see for yourself.
              • 4. Re: Cursor Question (no data displaying)
                rp0428
                >
                I appreciate your responses, but I have serveroutput on already. Please execute my SQL and see for yourself.
                >
                Please explain how we could possibly tell if you have serveroutput on by executing anything on OUR computers. That doesn't even make sense.

                You said you weren't getting any output so we assumed you were talking about the DBMS_OUTPUT calls.

                If you mean why this cursor
                cursor cur_country is
                    select country_iso_3alpha from country
                    where char_map in (var_tmpVar) and deleteflag = 'N';
                isn't finding any data it's because you have constructed a value for var_tmpVar of 'A1','B1','D1' and are trying to use that ONE value as an IN string thinking it will get interpreted as three values but it won't.

                So your cursor is looking for a record where the column CHAR_MAP has a value of "'A1','B1','D1'" and you likely don't have any records with that value. You are probably expecting the cursor to return records where CHAR_MAP has a value of 'A1' or 'A2' or 'A3'.

                You have to use dynamic sql if you want to dynamically construct an IN list like this.

                See Example 7-4 in chap 7 PL/SQL Dynamic SQL of the PL/SQL Language Reference.
                Example 7-4 Native Dynamic SQL with OPEN FOR, FETCH, and CLOSE Statements
                DECLARE
                  TYPE EmpCurTyp  IS REF CURSOR;
                  v_emp_cursor    EmpCurTyp;
                  emp_record      employees%ROWTYPE;
                  v_stmt_str      VARCHAR2(200);
                  v_e_job         employees.job%TYPE;
                BEGIN
                  -- Dynamic SQL statement with placeholder:
                  v_stmt_str := 'SELECT * FROM employees WHERE job_id = :j';
                
                  -- Open cursor & specify bind variable in USING clause:
                  OPEN v_emp_cursor FOR v_stmt_str USING 'MANAGER';
                
                  -- Fetch rows from result set one at a time:
                  LOOP
                    FETCH v_emp_cursor INTO emp_record;
                    EXIT WHEN v_emp_cursor%NOTFOUND;
                  END LOOP;
                
                  -- Close cursor:
                  CLOSE v_emp_cursor;
                END;
                /
                For your use case you would replace
                  -- Dynamic SQL statement with placeholder:
                  v_stmt_str := 'SELECT * FROM employees WHERE job_id = :j';
                
                  -- Open cursor & specify bind variable in USING clause:
                  OPEN v_emp_cursor FOR v_stmt_str USING 'MANAGER';
                With
                v_stmt_str := q'[select country_iso_3alpha from country where char_map in ('A1','B1','D1') and deleteflag = 'N']';
                OPEN v_emp_cursor FOR v_stmt_str;
                • 5. Re: Cursor Question (no data displaying)
                  Paulie
                  >


                  Hi,
                  --Sample Query
                  
                  select
                  'A1B1D1', parseCountries('A1B1D1'),
                  '', parseCountries('')
                  from dual;
                  The sample query above demonstrates the problem. The expected output for the first part of the query should be 'AWE/HPY/TWN' but it's blank. It's as if the lines of code after the "exit when cur_country%NOTFOUND;" aren't being processed. That dbms_output doesn't even show up at all. It should be displaying at least "--><--" if the variables were null, but it's not even showing that.
                  I think that you have a logic error in your code. I ran your stuff in SQL Developer - it compiles
                  fine - and runs - the sample query's second field is blank - which suggests that everything is
                  working, but that the logic just isn't right. Don't have time to debug just at the minute, but
                  everything else seems OK.

                  HTH,

                  Paul...
                  • 6. Re: Cursor Question (no data displaying)
                    Etbin
                    var_relto := seqcode;
                     
                    --check to make sure it's not an empty string
                    if length(var_relto)>0 then
                    ...
                    when you input an empty string '' for <tt><b>seqcode</b></tt>, I think <tt><b>length(var_relto)</b></tt> returns null not *0* (a well known inconsistence)

                    Regards

                    Etbin

                    Edited by: Etbin on 22.7.2012 10:17

                    http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:5984520277372
                    • 7. Re: Cursor Question (no data displaying)
                      896971
                      rp0428 wrote:
                      Please explain how we could possibly tell if you have serveroutput on by executing anything on OUR computers. That doesn't even make sense.
                      Aaaah, the irritable-internet-response, that's what was missing from this discussion. Now this feels more like an Oracle forum!

                      You have to use dynamic sql if you want to dynamically construct an IN list like this.
                      You are correct sir, and the info you provided allowed me to successfully put this function to bed. Thanks much!

                      For future googlers, I did have to go the extra mile and create a new collection of TYPE TABLE to accomplish this, as you cannot use a single variable to hold all the values of an "IN" clause. See [http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:227413938857] for more details (search for "Using IN for Prepared Statement." on that page).