Return a variable value with ORACLE Stored Procedure Select statement

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

jamesdean666Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
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
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
what is the data type of this "rs_Info_Type" ?
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:

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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
jamesdean666Author Commented:
TYPE rs_Info_Type IS REF CURSOR;
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
i think a mix up there.. not sure what ref cursor has to do with the select statement returning value into a variable.
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
did you try the code i had given ?
0
 
jamesdean666Author Commented:
working on it.. my vb6 code doesn't like it.. give me just a few please.. this stuff is new to me.. :)
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
no worries. take your time. i asked that because i was not sure whether you had seen the update or not.
0
 
jamesdean666Author Commented:
this code doesn't seem to work.. i don't get any rows returned when I run it with valid criteria
0
 
jamesdean666Author Commented:
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
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
"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
 
slightwv (䄆 Netminder) Commented:
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
 
jamesdean666Author Commented:
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
 
jamesdean666Author Commented:
Sorry for the tardy response.. I ended up going with the Function approach.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.