Solved

How to use parameters in oracle stored procedure or query

Posted on 2012-04-03
11
535 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 340 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 340 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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 340 total points
ID: 37800924
0
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 20 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 100

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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

738 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