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
-1 votes
1 answer
38 views

ORACLE PL/SQL - using decode

I need to list all undo tablespace names in an Oracle (19c) database (regardless of whether they're used or not) from the ts$ table. My SQL statement below gives that info but it also lists all the ...
user avatar
  • 29
-1 votes
1 answer
24 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 :...
user avatar
-1 votes
2 answers
35 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&...
user avatar
0 votes
0 answers
35 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(...
user avatar
0 votes
1 answer
28 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 ...
user avatar
  • 1,294
-6 votes
1 answer
29 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 ...
user avatar
1 vote
1 answer
32 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 ...
user avatar
1 vote
0 answers
48 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/...
user avatar
  • 11
0 votes
1 answer
21 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 ...
user avatar
  • 1,294
1 vote
1 answer
66 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 = ...
user avatar
  • 1,294
0 votes
1 answer
86 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 ...
user avatar
  • 1,294
0 votes
0 answers
23 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 ...
user avatar
  • 101
0 votes
0 answers
60 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:...
user avatar
  • 3
1 vote
1 answer
95 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 ...
user avatar
  • 506
0 votes
2 answers
67 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 ...
user avatar
0 votes
0 answers
103 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 ...
user avatar
  • 506
0 votes
1 answer
21 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 ---------------------------------- |...
user avatar
  • 1,294
0 votes
3 answers
72 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)...
user avatar
  • 1,294
0 votes
0 answers
45 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 ...
user avatar
  • 3
0 votes
1 answer
387 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 ...
user avatar
0 votes
1 answer
184 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 ...
user avatar
  • 1,373
0 votes
0 answers
34 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 ...
user avatar
  • 11
0 votes
2 answers
72 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 ...
user avatar
  • 1,294
0 votes
0 answers
309 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 ...
user avatar
  • 11
0 votes
1 answer
268 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 ...
user avatar
0 votes
0 answers
280 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 ...
user avatar
  • 1
0 votes
1 answer
386 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 ...
user avatar
  • 13
0 votes
1 answer
73 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 ...
user avatar
0 votes
1 answer
51 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 ...
user avatar
0 votes
1 answer
88 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 ...
user avatar
  • 506
0 votes
1 answer
127 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'...
user avatar
  • 1,294
0 votes
1 answer
264 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....
user avatar
  • 1,294
0 votes
0 answers
95 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 ...
user avatar
0 votes
1 answer
139 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 ...
user avatar
  • 1,294
0 votes
2 answers
34 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 => '<...
user avatar
0 votes
3 answers
71 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 ...
user avatar
  • 29
-1 votes
1 answer
50 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 ...
user avatar
  • 147
2 votes
2 answers
157 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: ...
user avatar
  • 1,294
0 votes
2 answers
894 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 ...
user avatar
  • 29
-2 votes
1 answer
94 views

How to auto run oracle stored procedure [duplicate]

Is there any possibility to auto run procedure after a specific time/date? For example I want to auto run this stored procedure after 2 days: CREATE OR REPLACE PROCEDURE deleteresetlinks AS BEGIN ...
user avatar
0 votes
1 answer
45 views

How can I make this DECLARE PROCEDURE working?

I'm new to Oracle DB, coming from MS SQL Server. I created this script: DEFINE USER_NAME = &1 DEFINE PASSWORD = &2 DEFINE TABLESPACE = &3 CONNECT &DB_ADMIN/&DB_PWD@//localhost:...
user avatar
  • 123
-3 votes
1 answer
230 views

Cannot connect new users to pluggable database

I'm new to Oracle DB (coming from MS SQL Server). For a start, I downloaded a Docker container for Oracle Database 18c XE. When I run that container, after creating a pluggable database, I don't seem ...
user avatar
  • 123
-1 votes
1 answer
72 views

Oracle SQL: How to get order of operations on multiple AND and OR conditional groups in this query

I am not very familiar with Oracle operators precedence, my question is how do ORACLE process the order of operations in multiple AND and OR blocks, and how can I properly use the parentheses so the ...
user avatar
  • 99
2 votes
3 answers
2k views

How to solve error "ORA:01-006 :bind variable does not exist" when working with bind variables?

I have a table with below structure and data : create table TEST_TABLE ( item_number NUMBER, item_name VARCHAR2(50), item_col VARCHAR2(50), item_pol VARCHAR2(50) ) Sample data: ...
user avatar
  • 1,294
0 votes
1 answer
61 views

How to view user created table in OracleDB?

So I created a table in my OracleDB, however when I use the command to view all tables in my environment, it gives a bunch of other tables, scrolling and filling my command prompt with lot of other ...
user avatar
0 votes
1 answer
861 views

PL/SQL: Selecting multiple values from the same column

We have a table in which all kinds of different Words are stored with a unique id each. In other tables, certain fields which should contain words instead only contain the id of these words. Let's ...
user avatar
-1 votes
1 answer
416 views

Oracle SQL - How to group by a column and count other column for each different value?

I'm struggling trying to get the desired result when querying 2 tables. These are the tables: Table1 column1 (fk) column2 other_columns... 2 a 2 a 3 c 4 a 4 b 4 b 4 d Table2 column1 (pk) ...
user avatar
  • 3
1 vote
1 answer
1k views

PL/SQL - how to identify sequences used by tables?

Is there a way to get a resultset from executing a query against an Oracle database that would return a listing of all the tables in the database and which sequence it is using (if any)?
user avatar
  • 133
1 vote
2 answers
75 views

Oracle procedure sometimes runs endlessy, other times finishes instantly

The procedure always compiles instantly and I'm pretty sure I don't have any infinite loops or some other bug in there. When running the procedure in SQL Developer it sometimes finishes instantly (as ...
user avatar
  • 113
-1 votes
2 answers
2k views

sql query to fetch exchange rate by dividing the amount in the same table

Given a table sales, with item selled in two different currencies how to get the exchange in EUR Sales Table: item currency Amount A USD 1000 A EUR 833.33 B JPY 50 B EUR 200 C EUR 10 C CAD 2 ...
user avatar
  • 11

1
2 3 4 5
12