Solved

Oracle Stored Procedure

Posted on 2013-01-30
7
542 Views
Last Modified: 2013-01-31
I am new to writing stored procedures in oracle and was looking for someone to help me.  I have a process that I have to run every month and the only thing that changes is one table (ie AdHoc111).  So what I wanted to do is pass the paramater as the table name and then have the results  put into 6 different tables.   So all i would have to do is execute sp (Adhoc111).  Also each time it was run it would clear the results tables.  I am attaching the steps I am using currently. Any Help would be great


Montrof
sp.sql
0
Comment
Question by:montrof
7 Comments
 
LVL 8

Expert Comment

by:mustaccio
ID: 38837269
If you run your script using SQL*Plus, I think the easiest would be to use a substitution variable. Replace all references to Adhoc111 with "&1" (without the quotation marks), which will be substituted by the first script parameter, e.g.

ALTER TABLE mmtrof.&1 ADD IsdeskLoyalty int;

Open in new window


Then you'll call your script thusly:

sqlplus user/pass@sid @sp.sql Adhoc111

Open in new window

0
 
LVL 1

Author Comment

by:montrof
ID: 38837294
I am sorry i am not familiar with this process I connect to oracle via toad and that is where I write and execute my scripts.
0
 
LVL 35

Accepted Solution

by:
Mark Geerlings earned 500 total points
ID: 38837296
It looks like you may be new to Oracle but familiar with SQL server.  Be aware that those two systems are very different!

Oracle stored procedures are optimized for the four basic SQL verbs: select, insert, update and delete.  Any other SQL verbs (create, truncate, drop, etc.) are DDL commands that are *NOT* directly supported in standard Oracle PL\SQL syntax.  These can be used in Oracle stored procedures inside the "execute immediate" command, but there is a performance penalty for these.

Also, temporary tables can be used in Oracle, but they are rarely needed in Oracle.  If you find a business problem that can be solved most easily by using a temporary table (I have seen a few of these) the temporary table should be created just once, outside of the procedure that will use it.  Then, when you write your procedure you simply use the "global temporary table" as you would use a normal table.  Oracle will make sure that the contents of the "global temporary table" are unique to your process, even if someone else runs the same procedure at about the same time.

I just noticed commands like this in your example:
UPDATE mmtrof.tmp_stage2 SET city = LTRIM(RTRIM(city));
UPDATE mmtrof.tmp_stage2 SET pid = LTRIM(RTRIM(pid));
UPDATE mmtrof.tmp_stage2 SET name = LTRIM(RTRIM(name));
UPDATE mmtrof.tmp_stage2 SET r_code = LTRIM(RTRIM(r_code));

That is a *VERY* inefficient way to get the job done in Oracle!  In Oracle it is *MUCH* more efficient to do that as a single update that sets the value of four different columns in one command like this:
UPDATE mmtrof.tmp_stage2 SET city = LTRIM(RTRIM(city)),
 pid = LTRIM(RTRIM(pid)),
 name = LTRIM(RTRIM(name)),
 r_code = LTRIM(RTRIM(r_code));

Depending on how many rows your table has and on whether these columns are likely to include leading or trailing blank values or not, it may be much more efficient to include a "where" clause to make sure the update doesn't waste time on rows that don't actually need to be changed, like this:

UPDATE mmtrof.tmp_stage2 SET city = LTRIM(RTRIM(city)),
 pid = LTRIM(RTRIM(pid)),
 name = LTRIM(RTRIM(name)),
 r_code = LTRIM(RTRIM(r_code))
where city <> LTRIM(RTRIM(city))
  or pid <> LTRIM(RTRIM(pid))
  or name <> LTRIM(RTRIM(name))
  or r_code <> LTRIM(RTRIM(r_code));


Now that I've given you some general tips for how to work with Oracle stored procedures, maybe you can re-write what you have to be closer to the way that Oracle works best.
0
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
LVL 1

Author Comment

by:montrof
ID: 38837316
Ok so will it be possible to make this into a stored procedure or am i wasting my time and should just run the scripts piece by piece and clean up the code
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 38837347
In Oracle, stored procedures are best for queries (select statements) and DML (insert, update or delete statements) that will be called or re-used repeatedly.  These stored procedures can easily accept bind variables so they can work on different records each time they are called.

Creating or dropping tables though is usually not best done via stored procedures in Oracle.  When those tasks need to be done (usualy rarely) they are best done in Oracle via *.SQL scripts that are then executed via SQL*Plus, TOAD, SQL Developer or a similar tool.

We can't decide for you what is best for you or your system.  I'm trying to help you understand how Oracle works most efficiently and most easily.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38837507
markgeer has provided excellent advice.

I just wanted to add on to this:
"CREATE global TEMPORARY TABLE mmtrof.TmpTbl_FCS"

In Oracle if you need a temporary table, you create these once.  Each session that inserts rows into these are the only ones that can access the data.

More about them is in the online docs:
http://docs.oracle.com/cd/E11882_01/server.112/e25494/tables003.htm#ADMIN11633

Now about what this is doing that you think needs to be a procedure:
At the end of your script, you do a lot of selects.  What are you seeing as the output of the procedure?

You are also altering tables to add columns.  This is a once and forget type of operation.  Are you thinking this is part of the procedure?

>>We can't decide for you what is best for you or your system.

I second this statement.  We can help provide answers to questions to the best of our ability but we cannot learn your system to the level of detail necessary to say what will and will not work for you in the long run.
0
 
LVL 1

Author Closing Comment

by:montrof
ID: 38839235
Thank you for the advice and assistance.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
This video shows how to recover a database from a user managed backup
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

896 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now