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
1
vote
2 answers

Trying to use a FORALL to insert data dynamically to a table specified to the procedure

I have the need to dynamic know the name of the table that has the same data structure as many others and I can pass in a generic associative array that is of the same structure. Here is the proc PROCEDURE INSRT_INTER_TBL(P_TABLE_NAME IN VARCHAR2,…
programmerNOOB
  • 121
  • 3
  • 19
1
vote
1 answer

Using UDT variable within an EXECUTE IMMEDIATE in a PL/SQL function

I am building a function on PL/SQL using Oracle 11g. I am trying to use a table variable within an EXECUTE IMMEDIATE statement, but it is not working, as you can see: ERROR at line 1: ORA-00904: "CENTER_OBJECTS": invalid identifier ORA-06512: at…
Siqueira
  • 423
  • 1
  • 7
  • 29
1
vote
2 answers

How to store the result of an execute immediate in PLSQL?

I have a table which has select queries and insert queries stored as varchars. I have to execute the select query and insert the result of the select query using the insert query using a procedure. Right now I'm executing immediate and bulk…
cumberdame
  • 41
  • 2
  • 9
1
vote
1 answer

Passing table name and column name dynamically to PL/SQL Stored procedure

I am trying to pass table name and column name to a stored procedure in oracle , but it gives me following error: table or view does not exist Below is the code: create or replace procedure jz_dynamic_sql_statement (p_table_name in…
akaminko
  • 71
  • 1
  • 2
  • 11
1
vote
1 answer

How to corrrectly use oracle EXECUTE IMMEDIATE in this code

i have following pl sql code that will extract a xml tag value and treat that value as stored function using oracle's EXECUTE IMMEDIATE statement: This code will find xml tag and extract its value 'get_val' (which it self is a stored…
user5005768Himadree
  • 1,375
  • 3
  • 23
  • 61
1
vote
3 answers

Generating Dynamic SQL in Oracle

I have a problem with my plsql code and try almost everything. Now i'm loosing my mind and power to solve my problem :) The case is that I want to search all tables schema for specific string assigned to variable v_ss and print it to DBMS_OUTPUT. I…
otemek
  • 67
  • 1
  • 9
1
vote
1 answer

How to use `execute immediate` to assign a value to a variable?

I am trying to use execute immediate as a special requirement for assigning the value of a variable. I am using the following code and getting the exception as below. declare lv_kyc_main_GBL KYC_GBL_MAIN.KYC_MAIN%rowtype; l_str varchar2(400); …
Aditya Raman
  • 309
  • 8
  • 19
1
vote
1 answer

How to add table name into dynamic query in execute immediate?

I'm confused, because I don't know how to use variable as string in execute immediate clause. declare variable1 varchar2(30): cur sys_refcursor; begin open cur for select tablename from table1; loop fetch cur into variable1; …
bazyl
  • 263
  • 1
  • 7
  • 17
1
vote
1 answer

Execute immediate statement

I'm trying to insert data into table using execute immediate statement. But I get an error FROM keyword not found where expected Could anyone take a look what's wrong? declare c1 SYS_REFCURSOR; v_tabl_name varchar2(30); begin open c1 for …
bazyl
  • 263
  • 1
  • 7
  • 17
1
vote
3 answers

Formatting execute immediate with to_date ORACLE

Am stuck on a formatting procedure within a package..... script works ok however integrating it with a package is proving difficult!! Am not used to oracle...I have script running but not in package...well not all of it...Drop Table worked CREATE OR…
Ggalla1779
  • 476
  • 7
  • 18
1
vote
1 answer

Need to use dynamic variables in where clause used in EXECUTE IMMEDIATE statement in ORACLE

ORACLE (using SQL DEVELOPER). I need to properly structure EXECUTE IMMEDIATE statement. I do not have "create" priveleges. The task is to get number of rows per table per date for dynamic list of tables/dates. I have the following: DECLARE CURSOR…
Elena LL
  • 13
  • 1
  • 4
1
vote
2 answers

PL/SQL - Create tables based on cursor using execute immediate?

I've written the following code that selects some student test data and using a cursor, inserts it into a table. What id like to be able to do is create one table for each student and insert their relative data. This could be one row or multiple…
bkm
  • 11
  • 2
1
vote
1 answer

Dynamically assigning variables oracle sql

I have a table attribute_config with below columns: table_name column_name key Let us say is has below 2 rows account accountphone accountnum customer customernumber customerid Key can be only accountnum or customerid. I have to write code…
user2503883
  • 23
  • 1
  • 6
1
vote
1 answer

How to do "Insert into... Select... in a PL/SQL block (IF/THEN)

I'm trying to run the script below in PL/SQL Developer, and I got an error says 'V_INSERT' is not a procedure or is undefined, and that statement is ignored. Anyone can help? Thanks! DECLARE chktime date; v_trunc varchar2(200); v_insert…
gcbm1984
  • 53
  • 1
  • 4
  • 9
1
vote
1 answer

how to retrive the value of variables outside dynamic pl-sql?

I perform the following query: declare i number; begin execute immediate 'select count(1) from someTable' returning into i; dbms_output.put_line(i); end; and get this Error: returning clause must be used with insert, update and delete!
user3620872
  • 41
  • 3
1 2 3
…
12 13