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!

CLient Extension (PAXCCECB.pls)

Discussion in 'SQL PL/SQL' started by neelambhumij, Mar 4, 2014.

  1. neelambhumij

    neelambhumij Active Member

    Messages:
    51
    Likes Received:
    2
    Trophy Points:
    160
    Location:
    JOHANNESBURG
    Hi all

    Can anybody assist me with PAXCCECB.pls?
    my client want to have some changes on it(nt specified what yet), SO PLEASE TELL ME THE PROCESS TO CHANGE IT..
    Do I need to change the .pls file or the corresponding package in toad.

    Please suggest.

    Thanks

    Kind Regards
    Neelam
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You change the source file then re-create the package.
     
    neelambhumij likes this.
  3. neelambhumij

    neelambhumij Active Member

    Messages:
    51
    Likes Received:
    2
    Trophy Points:
    160
    Location:
    JOHANNESBURG
    Hi David

    i have a doubt....
    I found the .pls file (source file) is having a package n only 1 procedure in it.
    while if I open the same package in toad.. i get multiple procedures...
    How they are related??

    Can you plz give me an example??

    Thanks and Regards
    Neelam
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    There may be more than one file for this package, or you may have opened the wrong file. A package will have a specification and a body which must match in declarations. If there are more procedures in the current package than shown in the text file there is a newer definition of that package somewhere.


    You need to find that newer copy.
     
  5. neelambhumij

    neelambhumij Active Member

    Messages:
    51
    Likes Received:
    2
    Trophy Points:
    160
    Location:
    JOHANNESBURG
    Hi David..

    I tried my best to find the file in PA_top folder.. unfortunately failed..
    Can you plz suggest me the path... may be as that in your system/environment.

    Thanks and Regards
    Neelam
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    No I can't as all installations are different. You CAN extract the code from the database using either the USER_SOURCE view or the DBMS_METADATA.GET_DDL function. To use the latter:


    Code (SQL):

    SQL> SET long 50000 linesize 5000 trimspool ON pagesize 0
    SQL>
    SQL> SELECT dbms_metadata.get_ddl('PACKAGE','DATES_PKG','GRIBNAUT') FROM dual;


      CREATE OR REPLACE PACKAGE "GRIBNAUT"."DATES_PKG"
    AS
        FUNCTION julian_date
            ( date_to_convert DATE )
            RETURN NUMBER;
        FUNCTION minutes_since_midnight
            ( timevalue DATE )
            RETURN NUMBER;
        FUNCTION minutes_elapsed
            ( lowdate DATE
            , highdate DATE )
            RETURN NUMBER;
    END dates_pkg;
    CREATE OR REPLACE PACKAGE BODY "GRIBNAUT"."DATES_PKG"
    AS
        FUNCTION julian_date
            ( date_to_convert DATE)
            RETURN NUMBER
        IS
            varch_value VARCHAR (10);
            num_value NUMBER (20);
        BEGIN
            --
            -- First, we take a date and convert it to a date by converting it
            -- to a character string using the same format we will use to
            -- convert it BACK to a date again
            --
            -- Oh, then we convert it back to a character string
            --
            -- In Julian format, which is a number
            --
            SELECT TO_CHAR
                   ( TO_DATE(TO_CHAR(date_to_convert,'MM/DD/YYYY'),'MM/DD/YYYY')
                   , 'J')
            INTO   varch_value
            FROM   dual;
            --
            -- Okay, so we had a Julian date as a number but we changed it to
            -- a character string so we could go back and make it a ...
            -- NUMBER ... again
            --
            SELECT TO_NUMBER (varch_value)
            INTO   num_value
            FROM   dual;
            --
            -- So, we finally make up our mind and keep it a number and
            -- return it from the function
            --
            RETURN (num_value);
        END julian_date;

        FUNCTION minutes_since_midnight (
            timevalue DATE)
            RETURN NUMBER
        IS
            secs_elapsed NUMBER (20);
            mins_elapsed NUMBER (20);
        BEGIN
            --
            -- So now we take a date and extract the time portion of it,
            -- convert that BACK to a date, then convert THAT to a string
            -- of seconds and convert THAT to a number
            --
            -- Is it me, or are we essentially driving across town just to
            -- go next door?
            --
            SELECT TO_NUMBER
                   ( TO_CHAR(TO_DATE(TO_CHAR(timevalue,'HH:MI AM'),'HH:MI AM')
                   , 'SSSSS') )
            INTO   secs_elapsed
            FROM   dual;
            --
            -- Oooo, now we divide that total number of seconds by ...
            -- wait for it ...
            -- any second now ...
            -- 60!  Who would have thought that 60 seconds equals
            -- one minute?
            --
            SELECT (secs_elapsed / 60)
            INTO   mins_elapsed
            FROM   dual;
            --
            -- Before we rest on our laurels we return the minutes since midnight
            --
            RETURN (mins_elapsed);
        END minutes_since_midnight;

        FUNCTION minutes_elapsed
            ( lowdate DATE
            , highdate DATE )
            RETURN NUMBER
        IS
            final_number NUMBER (20);
            low_julian NUMBER (20);
            high_julian NUMBER (20);
            num_days NUMBER (20);
            num_minutes NUMBER (20);
            temp_mins NUMBER (20);
            min_low NUMBER (20);
            min_high NUMBER (20);
        BEGIN
            --
            -- Now, why didn't we use this julian_date function in the
            -- last installment of Julian conversions?
            --
            -- Oh, yeah, because we just WROTE that wonderful function
            --
            -- So, okay, we take our date values and return the Julian
            -- representations of them using all of the mathematical
            -- aerobics from earlier
            --
            -- I guess this is so much easier than simply subtracting
            -- them
            --
            SELECT julian_date (lowdate)
            INTO   low_julian
            FROM   dual;
            SELECT julian_date (highdate)
            INTO   high_julian
            FROM   dual;
            --
            -- Woo-hoo! Higher math time!  Subtract the Julian dates
            -- and get the number of days
            --
            -- Isn't that what we'd get if we just subtracted the
            -- submitted dates as-is?
            --
            -- Of course it is
            --
            SELECT (high_julian - low_julian)
            INTO   num_days
            FROM   dual;
            --
            -- Now we calculate the total minutes elapsed
            -- using our values generated by our extreme
            -- gyrations
            --
            -- I'm out of breath just thinking about all of this work
            --
            SELECT (num_days * 1440)
            INTO   num_minutes
            FROM   dual;
            --
            -- And now we put those other mathematical moves
            -- to use
            --
            -- Tell me again why we think we're smarter than
            -- the average bear?
            --
            SELECT minutes_since_midnight (lowdate)
            INTO   min_low
            FROM   dual;
            SELECT minutes_since_midnight (highdate)
            INTO   min_high
            FROM   dual;
            --
            -- Now this is disgusting
            --
            -- Using a TEMP variable to aid in simple mathematical
            -- processing
            --
            SELECT (min_high - min_low)
            INTO   temp_mins
            FROM   dual;
            --
            -- And this is better than:
            -- select (end_date - start_date)*1440 because?
            --
            SELECT (num_minutes + temp_mins)
            INTO   final_number
            FROM   dual;
            RETURN (final_number);
        END minutes_elapsed;
    END dates_pkg;

    SQL>
     

    Spool that output to a file and you should have usable text to modify. Once modified you can create the modified package. You will need to include the terminating '/ characters after the create package and create package body sections.
     
    neelambhumij likes this.
  7. neelambhumij

    neelambhumij Active Member

    Messages:
    51
    Likes Received:
    2
    Trophy Points:
    160
    Location:
    JOHANNESBURG
    thanks David..

    I got the package body from the Toad.
    Do I need to update the pls file using this pckage body?
    I guess m not able to express perfectly so plz dont mind.
    Please have a look on both the files so that u can suggest me properly.
     

    Attached Files: