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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.