Skip to content

Tag: oracle

ORA-04063: view has errors

I have 3 users (schemas) in my database. User A holds table X I created a view Y for user B and a view Z for user C. The views are identical: The first view works fine; for the second one I have the following error when trying to see data (from SQL developer): ORA-04063: view “User_C.Z” has errors…

How to store multiple rows in a variable in pl/sql function?

I’m writing a pl/sql function. I need to select multiple rows from select statement: if i use: it only stores one value, but i need to store all values that this select returns. Given that this is a function i can’t just use simple select because i get error, “INTO – is expected.&#8221…

Using the OR operator in a nested SQL Statement

I have a database with vendors, what they sell and where they are located. I need to search for vendors that are based off of a specific locality like a state or for vendors who sell a certain number of products. An example of this question is: What are the full names of all vendors who can supply more than

Concatenate with string but exclude when null

Oracle v11 Exclude a string in a Concat when the value is NULL? Work Query: TableA Output I’m getting Expected Output: Answer Something like this… I added simulated inputs to test all four possibilities. However, note that if you may have last name but no first name, and also first name but no las…

Create a stored procedure in a package oracle

I have created the following package in Oracle: Here is my procedure that I’m trying to create: When I try to create the procedure, I get the following messages: Error(8,3): PL/SQL: SQL Statement ignored Error(8,8): PLS-00456: item ‘P_ITEM’ is not a cursor I’m new to this with packages…

Sampling a large number of rows from a table

I want to extract a roughly 5 million row sample from a table that will contain somewhere between 10 million and 20 million rows. Due to the large number of rows, efficiency is key. As such, I am trying to avoid sorting the rows where possible, hence why I am avoiding the dbms_random.value solution that I hav…