How to use parameters in oracle stored procedure or query

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
marcguAsked:
Who is Participating?
 
slightwv (䄆 Netminder) Commented:
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
 
marcguAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
slightwv (䄆 Netminder) Commented:
0
 
marcguAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
gatorvipCommented:
>>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
 
mlmccCommented:
What tool are you using to run the procedure?

mlmcc
0
 
marcguAuthor Commented:
Sql developer 3
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.