oracle package sp, no idea how to debug (with code)

Question

I am working package store procedure accapte array(table) paramter, you can see the relate question An Oracle stored procedure accept array(table) parameter in package example needed.There are no errors when I create storeprocedur, but when I call it, there are some errors. this is what is the store procedure:

CREATE OR REPLACE TYPE t_table IS TABLE of VARCHAR2(255);
    /
    CREATE OR REPLACE PACKAGE simon_pkg IS
        TYPE t_table IS TABLE of VARCHAR2(255);
        TYPE c_cursor IS ref CURSOR;
        PROCEDURE f (t_input in t_table,c_out out c_cursor);
    END;
    /
    CREATE OR REPLACE PACKAGE BODY simon_pkg IS
        PROCEDURE f (t_input in t_table,c_out out c_cursor) IS
            v_cursor c_cursor;
        BEGIN
            OPEN v_cursor FOR
                SELECT last_name
                FROM employees
                WHERE last_name IN
                    (SELECT * FROM TABLE(t_input));
            c_out := v_cursor;
        END f;
    END;
    /

this is what I am doing when I call it:

DECLARE
    TYPE c_cur IS REF CURSOR;
    c_result c_cur;
    m_table t_table := t_table();
BEGIN
    m_table.EXTEND(2);
    m_table(1) := 'Urman';
    m_table(2) := 'Vargas';
    simon_pkg.f(m_table,c_result);
    SELECT * FROM TABLE(c_result);
END;
/

waiting for help! please.

Why do you have two type definitions - one on schema level and one on package level? — Frank Schmitt, Jul 11 '12 at 18:53
@FrankSchmitt I read some thread said pl/sql and sql have not same engine, if you want use select in pl/sql you need declare a type in your schema,so I do that, and I think I need a type in my package store procedure, so I do it again. but I got answer already from Justin Cave. only one in schema is enough. — Simon Sheng, Jul 11 '12 at 19:40
@APC I just know how to copy from windows consle windows(sql*plus), for this I have answer already, I will add errors all time later. — Simon Sheng, Jul 11 '12 at 19:41

Justin Cave · Accepted Answer · 2012-07-11T19:23:41.667

It is always helpful to post the errors you are getting. That way we don't have to guess. The errors I can see...

First, you don't need to declare the types in your package. You are already defining a SQL type

CREATE OR REPLACE TYPE t_table IS TABLE of VARCHAR2(255);
/

Your package declaration should not declare a separate PL/SQL type with the same name. That's only going to make your life more challenging as you have to figure out which of the two types any given reference to t_table is using. Your package declaration also doesn't need to declare a type for a weak ref cursor-- Oracle already provides the sys_refcursor type. Your package specification, therefore, merely needs to be

CREATE OR REPLACE PACKAGE simon_pkg 
IS
    PROCEDURE f (t_input in t_table,
                 c_out out sys_refcursor);
END;
/

That means that your package body can be

 CREATE OR REPLACE PACKAGE BODY simon_pkg IS
    PROCEDURE f (t_input in t_table,
                 c_out  out sys_refcursor) 
    IS
    BEGIN
        OPEN c_out FOR
            SELECT last_name
            FROM employees
            WHERE last_name IN
                (SELECT * FROM TABLE(t_input));
    END f;
END;

Calling the procedure can be done a few different ways depending on what you are trying to accomplish. You can write code that iterates through the cursor and writes the output to the dbms_output buffer (assuming that whatever tool you are using knows how to read from the buffer to display the output).

DECLARE
    c_result sys_refcursor;
    m_table t_table := t_table();
    l_last_name varchar2(100);
BEGIN
    m_table.EXTEND(2);
    m_table(1) := 'Urman';
    m_table(2) := 'Vargas';
    simon_pkg.f(m_table,c_result);

    LOOP
      FETCH c_result INTO l_last_name;
      EXIT WHEN c_result%notfound;
      dbms_output.put_line( l_last_name );
    END LOOP;
    CLOSE c_result;
END;
/

If you are using SQL*Plus (or something else that implements SQL*Plus variables)

VARIABLE rc refcursor;

DECLARE
    m_table t_table := t_table();
BEGIN
    m_table.EXTEND(2);
    m_table(1) := 'Urman';
    m_table(2) := 'Vargas';
    simon_pkg.f(m_table,:rc);
END;

PRINT rc;

Assuming that you are using SQL*Plus and the HR schema, you can see the output from the first option

SQL> CREATE OR REPLACE TYPE t_table IS TABLE of VARCHAR2(255);
  2  /

Type created.

SQL> CREATE OR REPLACE PACKAGE simon_pkg
  2  IS
  3      PROCEDURE f (t_input in t_table,
  4                   c_out out sys_refcursor);
  5  END;
  6  /

Package created.

SQL>  CREATE OR REPLACE PACKAGE BODY simon_pkg IS
  2      PROCEDURE f (t_input in t_table,
  3                   c_out  out sys_refcursor)
  4      IS
  5      BEGIN
  6          OPEN c_out FOR
  7              SELECT last_name
  8              FROM employees
  9              WHERE last_name IN
 10                  (SELECT * FROM TABLE(t_input));
 11      END f;
 12  END;
 13  /

Package body created.

SQL> set serveroutput on;
SQL> DECLARE
  2      c_result sys_refcursor;
  3      m_table t_table := t_table();
  4      l_last_name varchar2(100);
  5  BEGIN
  6      m_table.EXTEND(2);
  7      m_table(1) := 'Urman';
  8      m_table(2) := 'Vargas';
  9      simon_pkg.f(m_table,c_result);
 10
 11      LOOP
 12        FETCH c_result INTO l_last_name;
 13        EXIT WHEN c_result%notfound;
 14        dbms_output.put_line( l_last_name );
 15      END LOOP;
 16      CLOSE c_result;
 17  END;
 18  /
Urman
Vargas

PL/SQL procedure successfully completed.

And the output from the second option

SQL> VARIABLE rc refcursor;
SQL> DECLARE
  2      m_table t_table := t_table();
  3  BEGIN
  4      m_table.EXTEND(2);
  5      m_table(1) := 'Urman';
  6      m_table(2) := 'Vargas';
  7      simon_pkg.f(m_table,:rc);
  8  END;
  9  /

PL/SQL procedure successfully completed.

SQL> PRINT rc;

LAST_NAME
-------------------------
Urman
Vargas

Oh, I love this answer. and I just know how to copy from windows consle whindow. I will attach error all time. big thank you. — Simon Sheng, Jul 11 '12 at 19:21

oracle package sp, no idea how to debug (with code)

1 Answers1