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
1answer
13 views

Oracle PL-SQL “Regular expression” to replace each “(space)and(space)” with ',' in a string

I have a string like this x=y and g=h and u=1 and I need to replace each (space)and(space) with ,. I'm using the powerful regular_expression for this but it does not give me the desired result. ...
1
vote
0answers
23 views

Problem using the “listagg” function to produce the desired result in Oracle PL-SQL

I'm working with listagg and I have a problem here. It does not give the desired result and I just do not know where the problem is . The deposit_query might be like this : sum (dep_balance) >1 ...
1
vote
1answer
41 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, ...
0
votes
0answers
11 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
0answers
18 views

How to separate a part of a string from other parts in Oracle PL-SQL using “regular_expression”

I have a string like this : string := ' (sum(x) = 12 and sum(y) = 13 and sum(z) = 14 ) and w = 'p' AND q = 'p' AND ...
0
votes
1answer
27 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
30 views

Oracle Apex Shoot email when approvers approve an issue using triggers and apex mail [migrated]

I am developing oracle apex app where currently when issue is raised it sends email to all approvers who are Approver 1 in the particular issue dept. After this, only when both approvers select ...
0
votes
0answers
36 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
20 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 ...
1
vote
2answers
25 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
43 views

Is there a better way to join 4 tables?

hope you're doing well. I have 4 tables as below: z_branchlf_user :(USER_NAME,BRANCH_CODE) Z_BRANCH_TO_CUSTOMER :(CUS_NUM, BRANCH_CODE,REL_CODE) z_fact_customer_branch :(EFFDATE , CUS_NUM , ...
0
votes
0answers
20 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
0answers
7 views

Is there a way to provoke an exception for wrong type assignment?

I am writing a kind of job-queue handler, and I need a way to check that the correct parameter types have been specified; my approach so far has been to try to provoke an exception that I can then ...
0
votes
1answer
28 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
20 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
76 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 ...
0
votes
1answer
150 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
110 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
60 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
34 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
206 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
93 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
113 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
61 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 ...
0
votes
5answers
129 views

Alternative to Triggers

I am working on an oracle database and want to record the exact time of any transaction happening on a specific table in the schema. Using triggers on that table is out of question as the client do ...
0
votes
0answers
37 views

Case inside case in oracle query

Can anyone help me? I want to use query using CASE WHEN with logic where time less than 03:00 PM on Monday until Friday will get value start from tomorrow 03:00 PM until today 03:00 PM SELECT ...
0
votes
1answer
62 views

Toad for Oracle: Procedures, Packages, Job, and DBMS_SCHEDULER

If I have about 10 queries which contain subqueries, would it be a best practice to contain all of them in one procedure? This script creates a small fraction of the data required for a report. DROP ...
0
votes
1answer
126 views

Automating SQL scripts in Toad for Oracle (13.1)

I'm not a DBA, so bear with me. I've scheduled tasks through Toad's automation designer, but this uses Windows task scheduler. It's using Oracle 12c. All I'm doing is updating (creating/dropping) ...
1
vote
0answers
44 views

Missing CALLs on identifiers_all using PL/SCOPE

Posted this on StackOverflow, but maybe this is the right place for it: I'm trying to use the view all_identifiers from PL/SCOPE to find out calls to procedures and functions but it's not showing all ...
0
votes
1answer
44 views

Receiving error : ORA-01747 While trying to calculate COUNT(*) of a specific table

Hope you're doing well. I have two tables and as I far as I know for this scenario there is no need for their structures. When I run this query : SELECT COUNT(T.*) AS X FROM table1 T INNER JOIN ...
0
votes
0answers
57 views

How to insert from one table to another from trigger

So i have an after insert trigger to insert some data into a temp table, and i want to add to the end of the trigger the ability to insert the rows from the temp table back to the original table. This ...
0
votes
0answers
52 views

