• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 593
  • Last Modified:

Oracle Stored Procedure

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
montrof
Asked:
montrof
1 Solution
 
mustaccioCommented:
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
 
montrofAuthor Commented:
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
 
Mark GeerlingsDatabase AdministratorCommented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
montrofAuthor Commented:
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
 
Mark GeerlingsDatabase AdministratorCommented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
montrofAuthor Commented:
Thank you for the advice and assistance.
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now