?
Solved

Passing a var from one procedure to another for output

Posted on 2011-05-06
10
Medium Priority
?
390 Views
Last Modified: 2013-12-18
I have a store procedure to which I would like to avoid adding an out param, but need to get some info out of it. Is there any way in the world to pass a variable set in that stored procedure to another stored procedure for output? Example or hints welcome.
0
Comment
Question by:mjacobs2929
  • 5
  • 3
9 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 35705885
use package variables, system_contexts, or write to table

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35705895
if the two procedures have a relationship where they need to share some data,  then they should probably be in a package anyway
0
 
LVL 7

Expert Comment

by:Piloute
ID: 35706789
Hi,

Well, here's a hint : dbms_pipe

Cheers,
P
0
Independent Software Vendors: 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!

 

Author Comment

by:mjacobs2929
ID: 35718783
The procedure is already in a package. Adding an OUT param will work of course, but I would rather not add this to the procedure (atm it only has IN params).

The point of this question is to ask if there is another way to get a variable out of a procedure other than adding an OUT param to the procedure?

I assume a package var will still require adding an OUT param.

dbms_output is not a satisfactory solution, as I need to output this var to screen on a web page.

Cheers,
MJ
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 1000 total points
ID: 35719637
using a package variable doesn't require adding a parameter

something like this..,

create pacakge body my_package
is
g_some_value  integer;

procedure my_proc
is
begin
    g_some_value := 1234;
end;

procedure my_other_proc
is
begin
     dbms_output.put_line(to_char(g_some_value));
end;
end;

Open in new window


if you want the variable to be accessible outside of the package you could declare it in the package spec instead of the package body
or, better, create a function to expose it so it can't be modified outside of the package
0
 

Author Comment

by:mjacobs2929
ID: 35817927
Please close this question.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35817959
please select the answer (or answers) that helped and split the points accordingly.


if no answers were accetable, please explain the deficiency
0
 

Author Closing Comment

by:mjacobs2929
ID: 35863587
The solution here did not really address the problem as stated. In the end I had  to deal with this problem by adding an out parameter, which is what I wanted to avoid.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35863606
did you actually try

a package variable?
or
system_contexts?
or
writing to table?


all of those were suggested in the first post, the package variable method was also demonstrated

none of them require out parameters

please explain which of those answers failed and how
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

839 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