Solved

Oracle Stored Procedure

Posted on 2013-01-30
7
559 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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 77

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

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
mysql database, schema and table creation 13 58
SubQuery link 4 36
MS SQL Update query with connected table data 3 41
Optimize the query 5 43
Creating and Managing Databases with phpMyAdmin in cPanel.
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

821 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