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

I need to stop an infinite while loop

while the script is running, it needs to be outside the input to catch the keyword and break the loop, I need to break the loop instantly with the 'esc' word, while the script waits for input by the user ¿how do i that? import time ; import keyboard…
Brandhorst Beaumont
  • 1
0
votes
1 answer

Oracle: Invalid ALTER command in execute immediate

In the procedure, in the ALTER command, I need to dynamically substitute the name of the trigger that needs to be activated. declare v_trg_name varchar2(25) := 'article_comment_audit'; begin execute immediate 'ALTER TRIGGER' || v_trg_name ||…
Yulia P
  • 5
  • 1
0
votes
1 answer

execute immediate with $$

I have a long query that returns 1 column and 1 row: Query 1: select test_query from ( SELECT LISTAGG('...') ... AS xx, LISTAGG('"' || c.COLUMN_NAME || '"', ', ') WITHIN GROUP(ORDER BY c.COLUMN_NAME) AS column_list ..... FROM…
x89
  • 2,798
  • 5
  • 46
  • 110
0
votes
1 answer

REPLACE a variable with another variable which is stored in a table/variable

I have a table VARIABLE_REPLACE DATA1 var TBR_SP lc_location I have a requirement if a string variable has 'TBR_SP' then that string should be replaced with the value of lc_location variable. declare str varchar2(1000):='TBR_SP123';…
Jaseer Valliyampurath
  • 1
0
votes
1 answer

Dynamic SQL Immediate Execute possible with HEX-code using UTL_RAW.CAST_TO_VARCHAR2?

I want to read different sql statements from a CLOB field to HEX-code I then want to cast the HEX-code back within an a sql-script to varchar2 and to execute it. Generating HEX-code and casting works, but it does not execute. Can anyone please help…
Cologne2202
  • 75
  • 5
0
votes
0 answers

How assigne value from execute immediate into variable

--create table locations_localtab as select * from HR.locations; SET SERVEROUTPUT ON; declare type tLOC_type is table of locations_localtab%rowtype index by binary_integer; tLOC tLOC_type := tLOC_type(); vPostal_code…
Smalllucjano
  • 3
  • 1
0
votes
2 answers

GRANT to EXECUTE execute immediate truncate table in Oracle

My user is owner of a simple schema in an instance of Oracle in my Job, let´s call my USER E, with some resctinct privileges. Also I have an USER E_ETL, to receive information of another database with ETL techonology. My user E is the owner of some…
Favini
  • 3
  • 4
0
votes
2 answers

Oracle compare two numbers in select

I would like to execute pl sql query below and get result of the comparison. EXECUTE IMMEDIATE 'select 5<6 FROM DUAL' ; Executing error is something like below: ORA-06550: line 1, column 18: PLS-00103: Encountered the symbol "select 5<6 FROM…
ilhan
  • 125
  • 2
  • 13
0
votes
1 answer

SQL Command Not Properly Ended for EXECUTE IMMEDIATE

I am trying to write a script that will end sessions. I get the SQL command not properly ended when trying to EXECUTE IMMEDIATE and I can't figure out what I am doing wrong and why I am getting the error message. I have removed the ; form the line…
Moxie C
  • 442
  • 1
  • 15
  • 32
0
votes
2 answers

execute immediate - ORA-00904: STRING: invalid identifier

I get the error ORA-00904: ggCategory: invalid identifier. If I run the select normally, it works without any problems and returns the correct values. Does anyone know where the syntax error is? execute immediate 'create table TEST_TABLE as ( …
user9715288
0
votes
2 answers

ORA-01008: not all variables bound - "not all variables bound"

This is my plsql block, dynamic SQL. I can't find a reason why it comes up with an error 'no ORA-01008: not all variables bound ORA-06512: at line 23'. I can't find the error on my EXECUTE IMMEDIATE statement. DECLARE form_name …
ebbie
  • 1
  • 3
0
votes
1 answer

Dynamically building DBMS_CLOUD.CREATE_EXTERNAL_TABLE, Execute Immediate doesn't work

I'm facing an issue when I'm trying to build a table dynamically using DBMS_CLOUD.CREATE_EXTERNAL_TABLE inside a stored procedure or packaged program. A dbms_ouput.put_line of the dynamic code will produce the correct code which i can copy and run…
B-Rad
  • 25
  • 6
0
votes
2 answers

execute immediate for tables having number in their names

I have a execute immediate in my package and when the v_object_name is something like 20200823_AGL, I get this error: ORA-00903: Invalid table name How can I fix it? EXECUTE IMMEDIATE 'SELECT MAX(LAST_UPDATE),COUNT(*) FROM ' || v_object_name ||…
mona shiri
  • 57
  • 8
0
votes
1 answer

[PL/SQL]EXECUTE IMMEDIATE CREATE INDEX is failing with invalid CREATE INDEX option

For every table_name I need to create index based on index_required returned from specified query I wrote below PL/SQL Procedure: DECLARE sIndexRequired VARCHAR(50); sTableName VARCHAR(50); cSQL CLOB ; BEGIN FOR r IN [...]…
MichalAndrzej
  • 77
  • 1
  • 11
0
votes
1 answer

How to resolve issue of stored proc not executing dbms_stats.gather_table_stats() when executing as it string via EXECUTE IMMEDIATE

In my stored procedure, I am using "EXECUTE IMMEDIATE" to execute statement that is in string format but this is not generating the result. I have added proc below: CREATE OR REPLACE PROCEDURE sp_TEST(tablenamestring Varchar2) IS ownerName…
CodesDDecodes
  • 132
  • 1
  • 15
1 2 3
…
12 13