Solved

Return a variable value with ORACLE Stored Procedure Select statement

Posted on 2011-02-24
14
596 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
  • 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
 
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
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.

 

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 76

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 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 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

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.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Delphi selector screen 2 58
Number Format 1 45
levels for reporting 5 51
PL/SQL Search for multiple strings 5 22
Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

746 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now