logo
down
shadow

Oracle - Cannot fetch using table function on type-table, ORA-06504


Oracle - Cannot fetch using table function on type-table, ORA-06504

Content Index :

Oracle - Cannot fetch using table function on type-table, ORA-06504
Tag : oracle , By : Kenny
Date : November 28 2020, 09:01 AM

To fix the issue you can do There are lots of things about your code that confuse me including the fact that you only have one call to extend. Your variable naming conventions are also rather confusing.
When you want to iterate through the collection, though, just iterate through the collection. There is no need to take the collection, send it from PL/SQL to SQL, just to get it back into PL/SQL. Just
FOR i IN 1..l_cur.count
LOOP
  col_count := col_count + 1;
  dbms_output.put_line( l_cur(i).row_no );
END LOOP;
col_count := l_cur.count;
fetch l_your_cursor
 into l_row_no, 
      l_err_msg, 
      ...
open l_rc
 for select ty_PKG_CHA03AA_012B_excp_rpt( <<list of attributes>> )
       from table( l_cur );

Comments
No Comments Right Now !

Boards Message :
You Must Login Or Sign Up to Add Your Comments .

Share : facebook icon twitter icon

Fetch Oracle table type from stored procedure using JDBC


Tag : oracle , By : user179938
Date : March 29 2020, 07:55 AM
I hope this helps you . You can't access PLSQL objects (cases 2 & 5 = package-level objects) from java, see "java - passing array in oracle stored procedure". You can however access SQL types (case 1 and 4).
To get OUT parameters from PL/SQL to java, you can use the method described in one of Tom Kyte's thread using OracleCallableStatement. Your code will have an additional step since you're retrieving a table of Object instead of a table of VARCHAR.
SQL> CREATE TYPE t_type AS OBJECT (val VARCHAR(4));
  2  /
Type created

SQL> CREATE TYPE t_table AS TABLE OF t_type;
  2  /
Type created

SQL> CREATE OR REPLACE PROCEDURE p_sql_type (p_out OUT t_table) IS
  2  BEGIN
  3     p_out := t_table(t_type('a'), t_type('b'));
  4  END;
  5  /
Procedure created
SQL> CREATE OR REPLACE
  2  AND COMPILE JAVA SOURCE NAMED "ArrayDemo"
  3  as
  4  import java.sql.*;
  5  import oracle.sql.*;
  6  import oracle.jdbc.driver.*;
  7  
  8  public class ArrayDemo {
  9     
 10     private static void log(String s) throws SQLException {
 11        PreparedStatement ps =
 12           new OracleDriver().defaultConnection().prepareStatement
 13           ( "begin dbms_output.put_line(:x); end;" );
 14        ps.setString(1, s);
 15        ps.execute();
 16        ps.close();
 17     }
 18  
 19     public static void getArray() throws SQLException {
 20  
 21        Connection conn = new OracleDriver().defaultConnection();
 22  
 23        OracleCallableStatement cs =
 24           (OracleCallableStatement)conn.prepareCall
 25           ( "begin p_sql_type(?); end;" );
 26        cs.registerOutParameter(1, OracleTypes.ARRAY, "T_TABLE");
 27        cs.execute();
 28        ARRAY array_to_pass = cs.getARRAY(1);
 29  
 30        /*showing content*/
 31        Datum[] elements = array_to_pass.getOracleArray();
 32  
 33        for (int i=0;i<elements.length;i++){
 34           Object[] element = ((STRUCT) elements[i]).getAttributes();
 35           String value = (String)element[0];
 36           log("array(" + i + ").val=" + value);
 37        }
 38     }
 39  }
 40  /
Java created
SQL> CREATE OR REPLACE
  2  PROCEDURE show_java_calling_plsql
  3  AS LANGUAGE JAVA
  4  NAME 'ArrayDemo.getArray()';
  5  /

Procedure created

SQL> EXEC show_java_calling_plsql;

array(0).val=a
array(1).val=b

Tag : chash , By : Brownell
Date : March 29 2020, 07:55 AM
I wish did fix the issue. Having spent over a day investigating this I was directed to the answer just 10 minutes after posting my question. Typical!
The answer was found here - https://community.oracle.com/thread/659625 - and all that's required is to wrap the calling code in a transaction. Working code looks like this:
using (var connection = new OracleConnection(connstring))
{
    connection.Open();

    using (var command = connection.CreateCommand())
    {
        // Start a local transaction
        using (var transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted))
        {
            // Assign transaction object for a pending local transaction
            command.Transaction = transaction;
            command.CommandText = "FNC_AXA_APPTS";
            command.CommandType = CommandType.StoredProcedure;

            OracleParameter retVal = new OracleParameter("PRS", OracleDbType.RefCursor);
            retVal.Direction = ParameterDirection.ReturnValue;
            command.Parameters.Add(retVal);

            command.Parameters.Add(new OracleParameter("EG_PARAM", OracleDbType.Varchar2, 50)).Value = paramValue;

            command.ExecuteNonQuery();

            using (OracleDataReader reader = ((OracleRefCursor)command.Parameters["PRS"].Value).GetDataReader())
            {
                dt.Load(reader);
            }
        }
    }
}

Inserting data into Object type table from another existing table..Oracle 11g


Tag : development , By : TRobison
Date : March 29 2020, 07:55 AM
it fixes the issue Example table is a type so you can't insert anything into it, first you would need to define a variable that would be an instance of your example_table. Then you can use a bulk collect to actually populate your table.
declare
  example_tab_inst EXAMPLE_TABLE;
