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

Create a procedure that will take backup of the consumer (all the reference table) where it stores all the consumer details

Create a procedure that will take backup of the consumer (all the reference tables which are basically the references to table named customer where it stores all the consumer details. ) Customer ...
-1
votes
1answer
28 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
38 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
61 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
39 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
37 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
28 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
24 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
11 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
67 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
63 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
34 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
34 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
43 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
28 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
21 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
78 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
28 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
314 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
24 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
81 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
28 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
22 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
47 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
98 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
24 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
803 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
432 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
32 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
331 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
65 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
125 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
votes
1answer
35 views

PL/SQL Statment ignored [closed]

I am really new in PL/SQL and I stuck over here. I want to write function which return allProject create or replace FUNCTION GETALLPROJECTS RETURN VARCHAR2 AS currentUserID varchar2(10); BEGIN ...
0
votes
1answer
32 views

Is there a better way of displaying 'Count' of records beside other columns in a select query?

I have a table with below structure : Test1(c_num number ,c_first_name varchar2(50), c_last_name varchar2(50)) 1)There is a normal index on c_num column. 2)The table has nearly 5 million records. I ...
1
vote
2answers
64 views

How to use 'Bind Variable' with 'With clause' in a Dynamic query using PLSQL

I have three tables with these structures: 1) z_test_a(c_num number , <other columns>) 2) z_test_b(c_num number , Amount number , <other columns>) 3) z_test_b(c_num number , Amount ...
2
votes
2answers
65 views

How to create index to improve performance of an aggregate function that creates a table in oracle

I am creating an Oracle ORDS API using APEX_JSON. I recently started using bind variables instead of string concatenation using ||. I am trying to use an in clause in my where condition. The problems ...
-1
votes
2answers
58 views

How to create a same user in different PDB while it exists in other PDBs?

I have a list of 100 PDBs in the CDB, say the account_user exists in 50 PDBs and to create the same account_user in other 50 PDBs instead of logging into each PDB and creating user, is there a way to ...
1
vote
2answers
181 views

How to use 'Bind Variables' in a 'Dynamic Query ' when the exact number of variables are not known

I have a procedure in which I'm using Dynamic SQL(variable actual_query) and I'm generating this query based on the input parameter i_tables which is a concatenation of the name of some tables. It has ...
0
votes
1answer
43 views

How to display the exact query in the output section of a stored procedure when using 'Dynamic SQL' and 'Bind variables' in Oracle

I have a Procedure as you can see below. In this procedure I have a dynamic querywhich is qry variable and I'm using Bind variable in order to eliminate the risk of injection and improve performance: ...
3
votes
1answer
38 views

How to figure out the latest version of a table in the 'recyclebin' section of PL-SQL developer

There are three tables with the same name Pantea in my recyclebin section of pl-sql developer and I don't know how to find the one that had been dropped the last (I mean the last version of the table)...

1
2 3 4 5
11