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

Why PLSQL optimize level is not providing expected result? [closed]

I am trying to understand complier optimizations in plsql in depth.Theoretically, default optimisation level PLSQL_OPTIMIZE_LEVEL is set to 2.To get better performance, we can set it to 3.To explain ...
0
votes
1answer
26 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
43 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
28 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
35 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
21 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
30 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"...
2
votes
0answers
52 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
59 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
33 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
46 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
60 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
36 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 ...
0
votes
2answers
94 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
41 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: ...
2
votes
1answer
34 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)...
2
votes
1answer
46 views

Raise application error in a stored procedure when inputs contain keywords like 'delete' , 'insert','update'

I have a Stored Procedure which gets 6 input queries and the inputs are the same as where clause of a query. For instance where x=1 and z=5 and e=4. The Application makes these input strings and pass ...
3
votes
1answer
85 views

Convert column value stripping html tags into sql view with rows and columns

I have a table named data with a column pid & display_data. Pid is the serial number of all rows. The value of display_data column is as below : For each row display_Data has values in the format =...
0
votes
1answer
40 views

How to prevent a highly dynamic PL-SQL query from injection using bind variables

I have a procedure as you can see below: create or replace procedure Injection_test(qry1 varchar2, qry2 varchar2, ...
0
votes
0answers
37 views

Pass Variables to SQLDeveloper Report in anonymous block

I can pass variables to anonymous block of this form SQL> set serveroutput on; SQL> DECLARE a number := &i_nr; BEGIN ..... But it doesn't works into SQLDeveloper PL/SQL DBMS Output (...
0
votes
2answers
74 views

What is the main difference between '[[:space:]]' and '\s' in Oracle pl-sql regular expression?

I have a string like this x=y AND z=y AND p=q , I need to replace every (space)AND(space) pattern with * so that the original string turns into x=y*z=y*p=q. I'm using Regular expression for this ...
1
vote
0answers
24 views

Unit Testing ORDS - Oracle Rest Data Services

I've been wanting to develop some unit tests for my ORDS functions as part of my push for better CI/CD - I am running ORDS functions on an Oracle Autonomous Database 19c, and while I'm developing a ...
0
votes
1answer
34 views

The difference between using parallelism “/*+parallel(4)*/” in the subquery or the outer query in terms of performance in Oracle

I have the below query : select /*+parallel(4)*/ ---> First Location <Desc_1> Amount, <Desc_2> Amount, <Desc_3> Amount from (select /*+parallel(4)*/ ---> ...
0
votes
0answers
25 views

In ORACLE 11g , triggers, how i can get EXCEPTIONS, BEFORE they can be trigged

I had a package, very extensive who cannot be modified nor debugged. So i wrote an trigger then to get the table info before the process crash, BUT, in testing of the trigger as i insert in the table, ...
0
votes
0answers
41 views

“INNER JOIN” vs “INTERSECT” performance comparison when dealing with huge amounts of data in Oracle

I have two tables with the structure below: create table Table_One (Ef_date Date, Customer_Num Number, <Other_Columns> <Combination of Number/varchar2(50)>); create ...
2
votes
2answers
46 views

Merge Datetime Ranges

I have been struggling to merge datetime ranges in oracle SQL/PLSQL (Database Standard Edition 11gR2). I am trying to merge datetime ranges so that the following data order_id start_date_time ...
1
vote
1answer
43 views

Are there better alternatives rather than using a “Full outer join” when it is not possible to use a “left or right join”?

I have two tables with below structure : Create table Modern_transaction (Eeffective_date Date, Customer_Num Number, Branch_Code Number, MTrns_type_one Number, ...
1
vote
1answer
48 views

Error “ORA-12726:unmatched bracket in regular expression” while trying to separate a part of a string from other part

I have a string like this : v_sgtring:'sum:[sum(x)>1 and sum(y)>2 and sum(z)>3],where:[n=1 and m=2]' and I need to separate sum:[] from where:[] and assign them to different variables like ...
0
votes
1answer
37 views

Error “ORA-0091:Invalid Character” when executing a PL-SQL statement in Oracle [closed]

I'm new to oracle pl-sql and I'm facing error ORA-0091:Invalid character when executing this query below. But when I comment the Execute Immediate statement and use dbms_output.put_line the result of ...
0
votes
0answers
39 views

Trigger to loop and send incremental email based on condition [duplicate]

I have a requirement in Oracle apex to implement dynamic approval workflow in issue tracker. I am trying to write a trigger for the same as below. I need to further modify it so that it runs ...
0
votes
1answer
24 views

How do I get data from multiple tables if the agency_code doesn't match

I have a table with these fields Employment_Form (table name) employee_number full_name agency_code The second table is HumanResources(table name) employee_number full_name agency_code The ...
2
votes
2answers
30 views

how to select the particular records with filter conditions when duplicates found in combination of two columns?

consider a table "SCHOOL" table, which contains the below data when, duplicates found in NAME and LAST NAME combination it should look up for the STATUS which is "Active". if still it has duplicates, ...
0
votes
0answers
21 views

Is it possible for a FOR LOOP cursor execute operations asynchronously?

I was having a problem in which the number in a table isn't updated between each commit, it was supposed to be incremented 1 by 1 but it was been incremented 2 by 2, I included logs between the ...
0
votes
1answer
32 views

ignored in stored procedure with clause

Hi i'm currently working with oracle that will give me my requered output. I able to make the query but when i'm putting it in stored procedure it gives me error of Error(11,1): PL/SQL: SQL Statement ...
0
votes
1answer
24 views

How to assign variable as column value

I'm new with Oracle. This is something simple that I'm just not getting. I'm in a situation where I need to split a string with values separated by commas (i.e. 25,27) and "decode" it (example: if 25 ...
-1
votes
1answer
94 views

PL/SQL returning Procedure value and putting it in an insert clause [closed]

I'm doing an assignment and the last question states that I call the procedure from question 2 and function from question 3 and then put them in an insert clause(question 4). Write a PL/SQL Code for ...
0
votes
1answer
462 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 '...
2
votes
0answers
111 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 ...
1
vote
2answers
41 views

Input to be printed in different output format

I have a value (e.g. ORACLE) and I would like to print each letter on an individual line using an Oracle query. Input value: ORACLE Output: O R A C L E
0
votes
1answer
20 views

first row and last row fields value from where data changed [closed]

Assume I have a table like this: I'm trying the have a output like following picture How best to achieve this?
1
vote
1answer
69 views

PL/SQL Script Line with Only a Dot…What is the purpose/function of the dot?

I have inherited a pl/sql script from a previous dba that worked at my company. It is used to "clean up" failed distributed transactions. The script is easy enough to understand, except for one line ...
0
votes
1answer
54 views

How to get the SQL_ID that is blocking another SQL?

Well, I'm personalizing a useful script that I found and I need put in this script what is the sql_id of the SQL that is blocking another SQL. Here is my script, if you try this, You never will get ...
0
votes
1answer
344 views

How do I create a simple SELECT statement as a stored procedure in PL/SQL? - Regardless of the best practises around Stored Procs

Was wondering how to create a simple select statement to return all records within a table in PL/SQL such as CREATE OR REPLACE PROCEDURE getStudents AS C1 SYS_REFCURSOR; BEGIN OPEN C1 FOR SELECT * ...
0
votes
0answers
104 views

SQL query tune to replace UNION - Oracle

SELECT FN_ENQDATA_STRING (col1) FROM tab1 WHERE tab1.col2 = tab2.col2 AND tab1.col3 = ? I also have tab2 which has same columns as tab1 and i need to call function for that too. What I can do is ( ...
1
vote
2answers
207 views

PL/SQL - Drop User inside BeginEnd block

I have some experience with SQL Server but now I'm starting to work with PL SQL and I'm facing some problems because the difference between the two technologies. I have some scripts to drop my ...
0
votes
0answers
91 views

Import Schema dump into multiple schemas

I have a production schema and for upgrade they have decided to use 3 schema instead of one. For deploying this into production I have to take dump of schema A and import into 3 different schema with ...

1
2 3 4 5
11