CODEWITHSUNDEEP
oracle-databaseplsql
dixxon
dixxon

Reputation: 3

How to insert single quotes from a function to call in a select statement

I am trying to write a function for a VPD to block users from running select queries based upon the host/machine that they are running from. I think i have most of the function down, but i cant for the life of me work out how to get my variable into single quotes. I need it in quotes so that i can run a select statement against a table with a list of allowed connections (for example - select * from table where v_host = 'host';).

This is one of the variations i have tried so far, but i have had several other attempts and i feel like i'm banging my head a bit:

declare
    v_host VARCHAR2(100);
    v_host2 VARCHAR2(100);
begin
    v_host := q'[SYS_CONTEXT('USERENV','HOST')]';
    dbms_output.put_line(v_host);
end;
/

Upvotes: 0

Views: 37

Answers (2)

Alex Poole
Alex Poole

Reputation: 191265

It isn't entirely clear what you want the variable to contain but it looks like you might want:

v_host := q'[select * from table where v_host = ']'
    || SYS_CONTEXT('USERENV','HOST') || q'[']';

but here are some variations:

declare
    v_host VARCHAR2(100);
begin
    v_host := q'[SYS_CONTEXT('USERENV','HOST')]';
    dbms_output.put_line(v_host);
    v_host := q'[select * from table where v_host = 'host']';
    dbms_output.put_line(v_host);
    v_host := q'[select * from table where v_host = SYS_CONTEXT('USERENV','HOST')]';
    dbms_output.put_line(v_host);
    v_host := q'[select * from table where v_host = ']'
        || SYS_CONTEXT('USERENV','HOST') || q'[']';
    dbms_output.put_line(v_host);
end;
/

which produce:

SYS_CONTEXT('USERENV','HOST')
select * from table where v_host = 'host'
select * from table where v_host = SYS_CONTEXT('USERENV','HOST')
select * from table where v_host = 'dbfiddle-oracle18xe.localdomain'

db<>fiddle

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 142705

Is this what you're looking for?

SQL> declare
  2      v_host VARCHAR2(100);
  3      v_host2 VARCHAR2(100);
  4  begin
  5      v_host := SYS_CONTEXT('USERENV','HOST');
  6      dbms_output.put_line(v_host);
  7
  8      v_host2 := chr(39) || v_host || chr(39);
  9      dbms_output.put_line(v_host2);
 10  end;
 11  /
LITTLEFOOT
'LITTLEFOOT'

PL/SQL procedure successfully completed.

SQL>

If so, what did I do? Concatenated v_host to single quotes (chr(39)).

Upvotes: 0

Related Questions