plsql Questions
18
A very easy one for someone,
The following insert is giving me the
ORA-01722: invalid number
why?
INSERT INTO CUSTOMER VALUES (1,'MALADY','Claire','27 Smith St Caulfield','0419 853 694');
IN...
Corfam asked 23/9, 2012 at 1:24
5
Solved
For example I want to make my own Boolean type and call it Bool. How do I do that?
Or a type for traffic lights, i.e. that has only Red, Yellow, Green in it (and null of course).
Weakkneed asked 14/12, 2011 at 6:51
4
Solved
This is sending me a bit mad. I'm trying to add in a variable to a procedure, but it wasn't working - I just got this error message:
[Error] Syntax check (25: 7): ERROR line 25, col 7, ending_l...
2
Is there equivalent to this T-SQL query in PL/SQL (Oracle 12c)?
UPDATE A SET A.columnA = 10 WHERE A.columnB < 30 OUTPUT INSERTED.*, DELETED.*
The query updates table A and at the same time ret...
8
How do I print a new line in PL/SQL? I'm after something similar to '\n' in the C language.
Example:
begin
dbms_output.put_line('Hi, good morning friends');
end;
I need the output is like th...
10
Solved
When subtracting timestamps the return value is an interval data-type. Is there an elegant way to convert this value into the total number of (milli/micro) seconds in the interval, i.e. an integer....
11
Solved
I have a PL/SQL function with BOOLEAN in parameter:
function get_something(name in varchar2, ignore_notfound in boolean);
This function is a part of 3rd party tool, I cannot change this.
I woul...
8
Solved
I am trying to sum INTERVAL. E.g.
SELECT SUM(TIMESTAMP1 - TIMESTAMP2) FROM DUAL
Is it possible to write a query that would work both on Oracle and SQL Server? If so, how?
Edit: changed DATE to I...
Wildlife asked 28/7, 2010 at 10:43
5
Solved
I need to print out the letters of the word 'Hello' each on its own , I wrote this :
Declare
c1 Number:=1;
c2 Varchar2(5);
begin
for c1 in 1..5
loop
select substr('Hello' , c1 , 1 ) into c2 from du...
2
Solved
Introduction
I'm facing a very strange behaviour on my Oracle SQL Server (exactly: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production) when using the Oracle Advanced Queu...
Airtoair asked 14/1, 2016 at 10:59
1
I am calling a PL/SQL procedure with an associative array as per the documentation here.
However I am getting an exception array bind type must match PL/SQL table row type though I believe that i...
4
Solved
In PL/SQL, how do I declare variable MyListOfValues that contains multiple values (MyValue1, MyValue2, etc.)
SELECT *
FROM DatabaseTable
WHERE DatabaseTable.Field in MyListOfValues
I am using ...
4
Solved
I'm using Oracle XE 10g.
Please I beg you to read my question carefully. I have a weird use case for this but please bear with it.
Let's say I have the following records:
Table person
Name YearO...
4
This is what it is showing. No output even after running the code block
SQL> DECLARE
2 message varchar2(20):= 'Hello, World!';
3 BEGIN
4 dbms_output.put_line(message);
5 END;
6 /
PL/SQL...
5
I have an pl\sql script where I want to set the table name used in the script to a variable. So, from some examples I found on the web, I wrote the code below. The first section works, so I think m...
8
Solved
I wanted to delete some unused schemas on our oracle DB.
How can I query for all schema names ?
4
Solved
When I try to insert a value which has '&' in TOAD I get a prompt for a substitution variable.
So when I try:
insert into x values('hello & world');
it throws a prompt for substituting...
14
I have one table, gender, in which only two entries are there, 'male' and 'female' number of rows with these two entries only. Now I want to write a procedure which replaces these two values and th...
Cornela asked 31/7, 2013 at 11:20
3
Solved
I want to assign a value to a rowtype's field but I don't know how to do it.
Suppose that I have a table X inside my database.
Suppose also that I have the following variables
a ( X%ROWTYPE ), ...
Goldie asked 6/2, 2014 at 15:39
7
In Oracle's PL/SQL I can create a session based global variable with the package definition. With Postgresql's PLpg/SQL, it doesn't seem possible since there are no packages, only independent proce...
Breastpin asked 5/1, 2009 at 21:9
6
Solved
I just want to concat two columns with seperator '-'.
These are the two columns, want to concat.
I am using this query to concat them
select concat(amt,endamt)as amount from mstcatrule
and ...
Pragmatics asked 28/11, 2012 at 8:22
5
Solved
In the Oracle PL/SQL, I want to test the sleep function. I am using hr schema. but it gives me error :
PLS-00201: identifier 'DBMS_LOCK' must be declared
code:
begin
DBMS_LOCK.Sleep( 60 );
end...
3
Solved
I'm getting my first experience with Oracle and TOAD (I know SSMS). I came across this "%Type" next to an input parameter in an update procedure and I have no idea what it is or what it means. I fo...
6
Solved
Below is the code i use to extract data from a table to a flat file.
BEGIN
DECLARE
file_name VARCHAR2(50);
file_handle utl_file.file_type;
BEGIN
file_name := 'table.txt';
file_handle := utl_...
7
Solved
Where do you normally store your PL/SQL constants? On the package-body level? In the specification? I've also seen some people holding constants in a specialized package just for constants. What ar...
Bookplate asked 17/2, 2010 at 9:4
© 2022 - 2024 — McMap. All rights reserved.