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

Pages

Tuesday, 10 May 2016

Query to View Code for a Package

The relevant query is as follows:

SQL>     set long 10000

SELECT text
FROM    user_source
WHERE name = ‘<PACKAGE_NAME>’;
Share this article :

3 comments:

  1. Hello There,


    Query to View Code for a Package being contrived to exist for many projects simply so it can be run will be the first to hit the wall, but those projects where the functions to make existing transactions cheaper in real world applications will find the elusive real world demand.


    I need to setup auditing (or logging) for RESULTS of queries that are run by certain users against certain tables.

    For example, when they run some select statement against "patients" table I need to log all PatientID that were selected from this table. So, if there were 100 records selected, there should be 100 "audit" records with patientID logged. Is this possible?

    Fine-grained auditing will result in one audit record with the text of the select statement. But how can I audit the actual result of a select query? It seems to me that it should be "select for each row" trigger for this that does not exist.


    Appreciate your effort for making such useful blogs and helping the community.


    Gracias,
    Pranitha

    ReplyDelete
  2. Hi Bro,


    What you’re saying is absolutely correct #topic, but this isn’t the exact situation everywhere. Where most smart folk work on a project - why can’t you do this the Boss asks :).


    from foll. select, how can i get unique values for screen_type and screen

    select a.EMP_NO,a.SCREEN, b.SCREEN_TYPE
    from EMPLOYEE_TAB a, SCREEN_TAB b
    where a.id = b.ID
    and SCREEN_TYPE like '%S';

    EMP_ID SCREEN SCREEN_TYPE
    EMP_123 SCR100 SCRTYPE100S
    EMP_124 SCR100 SCRTYPE100S
    EMP_125 SCR100 SCRTYPE100S
    EMP_127 SCR102 SCRTYPE102S
    EMP_128 SCR102 SCRTYPE102S
    EMP_135 SCR102 SCRTYPE102S
    EMP_136 SCR102 SCRTYPE102Sv






    THANK YOU!! This saved my butt today, I’m immensely grateful.


    krishna

    ReplyDelete
  3. Hi There,

    Thanks for highlighting this and indicating about Query to View Code for a Package where more study and thought is necessary.

    I have some statements that I need to schedule in a procedure.

    Drop Table u_system.emp_load;
    CREATE TABLE u_system.emp_load
    (employee_number CHAR(5),
    employee_atten_time TIMESTAMP,
    machine_number CHAR(3),
    in_out CHAR(2)
    )
    ORGANIZATION EXTERNAL
    (TYPE ORACLE_LOADER
    DEFAULT DIRECTORY user_dir
    ACCESS PARAMETERS
    (RECORDS DELIMITED BY NEWLINE
    FIELDS (employee_number CHAR(4),
    employee_atten_time CHAR(20) date_format TIMESTAMP mask "dd/mm/yyyy hh24:mi:ss",
    machine_number CHAR(4),
    in_out CHAR(1)
    )
    )
    LOCATION ('emp2.dat')
    );
    insert into u_system.emp_attend(employee_number,employee_atten_time,machine_number,in_out)
    select * from u_system.emp_load;

    Follow my new blog if you interested in just tag along me in any social media platforms!

    Thanks,
    Preethi

    ReplyDelete