Solved

Oracle function: can the list of value in an IN be coded dynamically

Posted on 2013-01-10
22
481 Views
Last Modified: 2013-01-10
Hi EE,

i am an beginner with Oracle...

i have an IN clause...

sometimes, function will be working with one    value  IN  'M1' - for 1st year MED students
sometimes, function will be working with many values IN ('M2', 'M3', 'M4')  - for returning MED students

the student_classification field can have other values, so do not want to do NOT IN 'M1'

can i build the IN condition programatically like this, snippet is below:

the function compiles

BUT

i really want cnsQuote to be a value of  quote like this  '''

i am getting a compile error, so i changed it to '*' and it compiled

i appreciate any ideas.....  tx, sandra

p.s. it's probably amateur to have the SELECT appear twice, operating slightly differently
due to IF, i do not know how to build SELECT in a function programmatically

ideas are welcome...

also we have pl/developer and oracle developer and i do not know how to step thru code
so have to eyebal, insteadl... ideas are welcome...tx

============  snippet ==============
 ws_attrib varchar2(50);
 cnsQuote varchar2(1);
 
begin
  lngCount := 0;
  cnsQuote := '*';

'-- IN ('M2', 'M3', 'M4)

  If PARM_attrib = '*3' then
     ws_attrib := cnsQuote ||'M2' || cnsQuote || ',' ||
                        cnsQuote ||'M3' || cnsQuote || ',' ||
                        cnsQuote ||'M4' || cnsQuote;
  else
     ws_attrib := PARM_attrib   ;
  End if;


==== FULL FUNCTION BELOW ===

create or replace function DMC_COUNT_BY_ETHNIC_GENDER_YR
         (PARM_College      varchar2
         ,PARM_StudentPop   varchar2
         ,PARM_Gender       varchar2
         ,PARM_Ethnicity    varchar2
         ,PARM_Term         varchar2
         ,PARM_Attrib       varchar2
         ,PARM_YesNo        varchar2)


   
              return number is lngCount number;

 
 ws_attrib varchar2(50);
 cnsQuote varchar2(1);
 
begin
  lngCount := 0;
  cnsQuote := '*';


  If PARM_attrib = '*3' then
     ws_attrib := cnsQuote ||'M2' || cnsQuote || ',' ||
                  cnsQuote ||'M3' || cnsQuote || ',' ||
                  cnsQuote ||'M4' || cnsQuote;
  else
     ws_attrib := PARM_attrib   ;
  End if;
 
  If PARM_StudentPop = 'N' then
     select count(A.College)
            into lngCount
            from Academic_Study A
                   INNER JOIN PERSON P
                       ON A.person_uid = p.person_uid
         where A.College = PARM_College
                        and
               A.academic_period_admitted = PARM_Term
                        And
               P.gender = PARM_gender
                        And
               P.primary_ethnicity = PARM_ethnicity
                        And
               A.academic_period = PARM_Term
                        AND
               A.STUDENT_status in('AS', 'LA')
                        AND
               A.STUDENT_CLASSIFICATION in(ws_Attrib)
                        and
               DMC_IF_STUDENT_REG_COLLEG_TERM(A.PERSON_UID, A.ACADEMIC_PERIOD, A.COLLEGE) = PARM_YesNo;
  Else

           select count(A.College)
            into lngCount
            from Academic_Study A
                   INNER JOIN PERSON P
                       ON A.person_uid = p.person_uid
         where A.College = PARM_College
                        and
               A.academic_period_admitted <> PARM_Term
                        And
               P.gender = PARM_gender
                        And
               P.primary_ethnicity = PARM_ethnicity
                        And
               A.academic_period = PARM_Term
                        AND
               A.STUDENT_status in('AS', 'LA')
                        AND
               A.STUDENT_CLASSIFICATION in(PARM_Attrib)
                        and
               DMC_IF_STUDENT_REG_COLLEG_TERM(A.PERSON_UID, A.ACADEMIC_PERIOD, A.COLLEGE) = PARM_YesNo;
  End if;
     
  return lngCount;

end DMC_COUNT_BY_ETHNIC_GENDER_YR;
0
Comment
Question by:mytfein
  • 18
  • 4
22 Comments
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
ID: 38763565
look up the str2tbl function on EE.  


you can then treat your string of values as if they were a table
like this...


A.STUDENT_CLASSIFICATION in (select * from table(str2tbl(PARm_Attrib)))
0
 

Author Comment

by:mytfein
ID: 38763656
Hi sdstuber,

tx for writing...

is str2tbl a built in Oracle function...

bec i googled it on the web and found this:

http://www.java2s.com/Tutorial/Oracle/0540__Function-Procedure-Packages/ConvertCommaseparatedvaluestotablecollection.htm

some questions please:

1) am i supposed to run the create the logic to create a type called: myTable
2) what does "as table of Number" mean
3) so would pass into function a comma delimed string like this:  'M2, M3, M4'
      and function would create a table of rows:
          'M2'
         'M3'
         'M4'

     3B) is this table virtual... will it self-delete when query is done?

tx, s
               

SQL>
SQL>
SQL> create or replace type myTableType
  2  as table of number;
  3  /

Type created.

SQL>
SQL> create or replace
  2  function str2tbl( p_str in varchar2 ) return myTableType
  3  as
  4      l_str   long default p_str || ',';
  5      l_n        number;
  6      l_data    myTableType := myTabletype();
  7  begin
  8      loop
  9          l_n := instr( l_str, ',' );
 10          exit when (nvl(l_n,0) = 0);
 11          l_data.extend;
 12          l_data( l_data.count ) :=
 13              ltrim(rtrim(substr(l_str, 1, l_n - 1)));
 14          l_str := substr( l_str, l_n+1 );
 15      end loop;
 16      return l_data;
 17  end;
 18  /

Function created.
0
 

Author Comment

by:mytfein
ID: 38763710
Hi SDStuber,

also, what do these lines in the function mean?

as
  4      l_str   long default p_str || ',';
  5      l_n        number;
  6      l_data    myTableType := myTabletype();
  7  begin

===

a) i am familiar with return as number,  return as varchar2

what does the as followed by the fields mean?

b)  i think that l_data has a data type of myTableType
            why are we setting the type to myTabletype()

      l_data    myTableType := myTabletype();

c) if this is not an Oracle built in function
    so people name the type and function according to their company standards...

tx very much, s
0
 

Author Comment

by:mytfein
ID: 38763761
Hi SDStuber,

so i created under my own schema like this

a) create or replace type DMC_Type_ParseTable
  as table of number;

b) create or replace
    function DMC_str2tbl( p_str in varchar2 ) return DMC_Type_ParseTable
    as
        l_str   long default p_str || ',';
        l_n        number;
        l_data      DMC_Type_ParseTable := DMC_Type_ParseTable();

    begin
        loop
            l_n := instr( l_str, ',' );
           exit when (nvl(l_n,0) = 0);
           l_data.extend;
           l_data( l_data.count ) :=
               ltrim(rtrim(substr(l_str, 1, l_n - 1)));
           l_str := substr( l_str, l_n+1 );
       end loop;
       return l_data;
   end;
 


c) the went to pl/developer and typed:

   select * from table(DMC_str2tbl('m2, m3, m4'))

d) and got an error:
      numeric or value error at line 12

                             l_data( l_data.count ) :=

any ideas would be most appreciated, tx. s
0
 

Author Comment

by:mytfein
ID: 38763788
Hi SDStuber,

i copied this idea from the url above and still got the same error:

select * from TABLE ( cast ( DMC_str2tbl('m2, m3, m4' ) as DMC_Type_ParseTable ) )

tx, s
0
 

Author Comment

by:mytfein
ID: 38763821
Hi SDStuber,

google some more:

ok, so i found out that function is created by Tom Kyte

at this url, learned that i coded the parm to function incorrectly, have to give it
a string separator

http://www.orafaq.com/forum/t/129556/2/

select * from TABLE (cast (DMC_str2tbl('m2:m3:m4', ':' ) as DMC_Type_ParseTable ) )

yet i am getting another error:

wrong number of types or arguments

tx, s
0
 

Author Comment

by:mytfein
ID: 38763845
so looks like this function has a version with the string delimeter

so i copied from url their function like this and am getting and error:

invalid or missing option

tx, s

create or replace
  2  function DMC_str2tbl( p_string in varchar2 , p_delim in varchar2) return DMC_Type_ParseTable
  3  as
  4     l_string   long default p_string || p_delim;
  5     l_data    DMC_Type_ParseTable := DMC_Type_ParseTable();
  6     n     number;
  7  begin
  8    loop
  9       exit when l_string is null;
 10       n := instr( l_string, p_delim );
 11       l_data.extend;
 12       l_data(l_data.count) :=
 13       ltrim( rtrim( substr( l_string, 1, n-1 ) ) );
 14       l_string := substr( l_string, n+length (p_delim) );
 15    end loop;
 16    return l_data;
 17  end;
0
 

Author Comment

by:mytfein
ID: 38763862
so the url's type is varchar2, so change it to:

create or replace type DMC_Type_ParseTable as table
  of varchar2 (255);


still getting invalid option on function

tx, s
0
 

Author Comment

by:mytfein
ID: 38763874
fixed the error:

i had the numbers at the beg of the line.... removed them

and it compiled


create or replace
  2  function DMC_str2tbl( p_string in varchar2 , p_delim in varchar2) return DMC_Type_ParseTable
  3  as
  4         l_string       long default p_string || p_delim;
  5         l_data        DMC_Type_ParseTable := DMC_Type_ParseTable();
  6         n             number;
  7  begin
  8    loop
  9           exit when l_string is null;
 10           n := instr( l_string, p_delim );
 11           l_data.extend;
 12           l_data(l_data.count) :=
 13               ltrim( rtrim( substr( l_string, 1, n-1 ) ) );
 14           l_string := substr( l_string, n+length (p_delim) );
 15    end loop;
 16    return l_data;
 17  end;
0
 

Author Comment

by:mytfein
ID: 38763901
Hi SDStuber,

got function to work:  

pls see screen shot below...

however... weirdly it's not showing under list of function in pl/developer

replacement
0
 

Author Comment

by:mytfein
ID: 38763913
Hi SDStuber,

so i opened a new pl developer session, and see new function there

tx, s
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:mytfein
ID: 38763925
i tried you method w/o the cast and it works:

select * from TABLE (DMC_str2tbl('M2:M3:M4', ':' ) )
0
 

Author Comment

by:mytfein
ID: 38763963
s2so i am getting a bad bind variable when compiling my main function

pls see screen shot below:
0
 

Author Comment

by:mytfein
ID: 38763987
so i looked a tyour code and see that you do not have the :  infront of PARM_attrib
     when using the str2tbl func

A.STUDENT_CLASSIFICATION in (select * from table(str2tbl(PARm_Attrib)))

so i changed to:

A.STUDENT_CLASSIFICATION in (select * from TABLE (DMC_str2tbl(PARM_attrib, ':' ) ) )

and it compiled

even though i thought when using a parm in code it should be preceded with
a colon?

pls advise, tx,s
0
 

Author Comment

by:mytfein
ID: 38763997
sc3Progress !

this works now.... have to check COUNT results against the data now...

tx for your help.... s
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 38764007
sorry I just got back, I meant for you to search on experts-exchange, you wouldn't have needed to do any debugging, the code already worked, but since you got what you found working- no problem


I can't see the code in your screen capture to see what you're doing.

if your variable is inside pl/sql,  don't use ":" in front of it.

if you are not doing that, post the code as text - not a screen shot and I'll have a better idea
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 38764021
to help clarify - using a colon to note bind variables is for using sql within other systems like java, c#, pro*c, visual basic, etc.  Or from within dynamic sql.

the :variable_name syntax indicates to the oracle that the value is coming from the host, not something internal.

You don't need to do that in pl/sql because all variables are already bound.
0
 

Author Comment

by:mytfein
ID: 38764068
1)
create or replace type DMC_Type_ParseTable as table
  of varchar2 (255);

2)
create or replace function DMC_str2tbl( p_string in varchar2 , p_delim in varchar2) return DMC_Type_ParseTable
    as
       l_string   long default p_string || p_delim;
       l_data    DMC_Type_ParseTable := DMC_Type_ParseTable();
       n     number;
    begin
      loop
         exit when l_string is null;
        n := instr( l_string, p_delim );
        l_data.extend;
        l_data(l_data.count) :=
        ltrim( rtrim( substr( l_string, 1, n-1 ) ) );
        l_string := substr( l_string, n+length (p_delim) );
     end loop;
     return l_data;
   end;


3)
create or replace function DMC_COUNT_BY_ETHNIC_GENDER_YR
         (PARM_College      varchar2
         ,PARM_StudentPop   varchar2
         ,PARM_Gender       varchar2
         ,PARM_Ethnicity    varchar2
         ,PARM_Term         varchar2
         ,PARM_Attrib       varchar2
         ,PARM_YesNo        varchar2)


   
              return number is lngCount number;

 
begin
  lngCount := 0;

 
  If PARM_StudentPop = 'N' then
     select count(A.College)
            into lngCount
            from Academic_Study A
                   INNER JOIN PERSON P
                       ON A.person_uid = p.person_uid
         where A.College = PARM_College
                        and
               A.academic_period_admitted = PARM_Term
                        And
               P.gender = PARM_gender
                        And
               P.primary_ethnicity = PARM_ethnicity
                        And
               A.academic_period = PARM_Term
                        AND
               A.STUDENT_status in('AS', 'LA')
                        AND
               A.STUDENT_CLASSIFICATION in (select * from TABLE (DMC_str2tbl(PARM_attrib, ':' ) ) )
                        and
               DMC_IF_STUDENT_REG_COLLEG_TERM(A.PERSON_UID, A.ACADEMIC_PERIOD, A.COLLEGE) = PARM_YesNo;
  Else

           select count(A.College)
            into lngCount
            from Academic_Study A
                   INNER JOIN PERSON P
                       ON A.person_uid = p.person_uid
         where A.College = PARM_College
                        and
               A.academic_period_admitted <> PARM_Term
                        And
               P.gender = PARM_gender
                        And
               P.primary_ethnicity = PARM_ethnicity
                        And
               A.academic_period = PARM_Term
                        AND
               A.STUDENT_status in('AS', 'LA')
                        AND
               A.STUDENT_CLASSIFICATION in (select * from TABLE (DMC_str2tbl(PARM_attrib, ':' ) ) )
                        and
               DMC_IF_STUDENT_REG_COLLEG_TERM(A.PERSON_UID, A.ACADEMIC_PERIOD, A.COLLEGE) = PARM_YesNo;
  End if;
     
  return lngCount;

end DMC_COUNT_BY_ETHNIC_GENDER_YR;


4)

yours:

 A.STUDENT_CLASSIFICATION in (select * from table(str2tbl(PARm_Attrib)))

so i changed to:

mine with error:

A.STUDENT_CLASSIFICATION in (select * from TABLE (DMC_str2tbl(:PARM_attrib, ':' ) ) )

changed to:

A.STUDENT_CLASSIFICATION in (select * from TABLE (DMC_str2tbl(  PARM_attrib, ':' ) ) )

5)
(am vague on your explanation above...
    i put a : in front, bec. in Oracle Discoverer we identifiy parms by putting a : infront...
   also we are getting a BI tool called ARGOS and parms are identified with a : in front)


6) would you be willing in a new post, to assist with making this dynamic sql so that
     i have just one SELECT statement, and then maybe your comment about parm
     and dynamic sql would apply?

tx for your help, s
0
 

Author Comment

by:mytfein
ID: 38764077
also, would you be willing to answer in another post
   how to step thru a function using

 oracle developer
or

pl/developer

i do not see on tool bars of these tools the ability to step thru the code

maybe they were not installed properly?

tx, s
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 38764090
you use : in Discoverer and ARGOS because they are external.  The colon tells the sql statement that the data is coming from someplace else that is doesn't control.  In your case  Discoverer or ARGOS.


When you do

declare
     v_my_variable number;
begin
    select count(*) into v_my_variable from user_tables;
end;


you do NOT put a : in front of v_my_variable because it's not external,  it's internal to the oracle session.  It's part of pl/sql.


or - even simpler, you can just think of pl/sql as being special and doesn't need the colon like everyone else   :)


If you open a new question, I'll see it.  I or someone else will try to help.
0
 

Author Comment

by:mytfein
ID: 38764111
tx sdstuber for the explanation....
0
 

Author Comment

by:mytfein
ID: 38764260
Hi SDStuber,

in case you have time,
    this is a new post
           for help to make the above function use dynamic SQL
                   instead of having two similar SELECTS

tx, sandra
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now