Single quatation when using dbms_Sql package gives ORA-error

Single quatation when using dbms_Sql package gives ORA-error

Post by Oleg » Fri, 16 Mar 2001 18:57:37



Hi:

I would like to ask how to deal with single quatation mark when using
dbms_sql package.

For example, consider code:

declare
   c      NUMBER;
   stmt   VARCHAR2(100);
   rows   NUMBER;
   ind    NUMBER;
   v_emp VARCHAR2(100);

BEGIN

   stmt := 'SELECT 1 FROM EMP ';

   v_emp:= 'd''d''d';

   stmt := stmt ||
          ' WHERE emp LIKE ''' || v_emp|| '''';

   c := dbms_sql.open_cursor;
   dbms_sql.parse(c, stmt, dbms_sql.v7);
   dbms_sql.define_column(c, 1, ind);
   rows := dbms_sql.execute_and_fetch(c);
   dbms_sql.close_cursor(c);

END;

The line
v_emp:= 'd''d''d';
is legal.

But when code is executed it gives

ERROR at line 1:
ORA-00933: SQL command not properly ended
ORA-06512: at "SYS.DBMS_SYS_SQL", line 782
ORA-06512: at "SYS.DBMS_SQL", line 32
ORA-06512: at line 18

So the question is how to fetch by dynamic sql if statement contains
single quation mark?

Any suggestion?

Thanks!

Oleg

 
 
 

Single quatation when using dbms_Sql package gives ORA-error

Post by Yaroslav Perventse » Fri, 16 Mar 2001 20:16:14


Try:
 v_emp:= 'd''''d''''d';

Best regards
Yaroslav



Quote:> Hi:

> I would like to ask how to deal with single quatation mark when using
> dbms_sql package.

> For example, consider code:

> declare
>    c      NUMBER;
>    stmt   VARCHAR2(100);
>    rows   NUMBER;
>    ind    NUMBER;
>    v_emp VARCHAR2(100);

> BEGIN

>    stmt := 'SELECT 1 FROM EMP ';

>    v_emp:= 'd''d''d';

>    stmt := stmt ||
>           ' WHERE emp LIKE ''' || v_emp|| '''';

>    c := dbms_sql.open_cursor;
>    dbms_sql.parse(c, stmt, dbms_sql.v7);
>    dbms_sql.define_column(c, 1, ind);
>    rows := dbms_sql.execute_and_fetch(c);
>    dbms_sql.close_cursor(c);

> END;

> The line
> v_emp:= 'd''d''d';
> is legal.

> But when code is executed it gives

> ERROR at line 1:
> ORA-00933: SQL command not properly ended
> ORA-06512: at "SYS.DBMS_SYS_SQL", line 782
> ORA-06512: at "SYS.DBMS_SQL", line 32
> ORA-06512: at line 18

> So the question is how to fetch by dynamic sql if statement contains
> single quation mark?

> Any suggestion?

> Thanks!

> Oleg


 
 
 

1. ORA-06571 when selecting a function using DBMS_SQL package

Please Sirs, I need help on this.

Here is the situation:
I created the following function utilizing the DBMS_SQL package.  The
function returns the number of rows in the specified table (which is
the parameter).

  SQL> create or replace function n_rows (tab IN varchar2)
    2  return number
    3  is
    4  i number;
    5  cnt number;
    6  cursor_number number;
    7  BEGIN
    8    cursor_number:=dbms_sql.open_cursor;
    9    dbms_sql.parse(cursor_number, 'select count(*) from '||tab, 1);
   10    dbms_sql.define_column(cursor_number, 1, cnt);
   11    i:=dbms_sql.execute(cursor_number);
   12    IF DBMS_SQL.FETCH_ROWS(cursor_number)>0 THEN
   13      DBMS_SQL.COLUMN_VALUE(cursor_number,1,cnt);
   14    return (cnt);
   15    END IF;
   16    dbms_sql.close_cursor(cursor_number);
   17  END;
   18  /

  Function created.

It compiled!
Then I tried the following:

  SQL> ed
  Wrote file afiedt.buf

    1  declare
    2  x number;
    3  Begin
    4  x:=n_rows('employee');
    5  sys.dbms_output.put_line(x);
    6* end;
  SQL> /
  6

  PL/SQL procedure successfully completed.

The function worked fine!
But when I do a select I get the following:

  SQL> select n_rows('employee') from dual;
  select n_rows('employee') from dual
         *
  ERROR at line 1:
  ORA-06571: Function N_ROWS does not guarantee not to update database

why is this?
i checked the error message and it told me to recreate the package with
the required pragma...something about not being able to update the
database.

Any help or hints is greatly appreciated,
Thank you,
Corky

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.

2. Trying to Email

3. Permissions problem using DBMS_SQL Package

4. SQL for View

5. Using DBMS_SQL package Incorrectly?

6. Search a Class Library

7. Q: DBMS_SQL and select count(*) into :x - error ORA-01006

8. Query to get Identity Field

9. T-SQL debugger will run procedure, but won't single step and gives errors

10. Single apostrophe gives error

11. oexec gives ORA-01460 using OCI?