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
0 answers

Can we use collection variable as a table in execute immediate select statement?

I have written below code. Declare Type t1 is record (id, type) Type t2 is table of t1; V_val t2; Begin Select a.id, a.type bulk collect into v_val from table1 a where exits(select 1 from table2 b where a.column =…
Kirti Waghmare
  • 19
  • 2
1
vote
2 answers

Why I can't use a bind variable in an execute immediate statement?

I'd like to use bind variables instead of a string concatenation when I build up a dynamic SQL statement for execute immediate. In the example below I can use bind variables for a, b and ret, but when I try to bind for f I get a ORA-06502: PL/SQL:…
user272735
  • 10,473
  • 9
  • 65
  • 96
1
vote
1 answer

GBQ Execute Immediate Save Results to Table

Hi All I am trying to same the results from this query into a table on GBQ. I have linked the previous question for reference GBQ Convert Data types en Mass Starting Code execute immediate (select ''' select `Group`, ''' || (select…
UserX
  • 157
  • 13
1
vote
0 answers

UPDATE Statement doesn't update any row

I got two tables (ORACLE): D_CONTROL_CARGA and F_PS_CARGA_DIARIA_D SELECT control_id, control_cd, control_query_tx FROM ONHR_DIM.D_CONTROL_CARGA; SELECT FECHA_ID, CONTROL_ID, CONTROL_CD, CANTIDAD_CA FROM onhr_dim.F_PS_CARGA_DIARIA_D; I want to…
Nacho Monetto
  • 11
  • 1
1
vote
2 answers

How to use a field from an input parameter of a HANA stored procedure to generate table name dynamically for execute_immediate statement?

I have been trying to solve a requirement with no luck where I have to pass a table containing 3 fields: object name, customer & location from an AMDP to a stored procedure. The stored procedure should be used to return a table that stores the same…
abhishek hazra
  • 13
  • 3
1
vote
2 answers

How update a table in Big Query where the name of fields to update are values in another table

Folks! I need some ideas, with the follow problem: I have two tables: Table 1: +-------+------------+---------+ | ID | field_name | value | +-------+------------+---------+ | 1 | usd | 10.08 | | 1 | gross_amt | 52.0 | | 1 …
xarc
  • 213
  • 2
  • 14
1
vote
1 answer

Error numeric overflow oracle when call execute immediate

I define a procedure do parse a formula (type value String) to a value number: Some case value in formula can execute or raise error numeric overflow. Code : DECLARE formula VARCHAR2(1000) := '1111111111 * 2'; val NUMBER; BEGIN EXECUTE…
Phan Kieu Hung
  • 123
  • 1
  • 7
1
vote
2 answers

Why am I getting ORA-00900 Invalid SQL statement when trying to create a directory object with execute immediate?

I see many examples of this being possible in Oracle. It's just not working for me. Oracle 11. I am getting this error on line 15. Thank you all! declare v_path nvarchar2(256); v_object_exists number; begin -- Use the directory Oracle DB…
sois
  • 55
  • 6
1
vote
0 answers

I am getting an error due to variable when I execute stored procedure which dynamic PLSQL inside was there in the stored procedure

When I execute stored procedure which contain EXECUTE IMMEDIATE along with create table statement inside the stored procedure then getting error while execute this stored procedure. Here is the stored procedure - create or replace sp_exec as …
Md Wasi
  • 479
  • 3
  • 16
1
vote
2 answers

Manage the parallel degree of sql execution

I have a process that sends a lot of commands to be executed with parallel. exmaple of command: Insert /*+ parallel(16) */ ... The problem is that if the requested parallel is higher than the available servers, the command gets downgraded in the…
user2671057
  • 1,411
  • 2
  • 25
  • 43
1
vote
1 answer

Stored Procedure using Execute Immediate with binding

I have a working Stored Procedure and would like to improve/simplify it. CREATE OR REPLACE PROCEDURE DOCSADM.DRILL_COUNTV5 ( IN_TABLE IN VARCHAR2, IN_TYPE IN VARCHAR2, OUT_COUNT OUT NUMBER) AS BEGIN EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM '…
Fering
  • 322
  • 2
  • 18
1
vote
1 answer

PL/SQL Concatenation

i have a procedure that have in entry two parameters CREATE_PARTITION( yearSource IN VARCHAR2 , yearDestination IN VARCHAR2 ) when i want to insert the yearSource concatenated with another string , nothing is inserted in the table i declare the…
MsIsem
  • 23
  • 8
1
vote
1 answer

Dynamic SQL syntax using EXECUTE IMMEDIATE

Dynamic SQL update statement as below: EXECUTE IMMEDIATE 'UPDATE '||l_prefix||'CRS_CUSTOMERS SET CUSTOMER_SOURCE_REF_ID = '||i.CUSTOMER_REF_ID||' WHERE CUSTOMER_ID = '||i.CUSTOMER_ID; l_prefix is the parameter hold the prefix of table name, the…
user2102665
  • 429
  • 2
  • 11
  • 26
1
vote
0 answers

PL/SQL: Use parameters within execute immediate

I have a function like this (unfortunately, I cannot change it to send one array instead of multiple parameters): CREATE FUNCTION (p1_ IN OUT VARCHAR2(100), p2_ IN OUT VARCHAR2(100), <...>, p10_ IN OUT VARCHAR2(100) IS BEGIN gather_value(p1_); …
paulauskas96
  • 19
  • 3
1
vote
2 answers

Equivalent to SQLERRM when no error?

Does anyone knows if there is an equivalent to SQLERRM when no error occurred? I know I can count the affected rows like here, but can I automatically get the message I would get from SQLPLUS e.g. role granted. after an execute immediate in…
J. Chomel
  • 8,193
  • 15
  • 41
  • 69
1 2 3
…
12 13