All Questions
66 questions
-1
votes
3
answers
375
views
Select statement inside a Procedure gives me a error, why?
I'm new to pl/SQL, and I'm trying to create a procedure that displays the values from the table 'BUS'. Please help me out here.
I'm using SQL*Plus as a command-line interface tool for Oracle Database ...
0
votes
1
answer
45
views
How to include the output of a stored procedure in a select
I have a table with 3 date columns:
TAB1
col_1 col_2 col_3
col_2 and col_3 are used to prepare inputs to a stored procedure TRANSFORM():
Tcol_2:=col_2;
Tcol_3:=col_3;
TRANSFORM(Tcol_2 IN OUT ...
- 305
1
vote
2
answers
415
views
encountering "PLS-00302: component 'QUANTITY' must be declared" although the column 'quantity' is declared in the table
I was trying to iterate over a cursor to get some values and my approach was similar to this answer. And the code is as follows:
SET SERVEROUTPUT ON;
DECLARE
sold_phone_id Sale.phone_id%TYPE;
...
- 1,282
1
vote
0
answers
1k
views
how to pass ARRAY to store procedure using ibatis
im working with mybatis-spring-boot lib and Im facing a problem when passing a custom array to the mapper,
when a try my log shows that the array is been setting but my stored procedure doesn't get ...
- 4,735
1
vote
1
answer
2k
views
dbms_output.put_line not printing inside procedure even after 'set serveroutput on'
The below procedure executes fine but the 'dbms' inside it does not print any output.( procedure meant to jot out the names of the emps whose salaries are yet to be entered in the table)
The table has ...
- 47
0
votes
2
answers
139
views
Is there any way to keep a track of rows inserted by a oracle function in database
I have a procedure to update the balance from start date to end date and
also I want to keep a track of number of records being inserted . I am using dbms_output.put_line to get the number of ...
- 151
1
vote
1
answer
52
views
ORACLE stored procedure issues
I have created a stored procedure with one input paramter and a cursor to get result
create or replace PROCEDURE TEST_STORED_PROC2
(DISEASEID IN varchar2, stock2_cursor OUT SYS_REFCURSOR)
IS
...
- 11
1
vote
1
answer
490
views
How to get output of SQL select statement into text
I am trying to output the results of a SQL select statement into an e-mail, preferably text. I am using PL/SQL. I already have an e-mail function where I can specify a message body. However, I am not ...
- 2,109
0
votes
3
answers
171
views
PLSQL Procedure taking lot of time to execute
I have a Oracle PLSQL procedure which has a parent cursor and 2 child cursors (created in body) with reference to parent cursor value.
When I run the SQL statement direclt, it is returning the ...
- 978
-3
votes
5
answers
12k
views
Print pascal triangle in oracle pl/sql
How to print a pascal triangle with '*' in Oracle using Pl/SQL functions, I am expecting to write in minimal lines of code. Can anyone help me ?
This is what I tried,
begin
for i in 1..5 loop
...
- 1,517
0
votes
2
answers
119
views
DEFINE vs DECLARE - escaping quotes
I have defined a variable
define myStrings = "'abc','def'"
which I later need to use inside a procedure block and convert into a table of varchars
declare
type varcharListType is table of ...
- 8,415
0
votes
3
answers
50k
views
Warning: Procedure created with compilation errors in oracle
CREATE TABLE:
create table customer (Name varchar2(10), Address varchar(40), Contact number);
CREATE PROCEDURE FOR INSERT:
CREATE OR REPLACE PROCEDURE SP_CUSTOMER (
p_name customer.Name%TYPE,
...
- 29
0
votes
0
answers
1k
views
Declare array of TABLE%TYPE
I am using the to receive collection from Java to PL/SQL procedure to insert records into table.
CREATE OR REPLACE TYPE PROJECT_TYPE IS OBJECT
(
project_id NUMBER,
project_desc VARCHAR2 (10),
...
- 14.7k
0
votes
3
answers
301
views
Procedures or Functions (Pl/sql)
I am currently learning pl/sql using oracle 10g
I have a certain confusion
When should I use stored procedures and when should i go for functions?
Please help me out with some real world example.
...
0
votes
2
answers
8k
views
Raise parent key integrity constraint in procedure for each column
I have the following code in a package which inserts data into table.
I am able to get parent key not found exception. How can I specifically get constraint violated message for each column? E.g. if ...
- 14.7k