We help IT Professionals succeed at work.

Oracle (11gR2) select into pl/sql query

toooki
toooki asked
on
I am trying to assign the output of multiple pl/sql select queries to a variable in a pl/sql procedure. This works for a single select query:
mystring varchar2(200);
select field1 into mystring from mytab1;

But I wanted to concatenate the outputs of two separate sql queries into the variable:
In know this does not work:
mystring := (select field1 into mystring from mytab1) || ' ' || (select field2 into mystring from mytab2);

How could I write a single (if possible) sql statement to achieve the above?

I also have a second problem above. If the sql (in the later case concatenated o/p) returns null, I get no-data-found exception. How could I assign "mystring" to NULL in the event above sql(s) return null and it never raise exception?

Thank you.
Comment
Watch Question

Commented:
not sure your mytab1 has any relation to mytab2, if yes, probably u can connect them together by the Key.

mystring:= SELECT (NVL(A.FIELD1,'') || NVL(B.FIELD2,'')) INTO mystring FROM mytab1 A, mytab2 B WHRE A.KEY = B.KEY;

if both table got no relation, how r u going to concatenate them together?

Author

Commented:
Thank you.
mytab1 and mytab2 are unrelated.
But field1.mytab1 is a string. And field2.mytab2 is a string. So the concatenation should work....

I simplified the query in question. I am actually doing something like:
mystring := (select field1 into mystring from mytab1 where id = xx) || ' ' || (select field2 into mystring from mytab2 where id = xx);
//But still mytab1 and mytab2 are unrelated.
Thanks.
If there is no relation between mytab1 and mytab2, you could do either of following.

Option 1

SELECT field1 INTO tmpstring1 FROM mytab1;
SELECT field2 INTO tmpstring2 FROM mytab2;

mystring := tmpstring1 || tmpstring2;


Option 2

SELECT
  str
INTO
  mystring
FROM
(SELECT
  MAX(CASE WHEN src = 'tab1' THEN val ELSE '' END) || MAX(CASE WHEN src = 'tab2' THEN val ELSE '' END)
FROM
  (SELECT
    'tab1' AS src
    ,field1 AS val
  FROM
    mytab1
 
  UNION ALL

  SELECT
    'tab2' AS src
    ,field2 AS val
  FROM
    mytab2
  ) tab1_tab2
)
 
Sorry, small typo in Option 2 (missing the alias for the expression with MAX...), should be:


SELECT
  str
INTO
  mystring
FROM
(SELECT
  MAX(CASE WHEN src = 'tab1' THEN val ELSE '' END) || MAX(CASE WHEN src = 'tab2' THEN val ELSE '' END)  AS str
FROM
  (SELECT
    'tab1' AS src
    ,field1 AS val
  FROM
    mytab1
 
  UNION ALL

  SELECT
    'tab2' AS src
    ,field2 AS val
  FROM
    mytab2
  ) tab1_tab2
)
And you could extent Option 2 with a dummy value in the inner query to avoid the no data found error:

SELECT
  CASE WHEN LENGTH(str) = 0 THEN NULL ELSE str END
INTO
  mystring
FROM
(SELECT
  MAX(CASE WHEN src = 'tab1' THEN val ELSE '' END) || MAX(CASE WHEN src = 'tab2' THEN val ELSE '' END)  AS str
FROM
  (SELECT
    'tab1' AS src
    ,field1 AS val
  FROM
    mytab1
 
  UNION ALL

  SELECT
    'tab2' AS src
    ,field2 AS val
  FROM
    mytab2

  UNION ALL

  SELECT
    'dual' AS src
    ,'' AS val
  FROM
    dual

  ) tab1_tab2
)
try this and check if you are getting the expected result:

select  a.field1,b.field2 from mytab1 a, mytab2 b where a.id =b.id and a.id =xx ;
Sorry didn't saw tosse's post ...
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:
You should be able to use something almost like your first attempt:

select (select field1 into mystring from mytab1) || ' ' || (select field2 into mystring from mytab2)
into mystring from dual;
Qlemo: that syntax won't work, you can't do a "select into" in an inner query.
Here's a complete example that also avoids the no data found error (you can experiment with different values of object_id):


