CODEWITHSUNDEEP
oracle-databaseplsqloracle-apex
Jordan Holmer
Jordan Holmer

Reputation: 75

Oracle APEX error ORA-01460 ORA-02063

I am creating a Classic Report (based on Function) with the following code:

declare
    q long;
begin
-- The query with any conditions always applied
    q := 'select * from name_selection_atnd where 1 = 1';

-- If all items are null then kill query
if  :P500_FN is null 
    and :P500_LN is null
then
    q := q || ' and name_said = 0'; --will always return 0 rows
end if;

-- Append any optional conditions
if :P500_FN is not null then
  q := q || ' and name_first_name = :P500_FN';
end if;

if :P500_LN is not null then
  q := q || ' and name_last_name = :P500_LN';
end if;

return q;
end;

My final code will need to contain more items to search besides first and last name but for now I am testing with just these two parameters. When I only fill in a first name, the search works. When I only fill in a last name, it works. When I type in a first AND last name I get the error ORA-01460 and ORA-02063.

What could I be doing wrong?

Upvotes: 0

Views: 588

Answers (2)

XING
XING

Reputation: 9886

I can see that you used bind variables inside '' which would be never evaluated in a PLSQL block:

q := q || ' and name_first_name = :P500_FN';

This should be like this :

q := q || ' and name_first_name = '||:P500_FN;

Upvotes: 1

MT0
MT0

Reputation: 168560

You do not need dynamic SQL:

SELECT *
FROM   name_selection_atnd
WHERE  ( :P500_FN IS NULL OR name_first_name = :P500_FN )
AND    ( :P500_LN IS NULL OR name_last_name  = :P500_LN )
AND    ( :P500_FN IS NOT NULL OR :P500_LN IS NOT NULL );

Upvotes: 0

Related Questions