Questions tagged [plsql]
PL/SQL (Procedural Language/Structured Query Language) is Oracle Corporation's procedural language extension for SQL. Questions about PL/SQL should probably be tagged "oracle" as well. Questions with regular DML or DDL statements should be tagged with "sql" and "oracle", NOT with "plsql".
0
votes
1answer
34 views
SQL select case as
I'm writing a sql statment. It looks like this
select case when c.used = 1 then 'Used'
else 'Unused'
end
from my_table
How can I name the col with the as keyword. I tried it after ...
-1
votes
1answer
46 views
Inserting the record in multithreaded environment [on hold]
I have been given a below scenario.
We have to insert data in a table . the data will be inserted from multiple processes because it is multi-threaded environment. Suppose there are 5 process p1,p2,...
0
votes
1answer
26 views
Oracle PL/SQL: how to execute a procedure with a varray as out parameter?
I'm writing a stored procedure that will receive a code and return one or more VARCHAR2 values. The number of values will vary according to the code the procedure receives.
I don't know if this is ...
1
vote
1answer
22 views
In oracle,does a stored procedure fail to execute further, if another stored procedure (being called inside the first procedure) fails
There are two stored procedures - proc_outer() and proc_inner(). proc_inner() is being called through proc_outer() as given below -
create procedure proc_outer() as
-- some statements
Begin
-- some ...
0
votes
2answers
18 views
gsource_nm VARCHAR2(1024) := '$Source: /var/opt/pat/iisi/src/plsql/p_autoeph_hpe.sql,v $';
gsource_nm VARCHAR2(1024) := '$Source: /var/opt/pat/iisi/src/plsql/p_autoeph_hpe.sql,v $';
I am not able to understand this line of code written in PLSQL programming language, especially the below ...
-1
votes
1answer
21 views
Writing multiple passes in oracle sql or pl/sql
I am used to writing code in CCL where I had been creating reports which extract data and store them in record structures, and I usually write in multiple passes. Is it possible to do that with oracle ...
0
votes
2answers
19 views
PLSQL Dynamic Row Count in results
In PL SQL is there a way to produce the Order Count per customer as follows... Thanks for your help.
Cust Order# Order Count
ABC1 011 1
ABC1 052 2
ABC1 199 3
...
-5
votes
0answers
18 views
Display department_name,employee_name from the departments that have max no employees? [on hold]
i have a two table employees and departments?
1
vote
2answers
26 views
Can I execute a conditional inside of a plsql case statement then clause?
I am building a script that replicates logic inside of a php script currently, and I've come to this section:
switch ($_POST['type']) {
case 'person':
$...
1
vote
1answer
39 views
Put Column Name in Variable and use it in output statement
Here is What i actually wanted to do, Fetch Data From a Table without knowing any columns but i.j.Column_Name gives an error, so i wanted to put it in a variable. After reading the comments i think it'...
-2
votes
1answer
19 views
PLSQL : To show data from multiple tables efficiently on page loading
I am using data from multiple tables (6 tables) from multiple schemas to show a grid on the launch of an application. Due to many outer joins it is taking very long to load the initial page. How can i ...
2
votes
1answer
21 views
Alternative for conditional subquery in Oracle 11g
I'm getting more and more experienced with oracle pl/sql but this problem seems to be persistent: I have a procedure that merges external data into a table in the database that looks something like ...
0
votes
0answers
35 views
SQL Search Function or a Procedure [duplicate]
I am not great at SQl but there is a query that's bugging me
I want to Create a function or a procedure which takes string input and traverse through all tables in my database and output those rows ...
1
vote
1answer
37 views
Declare a cursor to use it after begin
I have a project where i have to check if how many products are left to sell from a company for certain clients. A client can have many companies, also i have to refuse when a client or a product don'...
0
votes
1answer
33 views
Cursor vs FOR Loop in Oracle
So I have a block of PLSQL code that will drop all indexes as shown in the following block of code:
DECLARE
DRP_STMNT VARCHAR2(100) := '';
BEGIN
FOR I IN (SELECT INDEX_NAME FROM USER_INDEXES) ...
0
votes
1answer
45 views
PL/SQL procedure for loop though a table and change values
Basically I need to make a for loop that will loop though the amount of rows. In each row I need to check a value and change it if it meets the requirements.
I'm new to Oracle so I just started ...
0
votes
0answers
51 views
HOW TO PRINT NUMBER PATTERN AS SIDES OF A SQUARE IN PLSQL [on hold]
begin
for i in 1..5 loop
dbms_output.put(i ||' ');
end loop;
dbms_output.new_line;
for j in reverse 11.. 15 loop
dbms_output.put_line(j);
end loop;
dbms_output....
2
votes
1answer
48 views
Running Shell Script on Different Linux Server using oracle PL/SQL SP
I want to run a shell script placed on Linux server when a stored procedure is called.
The below code works like a charm if the script is placed on the same server where database is installed, say "...
1
vote
1answer
42 views
Oracle DBMS_PIPE not working correctly
I have written a small proc as below:
PROCEDURE write_ToPipe ( i_key1 VARCHAR2,
i_value1 VARCHAR2,
i_key2 VARCHAR2,
...
1
vote
0answers
25 views
CLOB value return in PLSQL oracle : invalid LOB locator specified: ORA-22275
I am getting below error while returning a CLOB from a stored procedure:
ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified: ORA-22275
What is the solution to get a clob output?...
-2
votes
0answers
33 views
How to get return value from T-SQL method using PlSQL
I have following code in PlSQL to call remote method in T-SQL. I need its return value.
FUNCTION Get_Return_Value RETURN INTEGER
IS
stmt_ VARCHAR2(2000);
c_ INTEGER;
n_ INTEGER;
...
2
votes
1answer
45 views
Too Many Rows throwing but only one is selected
I have this procedure which just deletes a row based on a column field called AppID. This procedure get's a value from another column called AppNbr based on that rows AppID column. The procedure is ...
1
vote
0answers
32 views
Loop to find pre-determined sums
This might be a bit hard to explain...
In Oracle database (using SQL and PL/SQL), I have two tables.
Table A has invoices, with the total amount for the invoice.
Table B has individual invoice ...
0
votes
1answer
40 views
SQL Error: ORA-01422: “exact fetch returns more than requested number of rows”
I'm having a hard time understanding what's wrong with my SQL-script.
I get following error message:
Fehlerbericht -
ORA-01422: Exakter Abruf gibt mehr als die angeforderte Zeilenzahl zurück
...
0
votes
0answers
13 views
ODCITable Implementation doesn't call ODCITablePrepare correctly
The main idea is than I want to implement pivot function without know columns in advance. I found script for that here but it is for one column and i want to pivot two columns.
I have function Pivot ...
1
vote
1answer
28 views
oracle trigger with error
I'm new in programming and I'm not good at it, so please help me with the next problem:
I have this trigger:
begin
if (:new.POLUARE LIKE '%W%'
OR :new.POLUARE LIKE '%ZA%'
OR :new.POLUARE ...
0
votes
0answers
28 views
Can we read a particular page from a pdf file instead of the whole document using PL/SQL?
I am able to read the whole PDF document using DBMS_LOB.READ(),but I just want to read the PDF document page by page, is it possible?
And I want this to be done using PL/SQL only.
2
votes
2answers
42 views
Oracle SQL CURSOR Update with Join and Commit
i want to update a table with the value of another table. But: I need a commit after every 50000 rows. I don't want to discuss why and I know the tip to create a new table instead of update, but this ...
0
votes
2answers
52 views
How to use PL/SQL to filter XML string
I am new to PL/SQL programming and I'm trying to write my first program.
In an Oracle database I have a table called 'ngg_basiscomponent' with a column called 'data' containing some XML string. Here ...
0
votes
1answer
59 views
ORACLE: Getting Data Extract (>50 Million records) in SQL Developer
I am having a set of records say A
A : SELECT SINGLE_COLUMN FROM SAMPLE_TABLE1 WHERE SOME_FILTER.
It returns a set of numbers
Now I need to execute a query which will get the input from A and get the ...
0
votes
1answer
35 views
Fetch name based on comma-separated ids
I have two tables, customers and products.
products:
productid name
1 pro1
2 pro2
3 pro3
customers:
id name productid
1 cust1 1,2
2 cust2 1,3
3 cust3
...
0
votes
0answers
33 views
delete from pipeline function return object
I have a pipe line function like below. if I want to delete some rows from the fetched output, how should I do that.
I tried myObjectFormat.delete after the END LOOP in below code.
but it didn't work....
0
votes
1answer
40 views
Pass Multiple information on a procedure call [duplicate]
I want to pass multiple product keys to a procedure, like product 1,2,5,7
I'm calling the procedure the following way:
call procedure_test('emp1',(1,2,5,7));
passed with the following where ...
0
votes
0answers
17 views
PLSQL update a xmltype column node
I have to update a column in xml type with some values. I only update an nodes's element if another element has certain properties. The quad02 value VALOR12TIND is only updated if valor from VALOR73 ...
0
votes
2answers
29 views
Fetching a column value for current row in before insert Oracle trigger
I am creating a trigger in oracle which gets triggered before any insert statement. I want to insert a value in column2 using that trigger, but the problem is, I have to first get the the value of ...
0
votes
1answer
44 views
Open cursor for dynamic table name in PL/SQL procedure
I want to create procedure, that will use cursor, which is the same for arbitrary tables. My current one looks like this:
create or replace
PROCEDURE
some_name(
p_talbe_name IN VARCHAR2,
...
0
votes
1answer
27 views
Gather stats on partition table by serial method
Can anyone please tell the syntax for gather statistics on partition table by serial method instead of parallel method.
I am using below query, am not sure whether it is by serial method
EXEC ...
-2
votes
0answers
46 views
Multiset operator
Why multiset operator is only applicable to nested table?
When using multiset operator with associative array I get the following error.
PLS-00306: wrong number or types of arguments in call to
...
0
votes
2answers
37 views
How to use case in pivot
I want to create a pivot of working hour by department and date.
I had a sql like
select department,sum(workinghours),date group by department,date.
then I write a pivot sql
select * from(
...
1
vote
1answer
36 views
After Insert Trigger ORA-01422: fetch returns more than requested number of rows
Can anyone help me with this code below. There are 3 tables : Customer_A1, Reservation_A1 and Invoice_A1. I am writing a trigger that will execute every time a new reservation is made.
The trigger ...
1
vote
2answers
43 views
Search pattern and remove search string characters if not found and then search it again in oracle sql
I have to write a query which is having a where clause like below:
select x from table where col1 like '%6789%';
Now the requirement I have is if above query doesn't return anything then search with ...
0
votes
1answer
32 views
PL/SQL: ORA-06550: group function not allowed here
I'm having the following errors
with this code
DECLARE v_manager_id NUMERIC;
BEGIN
SELECT MANAGER_ID INTO v_manager_id, COUNT(MANAGER_ID)
FROM DEPARTMENTS
GROUP BY MANAGER_ID
HAVING COUNT(MANAGER_ID)...
0
votes
1answer
37 views
PLS-00302: component 'EXISTS' must be declared
I'm really having issues with the following function that keeps getting the error
PLS-00302: component 'EXISTS' must be declared
when testing my function. If I delete the if statement, he gives ...
0
votes
1answer
45 views
PL-SQL - ORA-00932: inconsistent datatypes: expected DATE got NUMBER
Trying to loop through a sysrefcursor but getting ORA-00932 seems like when looping, the dates, e.g.: 1990/01/01 get operated on as if they were a division ?
set serveroutput on;
DECLARE
email ...
0
votes
1answer
26 views
How to use PL/SQL in making the subject line of an email package variable?
How can I make a variable subject of an email using PL/SQL?
Currently, I am using a constant string as the subject:
'Open Action items for review'
I need to modify it to:
'Open Action items for ...
0
votes
0answers
12 views
Maintain stock_on_quantity on item_master table whenever an item is purchased or sold
Minimum 3 stocks in item_master table should be maintained.
Table: item_master
create table item_master
(
item_no number(5) primary key,
name varchar(10),
stock_on_hand number(5) default ...
0
votes
1answer
19 views
Write a procedure to display information of those students whose total is in between specified range?
Table: stud_master
create table stud_master
(
stud_no number(5) primary key,
name varchar(10),
dob date
);
Table: stud_marksheet
create table stud_marksheet
(
stud_no number(5) references ...
0
votes
1answer
47 views
Creating SQL-Injection proof dynamic where-clause from collection in PL/SQL
I need to execute a query where the where-clause is generated based on user input. The input consists of 0 or more pairs of varchar2s.
For example:
[('firstname','John')
,('lastname','Smith')
,('...
0
votes
0answers
25 views
How to rollback and exit in sqlplus whenever SQL WARNINGS occurs in Oracle PL/SQL? [duplicate]
I am executing multiple script files listed in a file in sqlplus using @ symbol.
Example:
Consider a script file 'all_scripts.sql' contains the following content.
@my_script_1.sql
@my_script_2.sql
@...
1
vote
2answers
28 views
Replacing multiple CHR() from PLSQL string
I have a PLSQL string which contains chr() special characters like chr(10), chr(13). I want to replace these special characters from the string. I tried the following ways
select regexp_replace('...
Related Tags
oracle × 14173
sql × 6427
oracle11g × 2192
stored-procedures × 1242
oracle10g × 859
database × 815
triggers × 764
plsqldeveloper × 737
java × 579
oracle-sqldeveloper × 562
cursor × 516
oracle-apex × 451
function × 329
xml × 326
procedure × 294
sqlplus × 279
oracle12c × 237
c# × 223
regex × 223
jdbc × 180
dynamic-sql × 174
performance × 156
mysql × 154
toad × 151
sql-server × 142
Hot Network Questions
- How to realistically implement magic-users in medieval warfare?
- Would planets be cubic in my "cuboverse"?
- Can anything which isn't rotating have angular momentum?
- How to prove using pumping lemma that language generated by a(b*)c(d*)e is regular?
- Arrows at intersection in TikZ
- Genre: Is genre ever relevant to the writing process?
- Destroying a permanent with ability "as long as this card is on the battlefield"
- Is there anything I can do about an unreasonably severe teacher?
- My prefix is a place with rounds
- Is it possible to tell what focal length was used by just looking at an image and not examining metadata?
- Is it allowed to call t($variable)?
- Are there any lore reasons why an Archfey or a Celestial patron would give a character a fiendish familiar, or vice versa?
- Does any real function have this weak continuity property?
- If everyone in the world disappeared except 35 random people, how long would it take for one of them to realize they're not alone?
- What does "He has insurance, but Christ" mean?
- Non-associative operations
- Why is it illegal to castle with both hands?
- Coloring areas with TikZ
- What is the role of the diode D1 in the circuit
- Future countries – party like it's 19999!
- A mechanic for party member discouragement?
- What macros should be used as 'scratch space'?
- Bank claims debit card was physically scanned - half a continent away. What could have happened?
- How to respond when a Doctor's Receptionist asks the reason for the appointment, after confirming appointment is made?