begin
  select 
   EXAMPLE(e.col1,e.col2,e.col3) 
  bulk collect into example_tab_inst 
  from emp_table e;
 .
 .
 .

Oracle pipelined table function and insert results into an Oracle table


Tag : sql , By : user91848
Date : March 29 2020, 07:55 AM
Does that help Oracle Setup:
Pipelined version of this split function:
CREATE OR REPLACE FUNCTION split(
  i_str    IN  VARCHAR2,
  i_delim  IN  VARCHAR2 DEFAULT ','
) RETURN SYS.ODCIVARCHAR2LIST PIPELINED
AS
  p_start        NUMBER(5) := 1;
  p_end          NUMBER(5);
  c_len CONSTANT NUMBER(5) := LENGTH( i_str );
  c_ld  CONSTANT NUMBER(5) := LENGTH( i_delim );
BEGIN
  IF c_len > 0 THEN
    p_end := INSTR( i_str, i_delim, p_start );
    WHILE p_end > 0 LOOP
      PIPE ROW( SUBSTR( i_str, p_start, p_end - p_start ) );
      p_start := p_end + c_ld;
      p_end := INSTR( i_str, i_delim, p_start );
    END LOOP;
    IF p_start <= c_len + 1 THEN
      PIPE ROW ( SUBSTR( i_str, p_start, c_len - p_start + 1 ) );
    END IF;
  END IF;
END;
/
INSERT INTO msg_log ( C001, C002, C003, C004, C005, C006, C007, C008, C009, C010, C011 )
SELECT *
FROM   (
  SELECT ROWNUM AS RN,
         COLUMN_VALUE As value
  FROM   TABLE( SPLIT( ',AA,BB,CC,,,FF,GG,,,HH,' ) )
)
PIVOT( MAX( value ) FOR rn IN ( 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11 ) )
SELECT * FROM msg_log;
C001 | C002 | C003 | C004 | C005 | C006 | C007 | C008 | C009 | C010 | C011
:--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :---
null | AA   | BB   | CC   | null | null | FF   | GG   | null | null | HH

How can a table be returned from an Oracle function without a custom type or cursor?


Tag : oracle , By : 03B
Date : March 29 2020, 07:55 AM
Related Posts Related QUESTIONS :
  • Save Package body and Spec
  • Oracle: using Automatic List Partitioning with Range Subpartitioning
  • How to capture SQL fired from Business Object Deski
  • Select Count from sub table in Oracle
  • Building oracles for hyperledger composer
  • Oracle iterates thru values on columns (Oracle 11)
  • How to transfer from one to another PC
  • how to configure oracle job to run at every minute of 00 second
  • I don't know what's bad in my SQL (in Oracle SQL Developer)
  • What is the purpose of this package?
  • execute immediate a pl/sql that having execute immediate
  • replace function in dml statements in oracle 8i
  • DBMS_OUTPUT.NEW_LINE and DBMS_OUTPUT.NEW_LINE() difference?
  • Can someone explain ORA-29861 error in plain english and its possible cause?
  • Simple oracle backup without using exp or expdp
  • Variable declared with variable keyword in sql plus(oracle 9i)?
  • optimizing oracle query
  • Customer Support Identifier-How to get this?
  • Display query results like \G in MySQL when using Oracle / sqlplus?
  • Does vsize give the correct result for number?
  • How to generate alphanumeric id in Oracle
  • to_date function pl/sql
  • How can I create a "dynamic" WHERE clause?
  • index for `where wakeup_time < sysdate`
  • select only new row in oracle
  • alias all column in a query with a prefix
  • Oracle 10g Express / APEX
  • How to force oracle to use index range scan?
  • Peoplesoft queries - performance
  • Select and Insert across dblink
  • Oracle table fragmentation
  • Running a Query in SQL Plus
  • Where can I get SQL for default Oracle's "HR" schema?
  • best way of oracle database partitions?
  • How do I know the number of keywords coming from a database?
  • Retrieving Stored procedures, Views, Functions, Triggers using Toad for Oracle
  • "FRM-40602: Cannot insert into or update data in a view" error
  • PL/SQL Continue exection after error handled
  • Combination of add_months and to_date gone wrong if date is 28-Feb
  • Oracle - How to know the load volume by times of a table?
  • Data Migration - Verify Data loaded where Primary Key can change
  • encountered the symbol FROM when expecting one of the following pl sql
  • Dynamically create tables based on the total counts of another table
  • Stored procedure is taking too much time to update the table columns
  • ORA-00984: column not allowed here error while loading data
  • Using a %ROWTYPE in a WHERE IN clause
  • Extract single repeating XML node through whole XML result
  • When the timezone is adjusted to user's timezone when retrieving data from TIMESTAMP WITH LOCAL TIMEZONE column?
  • Oracle synonym alternatives in H2?
  • ORA-00920: Invalid relational operator (no missing/extra parenthesis)
  • How to update data by select random row value from another table
  • Convert Coldfusion script in Oracle view with CASE and iteration
  • How to calculate the amount of ID monthly with the condition?
  • Ho to get UDT members by SQL?
  • How to set Oracle connection string for dotNET core console app?
  • Getting MySQL error stack trace on Oracle JDBC Connection
  • Why a long running pl/sql code that shows ACTIVE in v$session doesn't show up in v$active_session_history
  • Oracle SQL changes in sqldeveloper are not reflected in EFCore
  • Oracle query to get counts from multiple tables and display them side by side
  • Want to change NLS_DATE_FORMAT in oracle DB for a particular Schema only
  • shadow
    Privacy Policy - Terms - Contact Us © scrbit.com