29,197 questions
0
votes
2
answers
54
views
Error when calling dbms_cloud.export_data
I'm trying to do my first CSV export into an existing AWS S3 bucket. Calling with type CSV:
call dbms_cloud.export_data(credential_name => 'cred', file_uri_list => 'https://s3.us-east-2....
- 1
0
votes
2
answers
43
views
Oracle sql. how to check file existence on remote server
I'm trying to check if a file exists on a remote server, in a Forms 12c program unit.
I tried to use
utl_file.file_exists('\\server\d$\folder\filename.txt')
and also
utl_file.fgetattr('DBFOLDER','...
- 23
1
vote
2
answers
60
views
XML in Oracle PL/SQL - How to read a big hexBinary node?
I have an XML file with a node containing a file that has been converted into hexbinary.
The ultimate goal is to read that node, convert it back to an actual file and store the path of that file in a ...
1
vote
1
answer
83
views
New oracle apex plugin firing on page load/initialization
I have creating my first simple plugin. The idea is to have button in a page, on clicking of which an interactive report region will be reset, instead of going through "Actions menu" of IR.
...
- 597
0
votes
2
answers
75
views
How to SELECT inside a DECLARE block? [duplicate]
Context:
As a developer in a large company, I am allowed to query (read-only) any table in the database and see the result (for debugging and investigation purposes).
When I do so (either from ...
- 1,794
Best practices
0
votes
3
replies
42
views
How to apply value for a date series based on effective date in postgres
I have these two tables. Table 1 has item and the bucket it's supposed be in and the ratio and the effective date/start date. Table two has the value of the items for a date series. I want to join the ...
- 955
0
votes
1
answer
60
views
Where to define nested procedures in case when I need - variables + cursors + nested procedures?
I need to add 2 nested procedures into huge existing stored procedure.
But all my attempts fail with different errors.
Unfortunately, I cannot find in Oracle documentation - what is correct sequence ...
- 459
0
votes
1
answer
51
views
Reset APEX User Password [closed]
I am trying to reset the password for my APEX user account. I tried executing and committing the following update:
UPDATE wwv_flow_fnd_user
SET account_locked = 'N' , web_password = 'newpass',
...
- 1,461
1
vote
2
answers
98
views
Issue with compiling PL/SQL: invalid relational operator
Environment Oracle 19c (also 12c)
Here's my code:
CREATE OR REPLACE TRIGGER ClientTime
AFTER INSERT OR UPDATE OR DELETE ON ClientTime
FOR EACH ROW
DECLARE
v_pk_json CLOB;
v_changed CLOB;
...
- 6,191
0
votes
1
answer
41
views
Failed to write to, flush, or close a file. in PL/SQL while trying to write csv file using UTL_FILE
I am trying to create a CSV file of college courses for the current term from a Banner DB (Oracle) using PL/SQL. I’ve created lots of scripts to do this…and no had this issue before.
I’ve spent over ...
- 41
0
votes
0
answers
31
views
Different results when invoking function with raw sql and plsql [duplicate]
Well I have 2 distinct Oracle 12c databases, lets name them db1 and db2
I have a link from db1 to db2 (DBL_DB2), also grants for execution of pkg2 which contains procedure 2, which we call proc2 (in ...
2
votes
3
answers
88
views
Remove empty params from URL
URL like this
https://test.com/test?username=john&x1=1234&x2=5677
I want to remove params if any of them looks like param=.
So for example:
https://test.com/test?username=&x1=1234&x2=...
- 702
0
votes
1
answer
86
views
Unable to pass TO_TIMESTMAP and use filter condition in query oracle
DECLARE
v_sql_stmt VARCHAR2(2000);
v_INSERT_DATETIME TIMESTAMP(6);
BEGIN
SELECT
MAX(INSERT_DATETIME)
INTO V_INSERT_DATETIME
FROM
LOG_TABLE;
v_sql_stmt := '...
- 524
0
votes
2
answers
70
views
Oracle Procedure is not getting invoked
I have a procedure abc(?,?,?,?).
When I'm invoking like below from schema 'pqr' it is getting invoked returning results.
abc(?,?,?,?);
However, when I'm invoking like below from same schema 'pqr' it ...
- 5
0
votes
0
answers
62
views
Call Oracle stored procedure that takes ODCIVARCHAR2LIST parameter from C# using Dapper and Oracle Managed Data Access Core [duplicate]
I'm trying to call an Oracle stored procedure from C# that takes SYS.ODCIVARCHAR2LIST (a table of VARCHAR2) as input parameters.
The procedure works correctly in SQL Developer, but from C# (using both ...
- 4,064
1
vote
2
answers
147
views
Read data from a table in a PL/SQL procedure and insert into another table while one hot encoding
I am trying to read data from a table in PL/SQL through a cursor where I am trying insert the data back to another table but one hot encoded.
So the data is like this:
ORIG_TABLE
ID
Fruit
1
Orange
2
...
- 13
4
votes
1
answer
76
views
Do special characters in LDAP Search escaped by \XX have to be sent in binary form in the wire format?
I am trying to send LDAP Search queries from PL/SQL (OracleDB using DBMS_LDAP.search_s) towards Active Directory for the field objectGUID.
Because that is a binary field, and I need to query non-ASCII ...
- 43
3
votes
1
answer
80
views
Correct syntax to define parameters in a `StoredProcedure` Spring bean, fixing error ORA-06550
Why I am asking this
After upgrade of Oracle JDBC driver in a Spring Boot application from 23.5.0.24.07 to 23.7.0.25.01, all stored procedure invocations in Spring Boot (via StoredProcedure beans) ...
- 27.1k
3
votes
3
answers
178
views
Reduce nested IF…ELSE conditions that iteratively condition building a query by adding filters one by one
My stored procedure has this structure:
if Count 1>0 then
count 2 with (condition 1,)
if Count 2>0 then
count 3 with (condition 1,2)
if Count 3>0 then
count 4 with(condition ...
- 121
1
vote
1
answer
123
views
How to ensure unique index names when creating indexes for FKs with PL/SQL?
I'm trying to write a procedure that automatically creates indexes for foreign keys that don't have one yet. I created a function called SNK_VERIFICA_NOME_IDX which is supposed to ensure the index ...
- 767
0
votes
0
answers
60
views
How to generate access token using serviceAccountKey.json for sending push notifications?
I'm trying to send push notifications using Firebase Cloud Messaging (FCM) HTTP v1 API from my backend server, and I understand that I need to generate a server-to-server access token using a Firebase ...
1
vote
1
answer
71
views
How to check if multiline comments contain a specific word?
I need to search for specific words in code using all_source table.
Something like:
select t.*
from all_source t
where regexp_like(t.text, '(^|\s)SOME_WORD(\s|$)')
How to ignore text inside multiline ...
- 1,658
1
vote
3
answers
82
views
EXECUTE IMMEDIATE BULK COLLECT DYNAMIC USING
I have a requirement where i have to add where clause in dynamic sql based on certain conditions..
SELECT x
FROM DUMMY_TBL
WHERE COL1 = :1
OR based on a condition it can be as
SELECT x
FROM ...
- 47
4
votes
3
answers
87
views
Insert with (CTE and) select ... as of scn (flashback) and table alias(es) does not work in PL/SQL - ORA-00984: column not allowed here
In a legacy system we want to copy a lot of productive data/tables in other tables in a stored procedure.
That all copied data is consistent with each other, we want to use the <tableName> as of ...
0
votes
1
answer
166
views
How to delete large number of records from a table faster in plsql?
Table #1:
buck number(10),
sname varchar(20),
...
total of 20 columns
Table #2:
sname varchar(20),
sdiv varchar(10),
...
total of 15 columns
Table1 has 30M rows
Table2 has 10M rows
I want to ...
- 3
0
votes
1
answer
53
views
Oracle Apex IG throws error when adding multiple values in one cell
Interactive Grid Image
Hello, as a beginner in Oracle Apex, i have problem with Interactive Grid.
While adding more values in Person ID column, i have an error
ORA-06502: PL/SQL: value or conversion ...
- 27
0
votes
2
answers
244
views
PDF report scheduling using Oracle Apex 24.2
I am trying to schedule a PDF report to be sent to an email using Oracle Apex 24.2 or if I can store it on a custom directory on apex application ( not locally).
I know how to create a scheduled job ...
- 13
1
vote
2
answers
112
views
Oracle APEX PL/SQL Unknown Source of Invalid Identifier
The goal of this program is to match a form's element IDs corresponding to different questions with the actual questions, so the resulting table's column names are the questions themselves instead of ...
- 11
0
votes
1
answer
50
views
Oracle - Use trigger to update field in table using other fields in same table after Insert/Update
I'm trying to create a trigger in Oracle that will update a field within a table after an insert/update that concatenates 4 other fields within the same table to create the value.
Was able to compile ...
1
vote
4
answers
75
views
Count rows in a sys_refcursor of an unknown type
I have a PL/SQL function similar to this (pseudo code) :
function foobar() return sys_refcursor as
select_cursor sys_refcursor;
begin
open select_cursor for
select
a, b, c, d,...
- 1,794
-1
votes
1
answer
48
views
ORA-06502: PL/SQL when huge data is returned with ORDS (21c ATP)
I'm making use of below PLSQL procedure to return the CLOB data to my ORDS handler (ATP 21c database) but when the huge data (like more than 70 rows of header and lines) is returned then I'm getting ...
- 107
0
votes
1
answer
82
views
It is possible in Oracle Apex 24.2 to build something to download/export a table that has SDO_GEOMETRY in shapefile format?
I have tables in Oracle that contain spatial data using the SDO_GEOMETRY data type. I would like to create a button inside APEX that allows users to download a zipped shapefile export of this data. ...
1
vote
2
answers
60
views
How to initialize 2D Oracle tables without having to use a constructor
The following code works but it seems that I have to initialize a 2D table (arr2D) using a constructor. In this case I'm passing 2 arr1D objects to the constructor but what if I don't know the exact ...
- 2,133
2
votes
2
answers
48
views
Will after statement level trigger work on dml operations
I have created this statement level trigger but it executes like row level trigger...
CREATE OR REPLACE TRIGGER trg_citizens_log
AFTER INSERT ON citizens
BEGIN
INSERT INTO trigger_log ( log_id,...
- 66
0
votes
1
answer
37
views
Round a varchar2 column with a value of 00:00:00:00.000000
The data occasionally returns days:hours:minutes:seconds.0000 but is usually hours:minutes:seconds.0000 and the field in the table is a varchar2
Examples
01:04:23:14.679012 and
00:06:35:35.453899
I ...
- 49
2
votes
2
answers
143
views
regular expression to allow only alphanumeric characters
I want my regular expression should error out if my string contains any character other than Alphanumeric. The string should error out if there is a new line character or any white space character in ...
- 2,789
1
vote
0
answers
44
views
SqlAlchemy Trying to convert Oracle Stored Procedure to Python/Postgres, issues with instr/position
I'm working on a project where we are to convert some Oracle stored procedures into python via sqlalchemy. The issue is that we are also to use sqlite (We are unable to change this as those above us ...
- 79
0
votes
0
answers
68
views
Oracle Apex PL/SQL Rest POST Request Content Variables Error
I can send Twilio SMS messages or no content-variable WhatsApp Messages on Oracle APEX using APEX_WEB_SERVICE.MAKE_REST_REQUEST
But when I try to add some parameters and send whatsapp messages with ...
- 7
0
votes
1
answer
50
views
ORA-06550: line 5, column 20: PLS-00382: expression is of wrong type
I have this process Im reciving an error that seems to be related with the item P41_ATTACHMENT to upload a file in this table QDL_SCRAP_REQUESTS.
DECLARE
v_part_number_list VARCHAR2(4000) := :...
- 15
0
votes
1
answer
70
views
change into loop with single commits
I have the following Oracle PL SQL code to merge data.
PROCEDURE merger
IS
BEGIN
MERGE INTO target_table tat
USING (SELECT s.W_ID, s.c_id, s.s_id
...
- 1
0
votes
2
answers
123
views
How to execute an Oracle procedure in SQL Developer
I know this is a tired question but I can't get it to work (and I'm no Oracle guy). I have a procedure with a defined interface like this
TYPE t_id_values IS TABLE OF NUMBER
INDEX BY ...
- 1,190
1
vote
1
answer
78
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 ...
- 19
3
votes
0
answers
102
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
1
answer
91
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
165
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
1
vote
1
answer
59
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
74
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
86
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
100
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
86
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 ...
