Jan 172014
 

Here’s another quiz. The procedure below is executed, “something” is run and the output changes on the next execution:

exec why_does_the_output_change;

from this

-- What happens here?

exec why_does_the_output_change;

to this

To cover off the easy options – no change is made to the code of the procedure and there’s no SQL within it (so the “mystery code” isn’t updating/inserting/deleting table data for example).

What did I do? Put your answers in the comments!

(Just as I finished writing this I thought of a second way this could be done, though arguably it involves a code change – can you get them both? Are there more options?)

Get SQLfail sent to your inbox

  17 Responses to “PL/SQL Quiz: Why does the output of this procedure change (without changing it)?”

  1. You might use something which involves sysdate – it is changed so the result too …

  2. Something like
    declare
    t_client_info varchar2(100);
    begin
    dbms_application_info.read_client_info( t_client_info );
    if t_client_info is null
    then
    dbms_application_info.set_client_info( ‘x’ );
    dbms_output.put_line( ‘from this’ );
    else
    dbms_application_info.set_client_info( null );
    dbms_output.put_line( ‘to this’ );
    end if;
    end;

    Or even
    begin
    if dbms_types.NO_DATA = 100
    then
    dbms_types.NO_DATA := 99;
    dbms_output.put_line( ‘from this’ );
    else
    dbms_types.NO_DATA := 100;
    dbms_output.put_line( ‘to this’ );
    end if;
    end;

  3. I also suggest SYSDATE as well as Package Instantiation and Initialization.

    • Yep, setting package variables/state would work, it isn’t what I was thinking of though.

      Also, it seems I wasn’t clear enough – there’s another command I run between procedure executions which causes the output to change. What could the command be?

      Package initing could be a command, but SYSDATE would cause the output to change on its own.

  4. alter session set nls_date_format=’”from this”‘

    begin
    dbms_output.put_line( to_char( to_date( ’07-01-2014′, ‘dd-mm-yyyy’ ) ) );
    end;

    alter session set nls_date_format=’”to this”‘

    begin
    dbms_output.put_line( to_char( to_date( ’07-01-2014′, ‘dd-mm-yyyy’ ) ) );
    end;

  5. The procedure tests for an attribute value in an application context and outputs from or to depending on the value?
    In between calls you change the attribute value with DBMS_SESSION.SET_CONTEXT?


  6. sokrates@12.1 > create procedure why_does_the_output_change is
    2 j binary_integer;
    3 begin
    4 /* needs grant create any procedure */
    5 dbms_job.submit(j, q'|
    6 begin execute immediate q'{
    7 create or replace procedure why_does_the_output_change is
    8 begin
    9 dbms_output.put_line('to this');
    10 end why_does_the_output_change;
    11 }'; end;
    12 |');
    13 commit;
    14 dbms_output.put_line('from this'); dbms_lock.sleep(3);
    15 end why_does_the_output_change;
    16 /

    Procedure created.

    sokrates@12.1 > exec why_does_the_output_change;
    from this

    PL/SQL procedure successfully completed.

    sokrates@12.1 > exec why_does_the_output_change;
    to this

    PL/SQL procedure successfully completed.

    • Sneaky – I like it :) The methods I’m thinking of are both intentional features though – i.e. the reason they exist is to change the operation of code simply by running (some other) command. I’ve used both for their intended purpose in production.

      • ALTER SESSION SET EDITION = release_to_this;

        • Great work Anton – that’s one of the things I was thinking of! It’s actually the second idea that came to mind. Can you get the other?

          • easy enough then – changing the namespace

            Sokrates.

            sokrates > create procedure why_does_the_output_change is begin dbms_output.put_line('from this'); end why_does_the_output_change;
            2 /

            Procedure created.

            Xenophon.

            xenophon > create procedure why_does_the_output_change is begin dbms_output.put_line('to this'); end why_does_the_output_change;
            2 /

            Procedure created.

            xenophon > grant execute on why_does_the_output_change to sokrates;

            Grant succeeded.

            Sokrates then.

            sokrates > exec why_does_the_output_change
            from this

            PL/SQL procedure successfully completed.

            sokrates > alter session set current_schema = xenophon;

            Session altered.

            sokrates > exec why_does_the_output_change
            to this

            PL/SQL procedure successfully completed.

          • Nice – but that wasn’t what I was thinking of either!

            I thought there would be more ways to do this than two, I didn’t realise there would be quite so many possibilities though :)

          • If don’t know if this is the one, but it’s one you could use on a production system

            BEGIN
            $IF $$my_debug
            $THEN DBMS_OUTPUT.PUT_LINE(‘to this’);
            $ELSE DBMS_OUTPUT.PUT_LINE(‘from this’);
            $END
            END;
            /

            ALTER SESSION SET PLSQL_CCFLAGS = ‘my_debug:true’;

            BEGIN
            $IF $$my_debug
            $THEN DBMS_OUTPUT.PUT_LINE(‘to this’);
            $ELSE DBMS_OUTPUT.PUT_LINE(‘from this’);
            $END
            END;
            /

          • Yep, that’s what I was thinking of Anton! Nice work on all the alternatives too.

 Leave a Reply

(required)

(required)

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>