29,206 questions
1
vote
1
answer
58
views
are Sub query operators ( ALL and ANY) really needed, why do we need them instead of min,max and in operator
we are using subquery operator ( ALL & ANY ) along with a relational operator,
so instead of that we can just use MIN or MAX in case of numbers and IN in case of characters.
I want to know if ...
3
votes
0
answers
64
views
Recover from PLS-00123: program too large (Diana nodes) without dropping package
This question is not about the error, but about compiling the PACKAGE BODY after if the specification is fixed.
Using a query to generate the package source:
SELECT 'CREATE OR REPLACE PACKAGE test_me ...
- 55.7k
0
votes
0
answers
38
views
Granting roles to pl/sql subprogram [closed]
Hello I read docs from oracle about subprograms. And at the end gave an example how to grant rights to subprograms. But for what it have to do, i don't understans
Using the SQL GRANT command, you ...
- 31
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
0
votes
0
answers
40
views
Debug PL/SQL with VS Code and SQLDeveloper extension
I am trying to get debug of PL/SQL working with Visual Studio Code and the Oracle SQLDeveloper extension.
To keep it simple, I have a single user with single stored procedure that I want to debug.
I ...
- 953
-3
votes
0
answers
37
views
oracle copy rows from multiple tables according to relationship
what is the best practice to copy rows from multiple tables according to relationship into one table or to same multiple table in different schema at one transaction so if any error happens the whole ...
- 2,618
1
vote
1
answer
41
views
SELECT REF(...) works in SQL Developer but fails in SQL Developer VS Code Extension
I'm using Oracle Database 19c
When I run the following query in SQL Developer, it works fine:
SELECT REF(p) FROM persons p;
However, when I run the same query using the Oracle SQL Developer VS Code ...
1
vote
2
answers
60
views
PL SQL: inner Function within Merge is called more often than expected
I got a setup where I would like to call a function once per group and apply the result to multiple rows (multiple eans). However, the function is called as many times as I have eans. This might be ...
- 376
0
votes
0
answers
51
views
Change NLS_ session settings for Oracle REST service
Is their a way to set the NLA_LANG environment parameters within an anonymous PL/SQL code block being called via a REST endpoint in Oracle? Tried a number of the APEX_UTIL api's but nothing has worked,...
- 13
3
votes
1
answer
62
views
Allow Commit/Rollback in PRAGMA AUTONOMOUS_TRANSACTION
At the application level, we enforce "ALTER SESSION DISABLE COMMIT IN PROCEDURE" to restrict commits/rollbacks inside procedures, but this also blocks Pragma Autonomous Transactions as well.
...
0
votes
1
answer
34
views
Oracle APEX Interactive Grid with ordered rows
In Oracle APEX, I have a table that describes a one-to-many relation which is ordered with a column. So something like this:
create table T_MASTER (
MASTER_GUID RAW(16) DEFAULT ON NULL ...
0
votes
2
answers
41
views
How to get the updated count record wise using FORALL using PLSQL
Need help on getting the updated count while using the FORALL
Steps to replicate
Create table:
create table TEST_UPDATE (name_id number(9), name_name varchar2(40));
Insert data
insert into ...
0
votes
1
answer
29
views
Oracle PL/SQL: FORALL Shows 1 Row Updated Even When Collection is Empty
I have a PL/SQL block where I:
Declare and initialize a collection (TABLE OF my_table.key%TYPE).
Use a SYS_REFCURSOR to BULK COLLECT keys into the collection.
Print lst.COUNT, which shows 0.
Run a ...
- 33
-7
votes
3
answers
84
views
Finding out if a string has special characters [closed]
How to check if a string has at least one special character using oracle plsql?
- 325
0
votes
2
answers
77
views
Null value comparison in oracle PLSQL [duplicate]
I am getting output as ABC in below code. However i was expecting the output to be "Not ABC" as NULL is not equal to ABC. Can anybody help me understand what is happening here .
DECLARE
...
- 325