CODEWITHSUNDEEP
oracle-databaseplsql
Mrtalas
Mrtalas

Reputation: 23

PLSQL-Expected One of The Following

I have created following query. However, I got error below. I think that error is related to TO_CHAR(' AND MARITAL_STATUS in ('Married-civ-spouse') ') line. How can I define this other ways? Thanks in advance.

Error:
PLS-00103: Encountered the symbol ") " when expecting one of the following:

. ( ) , * @ % & = - + < / > at in is mod remainder not rem =>
<an exponent (**)> <> or != or ~= >= <= <> and or like like2
like4 likec between || member
06550. 00000 -  "line %s, column %s:\n%s"

This is my code:

SET SERVEROUTPUT ON;
DECLARE 
KATSAYI1 VARCHAR2(1000):=TO_CHAR('CREATE TABLE DQ_DNM2 AS SELECT * FROM DQ_TEST_DATA WHERE YEARMONTH=');
KATSAYI2 varchar2(100) := TO_CHAR(sysdate,'YYYY');
KATSAYI3 VARCHAR2(100) := TO_CHAR(KATSAYI2-2);
KATSAYI4 varchar2(100) := to_char(sysdate, 'MM');
KATSAYI7 varchar2(100) := to_char(KATSAYI4 + 11);
KATSAYI5 varchar2(100) := to_char(CONCAT(KATSAYI3,KATSAYI7));
KATSAYI6 VARCHAR2(1000):=TO_CHAR(CONCAT(KATSAYI1,KATSAYI5));
KATSAYI8 VARCHAR2(100) :=TO_CHAR(' AND MARITAL_STATUS in ('Married-civ- spouse') ');
KATSAYI9 varchar2(100) := to_char(CONCAT(KATSAYI6,KATSAYI8));
BEGIN
dbms_output.put_line( KATSAYI5);
DBMS_OUTPUT.PUT_LINE('İlgili tablo yaratıldı.');
EXECUTE IMMEDIATE KATSAYI9;
END;

/code here

Upvotes: 1

Views: 1258

Answers (3)

Mrtalas
Mrtalas

Reputation: 23

Thanks for your answers. Using double quotes worked in this case. Before opening this thread, I tried to use (") . Dont use that. Use double quotes.

Upvotes: 0

sameh.q
sameh.q

Reputation: 1709

Use chr(39) instead of '

select 'That'||chr(39)||'s the right way' from dual

will give you

That's the right way

You can use q'[ ]' to escape all characters and new lines

Example:

select q'[That's the right way also "" '' !@#$%^&*()_=+

anything you want... ]' from dual

That's the right way also "" '' !@#$%^&*()_=+

anything you want...

This approach is good for dynamic code execution, where you don't need to worry about escaping variables and concatenations; you just write your code in the same format of what will be executed. better in readability of your code.

Upvotes: 1

MT0
MT0

Reputation: 168588

You appear to be taking 2 off the years and adding 11 to the months (so in effect taking off 13 months):

SET SERVEROUTPUT ON;

DECLARE 
  p_yyyymm char(6) := TO_CHAR( ADD_MONTHS( SYSDATE, -13 ), 'YYYYMM');
BEGIN
  DBMS_OUTPUT.PUT_LINE( p_yyyymm );
  DBMS_OUTPUT.PUT_LINE('İlgili tablo yaratıldı.');
  EXECUTE IMMEDIATE 'CREATE TABLE DQ_DNM2 AS SELECT * FROM DQ_TEST_DATA WHERE YEARMONTH=''' || p_yyyymm || ''' AND MARITAL_STATUS in (''Married-civ- spouse'')';
END;

Upvotes: 0

Related Questions