Skip to main content

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.

Filter by
Sorted by
Tagged with
0 votes
0 answers
26 views

What are the implications of PLSQL_OPTIMIZE_LEVEL=2 and PLSQL_DEBUG=TRUE?

I have an Oracle database (19c EE) where several users develop PL/SQL Code. When I query the view USER_PLSQL_OBJECT_SETTINGS it shows me 700 entries with PLSQL_OPTIMIZE_LEVEL=2 and PLSQL_DEBUG=TRUE. I ...
WayneNani's user avatar
  • 113
0 votes
0 answers
49 views

Calling stored procedures multiple times in the same transaction

I have a PLSQL Package with several stored procedures inside. This package is used to create specific reports. I call the procedures like that : package.init(parameter1 => 'Blablabla'); package....
Luke's user avatar
  • 1
1 vote
0 answers
35 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 ...
Igor Oliveira's user avatar
0 votes
2 answers
39 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 ...
Idonknow's user avatar
  • 101
0 votes
0 answers
47 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 ...
Mujahid Boramani's user avatar
0 votes
1 answer
59 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 ); ...
Matt Miles's user avatar
0 votes
1 answer
69 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'...
gabriel119435's user avatar
0 votes
1 answer
49 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 ...
Vincy Wales's user avatar
0 votes
1 answer
44 views

WHY is NO DATA FOUND a predefined exception?

In MSSQL, neither SELECT queries that return no rows nor INSERT\UPDATE\DELETE queries that affect no rows are treated as an exception. In my tSQL code, there are plenty of situations where an empty ...
KevinP's user avatar
  • 105
-1 votes
1 answer
140 views

Need help with DBMS_COMPRESSION.GET_COMPRESSION_RATIO

I need some help on how to run the DBMS_COMPRESSION.GET_COMPRESSION_RATIO package on all the tables of a database to see if is worth the implementation or not. This is the procedure I'm running alone ...
Daniel Soto's user avatar
0 votes
1 answer
242 views

Receiving error "ORA:22905-can not access rows from a non-nested table item" while working with ref cursors in JSON in PLSQL

I have created below procedure. I am getting ORA:22905-can not access rows from a non-nested table item at last line : INTO JSON_DATA FROM TABLE(InvoiceDetails). What I am doing here is retrieving out ...
Niki's user avatar
  • 1
0 votes
1 answer
80 views

Error when creating trigger to insert values

I am new to databases and I know this is a simple question but I cannot find an answer anywhere, so I appreciate the help. I have 2 tables, each with the same structure. Table1 CaseNum Number(9,0) ...
bk_32's user avatar
  • 103
0 votes
0 answers
34 views

Export query result to Oracle Server

I have written PL/SQL code to store XML into cursor. Refer the below code Requirement: As of line at line 83, I am inserting the result into another table (XML_GEN) as XMLTYPE datatype. Line: insert ...
user3792741's user avatar
2 votes
2 answers
137 views

Getting wrong/misleading info related to oracle table index

