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
10 views

Syntax error near declare statement Cursor %TYPE

As a beginner, I m playing with PL/SQL where I am simply displaying list of employees. however, I am facing this syntax error issue near where I wrote declare statement, I am not able to figure out ...
0 votes
0 answers
17 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 ...
  • 1,356
0 votes
0 answers
36 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 ...
  • 1
1 vote
1 answer
50 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 ...
0 votes
0 answers
27 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 ...
  • 1
0 votes
0 answers
19 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 ...
0 votes
0 answers
20 views

PL/SQL get the session ID (SPID) during a stored procedure on Oracle 19c

I'm trying to get the session ID (SPID) during a stored procedure on Oracle 19c. In Oracle 12c the following query was enough: select s.sid, p.spid, substr(s.username,1,20) MY_SCHEMA, s.terminal, p....
  • 1
0 votes
1 answer
29 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....
0 votes
1 answer
21 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 ) ...
  • 1,356
-2 votes
1 answer
85 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 ...
  • 1
0 votes
1 answer
32 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 ...
  • 151
2 votes
1 answer
263 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 ...
  • 123
-1 votes
1 answer
27 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 :...
-1 votes
2 answers
38 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&...
0 votes
0 answers
143 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(...
0 votes
1 answer
36 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 ...
  • 1,356
-6 votes
1 answer
84 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 ...
2 votes
1 answer
38 views

sp_BlitzBackups alternative for Oracle Database

In the SQL Server world, there is a stored procedure called sp_BlitzBackups from the "First Responder Kit" that helps out a database administrator to compute a Recovery Time Objective and a ...
1 vote
0 answers
76 views

How to check compatibility of PL/SQL written for Oracle 9i but now to be migrated to Oracle 19c

I have lots of PL/SQL scripts which were developed years ago for an Oracle 9i application which should be migrated to Oracle 19c. I have read that normally there shouldn't be major changes in the PL/...
  • 11
0 votes
1 answer
28 views

what does the number in the "A_ROWS" column of the execution plan shows for operation "Index scan"?

I have a query as you can see below : select /*+gather_plan_statistics*/ * from mi_dimcustomer t where t.CUSTOMER_NUM = 321937 There's a Unique Index (IDX1_DIMCUSTOMER) on Customer_Num column of ...
  • 1,356
1 vote
1 answer
158 views

Three "Nested loop join" in the Execution plan despite using two inner joins in the main query

I have a query as you can see below : with cte as (select customer_num from vmi_segment_customer_relation where effective_date = to_date('12/30/2021', 'mm/dd/yyyy') and segment_id = ...
  • 1,356
0 votes
1 answer
169 views

Getting warnings while generating query Execution plan in Oracle , PLSQL developer

These are the steps I follow to generate a real Execution Plan in PLSQL developer : 1)Using this hint in my main query /+gather_plan_statistics/ select /*+gather_plan_statistics*/ from mi_factcustomer ...
  • 1,356
0 votes
0 answers
24 views

How to keep two database environment tables in sync by creating scripts using procedure

I have one table in the DEV environment and the same table was in SIT environment too. But I have added a few columns in the DEV environment table that I need to sync in SIT environment also. So, I am ...
  • 101
0 votes
0 answers
151 views

I want pagination in oracle stored procedure

I want pagination in oracle stored procedure in which I will give page start index, page size as input parameter and using offset for skip rows. My query for getting data is a string query of varchar2:...
  • 3
1 vote
1 answer
162 views

Truncate huge table of 6.4TB - Oracle

I've never truncated a huge table like that. How long can it take? or will be a millisecond or so like usually? Can I have some issue during this operation? this table has 1.4TB size plus 4.8TB of lob ...
  • 566
0 votes
2 answers
90 views

Oracle 12cR2 RAC - ORA-06508

Environment: DB Version: Oracle Database 12cR2 EE on Exadata RAC: True Problem: ORA-06508 on procedure. One of our users had a DBA role privilege, we don't want such a big privilege to be defined ...
0 votes
0 answers
208 views

Create a unique local index and ORA-14039

I'am trying to partition a index from a primary key. For that, I've done: Disable the constraint. SQL> alter table DANILO.SUB_PAT_TEST disable constraint SYS_C007918; Table altered. SQL> Try ...
  • 566
0 votes
1 answer
24 views

Query to show whether a record is appeared in the previous date or not

I have a table with below structure and sample data: create table TEST_ONE ( purchase_date DATE, customer_id NUMBER ) | purchase_date | customer_id ---------------------------------- |...
  • 1,356
0 votes
3 answers
151 views

Does using "qb_name()" hint (query block name) in a query have significant effect on performance?

I've just become familiar with the hint "qb_name" (query block name) in Oracle and below you can see an example of using this hint in a query: select /*+ qb_name(main)...
  • 1,356
0 votes
0 answers
70 views

DBMS_HS_PASSTHROUGH Bulk Fetch

i have question about DBMS_HS_PASSTHROUGH geting result set without stored_procedure i have oracle and i have database link so i am running this query select * from table where id >20 via ...
  • 3
0 votes
1 answer
603 views

oracle stored procedure errors: ORA-00904 invalid identifier, dynamic SQL

I am trying to find all tables that contain column called arg_column_name, look up value arg_column_value in that column of those tables, and return names of the tables and counts of found rows to the ...
0 votes
1 answer
207 views

Extract SDO_GEOMETRY line vertices into a nested table column (in a query resultset)

Oracle 18c: In a related post, I demonstrated how to use the GetVertices() function and the Table() function to extract the vertices of an SDO_GEOMETRY line: Select SDO_GEOMETRY line vertices as rows ...
  • 1,387
0 votes
0 answers
37 views

AND clause with IN function to compare/validate values from subquery

Greeting, In the hypothetical scenario, I have a procedure with with an update statement which works by updating the values of specified table with the variable inputs. However, I have five AND ...
  • 11
0 votes
2 answers
191 views

Inserting data into the VIEW , result in data being inserted into the original table, why is this happening?

I have a table as you can see below: create table table_test (col_id number(1)) The table has below data: col_id ------ 1 2 3 I create a view based on this table: create view v_table_test as ...
  • 1,356
0 votes
0 answers
677 views

Oracle Pl-SQL: Update for loop iterating for a very long time and not updating records

I trying to create a process in Oracle Apex to update certain columns of a table when a button is pressed which is related to accounting info. Moreover, I would like to validate the existence of ...
  • 11
0 votes
1 answer
684 views

Executing a query with a variable in a loop

I have two select, how can I do the same using a loop and a variable in PL SQL Developer. I have: select 1 as nm from dual; select 2 as nm from dual; I need something similar: DECLARE x NUMBER; BEGIN ...
0 votes
0 answers
526 views

How to use PIVOT or LISTAGG to solve this?

I have a table like below. USERID USERNAME ITEM 12345 JESSE ITEM1 12345 JESSE ITEM3 12345 DON ITEM2 12346 DANNY ITEM4 12346 ELISA ITEM1 12347 ANDY ITEM2 12347 ANDY ITEM4 What im trying to ...
  • 1
0 votes
1 answer
779 views

oracle pl/sql block throwing ORA-00922: missing or invalid option

I have the following segment of oracle pl/sql block DECLARE rec all_tab_columns%ROWTYPE; v_tableName VARCHAR2(100); v_columnName VARCHAR2(100); v_schemaName VARCHAR2(100); BEGIN ...
  • 13
0 votes
1 answer
232 views

left join working very slow with clause

with t1 as (select id, name from table_1 where t_date between to_date('2021-01-01', 'YYYY-MM-DD' AND to_date('2021-01-31', 'YYYY-MM-DD')) t2 as (select ...
0 votes
1 answer
92 views

ORA-00942, table does not exist [duplicate]

I'm trying to do a pl/sql with two loops. Inside second loop it gives me error: ORA-00942. I do the query manually and I don't have any error. Someone could help me please? Thanks begin ...
0 votes
1 answer
189 views

Oracle - why do I need INITRANS if I have MAXTRANS?

Probably is a dummy question, but I still don't understand why do I need the INITRANS parameter if I already have the MAXTRANS parameter. I have understand that, the default INITRANS for tables is 1 ...
  • 566
0 votes
1 answer
314 views

Why doesn't /*+ NO_INDEX*/ hint , affect the "execution plan"?

I have a sample table with below structure : create table mi_dimcustomer (customer_num number(10), <other columns> <data types>) And there is a unique index on column customer_num. I'...
  • 1,356
0 votes
1 answer
674 views

Facing error "cannot fetch plan for SQL_ID" while trying to capture the execution plan

I'm trying to capture the execution plan of my query on PL/SQL developer as you can see below : select * from vmi_dimcustomer t1 inner join vmi_factcustomer t2 on t1.customer_num = t2....
  • 1,356
0 votes
0 answers
139 views

Writing and execute a SELECT Stored Procedure in PL/SQL

I have a problem when i try to write a simple slect stored procedure in PL/SQL (I use a PL/SQL Developer IDE). I'm new in PL/SQL but i have experience in MS SQL where I wrote quite complex stored ...
0 votes
1 answer
335 views

The correct way for generating "Execution plan" for a PL-SQL statement in Oracle

I'm studying about Explain plan and Execution plan in Oracle and according to what I've read so far , The explained plan can differ from the actual plan used during statement execution. So I think ...
  • 1,356
0 votes
2 answers
37 views

How to check the source of analysis task in Oracle Performance Analyzer?

We have provided sql tuning set as input to the analysis task of performance analyzer variable l_task_id VARCHAR2(64); exec :l_task_id:=dbms_sqlpa.create_analysis_task( sqlset_name => '<...
0 votes
3 answers
129 views

Oracle PL/SQL tns exception handling

Could somebody please confirm for me that if I'm running a PLSQL script containing multiple blocks of code and I capture an error - does using the 'RAISE' command on it's own skip through to the ...
  • 41
-1 votes
1 answer
55 views

Oracle Execution Plan - Outline Question

I'm spending the last few days trying to learn some tips/tricks on tuning an Oracle query being run in Crystal Report. Obviously it has some performance issues. I suspect there are some deficiencies ...
  • 147
2 votes
2 answers
191 views

weird behaviour of NVL(<Column_name>,Sequence.nextval) when the colummn has data?

I've created a sequence like this : CREATE SEQUENCE seq_test2 MINVALUE 0 MAXVALUE 999999999999999999999999999 START WITH 0 INCREMENT BY 1; Both of these two queries show me the number 0: ...
  • 1,356
0 votes
2 answers
2k views

PLSQL Basics - outputting multiple columns and rows of information

I'm new to PLSQL so I have a very basic question that I hope somebody will help me with. I would like to run a straight forward select statement against an 11g DB and then a slightly modified version ...
  • 41

1
2 3 4 5
12