sequelize query to get different columns in for different row depending on a column value

I have a following schema module.exports = (sequelize, DataTypes) => { const Photo = sequelize.define( "Photo", { photoURL: { type: DataTypes.STRING, allowNull: ...
1
vote
2answers
256 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
21 views

update records that are there in .csv file which will be given as input [closed]

I have .csv file with list of Ids, using those IDs in file- giving them as input, I need to update the record in table, I have around 4000 Ids so 4000 update statements are not possible, need a ...
0
votes
1answer
40 views

Copy package in Production DB to Test DB with dependencies?

This is my first question here, so please let me know if this isn't the right place to ask. I'm a new programmer, fresh out of school. Primarily I develop in Python/C#, with a dash of SQL when needed....
0
votes
1answer
30 views

How to put result of SQL into bind variable

I'm trying to put the result of the query into a bind variable like this, but doesn't work: var teste number; exec :teste := (select trunc(avg(e.salary),2) from hr.employees e); How I make it to ...
0
votes
0answers
28 views

How to refresh the URI type data : insert into temptable values (SYS.URIFACTORY.getURI('URL'));

How to refresh the URI type data and reload to table , whenever the website url got refresh. My complete code: Created table with URI TYPE create table vignesh3(url URIType) loaded data ...
0
votes
0answers
42 views

Oracle Model Clause and For Iteration Constraints

I have a multidimensional model which I am trying to apply a FOR iteration against. However, I need to add a constraint to one of the dimensions in the FOR iteration (e.g. stop the MODEL from ...
2
votes
2answers
211 views

How to use “regular expression” to separate specific strings in Oracle

I have a string '(1:30,2:4,52:0,8:1)', and I need to use a regular expression to have this output: field1 field2 level 1 30 1 2 4 2 52 0 3 8 1 ...
0
votes
0answers
46 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 ...
0
votes
1answer
52 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? ...
-1
votes
1answer
885 views

Facing error 'No data found ' while trying to write a query using 'Cursor' PL/SQL in oracle

Hope you're doing well. I'm new to oracle PL/SQL and I'm trying to write a query just with the aim of practicing 'Cursor's . I have three tables with below structures : Student:(Student_id , ...
0
votes
2answers
60 views

Procedure to find a specific EXPRESSION in all tables in a specific schema in ORACLE

Hope you're doing well. Since I'm new to Oracle database and PL/SQL it's a bit difficult for me to understand complicated queries and now I'm trying to figure out what this procedure exactly does.I ...
0
votes
1answer
1k views

How to pass table name and column name dynamically in Cursor

I would like to delete millions of records from lots many tables. In the below code, i'm trying to select a table with some filter condition and trying to delete using its row id. In the below, for ...
0
votes
1answer
33 views

How to get my result from Procedure

How can i get my out parameters from my procedure. My stl_mbc_cdr table has over 100 records and i cant see my results. declare call_charge VARCHAR(10); MSG_OUT VARCHAR(50); BEGIN FOR ...
0
votes
1answer
96 views

Facing Error 'Missing expression' while trying to create a After insert trigger in PL/SQL

Hi everyone hope you're doing well. AS you might know by now I'm new to PL/SQL and I'm trying to write a trigger as you can see below : CREATE OR REPLACE TRIGGER T_Insert AFTER INSERT ON tag ...
1
vote
0answers
153 views

Start dbms_services on standby database

I'm developing a trigger which starts and stops services when the pdb is open. The point is that my trigger doesn't stop the service if I execute the ALTER PLUGGABLE DATABASE ALL SAVE STATE; with ...
1
vote
1answer
92 views

Facing error while trying to create a function using 'ACCESSIBLE BY' clause in PL/SQL

Hi everyone hope you're doing well I'm new to oracle PL/SQL and I'm trying to write a function using 'ACCESSIBLE BY' clause but I receive errors . this is my function create or replace function ...

1
2 3 4 5
10