PL/SQL select multiple varchar2 into one varchar2

Question

i'm a beginner at pl-sql and i am trying to write a code of a function to read in a coursename and display the lecturerName, coursename, and the title for which matches with the coursename.

However i am unable to get a decent output disregarding the different methods which i've tried, and i have gotten quite a few different error, currently below is the code that is able to compile but does not give any results.

Can anyone help me with this function and tell me where did i go wrong?.

Set echo on

set serveroutput on

CREATE OR REPLACE FUNCTION Courses(coursename IN VARCHAR2) RETURN VARCHAR2

IS
    results VARCHAR2(100);
    l VARCHAR2(30);
    c VARCHAR2(30);
    t VARCHAR2(30);

BEGIN

    FOR course IN(select lecturerName, coursename, title into l,c,t  from course where Coursename = coursename)

LOOP
    results := results || l || c || t;
END LOOP;


RETURN results;

END Courses;
/

SELECT Courses('SQL') from dual;


Courses('SQL')

You're mixing two different methods for calling SQL from PL/SQL: you either use `INTO`, or you use the `FOR c IN (sql) LOOP...`, not both. — Jeffrey Kemp, Nov 13 '14 at 09:47
You code has a mayor bug `where Coursename = coursename` is always true for any record, i.e. no matter what your input is you will always get full table selected. Use different names for column and variables. — Wernfried Domscheit, Nov 13 '14 at 10:06

score 1 · Answer 1 · answered Nov 13 '14 at 10:00

If you want the function to return all matches based on the course name, then the result will be similar to a table. I'm guessing you don't want to return 1 match, there may be more in your table. Also you may not want all the matches to be treated as one row, i.e. all of them merged together.

For this you need a pipelined function which returns a dataset, represented by a custom type.

First, create the TYPE:

CREATE OR REPLACE TYPE COURSEINFO as Table of VARCHAR2(512); --size can vary based on your needs

The function becomes:

CREATE OR REPLACE FUNCTION COURSES (
   p_coursename   IN VARCHAR2)
   RETURN COURSEINFO
   PIPELINED
IS
   text      VARCHAR2 (512);
BEGIN
   FOR course IN (SELECT lecturerName, coursename, title
                    FROM course
                   WHERE Coursename = p_coursename)
   LOOP
      text := course.lecturerName || ', ' || course.coursename || ', ' || course.title;

    PIPE ROW(text);
   END LOOP;

   RETURN;
END COURSES;

For input table:

course1 | title2 | lecturer3
course1 | title1 | lecturer1
course2 | title2 | lecturer2

and query:

select COLUMN_VALUE as COURSE_INFO from table(COURSES('course1'));

Produces the output:

lecturer1, course1, title1
lecturer3, course1, title2

Wernfried Domscheit · Answer 2 · 2014-11-13T10:04:12.163

0

It must be this one

FOR course IN (select lecturerName, coursename, title from course where Coursename = coursename) 

LOOP
    results := results || course.lecturerName || course.coursename || course.title;
END LOOP;

Or much more simple

select lecturerName||coursename||title||
from course 
where Coursename = 'SQL';

edited Nov 13 '14 at 10:04

answered Nov 13 '14 at 09:11

Wernfried Domscheit

54,457
9
76
110

PL/SQL select multiple varchar2 into one varchar2

2 Answers2