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
2
votes
1 answer

Prompt a user for input in oracle

How to prompt a user for input in oracle using SQLTools v1.6? I tried the following PL/SQL block: DECLARE type tes_tab is table of test_table%rowtype; test_tab tes_tab; BEGIN execute immediate 'SELECT * FROM test_table WHERE memid=' ||…
hsuyaa
  • 41
  • 2
  • 11
2
votes
4 answers

Getting ORA-01747: invalid user.table.column, table.column, or column specification in Oracle when inserting data using a dynamic insert

Here is the code. p_id number, p_mc varchar2 Both of the above parameters will be passed in during a procedure call. EXECUTE IMMEDIATE 'INSERT INTO COUNT_MASTER_TEMP ' || 'SELECT COUNT (ar.'|| p_mc || ')' || ' FROM app_recipient…
Burhan Khalid Butt
  • 275
  • 1
  • 7
  • 20
2
votes
1 answer

Why can't I use Boolean data type with Execute Immediate?

DECLARE dyn_stmt VARCHAR2(200); b1 boolean; FUNCTION f(x INTEGER) RETURN boolean AS BEGIN RETURN FALSE; END f; BEGIN dyn_stmt := 'BEGIN :b := f(5); END;'; EXECUTE IMMEDIATE dyn_stmt USING OUT b1; --line 9 b1:=f(5); --line 10 END; Commenting line…
Ravi
  • 30,829
  • 42
  • 119
  • 173
2
votes
2 answers

Execute immediate can't bind result into a variable

Without into tables_found procedure will run but i need to check if this table exist in my database . CREATE OR replace PROCEDURE dropdb(tables_found out number) IS BEGIN execute immediate 'SELECT…
gtzinos
  • 1,205
  • 15
  • 27
2
votes
2 answers

Oracle SQL: Use outer loop identifiers in inner loop in execute immediate

I have to execute 32 times a very similar operation, that is setting the value of a column in a row for a given record (for a given quarter). To simplify my code and thrive for beauty, I wanted to use a for loop with an execute immediate, using…
Stephane Dubois
  • 21
  • 4
2
votes
2 answers

PL/SQL EXECUTE IMMEDIATE inside LOOP (procedure to truncate all tables in schema)

I need to create procedure which will delete all data from tables in one schema. I try something like that CREATE OR REPLACE PROCEDURE CLEAR_ALL IS sql_truncate VARCHAR2(50); cursor c1 is SELECT table_name FROM all_tables WHERE owner…
Karol Chudzik
  • 91
  • 1
  • 1
  • 10
2
votes
2 answers

error in EXECUTE IMMEDIATE insert ORACLE

good night. I have a problem with the EXECUTE IMMEDIATE in oracle. I look in others topics, but no answer was helpful. This is the code: First, the table that i need insert inside of trigger... create global temporary table TEMP_PK (COL_NAME…
Paula Simiqueli
  • 35
  • 1
  • 9
2
votes
1 answer

run string as query in oracle

i got a little problem in Oracle. I try to create a sequence for generating IDs in a table that already has data in it. I try to use the following anonymous block. declare y varchar2(2000); BEGIN SELECT 'CREATE SEQUENCE ID_SEQ MINVALUE 1 MAXVALUE…
mitereiter
  • 35
  • 1
  • 4
2
votes
1 answer

PL/SQL Execute immediate exception handling inside for loop

In the below PL/SQL code, TABLE_ONE holds table name tname , column name cname and rowid rid. The For loop fetches records from TABLE_ONE and updates column cname in table tname for the record with row id rid. But if the record to be updated in…
user194210
  • 33
  • 1
  • 1
  • 5
2
votes
2 answers

How to Delete/Insert from different Tables and Views

I've got a wicked problem. In Oracle 10 there are pairs of Views and Tables where something like that is done over and over again: proc_log('DELETE 1'); DELETE FROM table_1; proc_log('INSERT 1'); INSERT INTO table_1 SELECT * FROM…
Joshua
  • 2,932
  • 2
  • 24
  • 40
2
votes
3 answers

execute immediate truncate table in sqlplus

Why does execute immediate 'truncate table trade_economics'; in a sqlplus script give the following error ? BEGIN immediate 'truncate table trade_economics'; END; * ERROR at line 1: ORA-06550: line 1, column 17: …
Vishal Saxena
  • 137
  • 2
  • 2
  • 6
2
votes
3 answers

PL/SQL - execute immediate in pipelined function

I want to execute dynamic query in my pipelined function and return results of this query. Is it possible to do this? Pipelined function is convenient for me to achieve good interface for my application cause it behaves like a table. The…
dzb
  • 61
  • 1
  • 1
  • 7
1
vote
2 answers

Is there a way to execute code dynamically in MySQL, similar to "execute immediate" in Oracle?

Like EXECUTE IMMEDIATE in Oracle, is there any way to execute code dynamically in a MySQL stored procedure? I really want to use a prepared statement within a MySQL stored procedure, to generate a new SQL statement in each iteration of a loop.
Eric_Chen
  • 227
  • 1
  • 4
  • 13
1
vote
0 answers

GBQ Execute Immediate into a CTE

I am building an application/script for users that do not have write access to the database. Normally I would use Execute Immediate and save that result into a table, and then pull from that table and continue on with the script. Is there a way to…
UserX
  • 157
  • 13
1
vote
1 answer

BigQuery Execute Immediate Identifier issue

I am creating a BQ Stored Procedure to truncate all the tables in a dataset. I have a 2 step process. Step 1 identifies all the matching tables. Step 2 is expected to iterate thru each table and truncate. I have the following code to achieve…
Ravi
  • 11
  • 2
1 2
3
…
12 13