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

Execute immediate fills up the library cache

I have a question regarding how queries executed through 'execute immediate' is treated in the library cache (We use Oracle 11). Let's say I have a function like this: FUNCTION get_meta_map_value ( getfield IN VARCHAR2, …
EBi
  • 11
  • 3
0
votes
1 answer

How to write a procedure to execute set of queries automatically

I have written a procedure to grant permissions on all the tables of a particular schema to rest other schemas. create or replace PROCEDURE GRANTS_PROC IS CURSOR GRANTS_CURSOR IS SELECT 'GRANT SELECT, INSERT, UPDATE,…
sabarish jackson
  • 149
  • 3
  • 10
0
votes
3 answers

How to resolve ORA-00933: SQL command not properly ended in oracle?

Here is the package created by passing 3 input parameters to function CREATE OR replace PACKAGE "PKG_CAMPAIGN_EMAIL_QTY" AS FUNCTION Getcampaignoutgoingemailqty( tablename IN VARCHAR2, ActivatedDate IN DATE, CompletedDate IN DATE) …
Rahmath
  • 17
  • 1
  • 1
  • 8
0
votes
2 answers

How do I work with a table column that contains SQL statement strings that need to be executed?

I need to build a PL/SQL procedure that takes data from a source table and inserts it into a target table. The source table has an ITEM1 column, an ITEM2 column, and a SRC_CODE column. The SRC_CODE column contains a string that is a SQL Select…
cddau
  • 21
  • 3
0
votes
2 answers

Execute immediate 'Create or replace view' gives 00900. 00000 - "invalid SQL statement" error

I have been stuck with this error. Can you please suggest what the mistake is in the code below? This is written inside a procedure that takes input for the variables. Thanks. execute immediate 'Create or replace view '||p_viewname||' AS …
P Mangu
  • 1
  • 1
  • 3
0
votes
3 answers

Can you exit a PL-SQL loop from within an execute immediate statement?

I have the following snippet (simplified to exclude extraneous details): <> LOOP fetch c1 into somerecord; EXECUTE IMMEDIATE 'begin EXIT cursor_loop WHEN 1 = 1; end;'; END LOOP cursor_loop; When I run this, it fails with a…
John O
  • 4,863
  • 8
  • 45
  • 78
0
votes
1 answer

Dynamic Query Error in PLSQL

I am trying to execute this procedure: CREATE OR REPLACE PROCEDURE SP_DYNAMIC AS tbl_list VARCHAR2(2000); DBLINK VARCHAR2(100); V_SQL VARCHAR2(1000); BEGIN DBLINK := 'SOME_LINK'; V_SQL := 'SELECT table_name,table_owner FROM…
Saroj
  • 71
  • 2
  • 11
0
votes
1 answer

Dynamic call Store Procedure (execute immediate ) Out parameters Problems

I have problem Dynamic Call Store Procedure v_sql := 'begin '|| p_procname || '(''test1'','' test2 '',:v_output2); end;'; execute immediate v_sql using out v_output2 ; dbms_output.put_line(v_output2 || ' ' ); In here ı can…
rusty1119
  • 41
  • 1
  • 2
  • 5
0
votes
1 answer

Resize datafile oracle using execute immediate

I need resize this datafile '+GMPDATDESA01/prod/datafile/apps_ts_tx_idx.269.878649919', why this simple procedure is wrong?. Other options, plis? SQL> begin 2 execute immediate 'alter database datafile…
Sandy Ramirez
  • 1
  • 1
0
votes
1 answer

PLS-00103: Encountered the symbol “end-of-file” when expecting one of the following: :=(@%;

i'm trying to execute a stored procedure dynamically since i got alof of them based on a simple number, so i created another procedure to do this, but i keep getting the erro on the title of my question, here is my procedure: PROCEDURE…
Isael Macias
  • 53
  • 1
  • 10
0
votes
1 answer

Execute Immediate in cursor on ibm db2

I'm having difficulties creating a SP in which I pass in a name of a table and query the SYS2 library to find out if it has an auto-increment field. If it does I query for the max value of that field in the table and then alter the table so the…
d.lanza38
  • 2,525
  • 7
  • 30
  • 52
0
votes
2 answers

How can I dynamically recover columnName_N from a cursor, N being a number incremented in a LOOP?

thanks for taking time to read and maybe answer my question! Note that I am a beginner and should not be considered a pro but i did search for the answer without finding it , maybe due to my uncommon problem and/or lack of knowledge about it. I have…
Raphaël
  • 173
  • 11
0
votes
2 answers

PLSQL Execute Immediate with Dynamic Using

I am dynamically building a search query with bind variables with at least 1 and at most 7 different potential criteria. I know I can do this - EXECUTE IMMEDIATE sql USING bind_var1, bind_var2 or EXECUTE IMMEDIATE sql USING bind_var3, bind_var5,…
Pirate11
  • 1
0
votes
2 answers

string variable in where clause with dynamic pl/sql

I have this code: declare instr varchar2(20); num_no number; begin select AR_ID, ID into instr, num_no from quelle_match where ID = 1; execute immediate 'CREATE or replace VIEW interm2 AS SELECT AR_ID, kurs, …
user3876251
  • 9
  • 1
0
votes
2 answers

DEFINE vs DECLARE - escaping quotes

I have defined a variable define myStrings = "'abc','def'" which I later need to use inside a procedure block and convert into a table of varchars declare type varcharListType is table of varchar(200); myList varcharListType; begin …
travega
  • 8,284
  • 16
  • 63
  • 91
1 2 3
…
12 13