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

How to create an create user in Oracle using Execute Immediate?

My database is an XE 18. My user has create user and I can create an user in SQL Plus, using this : ALTER SESSION SET "_ORACLE_SCRIPT" = true; CREATE USER auxiliar IDENTIFIED BY auxiliar2020 ; Then I create a package with a function that should be…
Arlen Andrade
  • 1
0
votes
1 answer

Quote issue during execute immediate

Getting error for syntax for the execute immediate select distinct hire_date BULK COLLECT into v_yr from employees; for i in 1..v_yr.count LOOP v_1:='select * from employees where EXTRACT(YEAR FROM TO_DATE(HIRE_DATE,''' …
TBose
  • 115
  • 2
  • 10
0
votes
1 answer

Creating a sequence inside an anonymous block using Execute Immediate

I'm trying to create a sequence inside an anonymous block (it's an script that will be launched on several environments) and I'm using execute immediate, this is the code: SELECT MAX(ID_VINCULACION) INTO vMAX_VINCULACION FROM…
robexpo
  • 3
  • 1
0
votes
1 answer

concatenate VArray to String and use in dynamic SQL - Oracle

I need to dynamically create a query and execute using Execute Immediate, I am facing the problem in appending the Vaaray variable. Getting Error pls-00306 wrong number or types of arguments in call to || Vaaray //Its a type number select ver_id…
Siva
  • 9,043
  • 12
  • 40
  • 63
0
votes
1 answer

how to resolve error while using grant admin option in execute immediate

I am trying to run the below script. My script works fine without ' WITH ADMIN OPTION' in EXECUTE IMMEDIATE. But when using ' WITH ADMIN OPTION' i get below error. "Error report - ORA-00900: invalid SQL statement ORA-06512: at line 17 00900.…
Sangathamilan Ravichandran
  • 351
  • 4
  • 20
0
votes
2 answers

How to convert strings into column names in Google Bigquery?

I have a large data set in Google BigQuery with millions of rows of dirty data (App tracking) that I am trying to clean up. One of my problems is that the same data got sent to different columns for different events triggered in the App. By this I…
Daniel
  • 1,005
  • 1
  • 16
  • 22
0
votes
1 answer

Need to iterate the result and then execute immediate each result using pl/sql

So I have this query: select 'alter index '||a.index_owner||'.'||a.index_name|| ' rebuild partition '||a.partition_name from dba_ind_partitions a where a.index_name in ('IDX_PI_T_BSCS_CONTRACT_HISTOR2', 'IDX_PI_T_BSCS_CONTRACT_HISTOR3', …
joao ferreira
  • 3
  • 1
0
votes
1 answer

ORACLE Using SERIALIZABLE to PL/SQL for download consistent data

For download consistent data from OLTP-database make proc: procedure move_tables_to_mst_layer(v_sync sync_data) is pragma autonomous_transaction; v_src_table varchar2(128); sql_stm varchar2(30000); v_proc …
Igor Egorov
  • 9
  • 4
0
votes
0 answers

Using Execute Immediate to insert the data by querying the XML whose size > 4 MB

I am currently unable to insert the data into a table by querying an XML whose file size is > 4 MB in PL/SQL. Please advice. Code : DECLARE vTable VARCHAR2(400); vExecSQL CLOB; vInsColNames VARCHAR2(32767); vSelColNames…
Pradeep
  • 3
  • 1
  • 3
0
votes
1 answer

Creating a database view from a dynamic string (EXECUTE IMMEDIATE) in a PL/SQL package - Questions?

I want to create a dynamic view at runtime made up of string of columns and a where clause using EXECUTE IMMEDIATE on one database which will be queried on a second database using a db_link. My question are the following. The view will be queried…
Shaun Kinnair
  • 495
  • 2
  • 10
  • 27
0
votes
1 answer

SQL Error: ORA-00984: column not allowed here 00984. 00000 - "column not allowed here"

i am getting error: Error at Command Line : 45 Column : 111 Error report - SQL Error: ORA-00984: column not allowed here 00984. 00000 - "column not allowed here" query: Insert into BL_DIFF_QUERY_ANL…
Ankit Kumar
  • 1
  • 2
0
votes
1 answer

Better solution of switch case

Hello I need help about a Switch case better solution on oracle . So I have a function FUNCTION GETSTAT(i_json_stats clob) RETURN CRESPONSE AS BEGIN IF JSON_GET_STAT IS NOT NULL THEN FOR i IN 1..JSON_GET_STAT.count LOOP V_FUNCTION…
DEVLOGIN
  • 87
  • 1
  • 9
0
votes
1 answer

How to fetch data from multiple Database Links and insert it into specific tables using LOOP

I do have Oracle DataBase which has multiple DB_LINKS. These DB_Links are stored in local (DB_LINKS_TBL)table. The target is to synchronize the remote data with local machine. I did the task using LOOP and REF_CURSOR insert but cannot insert…
Md Faramawy
  • 17
  • 3
0
votes
1 answer

Oracle dynamic SQL: UDF within execute immediate

I am trying to make some of my code dynamic. While typing the question how to use UDFs in dynamic SQL, I figured out the answer: One can call the UDF from outside! This works: Update my_table Set col1 = get_some_value(col2,col2) Where 1 = 1; This…
Peter Frey
  • 361
  • 4
  • 17
0
votes
1 answer

how to execute immediate sql string to shows result direcly?

I'm trying to execute sql string be executed like this image1 but. when I execute immediate my sql string, it doesn't return to any output like the first one. how can I make it return the output without pass the output into a variable? please help
Hanani Rafei
  • 43
  • 1
  • 4
1 2 3
…
12 13