Pages

OracleEBSpro is purely for knowledge sharing and learning purpose, with the main focus on Oracle E-Business Suite Product and other related Oracle Technologies.

I'm NOT responsible for any damages in whatever form caused by the usage of the content of this blog.

I share my Oracle knowledge through this blog. All my posts in this blog are based on my experience, reading oracle websites, books, forums and other blogs. I invite people to read and suggest ways to improve this blog.


Showing posts with label Misc. Show all posts
Showing posts with label Misc. Show all posts

Monday, May 5, 2014

TOAD - Privileges required for Debugging Oracle Procedure

 

The following privileges are required for debugger:
1. GRANT ALTER SESSION TO user_name;
2. GRANT CREATE SESSION TO user_name;
3. GRANT EXECUTE ON DBMS_DEBUG to user_name;

Minimum requirements to debug other than your own procedures, functions, and packages:
1. GRANT ALTER ANY PROCEDURE TO user_name; (compile)
2. GRANT CREATE ANY PROCEDURE TO user_name; (edit / save)

The following additional privileges are required for debugger inversion 10g and any version released after that:
1. GRANT DEBUG ANY PROCEDURE TO user_name;
2. GRANT DEBUG CONNECT SESSION TO user_name;

Also, verify that the DBMS_DEBUG package in the sys schema is valid.
After the above, EXIT and RESTART Toad.
 
References:
 
 

Wednesday, May 22, 2013

Five Ninja Tips for Success


Be willing to fail. This also means when you do fail, learn from that experience. You’ve already made it over one of life’s hurdles – college. You will most likely enjoy many successes, but you need to know that you will also fail.
I’ve learned the most from my failures. Here is one example in my life. When I was a counselor at a sleep-away camp, I was fired because on my day off I violated a rule and brought beer onto the campgrounds. I will never forget my embarrassment and anger and the trip back with my father, who had to pick me up and take me home. In retrospect, I learned that actions have consequences, that it hurts to be fired and that my father was wonderful because he knew I was devastated and needed no further chastising. I failed, but I learned more from that experience than many of my successes.
What makes America different is that you can pick yourself up from failure and try again. Steve Jobs was fired from Apple and returned to transform the company and our lives. Many of history’s most respected figures – from George Washington and Abraham Lincoln to Henry Ford – failed countless times before they found success. Failure is part of life, so be prepared for that. Don’t be afraid of it. When it happens, don’t let it shatter you. Failure is how we learn about ourselves and about life. Think about that each time you fail.
Ask lots of questions. You will not get very far in your career – or in life – if you already know all the answers. Look for mentors. So many times I have seen young people start out thinking they can do it all on their own. Some of you might even be thinking you have to do it on your own. But you can’t, and you shouldn’t.
My very first mentors were my parents. They taught me the value of setting goals and hard work. Along the way I’ve had many more mentors, both inside and outside my industry. Their perspectives and strong examples have helped shape my approach on everything from how I run a meeting to how I structure my work-life balance.
Be passionate about learning. Just because you’ve finished college doesn’t mean you have nothing left to learn. A ninja innovator is always learning. That love of learning is the catalyst for innovation. It will help you stand out from the crowd, because you’ll have the knowledge you need to go into battle to achieve success.
Take risks. success requires doing battle, and doing battle requires taking risks. Some of the best and most innovative ideas in history have been the result of fearless leaders who took risks. Now, taking risks is not the same thing as being careless. Like World War II General George Patton said, “Take calculated risks.” Amazon CEO Jeff Bezos is a great example of a smart risk-taker. On a macro level, Bezos’ strategy for Amazon bucks public-company convention. He creates his own competition and forsakes immediate profits to invest in keeping his customers thrilled. His philosophy plays out on the micro level in services like Amazon Prime and products like the Kindle, both of which have turned out to be hugely popular and successful for Amazon.
The final ninja tip may surprise you: Don’t become a slave to your work. You will be better at your job if you have different interests outside of your job. Creativity itself is often the ability to find connections between unrelated things. You also need to take a step back, spend time with your family and loved ones, and do things you enjoy. Time spent away from work is crucial to success, because it allows you to focus on the things that matter most, and that gives you the energy you need to work hard and be successful.
Each of you can make a difference. You can make something happen. You will be our next innovators, our next leaders. Whatever career path you choose, keep these five tips in mind. Don’t be afraid to fail; look for mentors; keep on learning; take calculated risks; and make time to get away so you can always come back refreshed. 

