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
1answer
18 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
35 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)?
0
votes
3answers
210 views

Trigger INSERTING into a row

I am trying to create a Trigger that will fire when a new student is being added to the table. It should update the a table called: MAJOR, by incrementing its MCOUNT domain by one. CREATE TABLE ...
0
votes
1answer
151 views

Executing an oracle variable string as I do in SQL Server

In SQL Server I can do something like: DECLARE @VAR VARCHAR(100); SELECT @VAR = SELECT * FROM SYS.DATABASES EXECUTE(@VAR) How can I execute a string that I've created inside a variable in Oracle? ...
5
votes
2answers
42k views

Commit and rollback in Oracle stored procedure

In my Oracle stored procedure, I have multiple insert and update statements like below: create or replace PROCEDURE SPTest AS BEGIN insert into emptest(empid,empname,deptno) (1,'ravi',10); ...
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 ...
1
vote
3answers
2k views

Call pl/sql function and discard result

I'm calling a function from pl/sql, but I do not require the result in this case. Is there a cleaner way to call this function without using a dummy variable? Works: junk := performWork(); Does ...
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 ...
0
votes
1answer
6k views

Not all variables bound ORA-01008: not all variables bound error

I am using Oracle SQL developer. I am running a query on employees table which is by default, having columns like first_name, last_name, employee_id, email, phone_no, hire_date, job_id, salary, ...
1
vote
1answer
62 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
123 views

Error handling in Pl/SQL

I'm coding something in PL/SQL for the first time and have come up with a situation. We are building a package to handle insertion tasks but it's supposed to handle the check violations instead of ...
1
vote
2answers
66 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: ...
0
votes
1answer
751 views

How to startup a oracle database with sql plus or with any developer tool without connecting to the Database [closed]

How to startup a database without connecting to Database. Actually what I did is I shutdown my database and then my sql plus screen hanged and so closed unhandled. then I tried to connect my ...
0
votes
1answer
1k views

create a backup table with million records

I want to move old records from TABLEA to TABLEA_AUDIT. TABLEA have around 1.5 million records and it have two nested tables. No of Records : 1557951 Size : 1024 MB I tried it by, 1.using ...
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 ...
2
votes
1answer
122 views

Why is an Oracle Package Variable Intermittently Incorrect after Multiple Updates

I am supporting an application that runs on Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production. We have a table that stores hourly data that uses triggers call a package to ...
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
33 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 ...
5
votes
1answer
8k views

Initializing Associative Array in PL/SQL

Using PL/SQL, suppose we've declared a nested table type and its instance as follows: type nested_tab is table of pls_integer; tab nested_tab;. We are then able to initialize our nested table as ...
0
votes
1answer
1k views

I get this error: 8/40 PLS-00364: loop index variable 'I' use is invalid

When I execute this stored procedure: create or replace procedure "delete-archive" is begin for i in (select * from table(RDSADMIN.RDS_FILE_UTIL.LISTDIR('DATA_PUMP_DIR')) where FILENAME like '...
1
vote
2answers
417 views

Suppress some output from PL/SQL script in Toad for Oracle

Question: In PL/SQL for Oracle running in Toad, can I suppress script output except for certain lines I want to see? e.g. I don't want create table, insert record, drop table. I do want my own ...
0
votes
1answer
687 views

perform multiple conditional join on multiple tables

I have a situation where I need to get result based on conditional joins For example, SELECT CGI.SALES_BRAND_BSNSS_ID, CGI.SALES_BRAND_CD, NVL(MTRX. matrix_score_cd,'RV1') ...
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 ========...
0
votes
1answer
304 views

Comparing multiple tables on two databases using dynamic PL/SQL

The issue I'm having is I have a block of dynamic SQL that I can't get the results from. Here's the SQL: CREATE OR REPLACE PROCEDURE GGCHECKER IS cursor c1 is SELECT TABLE_NAME, ...
1
vote
1answer
70 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 ...
3
votes
1answer
111 views

PLSQL using cursors or functions to query repeated data

Is it better to declare a commonly used lookup query as a package function or as a function (either returning rows or values or returning a sysref cursor)? Whats the difference? What are the best ...
0
votes
0answers
309 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
80 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
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 | ...
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 ...
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 ...
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 ...
0
votes
0answers
97 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
780 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
53 views

How to capture 'Deleted' and 'Updated' record after Merge in PL/SQL.TSQL 'Output' equivalent in PL/SQL

hope you're doing well. imagine I have a source and destination table like: Source(Column1 int , column2 varchar2(50)) Destination (Column1 int , column2 varchar2(50)) I want to merge these two ...

1
2 3 4 5
11