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

Dynamic INSERT Fails in procedure, but works as static SQL

I am working on a procedure to transpose data from a large matrix into a table consisting of three columns. I'm having some difficulty dynamically inserting rows into the table. When I try to execute the procedure block below, I get an error…
user3672527
  • 55
  • 1
  • 5
0
votes
1 answer

"ORA-01007: variable not in select list" when no rows are returned by EXECUTE IMMEDIATE

I have a procedure which receives as parameter a where clause (i.e. where col1 = 1). I am using this clause to search in some tables using an EXECUTE IMMEDIATE statement and the result to be inserted into a nested table, and than be displayed. The…
mikcutu
  • 1,013
  • 2
  • 17
  • 34
0
votes
0 answers

Dynamic SQL with variable amount of parameters

I know the following works (this would be part of the code inside a stored procedure): sql_string := 'INSERT INTO my_table (data_id, data_col) SELECT SYS_GUID(),''{"json_id1":'' || (:value_1 - val_from_view_1 + :i - 1) || ''}'' FROM my_view WHERE…
Octavio Kidd
  • 5
  • 4
0
votes
1 answer

Execute Immediate in Oracle 11g

I am trying to create a script in oracle to search through a number of tables and insert into a temp table i am creating but i am getting a few errors when trying to do anything other than a number. Declare Variables m_polCount NUMBER:= 0; …
PowPowPowell
  • 255
  • 1
  • 2
  • 11
0
votes
1 answer

Procedure which gives grants, Invalid table name error

I'm trying to create a procedure which gives grants to specific schema objects. Procedure seems to compile ok, but it gives an error when execution. Something is wrong in this simple procedure but I cannot find the reason for this.…
jrara
  • 16,239
  • 33
  • 89
  • 120
0
votes
4 answers

ORA-01403: No Data found WHY?

I have declared the following procedure: CREATE OR REPLACE PROCEDURE MODIFY_NOT_NULL( v_tbName IN VARCHAR2, v_cName IN VARCHAR2, v_defaultValue IN VARCHAR2 ) IS v_is_null VARCHAR2(1); BEGIN SELECT…
NoName123
  • 137
  • 5
  • 20
0
votes
1 answer

Execute immediate in netezza stored procedure is not inserting value to a table

When I am running this Netezza stored procedure, I am getting an error attribute 'SOME_VALUE' not found As per requirement I have to get value from one table (TABLE_A) and insert into another table (TABLE_B). This is the procedure: create or…
VivekT
  • 81
  • 2
  • 13
0
votes
3 answers

sql select statement as bind variable for dynamic plsql block

I am trying to run plsql anonymous block using execute immediate and the plsql block contains a bind variable for which the value is a sql select statement. But it seems this does not work. Is there any solution for to solve this. E.g. BEGIN …
PTK
  • 307
  • 4
  • 19
0
votes
0 answers

How to execute script conditionally in oracle?

I have a long DDL data migration script which has 15 transaction blocks. For each block, it takes data from one table and then inserts into another table. But now I have come across a scenario in which the database does not have that table. In this…
Udit Mishra
  • 150
  • 11
0
votes
1 answer

PL SQL, Error(32,43): PLS-00201: identifier 'HR' must be declared

I'm trying to execute the following procedure EXECUTE StudentNames(12345, true) I'm getting the below error; Error(32,43): PLS-00201: identifier 'HR' must be declared It's linked to this part of the code: IF p_bool AND v_studid = 12345…
saggart
  • 25
  • 6
0
votes
3 answers

Execute Immediate in oracle

I have below query which gives an error as encounter an symbol ( in the line where loop is used. I am trying to develop a function which takes dynamic paramater as table_name,column_name,table_id and used for other tables as well. FUNCTION…
Andrew
  • 3,632
  • 24
  • 64
  • 113
0
votes
0 answers

INSERT after EXECUTE IMMEDIATE in oracle

I am trying to run execute immediate statement to create a table and after that I want to insert data to it but it shows me an error that my table doesn't exist: DECLARE liczba NUMBER :=5; BEGIN IF liczba > 1 THEN EXECUTE IMMEDIATE…
SliceOfPig
  • 123
  • 3
  • 10
0
votes
2 answers

Oracle PL SQL - Issue with EXECUTE IMMEDIATE

DECLARE start_date VARCHAR2(12); end_date VARCHAR2(12); start_epochtime VARCHAR2(15); end_epochtime VARCHAR2(15); v_sql VARCHAR2(1024); BEGIN SELECT to_char(current_date,'YYYY-MM-DD') into start_date from dual; SELECT…
Rajini Rajas
  • 1
0
votes
2 answers

pl/sql : execute immediate update?

V_SQL4 := 'UPDATE EMP_TABLE m Set m.name = mft.name, m.age = mft.age, m.dept = mft.dept, Where m.id = mft.id and (m.name != mft.name Or m.age != mft.age Or m.dept != mft.dept…
kmy
  • 55
  • 6
0
votes
1 answer

Stored procedure execute Immediate error with WHERE clause

I am trying to copy over one row from my archive table to my original table. Without my WHERE clause, the whole table of table2 gets copied to table1. I don't want this of course. So based on the gridview's ID value listed, the table will copy over…
narue1992
  • 1,143
  • 1
  • 14
  • 40
1 2 3
…
12 13