CODEWITHSUNDEEP
sqloracle-databaseplsqldynamic-sql
nick gowdy
nick gowdy

Reputation: 6531

using dynamic sql to create column for select statement

I'm writing a stored procedure for paginated results and this result can be ordered by certain values. I did have a switch case in a select statement but because it was trying to do an orderby on rownum it was very slow.

Now I am trying to use dyanmic sql to build the query outside the select but I don't know if what I am doing is possible.

Here is my SQL in Oracle SQL Developer:

create or replace PROCEDURE Sp_tsa_trainees_pagination (
schemeid IN INT,
searchval IN VARCHAR2,
pagesize IN INT DEFAULT 20,
currentpage IN INT DEFAULT 1,
--orderby IN VARCHAR2,
cursor_  OUT SYS_REFCURSOR)

AS
-- LOCAL VARIABLES
totalcount INT;
numberofpages INT;
startposition NUMBER;
endposition NUMBER;
orderby VARCHAR2(100) := 'surname asc' ;
dynamic_query VARCHAR(255) := 'row_number() over (order by t.SURNAME DESC, t.FORENAMES DESC) AS rnum';
BEGIN

-- Get total number of trainees in scheme
select COUNT(t.ORG_REGISTRATION_ID) 
into totalcount FROM v_trainee t 
where t.ORG_REGISTRATION_ID = schemeid 
AND t.status = 'A' and LOWER(t.trainee_name) like '%' || LOWER(searchval) || '%';

  -- calculate number of pages in the pagination by dividing total number of records by how many to display for each page
  numberofpages := totalcount / pagesize;

  -- get start position by multiplying number of records to display for each page by current page
  startposition := pagesize *( currentpage-1);

  -- add calculated start position by number of records to display to get end position
  endposition := startposition + pagesize;

  CASE orderby 
    WHEN 'surname desc' THEN dynamic_query := 'row_number() over (order by t.SURNAME DESC, t.FORENAMES DESC) AS rnum';
    WHEN 'surname asc' THEN dynamic_query := 'row_number() over (order by t.SURNAME ASC, t.FORENAMES ASC) AS rnum';
END CASE;


    OPEN cursor_ FOR
    Select * from 
(
SELECT 
-- order by based on selection

dynamic_query rnum,

t.ORG_REGISTRATION_ID SearchId,
    t.FORENAMES Forenames,
    t.FORENAME Forename,
    t.SURNAME Surname,
    t.person_id PersonId,
    t.trainee_name TraineeName,
    t.STATUS Status,
    t.IPD_ANNUAL_REVIEW_DATE AnnualReviewDate,
    t.ANNUAL_REVIEW_STATUS AnnualReviewStatus,
    t.payment_received PaymentRecieved,
    t.TRAINEE_ID TraineeId,
    t.IPD_SIGNUP_DATE IpdSignupDate,
    t.START_DATE StartDate,
    t.END_DATE EndDate,
    t.LENGTH_ON_SCHEME LengthOnScheme,
    t.EMPLOYEE_NUMBER EmploymentNumber,
    t.SELECTED_LEVEL SelectedLevel,
    t.SELECTED_LEVEL_DESCRIPTION SelectedLevelDescription,
    t.ELIGIBLE_LEVEL EligibleLevel,
    t.ELIGIBLE_LEVEL_DESCRIPTION EligibleLevelDescription,
    sce.FORENAMES SceForenames,
    sce.FORENAME SceForename,
    sce.SURNAME SceSurname,
    sce.mentor_name SceName,
    sce.EMPLOYEE_NUMBER SceEmployeeNumber,
    de.FORENAMES DeForenames,
    de.FORENAME DeForename,
    de.SURNAME DeSurname,
    de.mentor_name DeName,
    de.EMPLOYEE_NUMBER DeEmployeeNumber,
    t.COMPLETED_ATTRIBUTE_LEVELS CompletedAttributeLevels,
    t.ATTRIBUTE_LEVEL_COUNT AttributeLevelCount,

    -- get percentage
    CASE t.ATTRIBUTE_LEVEL_COUNT
    WHEN 0 THEN 0
    ELSE
    COMPLETED_ATTRIBUTE_LEVELS / t.ATTRIBUTE_LEVEL_COUNT * 100
    END percentage,

    DECODE(F_ISTRAINEEGROUPMEMBER(t.ORG_REGISTRATION_ID, 'S', t.person_id),'Y','N','Y') WithoutTsaGroup,
    orr.status SchemeStatus,
    (select count(*) from TRAINING_GROUP_TRAINEE tgt where tgt.trainee_id = t.TRAINEE_ID) NUMBER_OF_GROUPS,
    TotalCount
  FROM v_trainee t
  INNER JOIN org_registration orr ON t.ORG_REGISTRATION_ID = orr.id
  LEFT OUTER JOIN v_mentor sce    ON t.sce_id = sce.MENTOR_ID
  LEFT OUTER JOIN v_mentor de     ON t.de_id = de.MENTOR_ID
  where t.ORG_REGISTRATION_ID = schemeid  AND t.status = 'A' 
  and LOWER(t.trainee_name) like '%' || LOWER(searchval) || '%'
  )
  where rnum >= startposition and rnum <= endposition;

END;

I want to use this variable with the assigned sql:

dynamic_query rnum,

But when I execute the stored procedure I get this error:

ORA-01722: invalid number ORA-06512: at "db.SP_TSA_TRAINEES_PAGINATION", line 46 ORA-06512: at line 13

So basically my question is can I assign a SQL to VARCHAR2 and then use it in a select statement dynamically.

Upvotes: 1

Views: 427

Answers (1)

Aleksej
Aleksej

Reputation: 22969

You may need dynamic SQL for this. For example:

create or replace procedure testDyn(n in number, C OUT SYS_REFCURSOR) is
    vDynamicPart varchar2(1000);
    vSQl         varchar2(1000);
begin
    --
    if  (n = 1) then
        vDynamicPart := 'count(1)';
    else
        vDynamicPart := 'count(null)';
    end if;
    --
    vSQl := 'select ' || vDynamicPart || ' from dual';
    open C for vSQl;
end;

If you call it

declare
    n1 number;
    n2 number;
    C1 SYS_REFCURSOR;
    C2 SYS_REFCURSOR;
begin
    testDyn(1, C1);
    testDyn(2, C2);

    fetch C1 into n1;
    fetch C2 into n2;

    dbms_output.put_line('n1: ' || n1);
    dbms_output.put_line('n2: ' || n2);
end;

you get:

n1: 1
n2: 0

Upvotes: 1

Related Questions