logo
Tags down

shadow

Struggling to collect and return DBMS_SQL.COLUMN_VALUE using User Defined Type


By : Primoo
Date : August 02 2020, 12:00 PM
I wish did fix the issue. This is my first question on StackOverflow and I'm self taught so please be gentle. , The first of your commented-out lines:
code :
    --l_val(n) := r_col_val(l_dum_val); -- ORA-06533: Subscript beyond count
    --l_val(n) := l_dum_val; --PLS-00382: expression is of wrong type
    l_val(l_val.count) := r_col_val(l_dum_val);
    l_val(some_index).l_col_val := r_col_val(l_dum_val);
CREATE OR REPLACE FUNCTION 
GET_REF_VAL
    (
    p_cursor IN SYS_REFCURSOR
    )
RETURN t_col_val
IS

    l_val t_col_val := t_col_val();
    l_col t_col_head := t_col_head();

    l_cursor        SYS_REFCURSOR := p_cursor;
    l_cursor_id     INTEGER;
    l_dummy         INTEGER;
    l_col_cnt       INTEGER;
    l_tab_rec       DBMS_SQL.DESC_TAB2;
    l_dum_val       VARCHAR2(250);

BEGIN

    l_cursor_id := DBMS_SQL.TO_CURSOR_NUMBER(l_cursor); 
    DBMS_SQL.DESCRIBE_COLUMNS2(l_cursor_id, l_col_cnt, l_tab_rec);

    /* COLUMN HEADERS */
    FOR r IN 1..l_col_cnt
    LOOP

        l_col.extend;
        l_col(l_col.count) := r_col_head(l_tab_rec(r).col_name);
        DBMS_SQL.DEFINE_COLUMN(l_cursor_id, r, l_dum_val, 4000); 

    END LOOP;

    /* COLUMN VALUES */
    LOOP 
        IF DBMS_SQL.FETCH_ROWS(l_cursor_id) = 0 THEN 
            EXIT; 
        END IF;

        FOR i IN 1 .. l_col_cnt
        LOOP

            l_val.extend;
            DBMS_SQL.COLUMN_VALUE(l_cursor_id, i, l_dum_val);        
            DBMS_OUTPUT.PUT_LINE(l_dum_val);
            l_val(l_val.count) := r_col_val(l_dum_val);

        END LOOP;
    END LOOP;  

    DBMS_SQL.CLOSE_CURSOR(l_cursor_id);
    RETURN l_val;

END;
/
CREATE OR REPLACE PROCEDURE
GET_REF_HEAD_AND_VAL
    (
    p_cursor IN OUT SYS_REFCURSOR,
    p_col OUT SYS.odcivarchar2list,
    p_val OUT SYS.odcivarchar2list
    )
IS

    l_cursor_id     INTEGER;
    l_col_cnt       INTEGER;
    l_tab_rec       DBMS_SQL.DESC_TAB3;
    l_value         VARCHAR2(250 byte);

BEGIN

    l_cursor_id := DBMS_SQL.TO_CURSOR_NUMBER(p_cursor); 
    DBMS_SQL.DESCRIBE_COLUMNS3(l_cursor_id, l_col_cnt, l_tab_rec);

    /* COLUMN HEADERS */
    p_col := SYS.odcivarchar2list();
    FOR r IN 1..l_col_cnt
    LOOP

        p_col.extend;
        p_col(p_col.count) := l_tab_rec(r).col_name;
        DBMS_SQL.DEFINE_COLUMN(l_cursor_id, r, l_value, 250);

    END LOOP;

    /* COLUMN VALUES */
    p_val := SYS.odcivarchar2list();
    LOOP 
        IF DBMS_SQL.FETCH_ROWS(l_cursor_id) = 0 THEN 
            EXIT; 
        END IF;

        FOR i IN 1 .. l_col_cnt
        LOOP

            p_val.extend;
            DBMS_SQL.COLUMN_VALUE(l_cursor_id, i, l_value);        
            --DBMS_OUTPUT.PUT_LINE(l_dum_val);
            p_val(p_val.count) := l_value;

        END LOOP;
    END LOOP;  

    DBMS_SQL.CLOSE_CURSOR(l_cursor_id);

