Questions tagged [plsql]
Procedural Language/Structured Query Language is Oracle Corporation's procedural language extension for SQL. IBM supports PL/SQL for DB2 since version 9.7. Questions about PL/SQL should probably be tagged "oracle" or "db2" as well.
603 questions
0
votes
2
answers
58
views
PLSQL query to return the trackingcodes only if all its cashout_trackingcodes belong exclusively to that trackingcode
I have a table with below structure:
create table TEST_REFUND_CASHOUT
(
f_gdate DATE,
trackingcode VARCHAR2(4000),
cashout_trackingcode VARCHAR2(4000),
cashout_date ...
- 1,502
0
votes
0
answers
49
views
Use of constants in CASE statements in PL/SQL
Can anyone explain why a utPLSQL unit test works when calling a stored procedure with the following code in:
RETURN CASE WHEN pi_is_scientific_notation = 'T' THEN v_display_format || 'EEEE' ELSE ...
- 1
1
vote
2
answers
82
views
How to reliably delete all tables with names matching a certain template?
We're maintaining a "hairy" vendor-supplied application, which sometimes forgets to drop its temporary tables. Years ago we've created a simple script to get rid of all of them after the ...
- 166
0
votes
1
answer
47
views
Best way to store the latest child in a one-to-many relation?
Hi I have a table in Oracle APEX to store documents as well as their revisions:
CREATE TABLE M_DOCUMENTS
(
DOCUMENT_GUID VARCHAR2(38) DEFAULT ON NULL sys_guid(),
...
0
votes
1
answer
74
views
Better approach to 'pivot' the data of a table
I have a table with below structure:
create table test_table
(queuename number,
duration_sum number,
rating_sum number,
rating_avg number,
rating_cnt number
)
Here is the sample data:
...
- 1,502
5
votes
1
answer
906
views
Query to delete records with lower eff_date in a large table with 400 million records
I have a table with below structure:
create table TEST_TAB
(
activity_type CHAR(1),
tracking_code NUMBER,
eff_date DATE
)
Sample data for this table:
insert into TEST_TAB (activity_type, ...
- 1,502
0
votes
1
answer
57
views
Identifying which PLSQL overloaded procedures get called
Consider the following code:
CREATE OR REPLACE PACKAGE the_package AS
PROCEDURE procA ( pval1 IN VARCHAR2 );
PROCEDURE procA ( pval1 IN NUMBER );
END;
/
The created a PLSQL package which has ...
- 143
0
votes
1
answer
561
views
How to print content of refcursor within a PL/SQL procedure?
This question concerns Oracle PL/SQL (Oracle version 12c/19c) and Oracle SQL Developer (version 20.4).
I have a procedure which calls a dynamically generated SELECT statement and save results into ...
user311946
0
votes
1
answer
92
views
dot in REGEXP_LIKE pattern
begin
if NOT REGEXP_LIKE('-', '^[[:alpha:][:digit:][:space:]-./]*$') then
dbms_output.put_line ('NOT') ;
else
dbms_output.put_line ('OK') ;
end if ;
end ;
it gives: OK
...
- 11
1
vote
0
answers
61
views
Object visualization problem with owner DBA
I'm having a problem viewing other owners' objects, even though I'm logged in as a user with DBA permissions
PL/SQL does not display the view/edit spec & body option
For testing purposes, I ...
0
votes
2
answers
77
views
Does Oracle database server stored runtime dynamic SQL?
I have a plsql below that executes dynamic sql.
DECLARE
sql_stmt VARCHAR2(200);
BEGIN
sql_stmt := 'SELECT employee_name FROM employees WHERE employee_id = :1';
EXECUTE IMMEDIATE sql_stmt ...
- 101
0
votes
0
answers
83
views
Copying data from old column to new column with large amount of records
While updating/copying the data from old column to new column we are facing the issue with tablespaces in database.
Records count: 158 million+
Using below query to update the records,
UPDATE ...
0
votes
1
answer
438
views
PL/SQL dynamic Index loop based on list variable
Currently, I am indexing column by column with the following code snippet:
DECLARE
already_exists EXCEPTION;
columns_indexed EXCEPTION;
PRAGMA EXCEPTION_INIT ( already_exists, -955 );
...
- 103
0
votes
1
answer
179
views
how to get explain plan from pl/sql function call?
consider this working pl/sql block:
declare
o_result boolean;
io_error varchar2(30000);
tab1 someTable1;
tab2 someTable2;
begin
tab1(1).col1 := 'str'...
- 119
0
votes
1
answer
89
views
How to create a stored procedure from another stored procedure dynamically in OracleI have
I have a Stored Procedure that outputs the DDL of another Stored Procedure. I need to execute this DDL dynamically. Meaning, the second Stored Procedure should get created when the first Stored ...
