CODEWITHSUNDEEP
oracle-databasestored-procedures
Fering
Fering

Reputation: 322

Oracle procedure with new type parameter giving ORA-00947 Error

I am working on a procedure that accepts a large number of inputs and then returns a populated cursor. The procedure will be called from a Java application. Yes, this would be easy to build as an ad-hoc query, but I am trying to only use proper function/procedure calls in my code.

I will be passing in a unknown number of parameters. I solved this issue with this answer which suggests a new type.

However now I get "PL/SQL: ORA-00947: not enough values" error and I can't figure out the issue. I know its concerning the where clause sub-query.

DESC BILINGUAL
Name        Null     Type          
----------- -------- ------------- 
KEYFIELD    NOT NULL VARCHAR2(16)  
PURPOSE              VARCHAR2(128) 
CONTENTS_EN NOT NULL VARCHAR2(128) 
CONTENTS_FR NOT NULL VARCHAR2(128)

This is an example of how it will be used and it works.

 select keyfield, contents_en, contents_fr
 from bilingual
 where keyfield in ('1111', '1111A');

Here is the procedure that I am writing.

CREATE OR REPLACE TYPE STRING_TABLE AS TABLE OF VARCHAR2(16);

CREATE OR REPLACE PROCEDURE DOCSADM.BILINGUAL_VALUES (
IN_KEYS string_table,
CUR OUT SYS_REFCURSOR) AS
BEGIN
    SELECT KEYFIELD, CONTENTS_EN, CONTENTS_FR
    INTO CUR
    FROM BILINGUAL
    WHERE KEYFIELD IN (SELECT column_value FROM TABLE(IN_KEYS));
END BILINGUAL_VALUES;

Upvotes: 0

Views: 91

Answers (1)

Padders
Padders

Reputation: 276

Generally speaking in PL/SQL you would open a ref cursor with OPEN .. FOR SELECT .. syntax rather than SELECT .. INTO (although support for CURSOR () functions in SQL means you could technically do either), e.g.

CREATE OR REPLACE PROCEDURE docsadm.bilingual_values (
   in_keys string_table,
   cur OUT SYS_REFCURSOR)
AS
BEGIN
   OPEN cur FOR
      SELECT keyfield,
             contents_en,
             contents_fr
      FROM   bilingual
      WHERE  keyfield IN (SELECT COLUMN_VALUE FROM TABLE (in_keys));
END bilingual_values;
/

Upvotes: 3

Related Questions