Forum Stats

  • 3,876,115 Users
  • 2,267,061 Discussions
  • 7,912,434 Comments

Discussions

Including variable in query using owa_util.listprint function

NewApexCoder
NewApexCoder Member Posts: 256
edited Oct 24, 2017 4:50PM in SQL & PL/SQL

I am working in an ORACLE package. I have a procedure that displays options for a user to select. The option is a simple drop down (select) list. I am using the OWA_UTIL.LISTPRINT function. I am trying to include a variable in the "WHERE" clause of the query, but I keep getting the "variable not recognized" error or the "Encountered the symbol '$%^' " symbol. I know its soemthing simple. Just a matter of syntax but I haven't figured it out. Code is below:

<span class="pln">htp</span><span class="pun">.</span><span class="pln">bodyOpen </span><span class="pun">;</span><span class="pln"><br/>  htp</span><span class="pun">.</span><span class="pln">centeropen</span><span class="pun">;</span><span class="pln"><br/>  htp</span><span class="pun">.</span><span class="pln">formopen </span><span class="pun">(</span><span class="pln">curl </span><span class="pun">=></span><span class="pln"> twbkwbis</span><span class="pun">.</span><span class="pln">f_cgibin </span><span class="pun">||</span><span class="pln"> </span><span class="str">'sykcrrp.p_display_report'</span><span class="pun">,</span><span class="pln"><br/>  cmethod </span><span class="pun">=></span><span class="pln"> </span><span class="str">'POST'</span><span class="pun">);</span><span class="pln"><br/>  htp</span><span class="pun">.</span><span class="pln">tableopen</span><span class="pun">;</span><span class="pln"><br/>  htp</span><span class="pun">.</span><span class="pln">br</span><span class="pun">;</span><span class="pln"><br/>  htp</span><span class="pun">.</span><span class="pln">tableRowOpen</span><span class="pun">;</span><span class="pln"><br/>  htp</span><span class="pun">.</span><span class="pln">tableData </span><span class="pun">(</span><span class="pln"> </span><span class="str">'Select Class Year: '</span><span class="pun">,</span><span class="pln"><br/>  cattributes </span><span class="pun">=></span><span class="pln"> </span><span class="str">'style="text-align:right;"'</span><span class="pln"> </span><span class="pun">);</span><span class="pln"><br/>  twbkfrmt</span><span class="pun">.</span><span class="pln">P_TableDataOpen </span><span class="pun">(</span><span class="pln"> cattributes </span><span class="pun">=></span><span class="pln"> </span><span class="str">'style="text-align:left;"'</span><span class="pln"> </span><span class="pun">);</span><span class="pln">   <br/>  OWA_UTIL</span><span class="pun">.</span><span class="pln">LISTPRINT </span><span class="pun">(</span><span class="pln"> p_theQuery </span><span class="pun">=></span><span class="pln"> </span><span class="str">'SELECT distinct class_description, class_code||'' - ''||class_description, null'</span><span class="pln"><br/>   </span><span class="pun">||</span><span class="str">' FROM ROSTER_TABLE'</span><span class="pln"><br/>   </span><span class="pun">||</span><span class="str">' WHERE EXISTS (SELECT ''x'''</span><span class="pln"><br/>   </span><span class="pun">||</span><span class="str">' FROM ROLE_TABLE'</span><span class="pln"><br/>   </span><span class="pun">||</span><span class="str">' WHERE Role_pidm = ||'</span><span class="pln">v_id</span><span class="str">'||'''</span><span class="pln"><br/><br/>   </span><span class="pun">||</span><span class="str">' AND Role_role = ''ROSTER_''||program)'</span><span class="pln"><br/>   </span><span class="pun">||</span><span class="str">' order by 1'</span><span class="pun">,</span><span class="pln"> <br/>  p_cname </span><span class="pun">=></span><span class="pln"> </span><span class="str">'p_class_year'</span><span class="pun">,</span><span class="pln"><br/>  p_nsize </span><span class="pun">=></span><span class="pln"> </span><span class="lit">7</span><span class="pun">,</span><span class="pln"><br/>  p_multiple </span><span class="pun">=></span><span class="pln"> TRUE </span><span class="pun">);</span><span class="pln">   <br/>  twbkfrmt</span><span class="pun">.</span><span class="pln">P_TableDataClose</span><span class="pun">;</span><span class="pln"><br/>  htp</span><span class="pun">.</span><span class="pln">tableRowClose</span><span class="pun">;</span><span class="pln">   <br/>  htp</span><span class="pun">.</span><span class="pln">tableClose</span><span class="pun">;</span><span class="pln"><br/><br/>  htp</span><span class="pun">.</span><span class="pln">bodyClose</span><span class="pun">;</span><span class="pln"><br/>  htp</span><span class="pun">.</span><span class="pln">br</span><span class="pun">;</span><span class="pln"><br/> htp</span><span class="pun">.</span><span class="pln">htmlclose</span><span class="pun">;</span><span class="pln"> <br/><br/>EXCEPTION<br/></span><span class="kwd">WHEN</span><span class="pln"> OTHERS </span><span class="kwd">THEN</span><span class="pln"> <br/>htp</span><span class="pun">.</span><span class="kwd">print</span><span class="pun">(</span><span class="pln">SQLERRM</span><span class="pun">);</span><span class="pln"> <br/></span><span class="kwd">END</span><span class="pun">;</span>