Creating a table as follows: CREATE TABLE "HOTEL3" ( "NAMEX" VARCHAR2(4000 CHAR), "CITY" VARCHAR2(4000 CHAR), "PRICE" NUMBER, "ID&...
deostroll's user avatar
  • 189
3 votes
1 answer
637 views

Oracle database: how to change varchar length of same column in multiple tables in one go?

My company uses Oracle 19 and we have many thousands of tables which have two columns called CRBY and MODBY in them, which are varchar2(200). These columns get populated by a script called on an ...
SeanGaff's user avatar
1 vote
0 answers
292 views

Password hash in apex oracle

i'm trying to hash or encrypt the password provided by the user in my apex oracle before store it in the DB table How can i do it ? i tried using DBMS_CRYPTO but it always generate an error saying ...
Safwen Hafsawy's user avatar
0 votes
2 answers
25 views

Compare dev/prod databases functions

I have two databases - dev and prod. I modify logic and debug in dev then pull everything to prod. A significant part of my app are PostgreSQL PL/pgSQL functions. I write them in dev, test, make sure ...
Sergei Basharov's user avatar
0 votes
1 answer
179 views

Update or insert records to the same table based on a similar table but with conditions

I'm tryin to update or insert records on the same table based on a similar table using Merge but based on the count of records in the source table. I have 2 tables say one is POV [pov, dc, item](the ...
Akash Reddy's user avatar
-1 votes
1 answer
66 views

How to rollback data mart update if data quality checks are not passed?

Good afternoon! I am working with Oracle database. I have a PL/SQL procedure which is scheduled with DBMS job to update every night a table SOME_TABLE: CREATE OR REPLACE PROCEDURE MY_PROC IS ...
rsx's user avatar
  • 1
0 votes
0 answers
141 views

Advice on Oracle Flashback Data Archive

I am looking for some help / advice on using Flashback Data Archive. In a test system we enabled FDA on a handful of tables. Before enabling FDA, our UNDO tablespaces were empty - 0 MB. After ...
alexherm's user avatar
  • 101
0 votes
2 answers
215 views

Script disabling/enabling foreign keys

I want to script disabling and enabling Foriegn KEY constraints. I'm testing with the following setup. As you can see from below I am able to successfully disable the constraints but I can't seem to ...
Pugzly's user avatar
  • 105
0 votes
1 answer
254 views

Set column value to different randomly chosen values from another table

My goal: Set PID_FM in every row to a randomly picked value out of all the values matched by the subquery. However the FM_ID is only picked once, and therefore the whole column has the same value. I ...
a1234's user avatar
  • 1
0 votes
1 answer
160 views

Remove all characters except two letters

I am looking for a logic to remove all characters in a string except AD & Numeric values. Examples String Expected Result AD-892839 statement AD-892839 BOD AD-89283 AD-89283 BOD AD-89283 ACGD ...
Madhu's user avatar
  • 11
1 vote
2 answers
1k views

DBMS_SCHEDULER running 10 instance of the same job

I'm trying to have a single scheduler that will run multiple (from 4 to 10) jobs at the same time. I know this is doable if I just make multiple dbms_scheduler of the same job, (job1, job2....) . but ...
user3039977's user avatar
1 vote
1 answer
577 views

Need a PL/SQL code to drop many partition in 1 single SQL statements

I have a requirement where we need to drop historical partitions which are not dropped . Requirement is dropping multiple partitions in one step instead of dropping one by one. like ALTER TABLE sales ...
Dhiren Singh's user avatar
0 votes
1 answer
1k views

How To turn a string with "pipe-separated" values into individual rows in Oracle PL/SQL

I have a table with below structure : create table student_info ( item_number number, st_firstname varchar2(50), st_lastname varchar2(50), st_score varchar2(50) ) Here is a ...
Pantea's user avatar
  • 1,432
1 vote
0 answers
157 views

PL SQL code that Inserts data from a different table and gives new id values from the same table that the data is being inserted

What could I change to make this insert work and give a new ID value on each row I am inserting? Right now it's giving all the inserted rows the same ID number. Sadly, I can't use triggers and ...
Kristofers Kaprāns's user avatar
1 vote
2 answers
675 views

PL/pgSQL select statement inside trigger returns null using where NEW

I'm creating a trigger that triggers ON INSERT to a table, and I wish to log the structure of tables inserted so I wrote this function: CREATE OR REPLACE FUNCTION update_table_log_received() RETURNS ...
Moifek Maiza's user avatar
0 votes
2 answers
289 views

Set up PL/SQL Developer

i have got PL/SQL developer installed. However, it asks me to enter a password, username, and database. I dont know what to enter in this case. Am I going to need "oracle SQL client" to set ...
Crues's user avatar
  • 3
0 votes
2 answers
320 views

How to implement "SELECT INTO" in Oracle with PostgreSQL commands?

I have seen many explanations about SELECT INTO in the oracle clause. I found a difference in applying SELECT INTO in Oracle and other SQL databases. In Oracle, the SELECT INTO statement retrieves ...
devins10's user avatar
1 vote
1 answer
2k views

Receiving error "can not access rows from a non-nested table item" while working with associative arrays

Below , I'm trying to use the data of the array I've declared, in the where clause of the query but I receive the following error : declare v_input varchar2(400) := '0,1,2,3,4'; type ...
Pantea's user avatar
  • 1,432
0 votes
1 answer
493 views

Receiving error "Local collection types not allowed" while working with associative arrays

I have an input parameter with this format '0,1,2,3,4' , what I want to do is to declare an array with a variable size (by this I mean the input parameter might be like '0,1' , so the size is not fix),...
Pantea's user avatar
  • 1,432
1 vote
0 answers
236 views

Is there a utPLSQL way to mock procedures, functions, or tables?

In tSQLt, you can isolate dependencies for unit testing using procedures like FakeTable to create a dummy version of whatever your test subject depends on, that contains only data you specify. This is ...
JAF's user avatar
  • 163
2 votes
1 answer
3k views

check if value exist in another table before insert SQL

I have table: " daysoff " and table: " rdv " the rdv table looks like this : temps_rdv etat_rdv ID_RDV 12-10-2022 reservee 2 the daysoff table looks like this : ID DATE_OFF ...
kylie's user avatar
  • 39
0 votes
1 answer
254 views

Oracle Edition-based redefinition(EBR) use case

We have some pretty high-load service in oltp database built using Oracle's procedures, functions, views and etc. Whenever we want to update our code base, it's usually throws an error like ORA-...
casmodeus's user avatar
0 votes
0 answers
524 views

Procedure updating table with CLOB data type column does not work properly

I have a table with below structure: create table bb_analysisitems_batch ( batch_id number, batch_name varchar2(100), batch_result clob ) There are other ...
Pantea's user avatar
  • 1,432
0 votes
1 answer
180 views

Loop through schemas and execute dml on the same table in each schema

I have a table "active_t" in a schema called "test", having the following structure: instance active IN 1 SI 0 The ask is to loop ...
Yuvraj's user avatar
  • 1
1 vote
1 answer
241 views

Oracle PL/SQL: Trigger Syntax

I'm kinda new(like 1 year of experience) in PL/SQL and i have to make a kind of tricky trigger, and i want to check if my syntax is correct, mainly the selects, inserts and variable value store i make ...
Nhoj Anec's user avatar
0 votes
0 answers
45 views

How to pass multiple values while calling a procedure without using comma

I have written a procedure to update the status of a column. Since there are more than 1000 records it's not easy to pass all values using commas. I have a list of case_intrl_id's in excel and want to ...
shashi's user avatar
  • 1
0 votes
0 answers
68 views

Apex page DML Mail Alert Trigger

I have a Trigger and Function which sends mail with "updated" column(s), when i am updating in my back-end (sql developer), i receive a mail with that particular column. But when i do UPDATE ...
Notadoctor's user avatar
0 votes
1 answer
34 views

Performance Problem : PL/SQL

SELECT NULL AS TRX_NUMBER , NULL AS CUSTOMER_TRX_ID , VW_SAP_MPP_INV1.DOCENTRY AS DOCENTRY , VW_SAP_MPP_INV1.LINENUM AS LINENUM , VW_SAP_MPP_INV1.ITEMCODE AS ITEMCODE , VW_SAP_MPP_INV1....
Notadoctor's user avatar
0 votes
1 answer
30 views

Better ways of concating the value of columns

I have a table with below structure and sample data: create table CUSTOMER_TEST ( customer_num Number, rel_one Number, rel_two Number, rel_three Number, rel_four Number ) ...
Pantea's user avatar
  • 1,432
-2 votes
1 answer
1k views

Which modern IDE to use when working with Oracle database? [closed]

Good afternoon! I am working with Oracle Database (mostly writing queries, stored procedures and creating tables). The problem is that I can not find a modern IDE to work with Oracle such as Visual ...
rsx's user avatar
  • 1
0 votes
1 answer
470 views

oracle - Calculate sum or avg in one query based on the their value

I've this table date customer amount percentage category 20220330 customer1 10 93 01 20220330 customer1 11 91 01 20220330 customer1 13 91 02 20220330 customer1 15 91 02 I' need to calculate sum ...
ciro's user avatar
  • 171
2 votes
1 answer
603 views

How to terminate user's sessions in Oracle DB exactly 8 hours after user connected?

I would like to terminate user sessions (and optionally also lock the user), exactly 8 hours after user established DB connection. I tried creating a profile, but it just doesn't terminate user's ...
korodani's user avatar
  • 123
-1 votes
1 answer
34 views

Deleting duplicate value in View from the Base table

BASIC INFO : I have a table : YARDI_PROPERTY -> PK -> HMY, and a VIEW which uses HMY from it. Issue : Getting duplicate HMY in the view. Requirement : TO delete those duplicates. IMPLEMENTATION :...
EAZY_EZ_HE's user avatar
-1 votes
2 answers
40 views

Struggling with improving efficency of this code

QUERY FOR COLUMN INSIDE A VIEW(need for this to work faster) CREATE OR REPLACE VIEW "ODS"."VW_YARDI_DLR_PROPERTY_INFORMATION" ("SPCODE", "HMY", "SPNAME&...
EAZY_EZ_HE's user avatar
0 votes
0 answers
626 views

During the trigger execution getting error : ORA-00604: error occurred at recursive SQL level 1 ORA-00900: invalid SQL statement

When I am trying to execute this trigger getting following error: CREATE OR REPLACE TRIGGER create_table_trigger AFTER CREATE ON SCHEMA DECLARE lv_sql VARCHAR2(5000); obj_name VARCHAR2(...
ashwani Kumar's user avatar
0 votes
1 answer
144 views

Query to update one column based on the amount of another column

I have a table with below structure : create table Z_TEST_ONE ( col_id NUMBER, bed_amnt NUMBER, bes_amnt NUMBER, bop NUMBER ) Sample data is like this : col_id bed_amnt ...
Pantea's user avatar
  • 1,432
-6 votes
1 answer
527 views

cursor syntax to fetch and drop tables in the db

I have a list of tables from a select query which gives me at least 50 tables daily . Need to drop them as they are temporary. I need a cursor to fetch and drop each one of them in a loop. I am using ...
mamidipalli satish's user avatar

1
2 3 4 5
13