All Questions
Tagged with dynamic-sql oracle-database
420 questions
0
votes
0
answers
60
views
Replace a character with an empty string inside a dynamic SQL using OPENQUERY
I need to replace a certain character inside a field with an empty string.
But this simple need becomes complicated because I need to extract data from a remote Oracle server and I need a parameter to ...
- 195
2
votes
1
answer
77
views
Oracle DBMS_SQL giving ORA-1007 variable not in select list
I get this on Oracle 11g and 19c.
Below is a simplified example of the code I am using to process some queries dynamically. It needs to be DBMS_SQL because the actual use case will have various ...
- 419
0
votes
2
answers
44
views
How to return columns from dynamic SQL in Python console
I'm trying to make a CRUD application that uses Oracle procedures for the operations.
For the READ component, I wrote a procedure that returns one or more entire rows depending on the specified ...
0
votes
1
answer
67
views
Dynamic Oracle Query, without dynamic
The title is indeed strange, but I will try to explain.
I have been programming Oracle (PL-SQL) in version 11G for several years.
I recently moved to a new workplace, and in the first task I was ...
- 39
1
vote
1
answer
80
views
Oracle PL/SQL: generic and uniform journalling trigger without hardcoding columns in the trigger
In my Oracle 19c-database, I would like to make 1 trigger per table where the jornalling is handled for insert, update and delete. I would like that every trigger is the same for each table, with the ...
- 11
0
votes
1
answer
566
views
Creating a Dynamic JSON_TABLE Query with a Variable in PL/SQL
I have a PL/SQL procedure that contains the following static SQL code, which works as expected:
SELECT LISTAGG(value, ',') WITHIN GROUP (ORDER BY NULL)
INTO SELECTED_GEO
FROM JSON_TABLE(v_json_clob, ...
- 29
0
votes
1
answer
111
views
How is the Definer Rights program unit obtaining ALTER SESSION Privilege?
The following is an excerpt from the Oracle 19c Database PL/SQL Language Reference documentation PL/SQL Subprograms 8.14 (emphasis added).
"During a server call, when a DR [DEFINER RIGHT] unit ...
- 550
0
votes
1
answer
450
views
Merge with Nested Tables. ORA-00902: invalid datatype
I have a PL/SQL procedure in which I create and populate a nested table using dynamic SQL.
I later want to use this nested table to update information in another table using a MERGE.
Declarations look ...
- 23
0
votes
2
answers
98
views
Invalid character in plsql
Here, when i execute this query it is showing me invalid character. how to solve is error.
EXECUTE IMMEDIATE('SELECT COUNT(*) INTO' ||VAR_L_COUNT||'FROM'|| P_TABLE ||
' WHERE ' ||...
- 13
-1
votes
2
answers
245
views
I have two questions about dynamic SQL ;please explain why, thank you
This is first code,no error
L_SQL := 'SELECT COUNT(1)
FROM TABLE A
WHERE COL1 = ''' || V_1 || ''' -- where col1 = '2021YEAR_REPORT'
AND COL2 LIKE ''%' || V_2 || ''' ...
- 3
0
votes
1
answer
184
views
ORACLE - Why does a dynamic SQL statement using DBMS_RANDOM fail when called from a stored procedure but not from an anonymous block
EDIT (8/25)
Thanks to @Alex Poole for providing the answer below but I wanted to share additional detail on these role limitations around PL/SQL objects as it helps explain not only how Oracle is ...
user2858650
0
votes
1
answer
847
views
PL/SQL Dynamic case when statement for parameters to pass into Java
I am fairly new to PL/SQL so I haven't been able to piece out some of the logic (specifically execute immediate)
In my PL/SQL statement, I am essentially trying to create a trigger for a prompt where ...
- 41
0
votes
1
answer
684
views
PL SQL Procedure using dynamic sql to take backup of a random table in a schema supplied during execution of the procedure
I would like to create a procedure that:
Creates a backup of a table when supplied the table name and the owner when the procedure is executed.
Does a count of the records of the backup table
Insert ...
0
votes
1
answer
205
views
How to know if a procedure has successfully executed using dynamic sql in PL/SQL?
I am trying to get an OUT parameter calling a procedure dynamically using bind parameters. The procedure has some IN parameters and an OUT parameter. The IN parameters are fetched from a single column ...
0
votes
2
answers
1k
views
How to execute Dynamic sql with insert statement in Oracle
Below sql command is not working in procedure
PROCEDURE P_EMPDETAIL
AS
V_WHERE := 'E.EMP_ID = 123'B
BEGIN
EXECUTE IMMEDIATE 'INSERT INTO EMPLOYEE E ' || V_WHERE || ;
END;
