Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Return a variable value with ORACLE Stored Procedure Select statement

Posted on 2011-02-24
14
Medium Priority
?
607 Views
Last Modified: 2012-05-11
I am trying to return the variable value of Insured in the attached ORACLE stored procedure code to my VB6 application.  I have been moving around the code I have and trying to incorporate a fetch statement into the mix, but I just can't seem to assign the value of CLIENT_NAME in the select statement to the variable Insured.  Excuse me if I am mixing up terminology, I am a newbie to both Oracle and VB6.  Can someone please show me the code of how this would work within my ORACLE stored procedure that I have attached?
PROCEDURE spBCT_BPOFileIndexLookup(PolicyNumber IN varchar2,  rs_Agency_Info OUT rs_Info_Type) is

    /*Variables*/
    /*internal:*/
    v_Add_1 CLIENT_NAMES.CLIENT_NAME%TYPE;

    /*output:*/
    Insured varchar2(200);

    BEGIN
        
    OPEN rs_Agency_Info for Select CLIENT_NAME into Insured from CLIENT_NAMES where CLIENT_NUMBER = PolicyNumber;
    

END spBCT_BPOFileIndexLookup;

Open in new window

0
Comment
Question by:jamesdean666
[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
  • 6
  • 6
  • 2
14 Comments
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 34975938
what is the data type of this "rs_Info_Type" ?
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 34975941

try the below :

PROCEDURE spBCT_BPOFileIndexLookup(PolicyNumber IN varchar2,  rs_Agency_Info OUT rs_Info_Type) is

    /*Variables*/
    /*internal:*/
    v_Add_1 CLIENT_NAMES.CLIENT_NAME%TYPE;

    /*output:*/
    Insured varchar2(200);

    BEGIN
       
    Select CLIENT_NAME into Insured from CLIENT_NAMES where CLIENT_NUMBER = PolicyNumber;

   exception when no_data_found then
      insured := 'no data from the select statement';

END spBCT_BPOFileIndexLookup;
0
 

Author Comment

by:jamesdean666
ID: 34975950
TYPE rs_Info_Type IS REF CURSOR;
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 34975963
i think a mix up there.. not sure what ref cursor has to do with the select statement returning value into a variable.
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 34976046
did you try the code i had given ?
0
 

Author Comment

by:jamesdean666
ID: 34976140
working on it.. my vb6 code doesn't like it.. give me just a few please.. this stuff is new to me.. :)
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 34976177
no worries. take your time. i asked that because i was not sure whether you had seen the update or not.
0
 

Author Comment

by:jamesdean666
ID: 34976207
this code doesn't seem to work.. i don't get any rows returned when I run it with valid criteria
0
 

Author Comment

by:jamesdean666
ID: 34976237
is there an alternate way to utilize the original code i sent with a cursor to do the same thing?  My code is set up that way that I am maintaining?
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 34976930
"this code doesn't seem to work.. i don't get any rows returned when I run it with valid criteria
" ---> what is the error you are getting ?

What is the value of the PolicyNumber which is being passed to this procedure ?

Can you run this query directly in the database to check whether we have records for that criteria after replacing the variable PolicyNumber with the actual value being passed.

Select CLIENT_NAME into Insured from CLIENT_NAMES where CLIENT_NUMBER = ?????

Thanks
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34979161
I agree with nav_kun_v.  You should return what you need.  Why return a ref cursor when you only need one value?

A slight tweak to his code in http:#a34975941

You need to set the OUT parameter to a varchar2.

Is CLIENT_NUMBER a varchar2 or a number in the database?
PROCEDURE spBCT_BPOFileIndexLookup(PolicyNumber IN varchar2,  Insured OUT varchar2) is
    BEGIN
        
    Select CLIENT_NAME into Insured from CLIENT_NAMES where CLIENT_NUMBER = PolicyNumber;

   exception when no_data_found then
      insured := 'no data from the select statement';

END spBCT_BPOFileIndexLookup;

Open in new window

0
 

Author Comment

by:jamesdean666
ID: 34982060
I realize this isn't a VB6 area.. :)

The code I am using for VB6 is this:
     strSQL = "{call BCTSCAN.spBCT_BPOFileIndexLookup ('1007860')}"
       
    End If
    q1.Open strSQL, cnMDB
   
    If Not q1.EOF Then
        tbInsured.Text = CR(q1!Insured, 2)

So.. I am trying to convert the value CLIENT_NAME in my SELECT statement to 'Insured' as required by the above VB6 code above.  When I try to use the 'non-cursor' route, the call doesn't work..  When I use the cursor, it return the 'name' CLIENT_NAME.

I am trying to put this all together.
   
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 34982156
I'm not a VB6 person.  'How' to call the stored procedure should probably be a separate question in the appropriate zones.  

We can help with how to get return values in and out of Oracle code.

Now that I think about it, if you only want to return a single value forget about a procedure and make it an Oracle function.
create or replace function spBCT_BPOFileIndexLookup(PolicyNumber IN varchar2) return varchar2 is
	insured varchar2(100);
BEGIN
        
    Select CLIENT_NAME into Insured from CLIENT_NAMES where CLIENT_NUMBER = PolicyNumber;
    return Insured;

   exception when no_data_found then
      return 'no data from the select statement';

END spBCT_BPOFileIndexLookup; 
/

Open in new window

0
 

Author Closing Comment

by:jamesdean666
ID: 36182573
Sorry for the tardy response.. I ended up going with the Function approach.
0

Featured Post

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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 information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

604 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