ORA-01756: quoted string not properly terminated

Question

please let me know the issue with following script (sql,oracle 10g)

  1  DECLARE @colname AS NVARCHAR(50)
  2  DECLARE @tablename AS NVARCHAR(500)
  3  DEClARE @query AS NVARCHAR(500)
  4  SET @colname = 'select wk_units1 from cnt_sls_dm.fct_sales_summary'
  5  SET @tablename = 'SELECT tablename from dmi_user.fct_sales_meta'
  6  set @query='select '+@colname+' FROM '+@tablename+'
  7* EXECUTE sp_executesql @query
SQL> /
ERROR:
ORA-01756: quoted string not properly terminated

Answer 1

Change line 6 to

set @query='select '+@colname+' FROM '+@tablename

Answer 2

The 6th line seems not correct in your example:

set @query='select '+@colname+' FROM '+@tablename+'

You finish the line with a '. Either you remove the +', either you finish your request with a where statement...

Answer 3

This error is quite self-describing, you have an unterminated quote.

You are trying to run an SQL Server code in Oracle. This won't work.

You cannot just turn T-SQL into PL/SQL by mere copying.

I corrected the syntax, but most probably you will need much more work than that.

DECLARE
    colname NVARCHAR2(50);
    tname   NVARCHAR2(500);
    query   NVARCHAR2(500);
BEGIN
    SELECT  wk_units1
    INTO    colname
    FROM    cnt_sls_dm.fct_sales_summary;
    SELECT  tablename
    INTO    tname
    FROM    dmi_user.fct_sales_meta;
    query := 'SELECT ' || colname || ' FROM ' || tname;
END;

Answer 4

This looks a lot like tSql rather than pl SQl, You might want to use || to concatenate strings in Oracle and varchar2 instead of nvarchar

Answer 5

following errors cuming up..................

 1  DECLARE
  2  *
  3  ERROR at line 1:
  4  ORA-01403: no data found
  5* ORA-06512: at line 10
SQL> DECLARE
  2      colname NVARCHAR2(50);
  3      tname   NVARCHAR2(500);
  4      query   NVARCHAR2(500);
  5  BEGIN
  6      SELECT  wk_units1
  7      INTO    colname
  8      FROM    cnt_sls_dm.fct_sales_summary
  9      where   rownum=1;
 10      SELECT  tablename
 11      INTO    tname
 12      FROM    dmi_user.fct_sales_meta
 13      WHERE sno=1;
 14      query := 'SELECT ' || colname || ' FROM ' || tname;
 15  END;
 16  /
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 10

Answer 6

execute immediate not working everything else is fine

SQL> ED Wrote file afiedt.buf

  1  DECLARE
  2      colname NVARCHAR2(50);
  3      tname   NVARCHAR2(500);
  4      query   NVARCHAR2(500);
  5  BEGIN
  6      SELECT  colname
  7      INTO    colname
  8      FROM    dmi_user.FCT_SALES_META
  9      where   sno=1;
 10      SELECT  tablename
 11      INTO    tname
 12      FROM    dmi_user.fct_sales_meta
 13      WHERE sno=1;
 14      query := 'SELECT ' || colname || ' FROM ' || tname;
 15     dbms_output.put_line(colname);
 16          dbms_output.put_line(tname);
 17          dbms_output.put_line(query);
 18          execute immediate(query);
 19* END;
SQL> /
        execute immediate(query);
                     *
ERROR at line 18:
ORA-06550: line 18, column 26:
PLS-00382: expression is of wrong type
ORA-06550: line 18, column 9:
PL/SQL: Statement ignored

Answer 7

following is the correct way to answer the question........ running fine.......... thanks to all who helped......

-Irveen

DECLARE
     type fct_sales_summary_cur is table of dmi_user.fct_sales_summary_cmp_1%rowtype index by binary_integer;
     cur_rec fct_sales_summary_cur;
            colname NVARCHAR2(50);
            tname   NVARCHAR2(500);
            query   VARCHAR2(500);
            cnt     number:=1;
    BEGIN
            loop
            SELECT  colname
            INTO    colname
            FROM    dmi_user.FCT_SALES_META
            where   sno=cnt;
            SELECT  tablename
            INTO    tname
            FROM    dmi_user.fct_sales_meta
            WHERE sno=cnt;
            --query:='select * from dmi_user.fct_sales_summary_cmp';
            query := 'SELECT '|| colname ||' FROM '||tname;
            -- dbms_output.put_line(colname);
            -- dbms_output.put_line(tname);
            --dbms_output.put_line(query);
            execute immediate query bulk collect into cur_rec;
            --dbms_output.put_line(cur_rec);
            dbms_output.put_line('------Table-Sno -----' || cnt);
            for i in cur_rec.first..cur_rec.last loop
            dbms_output.put_line(cur_rec(i).wk_units1);
            end loop;
            cnt:=cnt+1;
    exit when cnt=4;
    end loop;
    END;
/