Why Does Running This Query with Execute Immediate Cause It to Fail

Why does running this query with EXECUTE IMMEDIATE cause it to fail?

Try to lose the ";" from inside the string that you Execute Immediate.

EXECUTE IMMEDIATE  'CREATE GLOBAL TEMPORARY TABLE tmp_tab AS (' || query || ')';

Execute Immediate within a stored procedure keeps giving insufficient priviliges error

Oracle's security model is such that when executing dynamic SQL using Execute Immediate (inside the context of a PL/SQL block or procedure), the user does not have privileges to objects or commands that are granted via role membership. Your user likely has "DBA" role or something similar. You must explicitly grant "drop table" permissions to this user. The same would apply if you were trying to select from tables in another schema (such as sys or system) - you would need to grant explicit SELECT privileges on that table to this user.

Execute immediate error in Oracle

Your value column includes values which contain a single quote. (The ampersand isn't going to be a problem here). You can see how that breaks the statement by displaying it before execution:

dbms_output.put_line('Update EMP_DETAILS_T set ' || CR_EMP.COLUMN_NAME
  || ' = ' ||''''||CR_EMP.VALUE||''''|| '  where employee_id  =  '
  ||''''|| P_EMP_ID||'''');

... which reveals:

Update EMP_DETAILS_T set SOME_COLUMN = 'Standard & Poor's'  where employee_id  =  'ABC123'

... which has unbalanced single quotes; you can even see that in the syntax highlighting.

The simplest fix is to use bind variables for the two values - you can't for the column name - and pass the actual values with the USING clause:

EXECUTE IMMEDIATE 'Update EMP_DETAILS_T set ' || CR_EMP.COLUMN_NAME
    || ' = :VALUE where employee_id = :EMP_ID'
    USING CR_EMP.VALUE, P_EMP_ID;

error in EXECUTE IMMEDIATE insert ORACLE

It looks like you need to put single-quotes around the string literals being inserted into the COL_NAME and COL_TYPE columns. Try rewriting the trigger as:

CREATE OR REPLACE TRIGGER ICidade AFTER
  INSERT ON Cidade FOR EACH ROW DECLARE TYPE EmpCurTyp IS REF CURSOR;
  emp_cv EmpCurTyp;
  col_name_aux VARCHAR(100);
  col_type_aux VARCHAR(100);
  stm          VARCHAR(4000):='';
  BEGIN
    -- Pegando as PKS
    FOR j IN
    (SELECT d.Column_Name coluna,
        d.DATA_TYPE tipo
      FROM user_cons_columns ucc,
        user_constraints uc,
        (SELECT COLUMN_NAME,DATA_TYPE FROM ALL_TAB_COLUMNS WHERE TABLE_NAME='CIDADE'
        ) d
      WHERE uc.constraint_name=ucc.constraint_name
      AND uc.constraint_type  ='P'
      AND uc.table_name       = 'CIDADE'
      AND d.COLUMN_NAME       =ucc.Column_Name
    )
    LOOP
      stm := 'INSERT INTO TEMP_PK (COL_NAME, COL_TYPE, ROW_VALUE) ' ||
             'VALUES (''' || j.coluna || ''', ' ||
                      '''' || j.tipo || ''', ' ||
                      ':NEW.' || j.coluna || ')';

      EXECUTE IMMEDIATE stm;
    END LOOP;
  END ICidade;

Share and enjoy.

EXECUTE IMMEDIATE with USING clause giving errors

The USING clause is only for bind variables (i.e. where you would use column names in a select statement), not table names. Typical usage would look like this:

Select col1 from table1 where col2 = :a

If you want to use variable table names use something like this:

         v_tab    := 'ex.emp';
         v_strSQL := 'SELECT * FROM ' || v_tab;
         EXECUTE IMMEDIATE v_strSQL;

Stored procedure execute Immediate error with WHERE clause

You would do best to use a bind variable in your insert statement. Also, you need to list the columns you're inserting into as well as those you're inserting, to avoid the "too many values" error.

Eg:

declare
  val_ID table2.V_ID%type := 1;
begin 
  execute immediate 'insert into table1 (col1, col2, ...) (select col1, col2, ... from table2 where V_ID = :val_ID)' using val_id;
end;
/

Although in this instance there is absolutely no need to use dynamic sql at all, so you could just do:

declare
  val_id table2.v_id%type := 1;
begin 
  insert into table1 (col1, col2, ...)
  select col1, col2, ...
  from   table2
  where  v_id = val_id;
end;
/

Don't forget to commit after you've run the procedure!

Exception dose not working in execute immediate insert in oracle

That's because you are not running a select command, it is an insert command (insert select) which means that if the select won't return rows it just doesn't insert anything and no error is thrown for that. You should check whether the insert command has affected any rows. The way you do that in Oracle is checking the SQL%ROWCOUNT immediate after the execution, if it turns to be 0 then you do your job raising an exception. It would be something like:

DECLARE
   customException EXCEPTION;
   PRAGMA EXCEPTION_INIT( customException, -20001 );

....
BEGIN
     EXECUTE IMMEDIATE 'INSERT INTO emp_tb (empno,ename,job,mgr,hiredate,deptno,dname,loc) 
                       ('||v_query|| ')';
    IF (SQL%ROWCOUNT) = 0 then
       raise_application_error( -20001, 'This is a custom error' );
    end if;
EXCEPTION
   WHEN customException THEN
      dbms_output.put_line('No data Found...');  
END;

It's a long time without programming in Oracle PLSql So somethings there on the provided code may not compile but it is all there look into it in the internet and you will be good.

ora-00900 invalid sql statement execute immediate

As the error says, execute immediate is not a SQL statement. There is an execute command in some clients - SQL*Plus, SQL Developer, SQLcl and possibly others - which is a shorthand wrapper around an anonymous block; but from the error you don't seem to be using one of those. If you were you'd get ORA-06550 and PLS-0010: Encounter edthe symbol "alter table..." when expecting...

execute immediate is a PL/SQL statement. So it is only valid within a PL/SQL block.

It has no meaning in plain SQL; the only execute in the SQL reference is referring to directory object privileges.

---

Your alter table doesn't make much sense anyway, not sure if that's supposed to be an update, but if so it isn't say what to set to that interval value. It isn't obvious why you need it to be dynamic either. Possibly that reason and the actual statement have been lost in simplifying for posting your question.

---

Related Topics