this problem is driving me crazy if I have a pl/sql statement like that

stmt:= 'INSERT INTO myTable (blah, blah, blah, blah) values(blah, blah, blah, ''SELECT id FROM myTable where rownum < 100000 AND stop=F)';'',blah,blah,blah)';

How do I make in a way that stop=F becomes stop='F' with the comma on top so it can be used as a query when fetching the proper row?

At this time the query would be

SELECT id FROM myTable where rownum < 100000 AND stop=F;

But it should be

SELECT id FROM myTable where rownum < 100000 AND stop='F';

Thank you;

I know that is probably really dumb but i cannot figure out what, I tried with :

''SELECT id FROM myTable where rownum < 100000 AND stop=''F'')';''
''SELECT id FROM myTable where rownum < 100000 AND stop='''F''')';''

but nothing seems to work I always get a missing comma error in sql developer;

Really thanks to everybody guys!

CodePudding user response：

You were close with the examples, what you want is:

select 'SELECT id FROM myTable where rownum < 100000 AND stop=''F'');'
from dual

You only need a single quote to start/end your string. Double quotes will insert the quote inside that string.

You also had an extra single quote between ) and ; that was prematurely ending your string.

CodePudding user response：

The correct way to create text literals with quotation marks is to use Q-quoting, where you do not need to repeat quotation marks and the text may be used as-is:

select q'$ select 'text literal as is' from something$' as str
from dual

union all

select
 q'$string with repetition of two '', three ''', four '''' and more quotes$' as str
from dual

| STR                                                                    |
| :--------------------------------------------------------------------- |
|  select 'text literal as is' from something                            |
| string with repetition of two '', three ''', four '''' and more quotes |

db<>fiddle here

CodePudding user response：

Use bind variables rather than trying to embed the sub-query into the statement:

DECLARE
  stmt VARCHAR2(4000) := 'INSERT INTO myTable (col1, col2, col3, col4)
                          VALUES (:1, :2, :3, :4)';
  blah VARCHAR2(20) := 'blah';
BEGIN
  EXECUTE IMMEDIATE stmt
    USING blah,
          blah,
          'SELECT id FROM myTable where rownum < 100000 AND stop=''F'')',
          blah;
END;
/

or, without dynamic SQL and using PL/SQL variables:

DECLARE
  blah VARCHAR2(20) := 'blah';
  qry  VARHCAR2(200) := 'SELECT id FROM myTable where rownum < 100000 AND stop=''F'')';
BEGIN
  INSERT INTO myTable (
    col1, col2, col3, col4
  ) VALUES (
    blah, blah, qry, blah
  );
END;
/

CodePudding user response：

As you want this as a dynamic statement (whether or not that is necessary), you can use nested alternative quoting syntax:

stmt:= q'^INSERT INTO myTable (id, stmt, dt)^'
    || q'^ values (1, q'[SELECT id FROM myTable where rownum < 100000 AND stop='F');]', sysdate)^';

The outer part of the the statement uses ^ as the quoting delimiter, and the inner statement - the one you are trying to insert as a column value - is using []. But you can use any valid characters, of course.

That generates:

INSERT INTO myTable (id, stmt, dt) values (1, q'[SELECT id FROM myTable where rownum < 100000 AND stop='F');]', sysdate)

which works as a dynamic statement, and inserts:

SELECT id FROM myTable where rownum < 100000 AND stop='F');

db<>fiddle

Using bind variables would probably make more sense, even for a one-off action. The inner statement can still be constructed, as its own variable, using alternative quoting.

As an aside, if the intention is to execute that inserted statement at some point, you probably don't want that to have a trailing semicolon.