Sunday, April 28, 2013

Cloud Computing

What is Cloud???

The cloud is a computing service that charges you based only on the amount of computing resources we use.

Pay as you go


lVirtualization: 
The ability to run multiple operating systems on a single physical system and share the underlying hardware resources.

Cloud Computing: 
The provisioning of services in a timely (near on instant), on-demand manner, to allow the scaling up and down of resources


Key Characteristics :


Agility
Cost
Device independency
Reliability
Scalability
Security
Sustainability

Components in Cloud Computing :


Client
Services
Application
Platform
Storage
Infrastucture

Normally technology is organized into three layers that are placed like a pyramid:





Cloud Computing Architecture:

Infrastructure as a service (IaaS)
Platform as a service (PaaS)
Software as a service (SaaS)
Network as a service (NaaS)
Storage as a service (STaaS)
Security as a service (SECaaS)
Data as a service (DaaS)
Desktop as a service (DaaS - see above)
Database as a service (DBaaS)
Test environment as a service (TEaaS)
API as a service (APIaaS)
Backend as a service (BaaS)



Software as a Service :

Software as a service feature a complete application offered as a service demand single instance of the software runs on the cloud and services multiple end users or client organization

Ex : Google Apps

Platform as a Service :

Platform as a Service  encapsulates a layer of software and provides it as a service that can be used to build higher level services

2 perspectives for  Platform as a Service

1. Producer
2. Consumer

Infrastructure as a Service :

Infrastructure as a service delivers basic storage and computing capabilities as standardized services over the network

Computing Types :
Cloud computing comes in three forms: public clouds, private clouds, and hybrids clouds. Depending on the type of data you're working with, you'll want to compare public, private, and hybrid clouds in terms of the different levels of security and management required.


Public Clouds

A public cloud is one in which the services and infrastructure are provided off-site over the Internet. These clouds offer the greatest level of efficiency in shared resources; however, they are also more vulnerable than private clouds. A public cloud is the obvious choice when

Your standardized workload for applications is used by lots of people, such as e-mail.

You need to test and develop application code.

You have SaaS (Software as a Service) applications from a vendor who has a well-implemented security strategy.

You need incremental capacity (the ability to add computer capacity for peak times).

You’re doing collaboration projects.

You’re doing an ad-hoc software development project using a Platform as a Service (PaaS) offering cloud.


Private Clouds

A private cloud is one in which the services and infrastructure are maintained on a private network. These clouds offer the greatest level of security and control, but they require the company to still purchase and maintain all the software and infrastructure, which reduces the cost savings. A private cloud is the obvious choice when

Your business is your data and your applications. Therefore, control and security are paramount.

Your business is part of an industry that must conform to strict security and data privacy issues.

Your company is large enough to run a next generation cloud data center efficiently and effectively on its own.


Hybrid Clouds

A hybrid cloud includes a variety of public and private options with multiple providers. By spreading things out over a hybrid cloud, you keep each aspect at your business in the most efficient environment possible. The downside is that you have to keep track of multiple different security platforms and ensure that all aspects of your business can communicate with each other. Here are a couple of situations where a hybrid environment is best.

Your company wants to use a SaaS application but is concerned about security. Your SaaS vendor can create a private cloud just for your company inside their firewall. They provide you with a virtual private network (VPN) for additional security.

Your company offers services that are tailored for different vertical markets. You can use a public cloud to interact with the clients but keep their data secured within a private cloud.

Wednesday, April 24, 2013

Single Euro Payments Area (SEPA)

The Single Euro Payments Area (SEPA) is a payment-integration initiative of the European Union for simplification of bank transfers. As of March 2012, SEPA consists of the 27 EU member states, the four members of the EFTA (Iceland,LiechtensteinNorway and Switzerland) and Monaco.

http://en.wikipedia.org/wiki/Single_Euro_Payments_Area


Wednesday, April 10, 2013

Steps to remove the locked database object

--get object id
SELECT * FROM DBA_OBJECTS WHERE OBJECT_NAME LIKE 'XX_RES_ALLOC_DM%';

select * from all_objects where object_id IN (323889) ;

--find locked or not , if found then find session id
select * from v$locked_object;

 WHERE object_id IN (1193375,1169419,1169514)  ;         

