Making a search bar manually WITHOUT interactive report. (Oracle APEX)

Question

I am trying to make an application completely manually without using any interactive reports or generated PL/SQL. Everything is working at the moment but I am stumped when it comes to the search bar and I can't find anything online to help me.

I have a classic report called 'Browse Job Vacancies' and a 'Search' button; I also have the 'C1_JOB_TITLE_ITEM' search bar and a page process called 'Search'.

In the process I have this code:

SELECT 
    JOB_CODE, 
    JOB_TITLE, 
    JOB_DESCRIPTION, 
    SITE_NAME, 
    EMAIL_ADDRESS, 
    TELEPHONE_NUMBER, 
    SALARY, 
    START_OF_PLACEMENT, 
    APPLICATION_METHOD, 
    APPLICATION_CLOSING_DATE
FROM JOB
WHERE upper(job_title) = upper(:C1_JOB_TITLE_ITEM);

I am getting this error:

ORA-06550: line 1, column 64: PLS-00428: an INTO clause is expected in this SELECT statement

So I created this code:

DECLARE temp_row char;
BEGIN
    SELECT 
        JOB_CODE, 
        JOB_TITLE, 
        JOB_DESCRIPTION, 
        SITE_NAME, 
        EMAIL_ADDRESS, 
        TELEPHONE_NUMBER, 
        SALARY, 
        START_OF_PLACEMENT, 
        APPLICATION_METHOD, 
        APPLICATION_CLOSING_DATE
    INTO temp_row
    FROM JOB
    WHERE job_title = :C1_JOB_TITLE_ITEM;
END;

Here I am getting this error:

ORA-06550: line 13, column 16: PL/SQL: ORA-00947: not enough values

I am completely stumped on what to do at this point so any help at all is greatly appreciated. Sorry if I'm not being detailed enough this is my first time writing in Stack Overflow.

This shouldn't be in a process. This should be the Source for the report that you're trying to display. Since you don't want an interactive report, i'm guessing you want a classic report? — eaolson
Yes I'm using a classic report at the moment that shows all the current job vacancies and I want to use the search bar to select all jobs that have the same job title. — Top Lit

eaolson eaolson · Accepted Answer · 2018-12-10T01:40:33

(Editing because someone didn't think this was an answer.)

You say you have a page process. You don't need any sort of process for this. You should have a Region with the following properties (assuming 18.x):

Type = Classic Report
Source > Location = Local Database
Source > Type = SQL Query
Source > SQL Query > your query from your first block above

Apex will run the query and create a report based on it. Processes are for PL/SQL blocks that do something like populating data or manipulating the database in some way.

When you enter a keyword in your item and click your button (the button action should be Submit Page), Apex will populate C1_JOB_TITLE_ITEM in session state with the user's value, then use that in the bind variable when it re-generates the page.

Making a search bar manually WITHOUT interactive report. (Oracle APEX)

2 Answers