I have two tables with names of Locations and Countries in My Oracle database. Locations table has columns with names location_id(fk), street_address, state_province, and country_id. Countries table …
Tag: plsql
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 …
Oracle PL/SQL Functions: “Invalid Datatype”
The following function checks to see if a zip code is present in a table called ZIPCODE: The schema for the ZIPCODE table is as follows: To test this function, I issued the following statement which should return false as the zip code passed as an argument is present in the ZIPCODE table: However, I receive t…
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.”…
multiple alter table in execute immediate
I want to do something like this in PL/SQL, but it throws me an error. How should I do it? Answer Problem is that you can’t run DDL directly in PLSQL. You can run them in separate execute immediates:
SP2-0552: Bind Variable “NEW” is not declared
I am trying to learn pl/sql triggers. I am trying to create a simple trigger by tracking tutorial http://www.tutorialspoint.com/plsql/plsql_triggers.htm but I got below error. I searched on the …
How do I group “or” in pl/sql
I have legacy sql query that selects bit masks (among other data), something like: How do I group this output like: That should be 3 Answer In order to do bit-wise logic you have to do a “bit” of math. (Bad puns are free around here :-). Oracle defines the BITAND function. To get a bitwise ‘…
PL/SQL: procedure to process a comparison between two tables
This is an example what i need : Table 1 : Table 2: A procedure that feeds a table3 as a result of comparison between the table 1 and table 2 Table 3: Please, i need help if someone had the same challenge 🙂 Answer Data setup: The below query would give you the differences
How to automate source control with Oracle database
I work in an Oracle instance that has hundreds of schemas and multiple developers. We have a development instance where developers can integrate their work before test or production. We want to have source control for all the DDL run in this integrated development database. Currently this is done through a pr…
Oracle Error PLS-00323: subprogram or cursor is declared in a package specification and must be defined in the package body
Can someone help me put my pl/sql procedure in a package? I’ve tried and I’m struggling with it: This is what I have, for my package specification: This is my package body where im running into the problems: if anyone could help me fix the errors i’d appreciate it: Answer Your header and bod…