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

167 questions
44
votes
5 answers

Using bind variables with dynamic SELECT INTO clause in PL/SQL

I have a question regarding where bind variables can be used in a dynamic SQL statement in PL/SQL. For example, I know that this is valid: CREATE OR REPLACE FUNCTION get_num_of_employees (p_loc VARCHAR2, p_job VARCHAR2) RETURN NUMBER IS …
BYS2
  • 4,870
  • 4
  • 20
  • 31
14
votes
1 answer

Why cannot I use bind variables in DDL/SCL statements in dynamic SQL?

I am trying to execute an SQL command within dynamic SQL with bind variables: -- this procedure is a part of PL/SQL package Test_Pkg PROCEDURE Set_Nls_Calendar(calendar_ IN VARCHAR2) IS BEGIN EXECUTE IMMEDIATE 'ALTER SESSION SET…
sampathsris
  • 19,015
  • 10
  • 59
  • 90
5
votes
3 answers

Immediately self executing function and "this"

I want to create a javascript library, so I thought making it an immediately self executing function would be a nice thing to do to ensure scope safety and everything. But now I'm running into a problem with using the "this" keyword that I don't…
F.P
  • 15,760
  • 32
  • 114
  • 184
5
votes
1 answer

Invalid table name error while using Execute Immediate statement with bind variables

I'm trying to get this dynamic SQL running ( using EXECUTE IMMEDIATE) M_SQL_STATEMENT := 'SELECT MAX(:m_var1)+1 from :m_var2 RETURNING MAX(:m_var1)+1 INTO :m_var3'; EXECUTE IMMEDIATE M_SQL_STATEMENT USING M_COLUMN_NAME, UPPER(P_TABLE_NAME),…
Sathyajith Bhat
  • 19,739
  • 21
  • 90
  • 126
5
votes
4 answers

Execute Immediate fails even with CREATE table grant

I have a problem where I am creating a table using the execute immediate command in the stored procedure. However I get the error of "insufficient privileges". I checked other threads and made sure that the user has "CREATE TABLE" privilege granted…
Sameervb
  • 329
  • 2
  • 4
  • 13
5
votes
1 answer

Difference between EXEC_SQL, EXECUTE IMMEDIATE, DBMS_SQL and inline SQL

I've been going over some PL/SQL (In Oracle SQL Developer), and have seen several different formats of SQL being called. For the consistency and speed of current and future code, I'd like to know which is the preferred choice. There are four types…
Addison
  • 5,106
  • 2
  • 31
  • 49
5
votes
2 answers

Assign value to a field of rowtype where `field name` is a string

I want to assign a value to a rowtype's field but I don't know how to do it. Suppose that I have a table X inside my database. Suppose also that I have the following variables a ( X%ROWTYPE ), representing a row of the table X b ( VARCHAR2 ),…
Backslash36
  • 550
  • 1
  • 9
  • 23
5
votes
1 answer

What does := mean in oracle when we use it

What does := mean in oracle when we use it Please give me some demonstrations... and also how do we usually use a dynamic query in a stored procedure in oracle...
Mainakh Bhattacharjee
  • 73
  • 1
  • 1
  • 6
4
votes
3 answers

Why EXECUTE IMMEDIATE is needed here?

I am a SQL Server user and I have a small project to do using Oracle, so I’m trying to understand some of the particularities of Oracle and I reckon that I need some help to better understand the following situation: I want to test if a temporary…
Rafael Merlin
  • 1,997
  • 19
  • 29
4
votes
2 answers

execute immediate alter sequence not working

I'm stuck on this pretty simple script. It isn't working like I expect it to. declare st VARCHAR(1024); begin for x in (SELECT sequence_name FROM USER_SEQUENCES) loop st := 'ALTER SEQUENCE ' || x.sequence_name || ' INCREMENT BY 1000'; …
Alexander.Iljushkin
  • 4,335
  • 5
  • 27
  • 45
4
votes
2 answers

EXECUTE IMMEDIATE with multiple lines of columns to insert

just want to get an idea if this is the correct way to do an EXECUTE IMMEDIATE with multiple columns and lines and assigning it to a variable? I tried looking at examples but am not sure if I am concatenating the lines correctly? sql_stmt …
user1941350
  • 65
  • 1
  • 3
  • 9
3
votes
2 answers

Ref cursor with Execute immediate

I want to get the results in ref_cursor, but I am not able to do that. Please suggest me how to get the results in ref_cursor using Execute immediate CREATE OR REPLACE PROCEDURE TEST_PROC_QT ( p_name IN VARCHAR2, …
Naveen Chakravarthy
  • 779
  • 1
  • 12
  • 29
3
votes
2 answers

Oracle PL/SQL Release 12.2.0.1.0 vs 12.1.0.2.0 - execute immediate with parameters

DECLARE max_id INTEGER; BEGIN SELECT MAX(ID) + 1 INTO max_id FROM MY_TABLE; EXECUTE IMMEDIATE 'CREATE SEQUENCE MY_TABLE_ID MINVALUE 1 MAXVALUE 99999999999999 INCREMENT BY 1 START WITH ' || max_id || ' CACHE 100 NOORDER NOCYCLE …
pahan
  • 547
  • 1
  • 7
  • 22
3
votes
1 answer

How to propagate globally defined exception through execute immediate?

Here is a minimal test case that for some reason fails with ORA-06510: PL/SQL: unhandled user-defined exception CREATE PACKAGE my_test AS global_exception EXCEPTION; END; / set serveroutput on; BEGIN execute immediate 'BEGIN RAISE…
jva
  • 2,759
  • 1
  • 22
  • 41
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
  • 615
  • 7
  • 26
1
2 3
…
11 12