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
0answers
8 views

Extract SDO_GEOMETRY vertices into a nested table

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 ...
0
votes
0answers
27 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 ...
0
votes
2answers
47 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 ...
0
votes
0answers
26 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 ...
0
votes
1answer
56 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
0answers
53 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 ...
0
votes
1answer
34 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 ...
0
votes
1answer
37 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
1answer
41 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
1answer
32 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 ...
0
votes
1answer
30 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'...
0
votes
1answer
35 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....
0
votes
0answers
38 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
1answer
50 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 ...
0
votes
2answers
19 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
3answers
34 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 ...
-1
votes
1answer
48 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 ...
2
votes
2answers
123 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: ...
0
votes
2answers
140 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 ...
-2
votes
1answer
44 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 ...
0
votes
1answer
32 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:...
-3
votes
1answer
87 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 ...
-1
votes
1answer
42 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 ...
2
votes
3answers
407 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: ...
0
votes
1answer
42 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 ...
0
votes
1answer
99 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 ...
0
votes
0answers
27 views

Plsql query to split data based on startdate and end date

I want to split data based on start ,end date ,date range configured (yearly/monthly/weekly/quarterly) For example if the Startdate is 2015/10/02 and the Enddate is 2015/12/22 and my date range is ...
-1
votes
1answer
40 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) ...
0
votes
1answer
79 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)?
1
vote
2answers
68 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 ...
0
votes
0answers
47 views

Use Python to parallelize processing of rows, or is there a native way?

As mentioned in a recent question of mine, I'm new to Oracle: Running Oracle's sqlldr on VirtualBox VM from my MacBook (client) machine I'm back, this time with a question about the best way to ...
-1
votes
2answers
1k 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 ...
0
votes
1answer
31 views

Better way of handling incorrect date format in a column with "char(10)" data type / TRY_CONVERT equivalent in PLSQL

I have a source table with below structure: create table customer_info (customer_num number, birth_date char(10)) Unfortunately the birth_date column data type is char(10) instead of date. ...
0
votes
0answers
27 views

In what sort of way "v$transaction" view will help DBA's to improve the performance of the query?

I've just started learning some DBA tips and techniques in Oracle and I came across v$transaction view. I've read some articles regarding this view but some things are still ambiguous ! I want to know:...
0
votes
0answers
12 views

Search table data based on attribute and storing the output in nested table collection which is traversed through ref cursor

CREATE OR REPLACE PACKAGE BODY search_pkg IS /* PROCEDURE TO SEARCH PURCHASE DATA INPUT IS PURCHASE ID INPUT : PURCHASE_ID OUTPUT : DATA IS STORED IN THE COLLECTION DECLARED IN ...
0
votes
1answer
26 views

User Permisssions White List vs Black List

I have Applications, Roles, Users, Permissions, Application_Permissions, User_Permissions, Role_Permissions, Application_Roles and Role_Permissions tables. So, permission can be defined at application ...
1
vote
2answers
102 views

Query to find all dates between two specified dates using plsql

I have a table with below structure create table test_table ( customer_num NUMBER, card_number char(2), card_issue_date DATE, card_expire_date DATE ) Example data is: ...
1
vote
1answer
65 views

Query to find the "Number of days" a customer has an active card between a specified interval

I have a table with below structure : create table test_table ( customer_num NUMBER, card_number char(2), card_issue_date DATE, card_expire_date DATE ) Example data is: ...
0
votes
1answer
18 views

Need help identifying how to make a function selectable using table() clause and a custom data type

I would like to be able to run the following code to select records: Desired Syntax select * from table(my_new_package.get_data(123)) I have been successful in creating code that compiles, but I am ...
0
votes
0answers
80 views

Index not used in a remote query when values are supplied from a PL/SQL collection

I have a table with primary key column on a remote database: create table tab ( id primary key, pad ) nologging as select level id, lpad('x', 800) pad from dual connect by level <= 100000; If ...
0
votes
1answer
252 views

how to get the difference between 2 tables in 2 different schema's in Oracle? [duplicate]

I am using Oracle 11G. I have a table called Tickets in the schema called 'tickets1" I have the same table structure in another schema called 'tickets2' I want to find out What data is the same ...
0
votes
1answer
47 views

Does ' /*+parallel(<Specific Table >,4)*/ ' have better impact on query performance in comparison with simple parallel hint '/*+parallel(4)*/'?

I have three tables with below structure: create table customer_info (customer_num number, customer_firstName varchar2(100), customer_lastName varchar2(100), branch_code varchar2(...
0
votes
1answer
69 views

How can I use COUNT in the same query with ROUND() and AVG()?

I need to write a query where I need to first count the tickets sold for spectacles in a theater, then calculate the average number of tickets sold in each theater and to be round it to two decimal ...
1
vote
1answer
37 views

Comparing records from same table and deleting the old ones

Greeting, I have a beginner experience with programming in Oracle PL/SQL. The problem I am trying to solve is that in a hypothetical situation, I want to compare records in the same table based on the ...
0
votes
1answer
23 views

Procedure with "bind varibale" does not return the desired result set

I have a table with below structure: create table CUST_MODERN_CHANNEL_BRANCH ( customer_Num NUMBER, channel_Code NUMBER(5) ) Some example data : Customer_Num Channel_Code ========...
1
vote
1answer
284 views

Receiving error "invalid identifier" while trying to update a table in Oracle

I'm trying to transform a Merge query into an Update query for getting better performance but I could not succeed. Tables structure: create table TEST_FILE_MERGE ( customer_num NUMBER, ...
0
votes
0answers
749 views

Oracle - Set Object to NonEditionable

I am having an issue with Editioning in an Oracle 19c database. I have never heard of this feature before today, but I have found that somehow a number of objects in the database have had their ...
0
votes
1answer
35 views

Write Oracle proc/function to create dynamic views with changing column headers

I had similar requirement earlier but now i have been given a clear approach that needs to be implemented. I need to write a procedure(preferably)/function, which would on basis of app_id passed as ...
1
vote
1answer
195 views

Convert raw or hex value to signed 2's complement via Oracle SQL

Is there a way to convert either HEX or RAW value to signed 2's complement via Oracle SQL? example: hex: 8508 > dec: 34046 > dec from signed 2's complement: -31480 The number that I am trying to ...
0
votes
1answer
38 views

PLS_INTEGER giving Error

My procedure giving numeric overflow error: ERROR at line 1: ORA-01426: numeric overflow ORA-06512: at "HDM_DBUSER2.HDM_CLEANUP_UTIL", line 1196 because it exceeds pls_integer maximum ...

1
2 3 4 5
12