?
Solved

How to use parameters in oracle stored procedure or query

Posted on 2012-04-03
11
Medium Priority
?
544 Views
Last Modified: 2012-04-03
Hi!

I have created a stored procedure in MS SQL Server which I call in crystal report. Now, I need to make a smilar stored procedure (or any other possible solution) in Oracle where I can use parameters like below. Although, I have looked a some pages, no of them seem to work.

This is how my code looks like in SQL Server:

CREATE PROCEDURE [dbo].[ej_sokt]
 @periodval varchar(10),
 @periodval2 varchar(10)
 AS
SELECT SKOLKOMMUN, 
SKOLA, KLASS,
[KJ1].[KJ1].[KJ0P0ANS].PERIOD, 
[KJ1].[KJ1].[KJ0P0ANS].PERSONNR,
[KJ1].[KJ1].[KJ0P0VAL].SOKT_KOMMUN 
FROM  [KJ1].[KJ1].[KJ0P0ANS] 
LEFT OUTER JOIN [KJ1].[KJ1].[KJ0P0VAL] ON [KJ1].[KJ1].[KJ0P0ANS].PERSONNR = 
[KJ1].[KJ1].[KJ0P0VAL].PERSONNR 
AND [KJ1].[KJ1].[KJ0P0ANS].PERIOD = [KJ1].[KJ1].[KJ0P0VAL].PERIOD 
AND [KJ1].[KJ1].[KJ0P0VAL].PERIOD =@periodval AND VALNR ='1'
WHERE [KJ1].[KJ1].[KJ0P0ANS].PERIOD =@periodval2
ORDER BY SKOLKOMMUN, SKOLA, KLASS
GO

Open in new window


I´d be delighted if I could get a code exampe but a reference to a really good page showing how to work with simple parameters in oracle would also do.

/Marcus
0
Comment
Question by:marcgu
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1
11 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1360 total points
ID: 37800855
In Oracle the selected results needs to go into something.

What are you doing with the results?

The basic syntax goes something like the code below but it won't compile until we do something with the results.

CREATE or replace PROCEDURE ej_sokt(inperiodval in varchar(10), inperiodval2 in varchar(10))
is
begin

SELECT SKOLKOMMUN, 
SKOLA, KLASS,
KJ1.KJ1.KJ0P0ANS.PERIOD, 
KJ1.KJ1.KJ0P0ANS.PERSONNR,
KJ1.KJ1.KJ0P0VAL.SOKT_KOMMUN 
FROM  KJ1.KJ1.KJ0P0ANS 
LEFT OUTER JOIN KJ1.KJ1.KJ0P0VAL ON KJ1.KJ1.KJ0P0ANS.PERSONNR = 
KJ1.KJ1.KJ0P0VAL.PERSONNR 
AND KJ1.KJ1.KJ0P0ANS.PERIOD = KJ1.KJ1.KJ0P0VAL.PERIOD 
AND KJ1.KJ1.KJ0P0VAL.PERIOD =inperiodval AND VALNR ='1'
WHERE KJ1.KJ1.KJ0P0ANS.PERIOD =inperiodval2
ORDER BY SKOLKOMMUN, SKOLA, KLASS;

end;
/

Open in new window

0
 

Author Comment

by:marcgu
ID: 37800892
Hi!

Thanks for your comment,

To your question: What are you doing with the results? In SQL server I create a stored procedure which I can use when I e.g.  create reports in Crystal Reports.

I just need to be able to run the query/procedure whenever I want with different parameters and be able to display the result or use the result in a report. (Please, ask me again if this is unclear)

Best regards

Marcus
stored-procedure.jpg
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 1360 total points
ID: 37800905
I'm not a Crystal reports person but I believe it can handle an Oracle cursor.

Just add the cursor as an out parameter.

Something like:

CREATE or replace PROCEDURE ej_sokt(inperiodval in varchar(10), inperiodval2 in varchar(10), outresults out sys_refcursor)
is
begin

open outresults for
SELECT SKOLKOMMUN, 
SKOLA, KLASS,
KJ1.KJ1.KJ0P0ANS.PERIOD, 
KJ1.KJ1.KJ0P0ANS.PERSONNR,
KJ1.KJ1.KJ0P0VAL.SOKT_KOMMUN 
FROM  KJ1.KJ1.KJ0P0ANS 
LEFT OUTER JOIN KJ1.KJ1.KJ0P0VAL ON KJ1.KJ1.KJ0P0ANS.PERSONNR = 
KJ1.KJ1.KJ0P0VAL.PERSONNR 
AND KJ1.KJ1.KJ0P0ANS.PERIOD = KJ1.KJ1.KJ0P0VAL.PERIOD 
AND KJ1.KJ1.KJ0P0VAL.PERIOD =inperiodval AND VALNR ='1'
WHERE KJ1.KJ1.KJ0P0ANS.PERIOD =inperiodval2
ORDER BY SKOLKOMMUN, SKOLA, KLASS;

end;
/

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 1360 total points
ID: 37800924
0
 
LVL 101

Assisted Solution

by:mlmcc
mlmcc earned 80 total points
ID: 37800926
0
 

Author Comment

by:marcgu
ID: 37801728
Hi!
Thanks for all comments. I will surely read them with care.

 Just one last thing though. As I mentioned, Crystal is just one area where I want to use this.

Is there no easy way to run the procedure in sql developer just like I run it MS server management studio to display more than 100 rows the result.In MS Server management studio, I can just right click on the procedure, then add the parameters click ok, then view the result.

When I try to do like in attached file, I only get 100 rows.
execute-stored-procedure.pdf
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37801784
Sorry, not a sql developer person.

You might want to ask a new question on how to get more than 100 rows from sql delveloper when returning a ref cursor from a stored procedure.
0
 
LVL 20

Expert Comment

by:gatorvip
ID: 37802729
>>to display more than 100 rows the result.

I think this is what you're looking for. In SQL Developer: Tools / Preferences / Advanced / Sql Array Fetch Size (max 500) - set this to whatever you want.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 37802796
What tool are you using to run the procedure?

mlmcc
0
 

Author Comment

by:marcgu
ID: 37803032
Sql developer 3
0

Featured Post

Introducing Priority Question

Increase expert visibility of your issues by participating in Priority Question, our latest feature for Premium and Team Account holders. Adjust the priority of your question to get emergent issues in front of subject-matter experts for help when you need it most.

Question has a verified solution.

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses

770 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