9 Replies Latest reply on Mar 14, 2011 7:47 AM by Billy~Verreynne

    HTML not working in PL/SQL block..Help me ASAP

    808691
      declare
      l_col VARCHAR2(30) :=to_number(to_char(to_date('01-feb-2011','dd-mon-yyyy'),'dd'));
      CURSOR name_cur IS
        select name
            from   od_shift_schedule
            where   year=2011
            and    (month)=('Feb')
             and    decode(l_col,1,"01",2,"02",3,"03",4,"04",5,"05",6,"06",7,
                           "07",8,"08",9,"09",10,"10",11,"11",12,"12",13,"13",14,"14",15,"15",16,"16",17,"17",18,"18",19,"19",20,"20",
                           21,"21",22,"22",23,"23",24,"24",25,"25",26,"26",
                         27,"27",28,"28",29,"29",30,"30",31,"31")='W';
       BEGIN
        DELETE FROM nam;
        commit;
        FOR i IN name_cur
        LOOP
        dbms_output.put_line(i.name);
        htp.p('<b>Employee '||i.name||'  has been ticked.
         </b><br/>');
        EXECUTE IMMEDIATE 'insert into nam(name) values('''||i.name||''')';
        commit;
       END LOOP;
      end;
      Kindly help me with this

      If i'm wrong here kindly help me that how can i place a display message
        • 1. Re: HTML not working in PL/SQL block..Help me ASAP
          6363
          Re: HTML not working in PL/SQL block..Help me ASAP
          Using the community discussion forums for urgent issues is rude and a violation of the terms and conditions.

          http://www.oracle.com/html/terms.html

          >
          4. Use of Community Services
          Community Services are provided as a convenience to users and Oracle is not obligated to provide any technical support for, or participate in, Community Services. While Community Services may include information regarding Oracle products and services, including information from Oracle employees, they are not an official customer support channel for Oracle.

          You may use Community Services subject to the following: (a) Community Services may be used solely for your personal, informational, noncommercial purposes; (b) Content provided on or through Community Services may not be redistributed; and (c) personal data about other users may not be stored or collected except where expressly authorized by Oracle
          >

          Also please read the FAQ on how to ask questions.

          SQL and PL/SQL FAQ

          >
          2) Thread Subject line
          Give your thread a meaningful subject, not just "help please", "Query help" or "SQL". This is the SQL and PL/SQL forum. We know your question is going to be about those things, make it meaningful to the type of question so that people with the right sort of knowledge can pick it up and those without can ignore it. Never, EVER, mark your subject as "URGENT" or "ASAP"; this forum is manned by volunteers giving their own time to help and your question is never urgent or more important than their own work or than other people's questions. It may be urgent to you, but that's not forum members issue.
          • 2. Re: HTML not working in PL/SQL block..Help me ASAP
            BluShadow
            Not sure what you actually want. (apart from an answer ASAP, which is just damn rude!)
            declare
            l_col VARCHAR2(30) :=to_number(to_char(to_date('01-feb-2011','dd-mon-yyyy'),'dd'));
            Why are you converting the day to a number and then storing it in a varchar2 variable?
            • 3. Re: HTML not working in PL/SQL block..Help me ASAP
              Peter Gjelstrup
              Hi Basva,
              If i'm wrong here kindly help me that how can i place a display message
              Is your problem that dbms_output, does not show anything?

              Maybe you need to, add this SQL*Plus command
              SET serveroutput ON
              And there is no need for this to be dynamic. If there were, you would still have to use bind variables!
              EXECUTE IMMEDIATE 'insert into nam(name) values('''||i.name||''')';
              Just write a usual static statement
              insert into nam(name) values(i.name);
              Regards
              Peter
              • 4. Re: HTML not working in PL/SQL block..Help me ASAP
                riedelme
                Odd. I haven't seen anyone use HTP.P to produce HTML for years (well, in SQL*PLUS anyway. Its used in Apex) and suddenly there have been 3 posts about this in the last couple of weeks.

                If you want to test the HTP routines in SQL*PLUS you will have to set up the environment first - SQL*PLUS will allow you to use htp.p and return the results to you like dbms_output.put_line does but you have to do some extra work. When you do get this to work there will be some wierdness in the way things display (linefeeds, page wrapping, and things like that).

                Also, your example is not a full HTML page but only a fragment, so such HTML won't render if you put it in a browser. You need the proper structure - the HTML tags, for instance. Maybe you are intending to use the inserted rows as part of a generated page later.

                An example of doing this is below (untested)
                create or replace procedure drop_me as
                  nm owa.vc_arr;
                  vl owa.vc_arr;
                begin
                  nm(1) := 'X';
                  vl(1) := 'Y';
                  owa.init_cgi_env( nm.count, nm, vl );
                
                  htp.p('<html>');
                  htp.p('hello world');
                  htp.p('</html>');
                end;
                /
                
                exec drop_me
                exec owa_util.showpage 
                nm and vl are collections for init_cgi_env() arguments. The values don't appear to matter. owa_util.showpage is used to read the htp.p output buffer and return the results to the SQL*PLUS screen.

                Edited by: riedelme on Mar 9, 2011 6:22 AM
                • 5. Re: HTML not working in PL/SQL block..Help me ASAP
                  808691
                  HI Bulshadow,

                  I'm having
                  issue with HTML only
                  If possible from ur side to get this resolved ..it ll be good
                  Apart from this i do not have any problem with any thing.

                  Thanks
                  Basva
                  • 6. Re: HTML not working in PL/SQL block..Help me ASAP
                    Peter Gjelstrup
                    Basva wrote:
                    Apart from this i do not have any problem with any thing.
                    Not true, you are having some serious problems with that table ;)
                    • 7. Re: HTML not working in PL/SQL block..Help me ASAP
                      smon
                      Basva wrote:
                      HI Bulshadow,

                      I'm having
                      issue with HTML only
                      If possible from ur side to get this resolved ..it ll be good
                      Apart from this i do not have any problem with any thing.

                      Thanks
                      Basva
                      but what exactly is the issue with the html? Is it not displaying? not displaying correctly?
                      • 8. Re: HTML not working in PL/SQL block..Help me ASAP
                        puchtec
                        Why would you expect to get an output from this PL/SQL? You will only retrieve HTML in case your cursor returns at least one row which is not the case. To get your cursor returning rows (assuming that you use ' instead of " - otherwise your PL/SQL will not compile) you have to ensure that "01" equals "W" which will never be the case, wouldn't it?
                        • 9. Re: HTML not working in PL/SQL block..Help me ASAP
                          Billy~Verreynne
                          Basva wrote:
                          HI Bulshadow,
                          .. snipped..
                          Apart from this i do not have any problem with any thing.
                          Does not seem like - apart from not reading the FAQ and not understanding how the HTP package needs to be used...

                          .. you also have a problem with names. Calling <b>Blu</b>shadow <b>Bul</b>shadow is not exactly complimentary...

                          As for the HTP package - it needs to be used from a valid web environment. The web environment is created by calls to the OWA package. These calls are typically made when the Oracle session is created and initialised by the mod_plsql Apache module.

                          It seems like you do not know how this environment works, nor how to retrieve the HTTP Mime buffer created by the HTP package. Have a look at the sample code in {message:id=2251131} to see what a PL/SQL web session environment should contain and how to manually create it and retrieve the resulting HTTP buffer.