Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Including variable in query using owa_util.listprint function

NewApexCoderOct 23 2017 — edited Oct 24 2017

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:

htp.bodyOpen ;
  htp.centeropen;
  htp.formopen (curl => twbkwbis.f_cgibin || 'sykcrrp.p_display_report',
  cmethod => 'POST');
  htp.tableopen;
  htp.br;
  htp.tableRowOpen;
  htp.tableData ( 'Select Class Year: ',
  cattributes => 'style="text-align:right;"' );
  twbkfrmt.P_TableDataOpen ( cattributes => 'style="text-align:left;"' );   
  OWA_UTIL.LISTPRINT ( p_theQuery => 'SELECT distinct class_description, class_code||'' - ''||class_description, null'
   ||' FROM ROSTER_TABLE'
   ||' WHERE EXISTS (SELECT ''x'''
   ||' FROM ROLE_TABLE'
   ||' WHERE Role_pidm = ||'v_id'||'''

   ||' AND Role_role = ''ROSTER_''||program)'
   ||' order by 1', 
  p_cname => 'p_class_year',
  p_nsize => 7,
  p_multiple => TRUE );   
  twbkfrmt.P_TableDataClose;
  htp.tableRowClose;   
  htp.tableClose;

  htp.bodyClose;
  htp.br;
 htp.htmlclose; 

EXCEPTION
WHEN OTHERS THEN 
htp.print(SQLERRM); 
END;

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.

 

This post has been answered by padders on Oct 24 2017
Jump to Answer

Comments

Billy Verreynne

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

*** 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
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;
/
Marked as Answer by NewApexCoder · Sep 27 2020
NewApexCoder

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

Sorry about that

NewApexCoder

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

Thanks a lot

1 - 6
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Nov 21 2017
Added on Oct 23 2017
#sql-pl-sql, #variable
6 comments
575 views