PL/SQL for Beginners:

SQL in PL/SQL

Chris Saxon, Oracle Developer Advocate

[Link]@[Link]
@ChrisRSaxon & @SQLDaily
[Link]/sql
[Link]/c/TheMagicofSQL

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. |

 Ryan McGuire / Gratisography
Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
 Ryan McGuire / Gratisography
Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
Safe Harbor Statement
The following is intended to outline our general product direction. It is intended for
information purposes only, and may not be incorporated into any contract. It is not a
commitment to deliver any material, code, or functionality, and should not be relied upon
in making purchasing decisions. The development, release, and timing of any features or
functionality described for Oracle’s products remains at the sole discretion of Oracle.

[Link]/sql [Link]/c/TheMagicOfSQL
Copyright © 2019, Oracle and/or its affiliates. All rights reserved. |
@ChrisRSaxon
SQL is a First Class Citizen

[Link]/sql [Link]/c/TheMagicOfSQL
Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
@ChrisRSaxon
var sql =
"insert into other_language " +
"values ( 'SQL with disdain' )" ;

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
var sql =
"insert into other_language " +
"values ( 'SQL with disdain' )" ;
[Link] ( sql );

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
begin
insert into pl_sql
values ( 'SQL with respect' );
end;
/

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
create procedure invalid_proc as
begin

insert invalid_sql
values ( 'breaks' );

end invalid_proc;
/

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
create procedure invalid_proc as
begin

insert invalid_sql
values ( 'breaks' );

end invalid_proc;
/

4/15 PL/SQL:
ORA-00925: missing INTO keyword

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
create procedure invalid_proc as
begin

insert into non_existent_table

values ( 'also breaks' );

end invalid_proc;
/

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
create procedure invalid_proc as
begin

insert into non_existent_table

values ( 'also breaks' );

end invalid_proc;
/

4/15 PL/SQL:
ORA-00942: table or view does not exist

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
alter table my_table
drop ( some_col );

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
select name, type , status
from user_dependencies ud
join user_objects uo
on [Link] = uo.object_name
where referenced_name = 'MY_TABLE';

NAME TYPE STATUS

MY_FUNCTION FUNCTION INVALID
MY_PROCEDURE PROCEDURE INVALID

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
Which code
uses
my_table?

[Link]/sql [Link]
Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
select name, type
from user_dependencies
where referenced_name = 'MY_TABLE';

NAME
MY_PROC
TYPE
PROCEDURE Which
MY_FUNC FUNCTION
line?
What statement
Which
Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
select name, type, line, text
from user_source
where upper ( text ) like '%MY_TABLE%';
NAME TYPE LINE TEXT
MY_FUNCTION FUNCTION 2
return my_table%rowtype as
MY_FUNCTION FUNCTION 3
retval my_table%rowtype;
MY_FUNCTION FUNCTION 8
from my_table
MY_PROCEDURE PROCEDURE 4
insert into my_table

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
PL/Scope

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
 12 .2
ire s
Re qu
Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
SQL is a First Class Citizen
✔ SQL part of PL/SQL language

✔ Compile-time errors for SQL

✔ Data dictionary analysis

[Link]/sql [Link]/c/TheMagicOfSQL
Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
@ChrisRSaxon
 Ryan McGuire / Gratisography
Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
SQL Enhancements

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
create procedure my_proc ( val int ) as
begin

insert into my_table

values ( val );

end my_proc;
/

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
alter table my_table
add ( anoter_col int );

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
create procedure my_proc ( val int ) as
begin

insert into my_table

values ( val );

end my_proc;
/

4/15 PL/SQL:
ORA-00947: not enough values

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
create procedure my_proc ( val int ) as
begin

insert into my_table ( c1 )

values ( val );

end my_proc;
/

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
 [Link]
Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
insert into lots_of_cols (
C1 , C2 , C3 , C4 , C5 ,
C6 , C7 , C8 , C9 , C10 ,
C11 , C12 , C13 , C14 , C15 ,
C16 , C17 , C18 , C19 , C20 ,
C21 , C22 , C23 , C24 , C25 ,
C26 , C27 , C28 , C29 , C30 ,
C31 , C32 , C33 , C34 , C35 ,
C36 , C37 , C38 , C39 , C40 ,
C41 , C42 , C43 , C44 , C45 ,
C46 , C47 , C48 , C49 , C50 ,

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
C51 , C52 , C53 , C54 , C55 ,
C56 , C57 , C58 , C59 , C60 ,
C61 , C62 , C63 , C64 , C65 ,
C66 , C67 , C68 , C69 , C70 ,
C71 , C72 , C73 , C74 , C75 ,
C76 , C77 , C78 , C79 , C80 ,
C81 , C82 , C83 , C84 , C85 ,
C86 , C87 , C88 , C89 , C90 ,
C91 , C92 , C93 , C94 , C95 ,
C96 , C97 , C98 , C99 , C100,

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
C101, C102, C103, C104, C105,
C106, C107, C108, C109, C110,
C111, C112, C113, C114, C115,
C116, C117, C118, C119, C120,
C121, C122, C123, C124, C125,
C126, C127, C128, C129, C130,
C131, C132, C133, C134, C135,
C136, C137, C138, C139, C140,
C141, C142, C143, C144, C145,
C146, C147, C148, C149, C150,

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
C151, C152, C153, C154, C155,
C156, C157, C158, C159, C160,
C161, C162, C163, C164, C165,
C166, C167, C168, C169, C170,
C171, C172, C173, C174, C175,
C176, C177, C178, C179, C180,
C181, C182, C183, C184, C185,
C186, C187, C188, C189, C190,
C191, C192, C193, C194, C195,
C196, C197, C198, C199, C200,

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
 C196, C197, C198, C199, C200,

... and on and on ...

C396, C397, C398, C399, C400

)

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
 C196, C197, C198, C199, C200,

... and on and on ...

C396, C397, C398, C399, C400

) values (

... oh god ...

Ryan McGuire / Gratisography

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
based DML

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
type rec is record (
c1 int, c2 int, c3 int, c4 int,
c5 int, c6 int, c7 int, c8 int,
c9 int, c10 int, c11 int, c12 int,
c13 int, c14 int, c15 int, c16 int,
c17 int, c18 int, c19 int, c20 int,
...
);

rec_var rec;

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
begin

insert into lots_of_cols

values rec_var;

end;
Must match
columns of
lots_of_cols
Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
type rec is record (
c1 int, c2 int, c3 int, c4 int,
c5 int, c6 int, c7 int, c8 int,
c9 int, c10 int, c11 int, c12 int,
c13 int, c14 int, c15 int, c16 int,
c17 int, c18 int, c19 int, c20 int,
... is this really better? ...
);

rec_var rec;

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
Type

Image by Clker-Free-Vector-Images from Pixabay

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
 Same data type as
declare
lots_of_cols.c1
c1_var lots_of_cols.c1%type;
table_rec lots_of_cols%rowtype;

Same structure as
lots_of_cols
Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
create procedure single_row_ins (
table_rec in lots_of_cols%rowtype
) as
begin

insert into lots_of_cols

values table_rec;

end single_row_ins;

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
alter table lots_of_cols
add yet_another_column integer;

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
alter procedure single_row_ins
compile;

sho err

No errors.

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
begin

update lots_of_cols
set row = table_rec
where ...;

end;

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
Did the update
change any
rows?

[Link]/sql [Link]
Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
SQL%Attributes

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
begin

update lots_of_cols
set row = table_rec
where ...;

if sql%rowcount = 0 then
... do something ...

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
How do I write
queries in
PL/SQL?

[Link]/sql [Link]
Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
 Cursor Lifecycle

Open Execute Close

Explicit Cursor => Named

Implicit Cursor => Unnamed
Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
Impl c t C rs rs

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
begin

select c1, c2, ...

from single_row_lookup
where ...;

end;

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
begin

select c1, c2, ...

into v1, v2, ...
from single_row_lookup
where ...;

end;

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
 no rows -> NO_DATA_FOUND
begin

select c1, c2, ...

into rec_var
from single_row_lookup
where ...;

end;
> 1 row -> TOO_MANY_ROWS
Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
How do I get
many rows?

[Link]/sql [Link]
Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
for rws in (
select c1, ... from many_rows
) loop

other_proc ( rws.c1 );
... do stuff ...

end loop;

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
Associative Arrays

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
declare

type num_arr
is table of number
index by pls_integer;

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
declare

type num_arr
is table of number
index by pls_integer;

type tab_arr
is table of many_rows%rowtype
index by pls_integer;

rec_arr tab_arr;

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
begin

select *
bulk collect
into rec_arr
from many_rows;

...

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
 s ed
y u
begin
m o r
m e e d s
select * P G A x c e
bulk 03 6 : e e
0 4 collect
a n c I T
R A -
into st
rec_arr
n LI M
O from e i T
many_rows;E _
th GA
by ... AGGRE
A _
PG
Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
Ex%!$!t Cursors

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
declare

cursor cur is
select ... from many_rows;

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
for rws in cur loop

other_proc ( rws.c1 );
... do stuff ...

end loop;

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
declare

cursor cur is
select ... from many_rows;

rec cur%rowtype;

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
open cur;

close cur;

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
open cur;

fetch cur
into rec;

close cur;

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
open cur;

loop
fetch cur
into rec;

... do stuff ...

end loop;

close cur;

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
open cur;

loop
fetch cur
into rec;
exit when cur%notfound;
... do stuff ...
end loop;

close cur;

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
cursor cur is
select ... from many_rows;

type cur_rec_array
is cur%rowtype
index by pls_integer;

rec_array cur_rec_array

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
open cur;

loop
fetch cur bulk collect
into rec_array limit 100;

... do stuff ...

end loop;

close cur;

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
open cur;

loop
fetch cur bulk collect
into rec_array limit 100;
exit when rec_array.count = 0;
... do stuff ...
end loop;

close cur;

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
SQL Enhancements
✔ Record-based DML

✔ %anchoring & %attributes

✔ Select (bulk collect) into

[Link]/sql [Link]/c/TheMagicOfSQL
Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
@ChrisRSaxon
 Ryan McGuire / Gratisography
Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
SQL is Secure by Default

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
SQL

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
var sql =
"select full_name from users
where username = '" + name + "';

Value
from
client
Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
@
ChrisRSaxon
var sql =
"select full_name from users
where username = 'chris';

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
@
ChrisRSaxon
select full_name from users
where username = ''
union all
select owner || '.' || table_name
from all_tables where 1='1'--;

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
@
ChrisRSaxon
 Everything you
can select
select full_name from users
where username = ''
union all
select owner || '.' || table_name
from all_tables where 1='1'--;

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
@
ChrisRSaxon
Use Bind Variables!

[Link]/sql [Link]/c/TheMagicOfSQL
@ChrisRSaxon
var sql =
"select * from users
where username = ?";

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
@
ChrisRSaxon
 Value
placeholder
var sql =
"select * from users
where username = ?";

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
@
ChrisRSaxon
 Value
placeholder
var sql =
"select * from users
where username = ?";

Inputs never part of SQL =>

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
@
ChrisRSaxon
 SQL in PL/SQL is
automatically bound

[Link]/sql [Link]/c/TheMagicOfSQL
@ChrisRSaxon
begin
This is a
bind variable
select *
into user_rec
from users u
where [Link] = name_var;

end;

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
@
ChrisRSaxon
I need to change
the text of SQL

[Link]/sql [Link]
Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
Dynamic SQL

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
sql := 'select * from my_table where ...' ;

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
sql := 'select * from my_table where ...' ;

if v1 is not null then

sql := sql || ' and c1 = :bind_var1';
end if;

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
sql := 'select * from my_table where ...' ;

if v1 is not null then

sql := sql || ' and c1 = :bind_var1';
end if;

if v2 is not null then

sql := sql || ' and c2 = :bind_var2';
end if;

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
execute immediate
'select ... from my_table
where c1 = :bind_var1
and c2 = :bind_var2'

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
execute immediate
'select ... from my_table
where c1 = :bind_var1
and c2 = :bind_var2'
bulk collect into result_array

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
execute immediate
'select ... from my_table
where c1 = :bind_var1
and c2 = :bind_var2'
bulk collect into result_array
using v1, v2;

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
sql := 'select * from my_table where ...' ;

if v1 is not null then

sql := sql || ' and c1 = :bind_var1';
end if;

if v2 is not null then

sql := sql || ' and c2 = :bind_var2';
end if;

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
if v1 is not null and v2 is null then

execute immediate sql

bulk collect into result_array
using v1;

elsif v1 is null and v2 is not null then

execute immediate sql

bulk collect into result_array
using v2;

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
I want to change
the table
name

[Link]/sql [Link]
Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
execute immediate
'select * from :table_var'
bulk collect into result_array
using table_name;

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
execute immediate
'select * from :table_var'
bulk collect into result_array
using table_name;

ORA-00903: invalid table name

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
if trans_type = 'CARD_PAYMENTS' then

select ...
bulk collect into result_array
from card_payments;

elsif trans_type = 'CASH_PAYMENTS' then

select ...
bulk collect into result_array
from cash_payments;
...
Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
 But I don't
know which
tables!

[Link]/sql [Link]
Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
 Sanitize input!

Pixabay
Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
 Sanitize input!

DBMS_assert

Pixabay
Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
execute immediate
'select * from ' ||
dbms_assert.sql_object_name (
table_name
)
bulk collect into result_array;

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
SQL is Secure by Default
✔ Static SQL =>

✔ Dynamic SQL should be rare

✔ DBMS_assert to sanitize input

[Link]/sql [Link]/c/TheMagicOfSQL
Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
@ChrisRSaxon
 Will you
marry
me?
Ryan McGuire / Gratisography
Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
 YES!

Ryan McGuire / Gratisography

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
 L/S Q L
L & P
SQ

EV ER !
4 Pixabay

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
APEX

Image by esudrof ffrom Pixabay

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon
[Link]

#MakeDataGreatAgain
Ryan McGuire / Gratisography
Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | [Link]/sql [Link]/c/TheMagicOfSQL @ChrisRSaxon