END;
/


Share : facebook icon twitter icon

How do you return a user defined type from a WCF service?


By : spacegoat
Date : March 29 2020, 07:55 AM
it helps some times Just to second Yossi's/Rich's thoughts:
yes, you can add a reference to the shared dll (rather than using the generated proxy class) yes, it defeats a lot of the intent of data-contracts, and if any kind of custom serialization is happening, you may have issues extending your service

Oracle: Bulk collect with DBMS_SQL defined array


By : Asik Basheer
Date : March 29 2020, 07:55 AM
will help you If you're going to use nested tables in a record you need to reference their elements in the BULK COLLECT clause. Something like this:
code :
  SELECT null FLVO_ID,   
         c.flh_id_messaggio,  
         c.flh_integrazione_id        
    BULK COLLECT INTO curEniFlussiHub.FLVO_ID
              ,   curEniFlussiHub.flh_id_messaggio        
              ,   curEniFlussiHub.flh_integrazione_id
    FROM ENI_FLUSSI_HUB c  
   WHERE     c.flh_fornitura = P_FLH_FORNITURA  
declare
    recEniFlussiHub IS RECORD 
      ( 
         FLVO_ID                 dbms_sql.varchar2_table, 
         flh_id_messaggio        dbms_sql.varchar2_table, 
         flh_integrazione_id     dbms_sql.varchar2_table 
     ); 

  curEniFlussiHub        recEniFlussiHub;    

Why can int not be used as an l-value of a return type while a user-defined class can?


By : srikanth
Date : March 29 2020, 07:55 AM
wish helps you The function f1 returns a rvalue, which might become an xvalue (an “eXpiring” value). The function f2 returns a builtin type which is an rvalue becomming an prvalue (“pure” rvalue).
From 3.10 [Lvalues and rvalues]
code :
struct A {
    A() {}
    A(A&&) = delete;
    A& operator = (const A&) { return *this; }
};

ORA-01007 "variable not in select list" from dbms_sql.column_value call


By : user5499283
Date : March 29 2020, 07:55 AM
wish help you to fix your issue You have two problems in the code you posted. Firstly you have skipped part of the execution flow because you haven't called the DEFINE_COLUMN procedure. That is what is causing the ORA-01007 error, as the dynamic SQL processing hasn't been told about the select list columns via that call. For your current code you only need to define column 2, but assuming you will actually want to refer to the others you can define them in a loop. To treat them all as string for display you could do:
code :
...
  DBMS_SQL.PARSE(c, xsql, DBMS_SQL.NATIVE);

  d := DBMS_SQL.EXECUTE(c);

  DBMS_SQL.DESCRIBE_COLUMNS(c, col_cnt, rec_tab);

  FOR i IN 1..col_cnt
  LOOP
    -- dbms_output.put_line('col_name is ' || rec_tab(i).col_name);
    DBMS_SQL.DEFINE_COLUMN(c, i, varvar, 500);
  END LOOP;

  LOOP
    IF DBMS_SQL.FETCH_ROWS(c)>0 THEN
    ...
...
  LOOP 
    IF DBMS_SQL.FETCH_ROWS(c)>0 THEN 
      -- get column values of the row 
      DBMS_SQL.COLUMN_VALUE(c, 2, varvar); 
      ...
    ELSE 
      -- No more rows to copy: 
      EXIT; 
    END IF; 
  END LOOP; 
...
PL/SQL procedure successfully completed.

col_type            =    1
col_maxlen          =    30
col_name            =    OWNER
col_name_len        =    5
col_schema_name     =    
col_schema_name_len =    0
col_precision       =    0
col_scale           =    0
col_null_ok         =    false

col_type            =    1
col_maxlen          =    30
col_name            =    TABLE_NAME
...

DefaultTableModel addRow method adds row in format: column_name = column_value instead of just column_value