---from the session id(SID), find serail
select * from v$session where sid=3250;

---kill the session 'session id, serail '
alter system kill session (:sid, :SERIAL#) ;
alter system kill session '3120,9499' immediate;

Saturday, March 9, 2013

Memory Architecture


This chapter discusses the memory architecture of an Oracle Database instance. It contains the following topics:
See Also:
Oracle Database Administrator's Guide for instructions for configuring and managing memory

Introduction to Oracle Database Memory Structures

Oracle Database uses memory to store information such as the following:
  • Program code
  • Information about a connected session, even if it is not currently active
  • Information needed during program execution (for example, the current state of a query from which rows are being fetched)
  • Information that is shared and communicated among Oracle Database processes (for example, locking information)
  • Cached data (for example, data blocks and redo log entries) that is also permanently stored on storage devices

Basic Memory Structures

The basic memory structures associated with Oracle Database include:
  • Software code areas
    Software code areas are portions of memory used to store code that is being run or can be run. Oracle Database code is stored in a software area that is typically at a different location from users' programs—a more exclusive or protected location.
  • System global area (SGA)
    The SGA is a group of shared memory structures, known as SGA components, that contain data and control information for one Oracle Database instance. The SGA is shared by all server and background processes. Examples of data stored in the SGA include cached data blocks and shared SQL areas.
  • Program global area (PGA)
    A PGA is a memory region that contains data and control information for a server process. It is nonshared memory created by Oracle Database when a server process is started. Access to the PGA is exclusive to the server process. There is one PGA for each server process. Background processes also allocate their own PGAs. The total memory used by all individual PGAs is known as the total instance PGA memory, and the collection of individual PGAs is referred to as the total instance PGA, or just instance PGA. You use database initialization parameters to set the size of the instance PGA, not individual PGAs.
Figure 8-1 illustrates the relationships among these memory structures.
Figure 8-1 Oracle Database Memory Structures
Description of Figure 8-1 follows
Description of "Figure 8-1 Oracle Database Memory Structures"

Overview of the System Global Area

The System Global Area (SGA) and the set of database processes constitute an Oracle Database instance. Oracle Database automatically allocates memory for an SGA when you start an instance, and the operating system reclaims the memory when you shut down the instance. Each instance has its own SGA.
The SGA is read/write. All database background processes and all server processes that execute on behalf of users can read information contained within the instance's SGA, and several processes write to the SGA during database operation.
Part of the SGA contains general information about the state of the database and the instance, which the background processes need to access. This is called the fixed SGA. No user data is stored here. The SGA also includes information communicated between processes, such as locking information.
If the system uses shared server architecture, then the request and response queues and some contents of the PGA are in the SGA.
As shown in Figure 8-1, the SGA consists of a number of memory components, which are pools of memory used to satisfy a particular class of memory allocation requests.
The most important SGA components are the following:

Database Buffer Cache

The database buffer cache is the portion of the SGA that holds copies of data blocks read from datafiles. All users concurrently connected to the instance share access to the database buffer cache.
This section includes the following topics:

Wednesday, March 6, 2013

DBMS_UTILITY.GET_TIME


This function returns the number of 100ths of seconds that have elapsed from an arbitrary time.
Syntax
DBMS_UTILITY.GET_TIME RETURN NUMBER;
Usage Notes
You should not use GET_TIME to establish the current time, but only to calculate the elapsed time between two events. Without GET_TIME, Oracle functions can only record and provide elapsed time in second intervals, which is a very coarse granularity in today's world of computing. With GET_TIME, you can get a much finer understanding of the processing times of lines in your program.
Example
The following example calculates the number of 100ths of elapsed seconds since the calc_totals procedure was executed:
DECLARE
   time_before BINARY_INTEGER;
   time_after BINARY_INTEGER;
BEGIN
   time_before := DBMS_UTILITY.GET_TIME;
   calc_totals;
   time_after := DBMS_UTILITY.GET_TIME;
   DBMS_OUTPUT.PUT_LINE (time_after - time_before);
END;
Notice that in our anonymous block we had to declare two local variables, make our calls to GET_TIME, and then compute the difference. We will probably need to perform those actions over and over again in our programs. You might even want to perform timings that cross product lines (e.g., start a timing in a form and then check elapsed time from inside a report module).
Syntax diagrams and parameter descriptions adapted from Oracle, Inc. documentation.

Oracle Important Links


I've listed a few of the Oracle sites and blogs I visit regularly (this is by no means an exhaustive list). Of course, I'd like you to spend a bit of time here on oracle-developer.net before you visit any of these!

favourite sites and blogs

  • Ask Tom
    Do I need to explain why? Is there an Oracle developer out there who doesn’t owe something to this site and the man behind it?
  • JL Computer Consultancy and Oracle Scratchpad
    Jonathan Lewis’s website and blog are absolutely essential online resources for anyone with an interest in understanding how Oracle works and how to get the most out of it.
  • Tanel Poder's Blog
    Tanel's excellent blog is purely technical, which to my mind is exactly what an Oracle blog should be. In addition to his technical research, articles and presentations, Tanel has also developed a range of really useful performance utilities.
  • Oracle-Base
    Tim Hall’s site of concise articles and new feature guides is one of the best resources for getting a feel for a new Oracle release, especially for database developers.
  • William Robertson
    William is a major contributor to Oracle developer forums, as well as being an Oracle ACE and a member of the Quest PL/SQL Pipelines panel of experts. His site has a lot of useful scripts and some excellent articles. Read his superb article on the evils of scripting against a database!
  • Steven Feuerstein's Cyberworld
    Steven Feuerstein's website, including all-things-PL/SQL and links to his other sites of interest to Oracle developers.
  • About Oracle
    Rob Van Wijk's excellent blog for Oracle developers, full of technical content and techniques.
  • Striving For Oracle Performance
    Christian Antognini's blog adds to the wealth of information he already provided in his Troubleshooting Oracle Performance book.
  • Oracle Related Stuff
    Randolf Geist's detailed blog for Oracle developers and DBAs with a particular interest in the Cost-Based Optimiser and its mechanics.
  • OraStory
    Dominic Brooks' blog for Oracle developers with an interest in performance and design.
  • Structured Data
    Greg Rahn's excellent blog for anyone with an interest in Oracle database performance.

oracle forums

  • OTN SQL-PL/SQL Forum
    Some very high quality Oracle developers share their knowledge on this forum.
  • OraFAQ
    I haven’t been there in a while, but I used to use this quite heavily. Has some good developers answering a lot of questions on a wide range of categories.

general oracle resources

Credit goes to the below website(s):

Wednesday, February 27, 2013

AOL SCRIPTS

Value set creation:
/* Formatted on 2010/11/30 14:19 (Formatter Plus v4.8.8) */
BEGIN
   fnd_flex_val_api.create_valueset_table (value_set_name               => 'CAR_TRAN_TYPES3',
                                           description                  => 'Transaction Types',
                                           security_available           => 'N',
                                           enable_longlist              => 'N',
                                           format_type                  => 'C',
                                           maximum_size                 => 20,
                                           PRECISION                    => NULL,
                                           numbers_only                 => 'N',
                                           uppercase_only               => 'N',
                                           right_justify_zero_fill      => 'N',
                                           min_value                    => NULL,
                                           max_value                    => NULL,
                                           table_application            => 'Custom Accounts Receivable',
                                           table_appl_short_name        => 'CAR',
                                           table_name                   => 'CAR_CHARGE_AREAS',
                                           allow_parent_values          => 'N',
                                           value_column_name            => 'INVOICE_TRAN_TYPE',
                                           value_column_type            => 'C',
                                           value_column_size            => 20,
                                           meaning_column_name          => NULL,
                                           meaning_column_type          => NULL,
                                           meaning_column_size          => NULL,
                                           id_column_name               => NULL,
                                           id_column_type               => NULL,
                                           id_column_size               => NULL,
                                           where_order_by               => NULL,
                                           additional_columns           => NULL
                                          );
END;

**********************************************************
AOL Scripts:
set serveroutput on
set define off

declare
  l_stage varchar2(100);
  program_found EXCEPTION;
begin

fnd_program.debug_on;
fnd_program.set_session_mode ('customer_data');

---------------------------------------------------------------------------
-- Check if Program exists and if so delete it
---------------------------------------------------------------------------
l_stage := 'Stage 10 - Delete Program';

dbms_output.put_line('Delete programs if they exist');

if fnd_program.program_exists ('CARCLINV', 'CAR') then
   fnd_program.delete_program('CARCLINV', 'CAR');
end if;

----------------------------------------------------------------------------
-- Check if Executable exists and if so delete it
----------------------------------------------------------------------------
l_stage := 'Stage 20 - Delete Executable';

dbms_output.put_line('Delete Executables if they exist');

if fnd_program.executable_exists ('CARCLINV', 'CAR') then
    fnd_program.delete_executable('CARCLINV', 'CAR');
end if;

----------------------------------------------------------------------------
-- Create Executable 
----------------------------------------------------------------------------
l_stage := 'Stage 30 - Create Executable';

dbms_output.put_line('Create Executeable');

fnd_program.executable
    (executable                     => 'CARCLINV',
     application                    => 'CAR',
     short_name                     => 'CARCLINV',
     description                    => 'Supplier Rebate Contribution Invoices',
     execution_method               => 'PL/SQL Stored Procedure',
     execution_file_name            => 'car_claims_invoice_extract_pkg.carclinvmain');

-----------------------------------------------------------------------------
-- Create Program
-----------------------------------------------------------------------------
l_stage := 'Stage 40 - Create Program';

dbms_output.put_line('Create Program');

fnd_program.register
    (program                        => 'Supplier Rebate Contribution Invoices',
     application                    => 'CAR',
     enabled                        => 'Y',
     short_name                     => 'CARCLINV',
     description                    => 'Supplier Rebate Contribution Invoices',
     executable_short_name          => 'CARCLINV',
     executable_application         => 'CAR',
     execution_options              => NULL,
     priority                       => NULL,
     save_output                    => 'Y',
     output_type                    => 'TEXT',
     use_in_srs                     => 'Y',
     allow_disabled_values          => 'N',
     nls_compliant                  => 'N');

-----------------------------------------------------------------------------
--  Create Parameters
-----------------------------------------------------------------------------
fnd_program.delete_parameter('CARCLINV', 'CAR','p_file_name');

fnd_program.parameter
    (program_short_name             => 'CARCLINV',
     application                    => 'CAR',
     sequence                       => 10,
     parameter                      => 'p_file_name',
     description                    => 'Filename',
     enabled                        => 'Y',
     value_set                      => '10 Characters',
     default_type                   => null,
     default_value                  => null,
     required                       => 'Y',
     enable_security                => 'N',
     range                          => null,
     display                        => 'Y',
     display_size                   => 10,
     description_size               => 50,
     concatenated_description_size  => 25,
     prompt                         => 'File name'
    );

fnd_program.delete_parameter('CARCLINV', 'CAR','p_transaction_type');

fnd_program.parameter
    (program_short_name             => 'CARCLINV',
     application                    => 'CAR',
     sequence                       => 20,
     parameter                      => 'p_transaction_type',
     description                    => 'Transaction type',
     enabled                        => 'Y',
     value_set                      => 'CAR_TRAN_TYPES',
     default_type                   => null,
     default_value                  => null,
     required                       => 'Y',
     enable_security                => 'N',
     range                          => null,
     display                        => 'N',
     display_size                   => 20,
     description_size               => 50,
     concatenated_description_size  => 25,
     prompt                         => 'Transaction type'
    );
    
 commit;

 fnd_program.incompatibility
     (program_short_name   => 'CARCLINV',
      application          => 'CAR',
      inc_prog_short_name  => 'CARCLINV',
      inc_prog_application => 'CAR',
         scope                => 'Program',
     inc_type             => 'D');

--
-- Add to Request Group
--
l_stage := 'Stage 50 - Add to Request Grp ..';

if fnd_program.program_in_group('CARCLINV',
                                'CAR',
                                'LRL AR Interface Reports',
                                'AR')
then
   null;
else
   fnd_program.add_to_group
   ('CARRCALC',
    'CAR',
    'LRL AR Interface Reports',
    'AR');
end if;

if fnd_program.program_in_group('CARCLINV',
                                'CAR',
                                'LRL AR Manager Reports',
                                'AR')
then
   null;
else
   fnd_program.add_to_group
   ('CARCLINV',
    'CAR',
    'LRL AR Manager Reports',
    'AR');
end if;


commit;

dbms_output.put_line('-');
dbms_output.put_line('Procedure completed Successfully'||substr(fnd_program.message,1,200));

EXCEPTION
when others
then
   dbms_output.put_line('Error : '||l_stage||' '||substr(fnd_program.message,1,200));
   dbms_output.put_line('('||SQLERRM||')');
end;
/