CODEWITHSUNDEEP
sqloracle-databaseplsql
Irveen
Irveen

Reputation: 105

ORA-01756: quoted string not properly terminated

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

Upvotes: 0

Views: 25987

Answers (5)

Irveen
Irveen

Reputation: 105

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;
/

Upvotes: 0

Jon Spokes
Jon Spokes

Reputation: 2599

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

Upvotes: 0

Quassnoi
Quassnoi

Reputation: 425391

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;

Upvotes: 5

Romain Linsolas
Romain Linsolas

Reputation: 81627

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...

Upvotes: 0

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171421

Change line 6 to 

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

Upvotes: 2

Related Questions