In my code, my variable is v_id. I am passing this in. I know there is a value in there as I am able to output it to the screen as a number. Again, it looks like a syntax issue that I just haven't figured out yet. Having trouble concatenating it or using the apostrophes, or idk.

Any help on this would be great. Thanks in advance.

<span class="pln">  </span>

Best Answer

  • padders
    padders Member Posts: 1,081 Silver Trophy
    edited Oct 24, 2017 6:51AM Answer ✓

    You can use owa_util.bind_variables to open a cursor for a query string with multiple binds and pass the resulting cursor to owa_util.listprint, but beware of implicit conversions, e.g.

    DECLARE  PROCEDURE init_owa  IS      l_names owa.vc_arr;      l_values owa.vc_arr;  BEGIN      l_names (1) := 'DUMMY_NAME';      l_values (1) := 'DUMMY_VALUE';      owa.init_cgi_env (        num_params => l_names.COUNT,        param_name => l_names,        param_val => l_values);  END init_owa;  PROCEDURE dump_owa  IS      l_page htp.htbuf_arr;      l_rows INTEGER := 99999999;  BEGIN      owa.get_page (        thepage => l_page,        irows => l_rows);      FOR i IN 1 .. l_rows LOOP        dbms_output.put_line (l_page (i));      END LOOP;  END dump_owa;BEGIN  init_owa;  DECLARE      p_deptno dept.deptno%TYPE := 10;  BEGIN      owa_util.listprint (        p_thecursor =>            owa_util.bind_variables (              thequery =>                  q'{SELECT DISTINCT job, job || ' - ' || INITCAP (job), NULL }' ||                  q'{FROM emp WHERE deptno = TO_NUMBER (:b_deptno)}',              bv1name => 'b_deptno',              bv1value => TO_CHAR (p_deptno)),        p_cname => 'p_cname',        p_nsize => 99,        p_multiple => TRUE);  END;  dump_owa;END;/

Answers

  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,951 Red Diamond
    edited Oct 24, 2017 2:00AM

    Horrible. All of it.

    Using an outdated Web Toolkit API.

    Not using bind variables, leaving open a giant security hole.

    And using the same magnificent reasoning that made your code so horrible, and applied it to posting a PL/SQL question in a forum that EXPLICITLY states No Product Questions.

  • BluShadow
    BluShadow Member, Moderator Posts: 42,591 Red Diamond
    edited Oct 24, 2017 3:22AM

    *** Moderator Note: Question now moved to the SQL and PL/SQL space.  As Billy indicates, the "Community Feedback (No Product Questions)" space clearly states that you should not post product questions in there, and it is for feedback relating to the Developer Community.  Please ensure you choose the correct product related space for your questions in future.

  • padders
    padders Member Posts: 1,081 Silver Trophy
    edited Oct 24, 2017 6:51AM Answer ✓

    You can use owa_util.bind_variables to open a cursor for a query string with multiple binds and pass the resulting cursor to owa_util.listprint, but beware of implicit conversions, e.g.

    DECLARE  PROCEDURE init_owa  IS      l_names owa.vc_arr;      l_values owa.vc_arr;  BEGIN      l_names (1) := 'DUMMY_NAME';      l_values (1) := 'DUMMY_VALUE';      owa.init_cgi_env (        num_params => l_names.COUNT,        param_name => l_names,        param_val => l_values);  END init_owa;  PROCEDURE dump_owa  IS      l_page htp.htbuf_arr;      l_rows INTEGER := 99999999;  BEGIN      owa.get_page (        thepage => l_page,        irows => l_rows);      FOR i IN 1 .. l_rows LOOP        dbms_output.put_line (l_page (i));      END LOOP;  END dump_owa;BEGIN  init_owa;  DECLARE      p_deptno dept.deptno%TYPE := 10;  BEGIN      owa_util.listprint (        p_thecursor =>            owa_util.bind_variables (              thequery =>                  q'{SELECT DISTINCT job, job || ' - ' || INITCAP (job), NULL }' ||                  q'{FROM emp WHERE deptno = TO_NUMBER (:b_deptno)}',              bv1name => 'b_deptno',              bv1value => TO_CHAR (p_deptno)),        p_cname => 'p_cname',        p_nsize => 99,        p_multiple => TRUE);  END;  dump_owa;END;/
  • NewApexCoder
    NewApexCoder Member Posts: 256
    edited Oct 24, 2017 4:34PM

    LOL well thanks for that. Actual feedback on the issue would have been nice..But I guess thats constructive criticism....or just being a d!ck...idk...especially that early in the morning too...amazing.

    -- Doing the best with what I've got

    -- You really could have just suggested  using owa_util.bind_variables as @padders suggested.

    -- Yea I figured it was the wrong area to post in. I did search around for the correct spot but the site is a little tough to navigate ( I don't spend my entire day on here )

    But thanks for that..It did make me laugh

  • NewApexCoder
    NewApexCoder Member Posts: 256
    edited Oct 24, 2017 4:48PM

    Sorry about that

  • NewApexCoder
    NewApexCoder Member Posts: 256
    edited Oct 24, 2017 4:50PM

    Thanks for the help. I'll take a look and dissect. This should work, especially utilizing ow_util.bind_variables function.

    Thanks a lot

This discussion has been closed.