create or replace
PROCEDURE swc_tst_p IS

  mystring VARCHAR2(1024);

BEGIN


SELECT
  CASE WHEN LENGTH(str) = 0 THEN NULL ELSE str END
INTO
  mystring
FROM
(SELECT
  MAX(CASE WHEN src = 'tab1' THEN val ELSE '' END) || MAX(CASE WHEN src = 'tab2' THEN val ELSE '' END)  AS str
FROM
  (SELECT
    'tab1' AS src
    ,object_name AS val
  FROM
    all_objects
  WHERE
    object_id = 0
  
  UNION ALL

  SELECT
    'tab2' AS src
    ,object_name AS val
  FROM
    all_objects
  WHERE
    object_id = 1

  UNION ALL

  SELECT
    'dual' AS src
    ,'' AS val
  FROM
    dual

  ) tab1_tab2
);

END;

Open in new window

Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:
Sorry, cut & waste error. There should be no "into" in the inner queries:

select (select field1 from mytab1) || ' ' || (select field2 from mytab2) into mystring from dual;

Author

Commented:
Thanks a lot to all. The above queries are really helpful.

I finally need to insert the concatenated string to a table. So I simplified this way:

But I get a different problem:

insert into mylog (id, load_details)
values (1,
(select ((select load_message from mytab1 where id = 100)
|| chr(13) || chr(10) ||
(select load_message from mytab2 where id = 100)) from dual));

The above query seemed to have solved my issue. But I get this error with the above:
ORA01489: result of string concatenation is too long.

but the load_details field of mylog is a CLOB field. I made the field CLOB as I expected the content to exceed 4000 bytes (4000 characters in my database).

So just wonder if the above concat/insert is still doable...?
Also check this:
DECLARE
CURSOR C1 IS SELECT F1.ID,F2.T3 FROM F1,F2 WHERE F1.ID=F2.ID AND F1.ID =1;
TYPE COL_TYP IS TABLE OF C1%ROWTYPE INDEX BY PLS_INTEGER;
MYSTRING COL_TYP ;
BEGIN  
OPEN C1;
LOOP
FETCH C1 BULK COLLECT INTO MYSTRING ;
EXIT WHEN C1%NOTFOUND;
END LOOP;
CLOSE C1;

FOR i IN 1..MYSTRING.count
LOOP
DBMS_OUTPUT.PUT_LINE(      MYSTRING(i).ID
                   ||'-'||MYSTRING(i).T3
                   );
END LOOP;
end;

Author

Commented:
tosse's query is really helpful as I used that concept to help fix some other unrelated issue that I had..

Author

Commented:
slobaray, it seems a bit too complex for me. My apology...
But I think I could make the concatenation work:
as this works:

insert into mylog (id, load_details)
values (1,
(select ((select load_message from mytab1 where id = 100)
|| chr(13) || chr(10) ||
(select sysdate from dual)) from dual));

But now the problem is with the string length when it exceeds 4000 characters.

(select load_message from mytab1 where id = 100) --THis is about 3000K
(select load_message from mytab2 where id = 100) -- This is about 3000K

Author

Commented:
Option 1

SELECT field1 INTO tmpstring1 FROM mytab1;
SELECT field2 INTO tmpstring2 FROM mytab2;

mystring := tmpstring1 || tmpstring2;

The above gives the same error.
"Batchelor", Developer and EE Topic Advisor
Top Expert 2015
Commented:
You will need to explicitly convert one or both of the subresults to CLOB. If you use a var, it needs to be CLOB, too.
insert into mylog (id, load_details)
select 1, (select to_clob(load_message) from mytab1 where id = 100)
|| chr(13) || chr(10) ||
(select sysdate from dual) from dual;

Open in new window

You won't need the VALUES and another subselect, you can just select the "1" with the "main" select, as you can see. I left the subselect on dual for sysdate, though that is unnecessary, too. The shorter version would look like this:
insert into mylog (id, load_details)
select 1, (select to_clob(load_message) from mytab1 where id = 100)
|| chr(13) || chr(10) || sysdate from dual;

Open in new window

Author

Commented:
The above worked perfectly!!! Thank you.

Thank you all.