plsql interview questions
Top plsql frequently asked interview questions
I have seen many queries with something like this..
Select 1
From table
What does this 1 mean and how will it be executed and what will it return?
Please also guide me in what type of cases this can be used???
Thanks in advance
Source: (StackOverflow)
I wanted to delete some unused schemas on our oracle DB.
How can I query for all schema names ?
Source: (StackOverflow)
In SQL I (sadly) often have to use "LIKE" conditions due to databases that violate nearly every rule of normalization. I can't change that right now. But that's irrelevant to the question.
Further, I often use conditions like WHERE something in (1,1,2,3,5,8,13,21) for better readability and flexibility of my SQL statements.
Is there any possible way to combine these two things without writing complicated sub-selects?
I want something as easy as WHERE something LIKE ('bla%', '%foo%', 'batz%') instead of
WHERE something LIKE 'bla%'
OR something LIKE '%foo%'
OR something LIKE 'batz%'
I'm working with SQl Server and Oracle here but I'm interested if this is possible in any RDBMS at all.
Source: (StackOverflow)
What is the string concatenation operator in Oracle SQL?
Are there any "interesting" features I should be careful of?
(This seems obvious, but I couldn't find a previous question asking it).
Source: (StackOverflow)
I have a PL/SQL function (running on Oracle 10g) in which I update some rows. Is there a way to find out how many rows were affected by the UPDATE? When executing the query manually it tells me how many rows were affected, I want to get that number in PL/SQL.
Source: (StackOverflow)
In SQL Server we can type IsNull() to determine if a field is null. Is there an equivalent function in PL/SQL?
Source: (StackOverflow)
I'd like to create an in-memory array variable that can be used in my PL/SQL code. I can't find any collections in Oracle PL/SQL that uses pure memory, they all seem to be associated with tables. I'm looking to do something like this in my PL/SQL (C# syntax):
string[] arrayvalues = new string[3] {"Matt", "Joanne", "Robert"};
Edit:
Oracle: 9i
Source: (StackOverflow)
For INSERT, UPDATE and DELETE SQL statements executed directly against the database, most database providers return the count of rows affected. For stored procedures, the number of records affected is always -1.
How do we get the number of records affected by a stored procedure?
Source: (StackOverflow)
Is it possible to search every field of every table for a particular value in Oracle?
There are hundreds of tables with thousands of rows in some tables so I know this could take a very long time to query. But the only thing I know is that a value for the field I would like to query against is 1/22/2008P09RR8.
<
I've tried using this statement below to find an appropriate column based on what I think it should be named but it returned no results.
SELECT * from dba_objects
WHERE object_name like '%DTN%'
There is absolutely no documentation on this database and I have no idea where this field is being pulled from.
Any thoughts?
Source: (StackOverflow)
I have a database which has a NOT NULL constraint on a field, and I want to remove this constraint. The complicating factor is that this constraint has a system-defined name, and that constraint's name differs between the production server, integration server, and the various developer databases. Our current process is to check in change scripts, and an automated task executes the appropriate queries through sqlplus against the target database, so I'd prefer a solution that could just be sent straight into sqlplus.
On my own database, the SQL to drop this would be:
alter table MYTABLE drop constraint SYS_C0044566
I can see the constraint when I query the all_constraints view:
select * from all_constraints where table_name = 'MYTABLE'
but I am not sure how to work with the SEARCH_CONDITION's LONG data type or how best to dynamically delete the looked-up constraint even after I know its name.
So, how can I create a change script that can drop this constraint based on what it is, rather than what its name is?
EDIT:
@Allan's answer is a good one, but I am concerned (in my lack of Oracle expertise) that it may not be universally true that any constraint that might have a system-generated name will have associated with it a way to remove the constraint without having to know its name. Is it true that there will always be a way to avoid having to know a system-named constraint's name when logically dropping that constraint?
Source: (StackOverflow)
I wanted to print the value of a particular variable which is inside an anonymous block. I am using Oracle SQL Developer. I tried using dbms_output.put_line. But it is not working. The code which I am using is shown below.
SET SERVEROUTPUT ON
DECLARE
CTABLE USER_OBJECTS.OBJECT_NAME%TYPE;
CCOLUMN ALL_TAB_COLS.COLUMN_NAME%TYPE;
V_ALL_COLS VARCHAR2(500);
CURSOR CURSOR_TABLE
IS
SELECT OBJECT_NAME
FROM USER_OBJECTS
WHERE OBJECT_TYPE='TABLE'
AND OBJECT_NAME LIKE 'tb_prm_%';
CURSOR CURSOR_COLUMNS (V_TABLE_NAME IN VARCHAR2)
IS
SELECT COLUMN_NAME
FROM ALL_TAB_COLS
WHERE TABLE_NAME = V_TABLE_NAME;
BEGIN
OPEN CURSOR_TABLE;
LOOP
FETCH CURSOR_TABLE INTO CTABLE;
EXIT WHEN CURSOR_TABLE%NOTFOUND;
OPEN CURSOR_COLUMNS (CTABLE);
V_ALL_COLS := NULL;
LOOP
FETCH CURSOR_COLUMNS INTO CCOLUMN;
V_ALL_COLS := V_ALL_COLS || CCOLUMN;
IF CURSOR_COLUMNS%FOUND THEN
V_ALL_COLS := V_ALL_COLS || ', ';
ELSE
EXIT;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE(V_ALL_COLS);
END LOOP;
CLOSE CURSOR_TABLE;
END;
And I am getting the output only as anonymous block completed.
Source: (StackOverflow)
When I run the following in an Oracle shell it works fine
truncate table table_name
But when I try to put it in a stored procedure
CREATE OR REPLACE PROCEDURE test IS
BEGIN
truncate table table_name;
END test;
/
it fails with
ERROR line 3, col 14, ending_line 3, ending_col 18, Found 'table', Expecting: @ ROW or ( or . or ; :=
Why?
Thanks,
Klas Mellbourn
Source: (StackOverflow)