SQL & PL/SQL :: Create Object Then Use As Data Type Of New Table
Nov 11, 2012
I have existing table which needs to be copy to new table with object using the cursor. The exist table has a rank column which has some duplicate rank, which need to be remove and provide a series of numbers, like 1,2,3,4,5,...
create type UNIVERSITY as object (
U_RANK number(2),
U_SCHOOL varchar2(150),
U_COUNTRY varchar2(150),
U_SCORE number(3)
)
Can we create TABLE type object by using %ROWTYPE in SQL.
I am bale to create PL/SQL table type object .But i am unable to create SQL type
SQL> declare 2 type table_emp is table of scott.emp%rowtype index by binary_integer; 3 employees table_emp; 4 begin 5 select * bulk collect into employees from scott.emp; 6 end; 7 / PL/SQL procedure successfully completed SQL> create or replace type table_emp is table of scott.emp%rowtype index by binary_integer; 2 /
Warning: Type created with compilation errors
SQL> show errors Errors for TYPE DBO.TABLE_EMP: LINE/COL ERROR -------- ---------------------------------------------------------- 1/19 PLS-00355: use of pl/sql table not allowed in this context 0/0 PL/SQL: Compilation unit analysis terminated SQL>
How can I create global table type object with %rowtype
I have created the below types and oracle objects.
create or replace type T_SETDEL_RESP_REC as object ( respCode number, respDesc varchar2(255) ) -- create or replace type T_EMA_NP_RANGE_LNPTICKET_REC as object ( ticket number ) create or replace type T_RANGE_TICKET_TAB AS TABLE OF T_RANGE_TICKET_REC
The following type is created in the Package specification
type t_resp_rec IS RECORD ( resp_code number, resp_desc varchar2(255) );
I have the following two procedures
Procedure getResponse(p_call_request_id IN number, p_resp_rec IN t_setdel_resp_rec, p_range_ticket_tab IN t_range_icket_tab, p_endof_event IN varchar)
PROCEDURE ProcessResponse(p_call_request_id IN number, p_resp_rec IN t_resp_rec, p_ticket_tab IN t_ticket_tab, p_endof_event IN varchar2)
The get Response procedure is a wrapper procedure exposed to Java to pass values. The Process Response procedure is a main procedure where all logics and business rules are handled.
The Problem is:
How can I pass the values from get Response procedure to Process Response procedure. So that rules and validations are applied. Please note the p_ticket_tab may have many ticket numbers corresponding to p_call_request_id.
I have been creating lot many threads around the same problem, however i thought i knew but realized I do not know or else do not know how to. I have created object type with an attribute READINGVALUE NUMBER(21,6)...How can i use type attribute on this object while declaring variable.....can we use type attribute on NESTED TABLES, similar to the db tables?
example CREATE TYPE READING AS OBJECT(READINGVALUE NUMBER(21,6)); CREATE TABLE INTERVALREADINGS OF TYPE READING;
I have an Type-object typeObj1 that consists another Type-object typeObj2. this def has another Type-object typeObj3. how to access variable declared inside typeObj3. I have syntax below for each Type.
CREATE OR REPLACE TYPE typeObj1 AS OBJECT ( SYSTEM_IDENTIFER VARCHAR2(50), PROCESS_TYPE VARCHAR2(50), abc typeObj2
) /
[Code]...
/I have tried to access the type-object in where clause in following way
FROM TABLE(CAST(I_typeObj1 AS typeObj1)) ITTPRC, ...... Where ....... AND (ADDKEY.ADDTN_INFO_KEY_TYP_NM IN (SELECT ADDTN_INFO_KEY_TYP_NM FROM TABLE(ITTPRC.abc)))
AND (ADTINF.ADDTN_RQST_TYP_VAL_DT IN (SELECT ADDTN_RQST_VAL_DT FROM TABLE( ITTPRC.def)) OR ITTPRC.def IS NULL ) AND (ADTINF.ADDTN_RQST_TYP_VAL_NUM IN (SELECT ADDTN_RQST_VAL_NUM FROM TABLE( ITTPRC.def)) OR ITTPRC.def IS NULL ) AND (ADTINF.ADDTN_RQST_TYP_VALUE IN (SELECT ADDTN_RQST_VALUE FROM TABLE( ITTPRC.def)) OR ITTPRC.def IS NULL )
In this way i am able to access the variable inside typeObj3. But problem is i am getting error "ORA-01427 single-row subquery returns more than one row" when i pass more that one typeObj2.
Ive just been trying to create an add member procedure to retrieve an object that is in another table. But before i get the ref to bring the toy across i wanted to make sure i could insert an object into the new table. I keep getting theres too many arguments, the lack of sqlplus code, the spool function isnt working.
DROP TABLE completed_toys; / CREATE OR REPLACE TYPE comp_toyobj AS OBJECT ( completed_id NUMBER(5), request_id REF toy_obj,
[Code].....
Here is the error on the procedure call
Error report: ORA-06550: line 4, column 9: PLS-00306: wrong number or types of arguments in call to 'ADD_COMPLETED' ORA-06550: line 4, column 9: PL/SQL: Statement ignored 06550. 00000 - "line %s, column %s: %s" *Cause: Usually a PL/SQL compilation error. *Action:
SQL> create or replace type obj1 as object(v_ASSETID NUMBER(20), 2 v_ASSETTYPE varchar2(20)); 3 / Type created
--- >> create the package
SQL> create or replace package test_arr is 2 type nest_tab is table of obj1; 3 procedure insert_data(t_nest IN nest_tab); 4 end test_arr; 5 /
Package created
SQL> create or replace package body test_arr is 2 procedure insert_data(t_nest IN nest_tab) is 3 begin 4 for i in t_nest.first..t_nest.last loop 5 insert into asset(ASSETID, 6 ASSETTYPE) values (t_nest(i).v_ASSETID,
[code]....
-- >> a block to execute the above package:
SQL> declare 2 type ref_tab is table of obj1; 3 ref_tab1 ref_tab; 4 begin 5 ref_tab1 := ref_tab(1,'a'); 6 test_arr.insert_data(ref_tab1); 7 end; 8 /
But I am getting the below error when executing the package:
ORA-06550: line 5, column 13: PLS-00306: wrong number or types of arguments in call to 'REF_TAB' ORA-06550: line 5, column 13: PLS-00306: wrong number or types of arguments in call to 'REF_TAB' ORA-06550: line 5, column 1:
I am importing the dump.All the tables are getting imported but when it comes to the part as shown in the error it gets struck there and nothing is showed after.
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
The dumps for which I am doing the import is huge. Its around 46 GB.
select sl.sid, sl.serial#, sl.sofar, sl.totalwork from v$session_longops sl, v$session s, dba_datapump_sessions d where s.saddr = d.saddr and s.sid = sl.sid and s.serial# = sl.serial# order by start_time;
It showing the lock for two sid's. Should I kill the process and import the dump again? I used below command to import dums
I have created the below types and oracle objects.
create or replace type T_EMA_NP_SETDEL_RESP_REC as object ( respCode number, respDesc varchar2(255) )
create or replace type T_EMA_NP_RANGE_LNPTICKET_TAB AS TABLE OF T_EMA_NP_RANGE_LNPTICKET_REC create or replace type T_RANGE_TICKET_TAB AS TABLE OF T_RANGE_TICKET_REC
The following types are created in the Package specification
type t_resp_rec IS RECORD ( resp_code number, resp_desc varchar2(255) ); -- subtype t_ema_lnpticket is T186_IN_REQ_PARAMETER.T186_EMA_LNPTICKET%TYPE; -- Number type t_ema_lnpticket_tab is table of t_ema_lnpticket index by binary_integer;
I have the following two procedures
PROCEDURE getEMAReturnResponse( p_in_call_request_id IN number, p_ema_resp_rec IN t_ema_np_setdel_resp_rec, p_ema_range_lnpticket_tab IN t_ema_np_range_lnpticket_tab, p_endof_event IN varchar)
PROCEDURE Return_Response(p_in_call_request_id IN number, p_ema_resp_rec IN t_ema_resp_rec, p_ema_lnpticket_tab IN t_ema_lnpticket_tab, p_endof_event IN varchar2)
getEMAReturnResponse Procedure:
Accessed by Java application to pass the values. Should call the Return_Response procedure and pass the values received from Java.
Return_Response Procedure
The p_ema_lnpticket_tab is a sort of array that can have multiple values. Please see the example of values. Has all the business rules and validation that should be adhered.
Example of Vaules p_in_call_request_id = 1 p_ema_resp_rec = 12345, 'Operation Failed' p_ema_lnpticket_tab = (1,2,4,5) p_endof_event = Y
I've the following function returning OBJECT type. how to call this function
CREATE OR REPLACE TYPE GET_EMP_OBJ is object ( emp_name varchar2(50) , mgr_id number, dept_id number );
[Code]...
The above function got created successfully. And i'm confused how to call this functions. I tried like below but didn't work
DECLARE t_emp_info_1 GET_EMP_OBJ ; BEGIN t_emp_info_1 := get_emp(7566) ; for i in 1..t_emp_info_1.COUNT LOOP DBMS_OUTPUT.put_line ('Values are'||i.emp_name ) ; END LOOP; END;
vlanke@tank_db> desc filetable; ID NULL NUMBER(6,0) FILE_CONTENT NULL
I am new to Database field.
- I created a table with 2 columns id (datatype as number) and file_content datatype as BLOB. - My question is under desc filetable; query why does it not show me a data type as BLOB. - create table query does not return any error to me. How do I verify that FILE_CONTENT column has datatype as blob.
I am working on a webservice call from ORacle.I have a button on my form application called verify.Wheni click on verify button , a pl.sql procedure should be invoked and that procedure will call .net webserive to validate the address , the result from the webserivce will be in xml.I have to extract the xml into some variables and return these varibles to Forms application..I am plannig to use pl/sql table to store the result from web service call.
here are the output values:
Customer_Id varchar2(20), ErrorCode varchar2(30), ErrorDesc varchar2(3000), Fcount number, FErrorCode -- this is array,-- if fcount >1 then these values will be repeted. FErrorDesc -- this is array, FStatusCode -- this is array, FStatusDesc -- this is array, Street varchar2(3000), Street2 varchar2(3000), Suite varchar2(20), City varchar2(20), State varchar2(2), Zip_Code varchar2(10)
create record type and pl/sql table for these.I want to return pl/sql table as a out parameter to the form.
oracle 10G who knows why Oracle SQL doesn't support %TYPE when create a table. But it supports it when we use it in PL/SQL.
SQL> create table wg_1 (account_category JNL_WORK_LIST.ACCOUNT_CATEGORY%TYPE) ; 2 3 (account_category JNL_WORK_LIST.ACCOUNT_CATEGORY%TYPE) * ERROR at line 2: ORA-00911: invalid character
SQL> desc JNL_WORK_LIST Name Null? Type ----------------------------------------- -------- ---------------------------- ACCOUNT_NO NOT NULL NUMBER(10) BILL_REF_NO NOT NULL NUMBER(10) BILL_REF_RESETS NOT NULL NUMBER(3) JNL_EARNED_THRU_DT DATE [code]...
create or replace type testobj as object(col1 number); create or replace type tabtest as table of testobj; create or replace procedure proc(a out tabtest) is cursor c is
I have created one type as Object and I am trying to display the values available in object type for debugging purpose. To display the contents of object type.
CREATE TABLE Rel_Module ( Id NUMBER (6) PRIMARY KEY, Type CHAR (7) NOT NULL,
[Code]...
How I can easily load data into this new REL_MODULE table either using SQL or PL/SQL (e.g., Procedures, Functions, and Triggers). I don't need to write so many INSERT statement.
How can I reference an Object Type created on a remote database?This is the escenario:
In DATABASE A: CREATE OR REPLACETYPE USERA.EXPO_EXPEDIENTES_RECAUDOS OID 'DCADCB2EA2344DFAB1D205C03D708359' AS OBJECT ( exer_cd_expediente VARCHAR2 (50), exer_id_ident_expediente VARCHAR2 (30), exer_cd_sucursal NUMBER (3), exer_cd_ramo NUMBER (2), exer_nu_poliza NUMBER (7), exer_nu_certificado NUMBER (9), exer_nu_contrato NUMBER (7), exer_cd_nacionalidad VARCHAR2 (1), exer_nu_cedula_rif NUMBER (9), exer_nm_titular VARCHAR2 (70), exer_st_expediente VARCHAR2 (2), exer_de_status_exp VARCHAR2 (240), exer_fe_status_exp DATE, exer_cd_productor NUMBER (5), exer_nm_productor VARCHAR2 (60), exer_cd_mail_productor VARCHAR2 (50), exer_in_habilitado VARCHAR2 (1), exer_in_permite_habilitar VARCHAR2 (1), exer_in_carga_consulta VARCHAR2 (1), exer_cd_ramo_aplicacion VARCHAR2 (200), exer_cd_producto VARCHAR2 (6) )/ In DATABASE B:
After creating the public synonym and asigning the required privileges on the object in DATABASE A, I try to execute the following:
DECLARE x EXPO_ EXPEDIENTES_RECAUDOS;BEGIN null;END;
But i got the following error:
ORA-06550: line 2, column 12:PLS-00331: illegal reference to
USERA. EXPC_ EXPEDIENTES_RECAUDOS@DATABASEA
After investigating a little i found the following, but i dont know how to apply it, "The CREATE TYPE statement has an optional keyword OID, which associates a user-specified object identifier (OID) with the type definition. It should be used by anyone who creates an object type that will be used in more than one database."
create type emp_obj_dtl as OBJECT (ename varchar2(50),mgr NUMBER) create type emp_dtl_obj_typ as TABLE of emp_obj_dtl
Using the these object i have created on function as
CREATE OR REPLACE FUNCTION emp_test_func (peno NUMBER) RETURN emp_dtl_obj_typ AS lv_emp_dtl emp_dtl_obj_typ := emp_dtl_obj_typ (); BEGIN SELECT emp_dtl_obj_typ(emp_obj_dtl (ename, mgr)) INTO lv_emp_dtl FROM emp WHERE empno = peno;
RETURN lv_emp_dtl; END;
Now if i am executing query as
SELECT empno, emp_test_func (empno) emp_dtls FROM emp
It is often necessary to use pipelined function, in order to implement complex logic with ability to specify some parameters, but still return the results as "table" data. Let's assume, we have such function, with the following specification
function vip_turns_t(p_day_start date) return VIPTURNS_TABLE_T pipelined;
[Code]....
Now we can do "select from TABLE(vip_turns_t(sysdate))" from any client. Then it often turns out, that we want to expand existing pipelined function with new one, that would expand the column set with some extra columns (retaining all existing columns). It seems natural to me to create new object type UNDER VIPTURNS_ELEM_T and write the (second) pipelined function, foo_List(), which would "wrap" calls to vip_turns_t(), and supplement the values for extra columns, and "pipe" the broader row. The types for "pipelined wrapper" are created as follows:
create or replace type VIPTURNS_2_ELEM_T under VIPTURNS_ELEM_T ( m_exported_flag char(1),
[Code]....
Next I write function foo_list(), but it compiles with strange error. Below is sceleton for foo_list(), and in comments are errors I encountered. It appears, that compiler does not believe, that variable turn_rec is of VIPTURNS_ELEM_T type. But of what type it could be, then? And, is it possible to know exact type of turn_rec variable?
create or replace function foo_List return VIPTURNS_2_TABLE_T pipelined is
CREATE OR REPLACE TYPE sample_object IS OBJECT (id NUMBER ,name VARCHAR2(30)); / CREATE OR REPLACE TYPE sample_table IS TABLE OF sample_object;
I have read some docs ,but I didn't get any information where exactly we use this.provide one real time scenario with an example.How this is different from record.
I have search function that takes the input parameters and returns all the matching rows. this is straight forward only. My problem is having multiple types as a input parameter. that is the reason i am not able to pass the input value for these types.
My Input Type table looks like this.
CREATE OR REPLACE TYPE T_T_PARTY_REQUEST_CRITERIA AS TABLE OF T_O_PARTY_REQUEST_CRITERIA; / CREATE OR REPLACE TYPE T_O_PARTY_REQUEST_CRITERIA AS OBJECT ( SYSTEM_IDENTIFER VARCHAR2(50), PROCESS_TYPE VARCHAR2(50), [code]........
Last two inputs are type again.my question here is how to pass the values for these two T_T_PARTY_KEY_IDENTIFIER and T_T_ADDTN_IDENTIFIER_INFO. I have defined the the last two types following.
CREATE OR REPLACE TYPE T_T_PARTY_KEY_IDENTIFIER AS TABLE OF T_O_PARTY_KEY_IDENTIFIER; / CREATE OR REPLACE TYPE T_T_ADDTN_IDENTIFIER_INFO AS TABLE OF T_O_ADDTN_IDENTIFIER_INFO; / [code].......
I have pasted the query here from my function. when i pass the null as part of input for these 2 types my query is working. otherwise it is saying invalid Identifier.First I tried with first Type.
I am passing the value as
(PRKYTP.PRTY_KEY_TYP_NM = ITTPRC.PARTY_KEY_IDENTIFIER.PARTY_KEY_TYP_NM OR ITTPRC.PARTY_KEY_IDENTIFIER.PARTY_KEY_TYP_NM = 'ALL' OR ITTPRC.PARTY_KEY_IDENTIFIER.PARTY_KEY_TYP_NM IS NULL);
Error is Error(34,147): PL/SQL: ORA-00904: "ITTPRC"."PARTY_KEY_IDENTIFIER"."PARTY_KEY_TYP_NM": invalid identifier
SELECT DISTINCT T_O_PARTY_REQUEST_IDENTIFIER(PR.PRTY_RQST_ID) BULK COLLECT INTO T_T_P_R_CRITERIA FROM TABLE(CAST(I_T_T_PARTY_REQUEST_CRITERIA AS T_T_PARTY_REQUEST_CRITERIA)) ITTPRC, PRTY_RQST PR JOIN BUSN_APPLC BIAP ON BIAP.BUSN_APPLC_ID = PR.BUSN_APPLC_ID [code]........
I am trying to execute the procedure from toad, where the type is like an object.How can I do that?
CREATE OR REPLACE TYPE list_t AS TABLE OF list_objtype; CREATE OR REPLACE TYPE list_objtype AS OBJECT ( Emp_ID NUMBER(5), EMp_Name VARCHAR2(32 BYTE) );
In the following procedure I am calling list_t, but how would i pass the values to it?
PROCEDURE modify_settings ( p_List in list_t, p_info out varchar2) DECLARE p_List TABLE;
