Solved

How to use parameters in oracle stored procedure or query

Posted on 2012-04-03
11
520 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
  • 4
  • 3
  • 2
  • +1
11 Comments
 
LVL 76

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 76

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

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 76

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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

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…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

776 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