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.
596
questions
1
vote
1
answer
528
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 ...
0
votes
2
answers
170
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
1
answer
100
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
----------------------------------
|...
0
votes
3
answers
964
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)...
0
votes
0
answers
153
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 ...
0
votes
1
answer
2k
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
551
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
...
0
votes
0
answers
81
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
2
answers
2k
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
0
answers
2k
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
1
answer
3k
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
1k
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
1
answer
2k
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
1
answer
232
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
789
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
1
answer
2k
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
1
answer
3k
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
0
answers
276
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 ...
1
vote
1
answer
1k
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
2
answers
97
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
335
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
1
answer
106
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
2
answers
379
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
2
answers
4k
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
1
answer
971
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
1
answer
97
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
1
answer
1k
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
1
answer
456
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 ...
3
votes
3
answers
13k
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:
...
1
vote
1
answer
3k
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
1
answer
3k
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 ...
-1
votes
1
answer
2k
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)
...
2
votes
1
answer
10k
views
How can I identify sequences used by tables?
Is there a way to query an Oracle database that would return:
a listing of all the tables in the database
which sequence it is using (if any)
1
vote
2
answers
158
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 ...
-1
votes
2
answers
6k
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
1
answer
373
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
1
answer
33
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
2
answers
5k
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
1
answer
95
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
1
answer
246
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
1
answer
2k
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
1
answer
519
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
1
answer
553
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
1
answer
651
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
1
answer
226
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
1
answer
964
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
0
answers
3k
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
1
answer
359
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
1
answer
818
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
1
answer
216
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 ...