5 Replies Latest reply: Oct 30, 2013 7:18 AM by 19SeGro93 RSS

    Dynamic Collection column select

    19SeGro93

      Hi Gurus,

       

      I have a small problem with a collection.

      I have one collection with a type-position. The selected cell could contain 28,29,...,33.

      Afterwards I want to count the rows of the next collection, where (in this example) c033 is not null. The variable will be used as a string and not as a column.

      How can I use the variable as column in the 'where'-clause?

       

      here is my sql-statement:

       

      declare
      l_type_position varchar2(4);  --content will be: c033
      l_types number(3,0)

       

      begin

      --select the dynamic column for the next statement

        select 'c0'||(c009)   -- c009 = 33
        into l_type_position
        from apex_collections

        where  collection_name = 'UPLOAD_HEAD';

       

      select count(l_type_position)
      into l_types
      from apex_collections

      where  collection_name = 'SPREADSHEET_CONTENT'
      -- folowing row occurse the error

      and l_type_position is not null;   --  = and   c033 is not null;
       
      end;

       

      Thanks for help.

        • 1. Re: Dynamic Collection column select
          Marko Goricki

          Hi,

           

          you can use dynamic SQL or execute immediate statement.

           

          E.g.

           

          execute immediate 'select count(1) from apex_collections where collection_name = ''SPREADSHEET_CONTENT'' and :l_type_position is not null' into l_types using l_type_position;

           

          When using dynamic SQL watch for injections.

           

          Br,

          Marko Goricki

          APEX by G

          • 2. Re: Dynamic Collection column select
            19SeGro93

            Hi Marko,

             

            thanks for help, but the result is the same.

             

            In my case I have 120 rows and 10 rows have a value. The result of the immediate statement is 120, like my code.

             

            Can you describe the dynamic sql solution, please?

            • 3. Re: Dynamic Collection column select
              Marko Goricki

              It can't be the same. Maybe isn't null but blank.

               

              If you execute clean select into do you get 10?

               

              Br,

              Marko Goricki

              APEX by G

              • 4. Re: Dynamic Collection column select
                fac586

                This can be done using lexical replacement and dynamic SQL (as always, check for possible SQL injection):

                 

                declare
                
                  l_type_position varchar2(4);  --content will be: c033
                  l_types number(3,0);
                
                begin
                
                  -- select the dynamic column for the next statement
                  select 'c0'||(c009)   -- c009 = 33
                  into l_type_position
                  from apex_collections
                  where  collection_name = 'UPLOAD_HEAD';
                
                  -- check for SQL injection
                  l_type_position := dbms_assert.simple_sql_name(l_type_position);
                
                  -- count collection rows
                  execute immediate q'{
                      select count(}' || l_type_position || q'{)
                      from apex_collections
                      where  collection_name = 'SPREADSHEET_CONTENT'}'
                    into
                      l_types;
                   ...
                  end;
                

                 

                However, it's possible that this is an XY question. If more information on the nature of the requirement were provided it might be possible to identify a clearer, safer solution using static SQL.

                • 5. Re: Dynamic Collection column select
                  19SeGro93

                  That's it.

                   

                  Thanks to all