Return string variable from SP as cursor? 1

[CraigBest]

Hi Folks

I have a SP that processes a series of values to see if they exist, and if they do not a string variable in the SP gets written to with the bad value(s).

I want to return this string variable as a single-value cursor at the end of the proc, but I can't figure out how to write the OPEN <cursor_name> FOR SELECT <varaible_name> FROM ... <?> Since there's no table to return a value from what do I put here?

Can someone light the way for me?

Thanks

CraigHartz

 

[Turkbear]

Hi,
Can't you set the string ( acutally a VARCHAR2, I imagine) variable as an OUT parameter for the SP?

http://www.exampledepot.com/egs/java.sql/CreateProcedureAndFunction.html

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[CraigBest]

I'm having trouble getting the procedure to work defining the return value as an out parameter, which is why I was trying to do it a different way. I am going back to try the out parameter again and see if I can figure out what I'm doing wrong.

Thanks for trying!

CraigHartz

 

[Dagon]

If you really want to use a cursor technique, there is always the SYS.DUAL table. That's a dummy table with a single row.

 

[CraigBest]

Hi Dagon

OK I'm trying to use the Dual table as you suggested, it should work - but I'm not returning anything. I think I'm still missing something. Maybe you can help me spot the problem? The code for the proc is below.

PROCEDURE Confirm_Members_Exist (
p_MemList IN VARCHAR2,
cur_bad_ids OUT t_cursor

)
IS
v_mem_test VARCHAR2(20);
v_ctr NUMBER;
v_bad_ids VARCHAR2(200);

cur_mem_num t_cursor;

BEGIN

v_bad_ids := ' ';
populate_tempspace(p_MemList);

OPEN cur_mem_num FOR
SELECT stringcol FROM temp_space;

LOOP

FETCH cur_mem_num INTO v_mem_test;
EXIT WHEN cur_mem_num%NOTFOUND;

-- first test to see if account # exists
SELECT COUNT(*) INTO v_ctr FROM MEMBER_RELATIONSHIPS WHERE mrel_id = TO_NUMBER(v_mem_test);
IF v_ctr = 0 THEN
v_bad_ids := v_bad_ids || v_mem_test || ', ';
END IF;

END LOOP;

CLOSE cur_mem_num;

OPEN cur_bad_ids FOR
SELECT v_bad_ids col_1 FROM DUAL;

END Confirm_Members_Exist;


Hope you can help, thanks

Craig

CraigHartz

 

[Dagon]

When you say you are getting nothing, how exactly are you returning the data ? Are you just running the stored proc and expecting it to output the data to the screen ? If so, you will be disappointed. All your procedure does is open a cursor - it doesn't fetch or display any data.

You will need to write some more PL/SQL to fetch data from the cursor and display it to the screen using something like dbms_output. Also, if there are no rows in temp_space when you execute the procedure, your output variable will be NULL. In that case, the cursor will still return a row, but what it returns will be NULL so it may look like you have no output.

 

[CraigBest]

Hi Dagon

No problems with the Temp_Space part, that is working just fine. Also when we debug the code I can see the first cursor is functioning as it should; values are being read and are being evaluated poperly, the bad IDs are being moved into the variable as they should be.

Where I'm having the problem is trying to create and return the second cursor. All I want to do is take what's in the variable and return it as a cursor. But I have not been able to figure it out. I know it isn't right but I'm as a loss on how to structure this. I've been going through the books I have and searching online but I'm not finding the answer.

Hope you can help, I have a deadline coming up! Thanks for anything you can offer.

Craig

CraigHartz

 

[CraigBest]

OK well I finally figured it out. It was blowing up because the name of the proc in oracle was different from the name being called by the .Net program, which was why I was not getting a cursor back. Stupid error which wasn't easy to check because our DAL usually isn't debuggable. Turns out the proc code is fine, works exactly as intended, including the Select from Dual cursor at the end.

Anyway thanks very much for the help! As frustrating as it was I'm glad it turned out to be something stupid, I was really second-guessing myself over this.

Craig

CraigHartz