1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Populating Dynamic Columns

Discussion in 'SQL PL/SQL' started by kiran.marla, Jan 25, 2013.

  1. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Hi I got a requirement as follows ....

    I have two tables table1 and table2 with data.

    The following screen shot is for understanding how the data spreads.

    Table1:

    col
    ---
    R1
    R2
    R3
    R4
    ..
    RN

    Table2:

    col1 col2 col3 col4 .. colN

    we do not know many rows in table1. depends on no of rows of table1 , the no of cols are to be selected from table2.

    Awaiting for update.
     
  2. a_kamalraj

    a_kamalraj Forum Advisor

    Messages:
    121
    Likes Received:
    23
    Trophy Points:
    280
    HI Kiran,

    U mean to say the rows in Col1 if table1 should be come to col1 of table2 and same way row2 of table1 into col2 of table2?
    Hope my understanding is correct.

    Cheers
    Kamal
     
  3. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Exactly Kamal
     
  4. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    I managed to get the link between table1 and table2.

    In attached screen shot, observed that results are fetched from table1.

    Prompt is a heading. and result_column_name is the column name of table2.

    Now How to fetch Prompt value and character8 value.
     

    Attached Files:

  5. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Just created a function and passed parameters. This requirement comes in Collection Plan from Quality Module.

    Accomplished with execute immediate statement :

    cpw_coll_disp_f ( :collection_id,:prompt,:result_column_name );

    Code (SQL):


    CREATE OR REPLACE FUNCTION APPS.cpw_coll_disp_f (
       v_col_id     NUMBER,
       v_prompt     VARCHAR2,
       v_col_name   VARCHAR2
    )
       RETURN CHAR
    IS
       v_sql_stmt   VARCHAR2 (2000);
       v_res        VARCHAR2 (200);
    BEGIN
       v_sql_stmt :=
             'SELECT '
          || v_col_name
          || ' FROM QA_RESULTS_V QR,QA_PLAN_CHARS_V QPC
     WHERE QR.PLAN_ID = QPC.PLAN_ID AND COLLECTION_ID = :1 AND  QPC.PROMPT = :2'
    ;

       EXECUTE IMMEDIATE v_sql_stmt
                    INTO v_res
                   USING v_col_id, v_prompt;

       RETURN (v_res);
    EXCEPTION
       WHEN OTHERS
       THEN
          RETURN NULL;
    END;
    /
     
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
  7. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    My PL/SQL book indicates that in Oracle 11g you'll get an PLW-06009 warning in the event your When Others is not followed by a RAISE 'something'.

    This is not meant to refute the bad practice cited above though...just to note an exception (no pun intended) for a particular environment.

    CJ
     
  8. dariyoosh

    dariyoosh Forum Advisor

    Messages:
    118
    Likes Received:
    19
    Trophy Points:
    260
    Hi,

    Very good remark, but the problem is that this is unfortunately not true if the running PL/SQL block is an anonymous block.

    An example

    Code (SQL):
    ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';
    SET SERVEROUTPUT ON;

    <<bk>>
    DECLARE
        n1 BINARY_INTEGER := 12;
        n2 BINARY_INTEGER := 0;
        n3 BINARY_INTEGER;
    BEGIN
        bk.n3 := bk.n1 / bk.n2; -- Division by zero raises exception
        EXCEPTION
            WHEN OTHERS THEN
                sys.dbms_output.put_line(sqlerrm);
                -- I don't write RAISE or raise_application_error after
                -- which is a bad practice and oracle should give me a
                -- warning.
    END;
    /
    ORA-01476: divisor IS equal TO zero
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
     
    So as we can see the anonymous block was compiled and run without any compilation warning.

    Now, let's put the very same code within a procedure, therefore a stored code and not an anonymous block and to see how oracle behaves

    Code (SQL):
    ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';
    SET SERVEROUTPUT ON;

    CREATE OR REPLACE PROCEDURE testProc
    AUTHID DEFINER
    IS
    BEGIN
        <<bk>>
        DECLARE
            n1 BINARY_INTEGER := 12;
            n2 BINARY_INTEGER := 0;
            n3 BINARY_INTEGER;
        BEGIN
            bk.n3 := bk.n1 / bk.n2;
            EXCEPTION
                WHEN OTHERS THEN
                    sys.dbms_output.put_line(sqlerrm);
        END;
    END testProc;
    /

    SP2-0804: PROCEDURE created WITH compilation warnings

    SQL> SHOW ERRORS;
    Errors FOR PROCEDURE TESTPROC:

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    11/5     PLW-06017: an operation will raise an exception
    13/12    PLW-06009: PROCEDURE "TESTPROC" OTHERS handler does NOT END IN
         RAISE OR RAISE_APPLICATION_ERROR

    SQL>
     
    This time we get the PLW-06009 warning as expected. That is why I always prefer to encapsulate my anonymous code inside a procedure to be sure that I see such warning messages.
     
    DTSIGuy likes this.
  9. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Interesting but what will you do if 'set serveroutput on size XXXXXXX' hasn't been executed? The dbms_output call fails to report any error message:

    Code (SQL):
    SQL> l
      1  <<bk>>
      2  DECLARE
      3      n1 BINARY_INTEGER := 12;
      4      n2 BINARY_INTEGER := 0;
      5      n3 BINARY_INTEGER;
      6  BEGIN
      7      bk.n3 := bk.n1 / bk.n2; -- Division by zero raises exception
      8      EXCEPTION
      9          WHEN OTHERS THEN
     10              sys.dbms_output.put_line(sqlerrm);
     11              -- I don't write RAISE or raise_application_error after
     12              -- which is a bad practice and oracle should give me a
     13              -- warning.
     14* END;
    SQL> /
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
     
    With existing errors it may be best to forego the exception handler and let Oracle and PL/SQL do the work for you:

    Code (SQL):
    SQL> <<bk>>
      2  DECLARE
      3      n1 BINARY_INTEGER := 12;
      4      n2 BINARY_INTEGER := 0;
      5      n3 BINARY_INTEGER;
      6  BEGIN
      7      bk.n3 := bk.n1 / bk.n2; -- Division by zero raises exception
      8
      9  END;
     10  /
    <<bk>>
    *
    ERROR at line 1:
    ORA-01476: divisor IS equal TO zero
    ORA-06512: at line 7

    SQL>
    For user-defined errors either RAISE or RAISE_APPLICATION_ERROR should be used instead of relying on an output procedure that may not show any output. Also it's a good practice to make any user-defined error messages actually mean something to the end user, such that the actual error text be displayed so the users can report the actual error condition. Please read here:

    http://dfitzjarrell.wordpress.com/2008/03/04/what-was-that-masked-message/

    to see how mangling the error message can do more harm than good.
     
    dariyoosh likes this.
  10. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Hi David,

    Code (SQL):

    WHEN OTHERS
    THEN
            RETURN NULL;
     
    This is meant for demo. Actually there was a code which does another action. But here is not required. So Just I replaced with null..

    This will be alarm for others who does the practice with copy past option.. Thanks for reminding...
     
  11. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Posted code is often used by others as valid examples of how to properly code, so such 'examples' should be clearly marked. Posting such code without comments regarding the exception handler can cause confusion.
     
    kiran.marla likes this.
  12. dariyoosh

    dariyoosh Forum Advisor

    Messages:
    118
    Likes Received:
    19
    Trophy Points:
    260
    Of course, that's why I wrote SET SERVEROUTPUT ON at the beginning of my code. Yes, in fact what I did (I mean the way I used sys.dbms_output.put_line) is a bad programming practice to manipulate error messages, not only because it requires SET SERVEROUTPUT ON but more importantly because it is not a real time debugging tool (the result is cached and won't be printed until the sending subprogram or trigger completes. There is no mechanism to flush output during the execution of a procedure).

    In fact I should have mentioned (unfortunately I didn't) at the beginning of my post for the OP that the use of sys.dbms_output had not been suggested as the proper way of manipulating error messages and in that code the accent had been put on the PL/SQL warning for the anonymous blocks (the lack of PLW-06009 warning message in the absence of a RAISE after WHEN OTHERS in an anonymous block).

    Nice article, thanks for sharing. However given the fact that you use sys.dbms_output to print stack trace :), it seems to me that there is no SET SERVEROUTPUT ON in your article's code, you may want to add it at the beginning, otherwise if someone (in particular a beginner) who reads your article and doesn't already know that SET SERVEROUTPUT ON is required, he/she will be confused as he/she runs the very same code indicated in the article but will not see the stack trace in those examples.

    The correct and best approach is to not use sys.dbms_output at all for that purpose but create a log table which is populated by a log procedure defined as an autonomous transaction.

    A part from that, you give a nice presentation of how to conserve the complete StackTrace in that article.
     
  13. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I will agree that the 'best' approach is to populate a log table which can be queried later and will preserve the error text, the date, the user and the program unit being called but I cannot go as far as to say that is the 'correct' procedure. Any method that produces results that can be used to trace the problem and find a solution is 'correct'. My goal with that post was, and still is, to address code developers may write which obscures relevant error information under the guise of being 'user friendly'.

    I didn't print the SET SERVEROUTPUT ON instructions, simply the relevant code to produce the desired results; they are part of the script else I'd have never seen the output posted. There are also parts of that script which create a new user, connect as that user, connect as sys and drop the newly created user cascade when the relevant code has completed which are not shown, either. A blog isn't the same as a forum post; here we have a broad spectrum of users, with a wide range of experience with Oracle, from novice through experienced DBA/developer. I take the position that anything posted here needs to be able to run without modification for any user, thus the length of some of my examples. This is also why I keep 'preaching' that untested code should not be posted as it can, and does, confuse those members with little or no experience with Oracle when that code doesn't work.

    I appreciate the discussion on this, and I'm certain it has helped others we don't know or know about. I also respect your opinions; I do hope you also respect mine.
     
  14. dariyoosh

    dariyoosh Forum Advisor

    Messages:
    118
    Likes Received:
    19
    Trophy Points:
    260
    Of course, I respect your opinions, otherwise I wouldn't have participated in this discussion, and if I made a few remarks those were nothing but some personal point of view and ideas that I thought could be relevant to this discussion. And once again I thank you for the nice article (really good and expressed with simple and clear examples which illustrates the key points about stack trace and managing error messages while dealing with exceptions).

    Well, at least for me it has been really informative and interesting (particularly the last example in your article)

    Thanks again,