Building a SELECT result into a variable?

[tpbjr]

Hi again everyone,

This is kind of the second part to my chr(10) issue.

I have a process that attempts to INSERT but when throws an exception (DUP_VAL_ON_INDEX) I need to detect that a related child record(s) exists. If a child record exists then I have to delete the child record(s) but first I want to capture the records for logging purposes. It is the capture portion that I am trying to impliment under the DUP_VAL_ON_INDEX exception. I do not think this will work because I think the result must be one record but I am not 100% sure. In addition, I am trying to avoid build a CURSOR at the EXCEPTION block. This PL/SQL stuff is relative new/old for me. Meaning I only dig into this stuff once or twice a year so I appreciate your help.

This is what I think may or may not work.

SELECT trans_no || ', ' || plant || ', ' || trans_dt || ', ' || fiscal_yr||chr(10)
INTO SelMsg
FROM other_journal
WHERE ocd_trans_no = gv_ocd_trans_no

The result would look like this

TRANS_NO||','||PLANT||','||TRANS_DT||','||FISCAL_YR||CHR(10)
------------------------------------------------------------------------------------------------------------------------------------
124481, 001, 02/10/01, 2001
124482, 500, 02/10/01, 2001
124483, 001, 02/10/01, 2001

If I can get this result into a variable then I can send it to the log via DBMS_OUTPUT.PUT_LINE.

At least that is my plan.

Any ideas how to do this without a CURSOR build. To be honest, I am not sure if I can do a CURSOR build at the EXCEPTION block.

Thank you very much for your help.

Thank you for all your help

http://www.besware.com

Tom

 

[Dagon]

There is nothing to stop you having a cursor in the exception handler and I think this would be the safest approach. You don't know how many records there are going to be and you would need to output them one at a time. If you are fairly sure there aren't going to be that many, you could bulk collect them into a PL/SQL table:

DECLARE
   TYPE NumTab IS TABLE OF emp.empno%TYPE;
   TYPE NameTab IS TABLE OF emp.ename%TYPE;
   enums NumTab;  -- no need to initialize
   names NameTab;
BEGIN
...
EXCEPTION WHEN DUP_VAL_ON_INDEX THEN
   SELECT empno, ename BULK COLLECT INTO enums, names FROM emp;
   ...
END;

 

[tpbjr]

Hi,

I am not following your code!
DECLARE
TYPE NumTab IS TABLE OF emp.empno%TYPE;
TYPE NameTab IS TABLE OF emp.ename%TYPE;
enums NumTab; -- no need to initialize
names NameTab;
BEGIN
...
EXCEPTION WHEN DUP_VAL_ON_INDEX THEN
SELECT empno, ename BULK COLLECT INTO enums, names FROM emp;
...
END;


It looks like NumTab and NameTab are separate fields?
I am not sure if I understand how I am going to be able to display this data in the format that I described and shown in the previous post? Can you provide an example. My results would at most have 4 records. I need store the results into a varchar() variable.

Thanks for your support.

Thank you for all your help

http://www.besware.com

Tom

 

[SantaMufasa]

If I can get this result into a variable then I can send it to the log via DBMS_OUTPUT.PUT_LINE

If all you are trying to do is to output the results of the EXECEPTION-handler's SELECT, the you can use this variation of Dagon's suggestion:

DECLARE
   (no special DECLARE entries)
BEGIN
...
EXCEPTION WHEN DUP_VAL_ON_INDEX THEN
   for x in (SELECT empno, ename FROM emp
              WHERE <some condition for desired rows>) loop
       dbms_output.put_line('<error text of some sort>'
            ||x.empno||':'||ename);
   end loop;
END;

...But perhaps I missed something in translation.

Let us know if this provides any useful insight.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[tpbjr]

Ok, now I am really confused.

I have a table OTHER_JOURNAL (not a cursor) that I want to read in PL/SQL code. I am trying to not do a loop type thing.
At most it will return 4 records based on my WHERE clause.

