string within a query

Question

string within a query

Navigation

#1 by (1 votes)
#2 by (0 votes)

1

I am creating a package with dynamic queries to which I want to make a selection from an input parameter Table.

Inside the procedure I have:

querysql varchar2(100);

BEGIN

querysql := 'SELECT RPAD(NVL(description,' '),20,'n') FROM '||Tabla||'';
execute immediate querysql;
commit;
end;

If I made a selection of the normal field. Example:

querysql := 'SELECT description FROM '||Tabla||'';

There would be no problem but to tell him to fill in the empty spaces with the letter 'n' does not take it because it makes a mess with the strings.

I have tried to declare the variable space and fill to call it from the querysql as we do with tables but it does not work either. Example:

querysql varchar2(100);
espacio varchar2(1);
relleno varchar2(1);

    BEGIN

    espacio := ' ';
    relleno := 'n';

    querysql := 'SELECT RPAD(NVL(description,' ||espacio|| '),20,'||relleno||') FROM '||Tabla||'';
    execute immediate querysql;
    commit;
    end;

Any solution?

Thank you very much !!!

sql oracle

asked by moch 13.01.2017 в 10:00

source

2 answers

How do I select a Tab from a TabLayout? [closed] How to call a Store Procedure that returns a list of data in the Entity Framework Code First?

score 1 · Answer 1

To handle the quotes, you should simply duplicate them where necessary:

querysql := 'SELECT RPAD(NVL(description,'' ''),20,''n'') FROM ' || Tabla;

But I'll tell you that I do not see the sense of doing a execute immediate querysql; without handling the results of the query. I guess it's just an example, and you actually have more code ...

score 0 · Answer 2

You can use the CHR () function, it returns the character associated to the number you send according to UTF-8 coding. CHR (34) are the double quotes and CHR (39) the single quote.

You can write your query like this:

querysql := 'SELECT RPAD(NVL(description,' ||CHR(39)||espacio||CHR(39)||'),20,'||CHR(39)||relleno||CHR(39)||') FROM '||CHR(39)||Tabla||CHR(39)||'';