Build select stored procedure in Oracle 12

Question

I am primarily a .NET programmer, and occasionally build MS SQL Server stored procedures.

I need to build a procedure in Oracle to select and return some records, based on one or many parameters. I tried building one already, but have stopped in frustration.

Here is an example of what I want to create :

CREATE OR REPLACE PROCEDURE P_TS_COLLISIONS_SEARCH(
        county IN VARCHAR2,
        township IN VARCHAR2,
        col_MRN IN VARCHAR2,
        answer OUT VARCHAR2)

AS
BEGIN
    SELECT COLLISION_MASTER_RECORD_NUMBER, CITY, ROAD_NAME
      INTO answer
      FROM DWOBDEV.OBIEE_TS_COLLISION_FACT_VW
    WHERE  COLLISION_MASTER_RECORD_NUMBER = col_MRN
    AND    TOWNSHIP = township
    AND    COUNTY = county;

END P_TS_COLLISIONS_SEARCH;

I get these errors:

Error(9,5): PL/SQL: SQL Statement ignored Error(11,7): PL/SQL: ORA-00947: not enough values

How would I build this in Oracle 12?

This question does not show ANY effort or research. Here is a great place to start. https://www.google.com/search?q=how+to+create+a+stored+procedure+in+oracle+with+parameters&ie=utf-8&oe=utf-8 — Sean Lange, Mar 03 '17 at 15:30
Try to run the query that's in the procedure; it will give a result with 3 columns. In your procedure, you're trying to put these 3 values inside a single scalar variable, so... Also, notice that if your query will give more than one row, scalar variables will not be enough to host the results — Aleksej, Mar 03 '17 at 16:20
If you just want a query that returns results to the prompt, try something like this: http://stackoverflow.com/a/40360471/230471 — William Robertson, Mar 03 '17 at 17:24

score 1 · Accepted Answer · answered Mar 03 '17 at 16:50

You have a couple of issues.

You are selecting three columns INTO a single column. If you use this to return a single row, you have to match the number of columns to the number of variables selected to.
You are selecting the id into the result, but you already have it as an input value. Reselecting it would be redundant.
Also, you seem to be passing in an id already, it is unclear if this is a PK and if it is, you would not need to query on additional values. It would make a bit more sense to just pass in a single ID argument to get a specific row then an additional proc to take other arguments to match on other column values.

Here is a version of your proc that works. If you are intending to return more than one row, you'll need to change to return a cursor instead. Use the documentation / web search to find an example.

create table OBIEE_TS_COLLISION_FACT_VW
(collision_master_record_number number,
 county varchar2(50),
 township varchar2(50),
 city  varchar2(50),
 road_name varchar2(50));

insert into obiee_ts_collision_fact_vw values (1, 'WINCHESTER',     'SPRINGFIELD', 'MOSCOW', 'STATION');

CREATE OR REPLACE PROCEDURE P_TS_COLLISIONS_SEARCH(
    county IN VARCHAR2,
    township IN VARCHAR2,
    col_MRN IN VARCHAR2,
    city_o OUT VARCHAR2,
    road_name_o OUT VARCHAR2)
AS
BEGIN
    SELECT CITY, ROAD_NAME
      INTO city_o, road_name_o
      FROM OBIEE_TS_COLLISION_FACT_VW
    WHERE  COLLISION_MASTER_RECORD_NUMBER = col_MRN
    AND    TOWNSHIP = township
    AND    COUNTY = county;

END P_TS_COLLISIONS_SEARCH;

declare
    l_id number := 1;
    l_township VARCHAR2(50) := 'TOWNSHIP';
    l_county VARCHAR2(50) := 'COUNTY';
    l_city varchar2(50);
    l_road_name varchar2(50);
begin
    P_TS_COLLISIONS_SEARCH(l_county, l_township, l_id, l_city, l_road_name);
    dbms_output.put_line(l_city||'|'||l_road_name);
end;

William Robertson · Answer 2 · 2017-03-04T10:55:34.443

A bit of a guess as I don't know what kind of output you want, but maybe this:

create or replace procedure p_ts_collisions_search
    ( p_col_mrn   obiee_ts_collision_fact_vw.collision_master_record_number%type
    , p_township  obiee_ts_collision_fact_vw.township%type
    , p_county    obiee_ts_collision_fact_vw.county%type )
as
    rc sys_refcursor;
begin
    open rc for
        select collision_master_record_number
             , city
             , road_name
        from   obiee_ts_collision_fact_vw
        where  collision_master_record_number = p_col_mrn
        and    township = p_township
        and    county = p_county;

    dbms_sql.return_result(rc);
end;

Note that I have prefixed my parameters with p_ to differentiate them from the database columns, because and township = township doesn't achieve much. (An alternative would be to label them with the procedure name, e.g. p_ts_collisions_search.township.)

Dummy data:

create view obiee_ts_collision_fact_vw
as
select 1 as collision_master_record_number
     , 'London' as city
     , 'Bond Street' as road_name
     , 'West End' as township
     , 'Lalaland' as county
from dual;

Demo in SQL*Plus:

SQL> exec p_ts_collisions_search(1, 'West End', 'Lalaland');

PL/SQL procedure successfully completed.

ResultSet #1

COLLISION_MASTER_RECORD_NUMBER CITY   ROAD_NAME
------------------------------ ------ -----------
                             1 London Bond Street

However, it won't work everywhere, as the client needs to be able to process implicit results:

SQL> call p_ts_collisions_search(1, 'West End', 'Lalaland');
call p_ts_collisions_search(1, 'West End', 'Lalaland')
     *
ERROR at line 1:
ORA-29478: Implicit result cannot be returned through this statement.
ORA-06512: at "SYS.DBMS_SQL", line 2785
ORA-06512: at "SYS.DBMS_SQL", line 2779
ORA-06512: at "WILLIAM.P_TS_COLLISIONS_SEARCH", line 17

Alternatively, you could return the result set explicitly via an out parameter:

create or replace procedure p_ts_collisions_search
    ( p_col_mrn   obiee_ts_collision_fact_vw.collision_master_record_number%type
    , p_township  obiee_ts_collision_fact_vw.township%type
    , p_county    obiee_ts_collision_fact_vw.county%type
    , p_results   out sys_refcursor )
as
begin
    open p_results for
        select collision_master_record_number
             , city
             , road_name
        from   obiee_ts_collision_fact_vw
        where  collision_master_record_number = p_col_mrn
        and    township = p_township
        and    county = p_county;
end;

Demo:

SQL> var results refcursor
SQL> set autoprint on
SQL> exec p_ts_collisions_search(1, 'West End', 'Lalaland', :results);

PL/SQL procedure successfully completed.

COLLISION_MASTER_RECORD_NUMBER CITY   ROAD_NAME
------------------------------ ------ -----------
                             1 London Bond Street

Thanks all for the input! It's been very helpful! – R. Jo Mar 08 '17 at 20:54 — R. Jo, Mar 08 '17 at 20:54

Build select stored procedure in Oracle 12

2 Answers2