I would have to do define all fields of my table like this...
DECLARE
TYPE Trans_noTAB IS TABLE OF other_journal.trans_no%TYPE;
TYPE PlantTAB IS TABLE OF other_journal.plant%TYPE;
TYPE Trans_DTTAB IS TABLE OF other_journal.trans_dt%TYPE;
OJ_Trans_no Trans_noTAB;
OJ_Plant PlantTAB;
OJ_Trans_DT Trans_DTTAB;
BEGIN
EXCEPTION WHEN DUP_VAL_ON_INDEX THEN
SELECT trans_no, plant, trans_dt BULK COLLECT INTO OJ_Trans_no, OJ_Plant, OJ_Trans_DT
FROM other_journal
WHERE trans_no = gv_trans_no;
--AT THIS POINT I DO NOT SEE HOW I AM GETTING THE RESULTS
--INTO A VARIABLE CALLED myVariable, FOR EXAMPLE.
--THEN I USE THE VARIABLE IN THE
-- DBMS_OUTPUT.PUT_LINE(myVariable) COMMAND.
-- THEN I CAN USE THAT SAME VARIABLE DOWN STREAM IN MY CODE.

END;


What am I not understanding?


Thank you for all your help

http://www.besware.com

Tom

 

[Dagon]

You will have to use a loop to read the results, as they are in a PL/SQL table. However, you could concatenate them into a single variable for use by dbms_output.

 

[SantaMufasa]

AT THIS POINT I DO NOT SEE HOW I AM GETTING THE RESULTS INTO A VARIABLE CALLED myVariable

This situation illustrates the "fun" we have on a text-based forum versus our standing at your desk, explaining this stuff...so much disappears during text-only "translation".


So, now I am confused...what is special about "myVariable" and why can you not just say:

...
    myVariable := <expression>;
...

...if you want to get "THE RESULTS INTO A VARIABLE CALLED myVariable"?

Also, what is the downside of using an implicit cursor in a "CURSOR FOR LOOP"?...In this case, it seems that it would save your creating your entire DECLARE section.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[tpbjr]

I guess I am so not in the loop with PL/SQL and I have results that I have to produce, I am trying to anything and getting nowhere.

Then let me ask you this.

This is the format that I have in my script.

DECLARE
...
variables declared here
...
Initial main input CURSOR here
...
several subroutines here
...
BEGIN
OPEN ft_cursor
LOOP
FETCH ft_cursor INTO ...bla, bla bla

EXIT WHEN ft_cursor%NOTFOUND
BEGIN
INSERT INTO ....

EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
ok finally this is where I am
where and how do I declare a cursor at here
with out getting errors. I cannot declare
it at the top with the INPUT cursor because
it is based off of the reading of the INPUT cursor. Other things I do not know are such as as I loop through the INPUT cursor and declare my dynamic cursor from data from the INPUT cursor, I would think that I would have to close it or wipe it out each time. Again I just do not know enough about PL/SQL yet I am expected to get some results fast.

Thanks for any input you can give.


Thank you for all your help

http://www.besware.com

Tom

 

[Dagon]

You probably need something like:

declare
cursor c_main_curs is
select ...
from table
where column;

begin
  for rec_main in c_main_curs loop
    begin
      .. process records as normal
    exception when dup_val_on_index then
      declare
        cursor c_err_curs (p_input in varchar2) is
          select column1
           from table
        where column = p_input;
        v_output_variable varchar2(200) := null;
      begin
        for rec_err in c_err_curs(rec_main.key) loop
          v_output_var := v_output_var||rec_err.column1;
        end loop;
        dbms_output.put_line('Recs are: '||v_output_var);
      end;
    end;
  end loop;
end;

exception

 

[tpbjr]

Ok, I figured it out with the help of all of you. Sorry Dagon, I did not see your last suggestion until I come up with what is below. The code below is inside of my first cursor LOOP. The initial unit test appears to have passed.
Thanks again.


-- build message with all records that are about to be deleted
DECLARE
OJ_trans_no OTHER_JOURNAL.TRANS_NO%TYPE;
OJ_plant OTHER_JOURNAL.PLANT%TYPE;

CURSOR OJ_cursor IS
SELECT trans_no, plant
FROM OTHER_JOURNAL
WHERE ocd_trans_no = gv_OCD_trans_no;

BEGIN
gv_msg_build2 := 'DELETED THE FOLLOWING OTHER_JOURNAL RECORDS:' || chr(10);
OPEN OJ_cursor;
LOOP
FETCH OJ_cursor INTO OJ_trans_no, OJ_plant;
gv_msg_build2 := gv_msg_build2 || OJ_trans_no || ', ' || OJ_plant || chr(10);
EXIT WHEN OJ_cursor%NOTFOUND;
END LOOP;
CLOSE OJ_cursor;
END;

DBMS_OUTPUT.PUT_LINE(gv_msg_build2);
gv_msg_build1 := gv_msg_build1 || gv_msg_build2 || chr(10);


Thank you for all your help

http://www.besware.com

Tom