CODEWITHSUNDEEP
sqloracle-databasestored-proceduresplsql
Newbie
Newbie

Reputation: 1

Oracle procedure, how create when i need in column multiple results

I need help with create procedure in oracle like as SELECT statement:

CREATE TABLE test_schema.test_table
     (
      test1_col INTEGER,
      test2_col VARCHAR2(7)
      )
;

INSERT INTO test_schema.test_table VALUES (1,'TEST1');
INSERT INTO test_schema.test_table VALUES (1,'TEST1_1');
INSERT INTO test_schema.test_table VALUES (2,'TEST2');
INSERT INTO test_schema.test_table VALUES (2,'TEST2_2');
INSERT INTO test_schema.test_table VALUES (3,'TEST3');
INSERT INTO test_schema.test_table VALUES (3,'TEST3_3');

CREATE OR REPLACE PROCEDURE test_schema.test_name_procedure
(
  var_in_test IN VARCHAR
, var_out_test1 OUT NUMBER
, var_out_test2 OUT VARCHAR
) AS
BEGIN
SELECT
   test1_col
   test2_col
 INTO   
   var_out_test1
   ,var_out_test2
 FROM test_schema.test_table
WHERE test1_col = var_in_test;

END;

When i execute procedure:

I get information:

SQL Error [900] [42000]: ORA-00900: invalid SQL statement

Error : 900, Position : 0, Sql = EXECUTE test_schema.test_name_procedure(2), OriginalSql = EXECUTE test_schema.test_name_procedure(2), Error Msg = ORA-00900: invalid SQL statement

ORA-00900: invalid SQL statement

I need result in separate column.

When i tried execute procedure from begin and end, i get information:

BEGIN
 test_schema.test_name_procedure(2);
END;

SQL Error [6550] [65000]: ORA-06550: line 2, column 14:

Upvotes: 0

Views: 84

Answers (2)

d r
d r

Reputation: 7776

There are many issues here. Let's start with sample data like this:

Select * from tbl;
      COL1 COL2   
---------- -------
         1 TEST1  
         1 TEST1_1
         2 TEST2  
         2 TEST2_2
         3 TEST3  
         3 TEST3_3

Corrected code of your procedure could be:

Create or replace PROCEDURE test_p(p_var_in IN VARCHAR, p_var1_out OUT NUMBER, p_var2_out OUT VARCHAR2) IS
BEGIN
    SELECT  COL1, COL2
    INTO    p_var1_out, p_var2_out
    FROM    tbl
    WHERE   COL1 = p_var_in And ROWNUM = 1;
END test_p;

Added ROWNUM = 1 to your WHERE clause to ensure retrieval of just 1 row. With sample data provided, without additional condition (add your own), it would end with error because you can't store multirow results in a single variable (consider using cursor if that is the case).
Next, how to call the procedure:

SET SERVEROUTPUT ON
Declare
    v1    tbl.COL1%TYPE;
    v2    tbl.COL2%TYPE;
Begin
    test_p(2, v1, v2);
    DBMS_OUTPUT.PUT_LINE('COL1 = ' || v1 || '    |    COL2 = ' || v2);
End;
/

--  Result:
COL1 = 2    |    COL2 = TEST2

As you mentioned separate columns, maybe you need a function instead of a procedure - one option could be like here:

Create or replace FUNCTION test_f(p_var_in IN VARCHAR, p_what IN VARCHAR2) RETURN VARCHAR2 IS
BEGIN
    Declare
        var1    NUMBER;
        var2    VARCHAR2(20);
    Begin
        SELECT  COL1, COL2
        INTO    var1, var2
        FROM    tbl
        WHERE   COL1 = p_var_in And ROWNUM = 1;
        RETURN CASE p_WHAT WHEN 'COL1' THEN To_Char(var1) WHEN 'COL2' THEN var2 END;
    End;
END test_f;

Note: the function above returns VarChar2 datatype for both columns...

--  Calling function from a query:
Select To_Number(test_f(2, 'COL1')) "COL1", test_f(2, 'COL2') "COL2" From Dual;

--  Result:
COL1  COL2
----  ------
   2  TEST2

Upvotes: 0

Yeras_QazaQ
Yeras_QazaQ

Reputation: 87

Forgot "," after "test1_col" first column, check this section

SELECT
   *test1_col*
   test2_col
 INTO

Also you call procedure not correct, need to add variables to out parameters:

DECLARE 
var_in_test VARCHAR(1000) := '2';
var_out_test1 NUMBER;
var_out_test2 VARCHAR(1000);
BEGIN 
 stack_test_name_procedure(2, var_out_test1, var_out_test2);
END;

This request return exception TOO_MANY_ROWS, handle that exception

Upvotes: 0

Related Questions