Welcome, Guest: Register On Nairaland / LOGIN! / Trending / Recent / New
Stats: 3,150,628 members, 7,809,342 topics. Date: Friday, 26 April 2024 at 08:04 AM

Dbms_sql.native And Bulk Collect - Science/Technology - Nairaland

Nairaland Forum / Science/Technology / Dbms_sql.native And Bulk Collect (126 Views)

Paga Unveils 'collect API' For Businesses To Initiate Payments In Their Applicat / Native Instruments Traktor Pro 3.2.1.9 Mac Crack / Native Cat Spotted At Yankari Game Reserve In Bauchi State. Photos (2) (3) (4)

(1) (Reply)

Dbms_sql.native And Bulk Collect by chintuk(m): 1:33pm On Apr 27, 2023
Is it possible to select from table of objects while using dbms_sql.native? I have defined an object:

CREATE OR REPLACE TYPE SI_O IS OBJECT(
S_ID NUMBER,
P_ID NUMBER,
G_ID NUMBER,
DLT VARCHAR2(255),
ERROR_ID NUMBER,
B_ID NUMBER
);
I also defined a table of objects:

create or replace type SI_T is table of si_o;
Now I'm trying to use it in a procedure like that:

CREATE OR REPLACE PROCEDURE NATIVE_TEST IS
sqlCommand VARCHAR2(320);
tRows SI_T;
num BINARY_INTEGER:= -1;
l_ntt_desc_tab DBMS_SQL.DESC_TAB;
cur INTEGER;
cnt INTEGER;
nrowCnt INTEGER;

BEGIN

sqlCommand :=
' SELECT SI_O(S_ID, P_ID, G_ID, DLT, error_id,B_ID) FROM MYTABLE' ;

EXECUTE IMMEDIATE sqlCommand BULK COLLECT INTO tRows ;
sqlCommand := 'select count(*) cou from table(tRows)';
-- here is what I wish:
cur := DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.PARSE(cur,sqlCommand,dbms_sql.native);
nrowCnt:=DBMS_SQL.EXECUTE(cur);
DBMS_SQL.CLOSE_CURSOR(cur);
END NATIVE_TEST;
However I'm getting runtime exception ora-00904 "TROWS" invalid identifier. Everything is here simplified but I really wish to use dbms_sql.native like that ( if it would be posiible).

(1) (Reply)

Whatsapp’s Exciting Update: Multiple Accounts On The Same Smartphone Coming Soon / Simple Step By Step To Transfer Money With Ecobank / Your Favourite Clothes Brand

(Go Up)

Sections: politics (1) business autos (1) jobs (1) career education (1) romance computers phones travel sports fashion health
religion celebs tv-movies music-radio literature webmasters programming techmarket

Links: (1) (2) (3) (4) (5) (6) (7) (8) (9) (10)

Nairaland - Copyright © 2005 - 2024 Oluwaseun Osewa. All rights reserved. See How To Advertise. 8
Disclaimer: Every Nairaland member is solely responsible for anything that he/she posts or uploads on Nairaland.