By : sou
Date : March 29 2020, 07:55 AM
seems to work fine You need to use a custom cell renderer in your JTable.
How data appears is based on the class of the columns. The default renderer simply calls the .toString() function for the objects in the column. If the column contains a key value pair, its common for these to appear as key=value.
Related Posts Related Posts :
  • Vagrant is failing to unpackage Ubuntu Bionic box
  • Generate labels for radio buttons backed by an enum in a Vaadin 14 web app
  • After installing a CDK of angular getting an error during compilation
  • How to build or condition on datasorce in the code?
  • How are emojis with skin tones internally represented?
  • Must a reducer in Redux strictly return a new state object?
  • Why is Nexus advertising a version, which it wouldn't download?
  • Same-origin policy blocking API request on remote
  • What are dependencies required for cucumber-selenium-testng framework
  • Why configuring (0,0,0) as color for the video plane gives green color instead of black color
  • Proving A Language Is Undecidable Using Turing Reductions
  • Stripe Connect Fixed/Flat Fee based on value for Connected Accounts
  • Time complexity of algorithm which merges a stack of linked lists
  • CGAL: Is there a way to connect two edges from different surfaces?
  • Are there any known approaches to define application specific messages?
  • How to delete the property of all the vertices with the same label name?
  • Can someone explain partially observable Markov decision process (POMDP) with an example?
  • node-sass installation error: couldn't find package "error-ex"
  • Primary Key Validation in Snowflake datawarehouse
  • grep command to view limited text
  • Problem creating new channel application with dbmigrate
  • Quasi Stationary AC to "normal" AC, WindPowerPlants
  • Best event counter to use for measuring wall clock time using perf tools
  • i2cdetect does not recognize VL6180X sensors behind TCA9548A I2C multiplexer
  • How does the scan adverb behave when called with more than one argument?
  • How to deploy / host Tableau Extension
  • Stripe Proration - Pay Upfront?
  • How do I make sure one control is prioritized over another in a StackLayout in Xamarin Forms?
  • How I can draw each corner of each face of a cube in a diferent color?
  • Is more Reactive better?
  • Inserting to a number of containers in azure cosmos db as one atomic operation
  • Legacy GCE and GKE metadata requests from google_daemon/manage_addresses.py
  • chisel printf fail (built using chisel3 then verilator to C++)
  • Is there any programming language where you can forbid class casting of return types?
  • How to run a karate script using power-shell command prompt
  • How to compare if source list does not contain any object which is present in destination list using Java 8?
  • Automation testing for dynamic coordinates appearing in map using TestCafe or codeceptjs or cypress
  • Complex number calculations in Julia, weird result?
  • Is there a simply way to list of consumer components for an Interface?
  • Using SystemVerilog mailbox type as module IO
  • How to override Web service url in http request node in IIB
  • Can we have forests or data replication between different versions of marklogic?
  • RPM Build error: Too many levels of recursion in macro expansion. It is likely caused by recursive macro declaration
  • Dynamic ASSIGN of table row expression
  • What does Degit exactly stand for?
  • JHipster inheritance
  • Pentaho Kettle Variable Update
  • Is it bad practice to implement an interface twice?
  • Why do I need an intermediate global to get CUDA device-side function addresses?
  • Tableau incremental refresh from Snowflake
  • Decrypting AES GCM with Python without Authentication Tag
  • Big-O notation of 8^log2(n)
  • How to find last occurrence of a substring using Kusto Query Language?
  • Twilio Update a Task's current Queue
  • MERGE command results in gaps in sequence numbers
  • ERROR: DB2 SQL Error: SQLCODE=-206, SQLSTATE=42703, SQLERRMC=CON.CONTACT_ID, DRIVER=4.21.29
  • Conditional Upsert in Couchbase
  • Add dependency between 2 Dofn in Apache Beam
  • Factory overriding parameterized class in UVM
  • Generate token for multiple realms with Keyclock
  • shadow
    Privacy Policy - Terms - Contact Us © voile276.org