Solved

Oracle Stored Procedure

Posted on 2013-01-30
7
526 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
Comment Utility
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
Comment Utility
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 34

Accepted Solution

by:
Mark Geerlings earned 500 total points
Comment Utility
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 1

Author Comment

by:montrof
Comment Utility
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 34

Expert Comment

by:Mark Geerlings
Comment Utility
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)
Comment Utility
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
Comment Utility
Thank you for the advice and assistance.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

772 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

11 Experts available now in Live!

Get 1:1 Help Now