Follow us: Connect on YouTube Connect on YouTube Connect on YouTube

Pages

Saturday, 2 April 2016

SQL Case

Case is similar to decode but easier to understand while going through coding

Ex:
SQL> Select sal,
          Case sal
                    When 500 then ‘low’
                    When 5000 then ‘high’
                    Else ‘medium’
          End case
          From emp;

       SAL          CASE
       -----       --------
       500          low
      2500         medium
      2000         medium
      3500         medium
      3000         medium
      5000         high
      4000         medium
      5000         high
      1800         medium
      1200         medium
      2000         medium
      2700         medium
      2200         medium

      3200         medium
Share this article :

4 comments:

  1. Hi Dayakar,

    I have 2 FCI, let's call them InstA and InstB, they currently run on 2 separate active-passive clusters:
    A_node_1, A_node_2 -> they host intance A
    B_node_1, B_node_2 -> they host instance B
    the servers are in the same domain and same subnet and they have the same number of cores. Since my licenses are covered by SA I pay only for the active node, according to MS licensing policies.
    I want to configure an additional layer of high availability configuring AlwaysOn between Inst_A and Inst_B, I know that this solution is technically supported, I have to create a cluster with 4 nodes, configure A_node_1 and A_node_2 as the possible and preferred node for the FCI InstaA, and B_node_1 and B_node_2 as the possible and preferred node for the FCI InstB.

    My doubt is about licensing.
    MS says "you won't pay for the FIRST truly passive server in HA configuration", do I have to license 4 servers? 3? 2?

    Regards,
    Irene Hynes

    ReplyDelete
  2. Hello Mate,


    The sense of praise that I have found for you after reading SQL Case is overwhelming! Such a tremendous read!
    I am executing one package from Linux shell script.This will invoke JAVA application, from that Database procedure will gets executed.

    But when I am running continuously the same job, some time following error is coming ,but
    some time it executing successfully.

    When I am running the same procedure complete from the DATABASE, always its executing successfully.

    Unable to find out under which scenarios,existing STATE OF PACKAGE is becoming INVALIDATE


    ORA-04061: existing state of package "MAIN.PKG_ABCD_XYZ_HANDOFF" has been invalidated
    ORA-04065: not executed, altered or dropped package "MAIN.PKG_ABCD_XYZ_HANDOFF"
    ORA-06508: PL/SQL: could not find program unit being called: "MAIN.PKG_ABCD_XYZ_HANDOFF"









    By the way do you have any YouTube videos, would love to watch it. I would like to connect you on LinkedIn, great to have experts like you in my connection (In case, if you don’t have any issues).


    Best Regards,

    ReplyDelete
  3. Marhaba,


    Fully agree on SQL Case . We’re seeing a lot of projects tackle big complex problems but few seem to have taken into consideration and in particular reasons to adopt.


    I want to execute the
    different files at the run time in the SQL PLUS

    Ex: I have two different files like ABC.SQL, XYZ.SQL,

    I had declared one bind variable to hold file_name .

    var file_name VARCHAR2(200);
    DECLARE
    sr VARCHAR2(20) :='ABC';
    BEGIN
    IF sr ='ABC' THEN
    :file_name :='ABC.SQL';
    ELSE
    :file_name :='XYZ.SQL';
    END IF;
    END;
    /
    PRINT file_name


    Now i want to execute the file which is there in :file_name bind variable

    SQL>@:file_name



    It was cool to see your article pop up in my google search for the process yesterday. Great Guide.
    Keep up the good work!


    Obrigado,
    Ajeeth

    ReplyDelete
  4. Halo,


    Fully agree on #topic. We’re seeing a lot of projects tackle big complex problems but few seem to have taken into consideration and in particular reasons to adopt.

    Currently I'm trying to make a test using create job as a way to have multiple process on database starting in a loop.
    Basically I'm getting different behaviors when testing on different database machines. I'll define the machine names are A and B.
    If I started running the anonymous block below in the machine A for a loop to create 3 multiple jobs, is working fine, i.e, the create job using
    enabled parameter, created the job and automatically start:
    DECLARE
    L_job_name VARCHAR2(100);
    L_comments VARCHAR2(240);
    L_pls_block VARCHAR2(32000);
    L_thread NUMBER := 1; -- count of jobs
    L_max_threads NUMBER := 3; -- max number of jobs to be started
    L_lot_id NUMBER := 1234; -- add any number just to represent a lot
    BEGIN

    while L_thread <= L_max_threads loop
    ---
    L_job_name := 'job_' || L_lot_id || L_thread;
    L_comments := 'Job test ' || L_lot_id || ' and thread ' || L_thread;
    L_pls_block := 'BEGIN logger.log(''job running of thread' || L_thread || '''); END;';
    ---
    dbms_scheduler.create_job(job_name => L_job_name,
    job_type => 'PLSQL_BLOCK',
    job_action => L_pls_block,
    start_date => SYSTIMESTAMP,
    comments => L_comments,
    enabled => true);

    --DBMS_SCHEDULER.RUN_JOB(L_job_name, TRUE);

    L_thread := L_thread + 1;
    end loop;
    END;
    /

    but if I start the same code in the machine B, nothing is happening and I need to uncomment the line about DBMS_SCHEDULER.RUN_JOB(L_job_name, TRUE);

    I did another test in the same machine B using enabled FALSE when creating the job and during the call from DBMS_SCHEDULER.RUN_JOB changed to false in the user_credentials but only one job runned, i.e, the result was logged from just one job, instead of the 3 jobs that had most be created.

    Do you have an idea about what I need to look? Is there something related about database setup, credentials that I need to take a look?

    It was cool to see your article pop up in my google search for the process yesterday. Great Guide.
    Keep up the good work!



    Thank you,

    ReplyDelete