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

How to auto run oracle stored procedure

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 ...
-2
votes
0answers
18 views

select with materialized view working very slow [closed]

I have a materialized view mv_test, 900 millions rows CREATE MATERIALIZED VIEW MY_SCHEMA.MV_TEST (COL_1,COL_2,COL_3,COL_4,COL_5,COL_6,COL_7,COL_8,COL_9,COL_10,COL_11,COL_12,COL_13) TABLESPACE PROD ...
0
votes
1answer
21 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
42 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
36 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
138 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
39 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
20 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
15 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
34 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
49 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
66 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
42 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
49 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
25 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
25 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
70 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
64 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
40 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
83 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
46 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
67 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
29 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
22 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
194 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
2answers
48 views

how to select the not null value of a column when it has both null and not null value in teradata?

consider a table named "TABLE-A" with below data. ID hcc D_CD 1 null 1 1 5 1 2 null 2 2 10 2 2 20 3 3 null 3 I need to select ID,hcc fields from ...
0
votes
0answers
497 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
25 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 ...
0
votes
1answer
133 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
32 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 ...
0
votes
1answer
32 views

PL-SQL “LAST_VALUE” function does not give the desired result

I have a table with below structure: create table DOC_AMOUNT_DETAIL ( col_id NUMBER, amount NUMBER, operation_date DATE ) and some example data for this table is : col_id ...
0
votes
2answers
25 views

PL-SQL query to find the most recent record the “Operation_date” of which is lower than a specified date

I have a table with below structure: create table DOC_AMOUNT_DETAIL ( col_id NUMBER, amount NUMBER, operation_date DATE ) some example data for this table is : col_id | ...
2
votes
1answer
49 views

Can I make PL/SQL functions more strongly typed?

I like to use overloading to speed up development, and I want to structure my code to make future changes easier. I have a function that accepts a NUMBER or a VARCHAR2, but I want it to throw a ...
2
votes
2answers
83 views

Oracle Procedure alter pivoted data to unpivot and push into different destination

I have a PL/SQL procedure which takes string at runtime which is comma separated,splits it and pushes into table. Example of procedure and table is given here: Procedure is push_data. Current table ...
0
votes
0answers
138 views

Toad connection to new database oracle : ORA-12505. TNS: listener does not currently know of SID given in connect descriptor

I have created a new Oracle Database. I checked the tnsnames.ora file , I found the new instance is created in this file: SIRH = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = ...
0
votes
1answer
59 views

How to see the original text of the the function that has been wrapped by “DBMS_DDL.WRAP”

As I'm new to Oracle PL-SQL,I'm trying to be familiar with DBMS_DDL package and I used it to hide my source code,below you can see the example I'm working on: Declare v_string varchar2(32676); begin ...
0
votes
1answer
1k views

ORA-01789: query block has incorrect number of result columns

I am trying to solve this problem but somehow I couldn't. I get error ORA-01789: query block has incorrect number of result columns As I see the problem is UNION. SELECT * FROM( SELECT ...
0
votes
1answer
23 views

Combine tree SELECT statment into one

I am currently in situation that I dont know what to do, and have no idea how to solve problem. The problem is that I have one SELECT statment like SELECT PaymentType FROM ata WHERE ATAID = $ataId ...
0
votes
2answers
44 views

Trigger: Only raise error if related record was NOT created between now and last Sunday

I have a trigger that prevents inserts into a DELETES table under certain conditions: CREATE OR REPLACE TRIGGER D202574_custom BEFORE INSERT ON D202574 FOR EACH ROW DECLARE v_upd_row NUMBER; ...
0
votes
1answer
30 views

Seperate value of a specific column with “,” delimiter in Oracle using “Regular Expression”

I have a table with below structure: create table CUSTOMER_INFO ( cust_id NUMBER, cust_name VARCHAR2(50), cust_address VARCHAR2(50) ) Example Data : cust_id | cust_name | ...
0
votes
0answers
39 views

“Regular Expression” to find doubled words in a string

I have a table with below structure: create table EMPLOYEE_INFO ( e_number NUMBER, e_name VARCHAR2(50), e_personal_interest VARCHAR2(2000)) Some example data : e_number | ...
0
votes
1answer
706 views

Using 'Listagg' function and 'Regular Expression' to REVERSE the value of a specific column in Oracle

I have a table with below structure: create table TBL_TEST ( col_id NUMBER, col_name VARCHAR2(500) ) Some example data : col_id | col_name ----------------- 1 | aetnap 2 | ...
0
votes
1answer
33 views

Problem using 'Regular expression' in order to split characters of a column when there is no delimiter between them

I have a table with below structure: create table TBL_TEST ( col_id NUMBER, col_name VARCHAR2(500) ) Some example data : col_id | col_name ----------------- 1 | aetnap 2 | ...
-1
votes
1answer
31 views

Call procedure inside procedure using dynamic sql modifying the parameters getting passed

I have to call a procedure inside a procedure and replace all single quotes inside the parameter to double quotes. Ex: When i call my original proc begin data(10,'"push's the data"');end; It ...
0
votes
0answers
438 views

PLS-00306: wrong number or types of arguments in call to 'CREATE_JOB'

BEGIN DBMS_SCHEDULER.CREATE_JOB( job_name => 'DELETE_TEXT_TEST_LOG', job_type => 'STORED_PROCEDURE', job_action => '"DEV"."DELETE_TEXT_TEST_LOG"...
3
votes
0answers
75 views

Better ways to remove duplicate values of columns when using pl-sql “listagg” function in oracle

I have a table as you can see below : c_id | Name1 | Name2 | Name3 -------------------------------- 1 | a | b | c 1 | c | d | a 2 | d | e | a 2 |...
0
votes
3answers
151 views

Oracle PL/SQL does not work — bind variable not allowed

Oracle 12.2 PL/SQL When running: declare s_sql varchar2(200); begin s_sql := 'dbms_hs_passthrough.execute_immediate@mylink' || '(''exec sp_get_names'')'; execute immediate 'begin :x; end;' ...

1
2 3 4 5
11