Stack Overflow
Stack Overflow

Questions tagged [execute-immediate]

An Oracle statement to execute a dynamic query or anonymous PL/SQL block.

The EXECUTE IMMEDIATE statement can be used within PL/SQL to build and run dynamically generated SQL.

Dynamically generated SQL can be vulnerable to SQL Injection. To guard against this bind variables and the system package dbms_assert should be used.

Questions tagged should normally also be tagged and / or .

Further Reading

188 questions
3
votes
3 answers

How to store and populate data returned by Execute Immediate in Oracle?

In my project , i am trying to execute the following query : DECLARE Sid nvarchar2(30) := ''; /*Here the values will come from some other variable>*/ Bid nvarchar2(30) := ''; /*Here the values will come from some other variable>*/ ExecuteDSQL…
FullStack
  • 665
  • 11
  • 26
3
votes
4 answers

Oracle EXECUTE IMMEDIATE changes explain plan of query

I have a stored procedure that I am calling using EXECUTE IMMEDIATE. The issue that I am facing is that the explain plan is different when I call the procedure directly vs when I use EXECUTE IMMEDIATE to call the procedure. This is causing the…
Gunny
  • 164
  • 2
  • 11
3
votes
2 answers

EXECUTE IMMEDIATE Temp table in oracle does not get created ORA-00942

Based on this answer I was trying to create temp table, however I am getting exception ORA-00942:table or view does not exist I would assume there is something wrong with 'CREATE OR REPLACE GLOBAL TEMPORARY TABLE TempQandA(column1 number) ON COMMIT…
Matas Vaitkevicius
  • 58,075
  • 31
  • 238
  • 265
3
votes
1 answer

Oracle Execute Immediate with DDL and Nested table

I have a problem trying to use an Execute Immediate statement containing a CREATE TABLE statement and a user defined Table Type. I get error ORA-22905 on Oracle 11g. Is there any workaround to solve this issue? CREATE TYPE MY_TABLE_TYPE AS TABLE OF…
andreaplanet
  • 731
  • 5
  • 14
3
votes
2 answers

EXECUTE IMMEDIATE ' some commands '

Is it possible to execute some sql commands within one EXECUTE IMMEDIATE block? What is wrong with this syntax: declare pragma autonomous_transaction; begin execute immediate 'begin COMMENT ON TABLE t1 IS…
Nikita
  • 422
  • 5
  • 14
2
votes
1 answer

How to EXECUTE a STRING in Snowflake

Following up from this thread with a as ( select * from hubspot.information_schema.tables where table_catalog = 'HUBSPOT' AND TABLE_SCHEMA = 'MONGODB' and table_name != '_SDC_REJECTED' and table_type = 'BASE TABLE' ), b as ( select * , …
MC7836
  • 84
  • 8
2
votes
1 answer

How to execute a dynamic sql statement in sql*plus

I would like to modify a value by using a dynamic sql statement. The example is choosen to be reproductible. I know that I don't need a dynamic sql statement for that. variable a number =1; print a 1 exec execute immediate 'select 2 into :a from…
Pierre-olivier Gendraud
  • 1,545
  • 1
  • 8
  • 33
2
votes
1 answer

use of using EXECUTE IMMEDIATE for an DML Statement in Stored Procedure

I am new to PL SQL Procedures , we have this line of code inside the Execution section of a Stored Procedure . I am having a query here , please tell me whats the use of using EXECUTE IMMEDIATE for a DML Statement Here ? and in what cases we should…
user663724
2
votes
1 answer

Assigning multiple fields in a loop using execute immediate

I am using PLPDF's libraries to create spreadsheets for various files - I am trying to get a procedure written to take the values of each field and one-by-one insert them into the spreadsheet. This operation can include many different tables going…
Ken Sheldon
  • 23
  • 3
2
votes
2 answers

Oracle SQL "EXECUTE IMMEDIATE" ORA-00922/06512 Create Table

i searched a lot and many people got similar problems, but nothing seemed to match. This is no problem: create table CUSTOM_WORKFLOW_ID_COUNTER ( id_ NUMBER (19) GENERATED by default on null as IDENTITY, type_ VARCHAR2(50 CHAR), …
SWYM
  • 43
  • 1
  • 8
2
votes
1 answer

referencing new variable using execute immediate in trigger

I'm trying to reference my new variable from dynamic sql. If I try select :NEW.zh_naam into v_var from dual;, and I print out my variable, averything works perfectly. But when I try to use dynamic sql, like this execute immediate('select…
Walle
  • 540
  • 1
  • 9
  • 32
2
votes
2 answers

get dbms output message from oracle in php

CREATE OR REPLACE PACKAGE BODY simpleState IS PROCEDURE selectFromTable(tableName VARCHAR2, columnName VARCHAR2) IS TYPE c1 IS TABLE OF VARCHAR2(30); Notes c1; BEGIN EXECUTE IMMEDIATE 'Select ' || columnName ||…
student0495
  • 171
  • 3
  • 15
2
votes
1 answer

Is UPDATE and CONNECT BY supported?

I created a procedure using execute immediate, when I debug and step over to execute immediate in this procedure, it shows the following message : An Oracle exception has occurred in your code. If the code contains an exception handler, you can…
Ade Rianto
  • 31
  • 6
2
votes
1 answer

Incremental commit while using execute immediate when table name and column name are dynamic

I've the need to update a few tables. The tables and columns to be updated will be fetched from another table. So my update statement in the procedure looks like this EXECUTE IMMEDIATE 'UPDATE ' || In_owner || '.' ||…
Swapna Mohan
  • 75
  • 1
  • 12
2
votes
1 answer

Substitute dynamic operators in where clause instead of manual operators

Oracle sql developer, Substitute dynamic operators in where clause instead manual operators I am writing procedure that takes ARGUMENT which has set of operators. that dynamic operator i just want substitute in where clause. kindly guide me how i…
Jambu
  • 21
  • 1
1
2
3
…
12 13