Is there any way that I can check what are the elements present in a pl sql record type by querying in table?
For example if I want to check what are elements present in oe_order_pub.header_rec_type and I don't want to open the package, then in which table I should query? Is it possible?
i have created one varray whose elements are of record type. Now how can i access those record type elements?
structure of table t1:
select * from t1;
IDDESCRIPTION
1a
2b
3c
select * from t2;
ID1DESCRIPTION1
4aa
5bb
1cc
declare
type r1 is record (id t1.id%type);
type r2 is record (id1 t2.id1%type);
type r3 is record (id1 r1, id2 r2);
type var1 is varray(20) of r3;
[code].......
There is XMLType table with structural storage. Is there a way to make schema validation disabled on some elements of complex type?
It is unpractical to maintain a schema for the element due to high volatility. Ideally it will be stored in CLOB and extracted as is a whole branch without validation, none of the elements under this complex type will be extracted separately.
how to convert a record type to a table type?
View 4 Replies View RelatedI want to return the output of this query using one OUT parameter from the procedure using RECORD type or OBJECT type.
SELECT empno,ename,sal FROM emp WHERE deptno=30;
Let us assume the query is returning 50 records.
I want to send those 50 records to OUT parameter using record type or object type.
I have one requirement. We have a Package which consists of 2 Stored Procedures which has a RecordType output Parameter.
FUNCTION GET_NFE_INFO(O_status_code IN OUT NUMBER,
O_error_message IN OUT VARCHAR2,
O_message IN OUT "OBJ_FM_NFE_DOCHDR_REC",
I_fiscal_doc_id IN FM_FISCAL_DOC_HEADER.FISCAL_DOC_ID%TYPE)
return BOOLEAN is
I am working on a one Stored Procedure which would display the output from the above function as,
set serveroutput on size 9000;
DECLARE
L_stg_status FM_FISCAL_DOC_HEADER.STATUS%TYPE := 'NFE_P';
L_fiscal_doc_id FM_FISCAL_DOC_HEADER.FISCAL_DOC_ID%TYPE := 12325;
L_dummy VARCHAR2(1) := NULL;
L_status_code NUMBER(1) := 0;
L_error_message VARCHAR2(255) := NULL;
L_message "OBJ_FM_NFE_DOCHDR_REC" := NULL;
[Code]..
Here for each and every data value i am Printing it using DBMS_OUTPUT, L_message record has more than 100 columns. Is there a easy way of handling it instead of using DBMS_OUTPUT ?
We have a queue in which the message is coming from external system. The payload of the queue table is a PL/SQL record type. Once we get the message in the queue, we de-queue the message and read through the PL/SQL type collection and process the message.
From the below query, we are able to convert the PL/SQL collection message to XML message and see the data.
SELECT dbms_xmlgen.getxml
('SELECT USER_DATA
FROM <Queue_table> X
WHERE X.USER_DATA.SALE_ORDER.P_HEADER_REC.ORIG_SYS_DOCUMENT_REF=800501298')
FROM dual;
The new requirement is the message would come in a XML message in the queue. So my question is, is there any way through which the XML message can be converted to the PL/SQL record structure directly (it would be the opposite operation of the above query).
I am in the need of using a table type object in SQL query.
I have a package which has a spec in which I have declared :
TYPE TESTREC IS RECORD
(
RE_ID NUMBER(9),
RATING_TARIFF_NAME VARCHAR2(40),
);
TYPE TESTTABTYPE IS TABLE OF TESTREC;
TTR TESTTABTYPE;
In one of the package procedures I am collecting data into the above indicated table type object (TTR):
SELECT
RE_ID,
RATING_TARIFF_NAME
BULK COLLECT INTO TTR
FROM TESTPACKTAB;
This works fine. The values get collected into TTR and am able to print them too.
But when I :
SELECT
AA.RATING_TARIFF_NAME
INTO v_name
FROM
TABLE( TTR ) AA ;
in the same procedure immediately after the collection I get the error while running the procedure :
ORA-21700 : Object does not exist or is marked for delete.
I am using a record type to print some column in a same line.
Eg: I want to create index on some composite key columns. But i dont know how many columns are there. So want to use a loop which will count the number of column and then create the index like:
CREATE INDEX PRODUCT.XIF1AGMNT_PROD ON PRODUCT.AGMNT_PROD(LOAN_ID,LOAN_PROD_STRT_DT) TABLESPACE PRODUCT_INDEX;
I have a problem with passing procedure name dynamically with record type i/p parameter..I'm not attaching any insert/create table statements, as I'm unsure of forming the sql statement dynamically..
CREATE OR REPLACE PACKAGE med_order_pkg AS
TYPE deid_med_order_typ IS RECORD(....)
L_deid_med_order_typ deid_med_order_typ;
PROCEDURE RULE_MASTER_PRC (P_IN_RULE IN deid_med_order_typ);
END;
[code]....
From the above, I need to execute a procedure with record type as i/p parameter..V_SQL should form the statement & execute another procedure which comes into the variable V_MSG_PROC .I'm having difficuly in forming the statement...(I did it by hard-coding the procedure with parameter in the next line which is commented out & it works...So how can I modify V_SQL in the above statement?
using as template this table:
create table t1 (c1 number,c2 number);
CREATE OR REPLACE
TYPE REC IS RECORD (
R1 T1%ROWTYPE,
R2 NUMBER
);
I'm trying to create one RECORD type with all the columns from table T1 + one new field R2. But gives me an error.
The point to use T1%ROWTYPE and not to hardcode the columns from T1, is due to if we add a new column to T1, is created when is executed again RECORD definition and not to add the column.
I need to declare a record type in a package spec and used that define record type as a parameter in a procedure.
A procedure will call this package passing a record type . e.g xxtest.tmpprc(employee_rec employee_type);
(TYPE record_type_name IS RECORD (column_name1 datatype, column_name2 datatype, ...);
CREATE OR REPLACE package xxtest as
PROCEDURE tmpprc(trecordType IN VARCHAR2);
END;
What is functional diffrence between type, %rowtype and record.
View 3 Replies View RelatedI'm not attaching any tables / data..etc...I just want to know how to pass the record type to a procedure (which are actually obtained from a table) -- see ** below where I'm getting an error..Need to pass the whole record type "l_shl_order_msg"
CREATE OR REPLACE PROCEDURE CM_BUILD_MSG_PRC (P_IN_BLD_MSG_CURSOR IN SYS_REFCURSOR,
P_OUT_BLD_MSG_CURSOR OUT SYS_REFCURSOR)
IS
l_shl_order_msg CRAE_INTERFACE.GLB_VAR_PKG.deid_SHELL_order_typ;
V_MSG_SHELL_NAME VARCHAR2(1000);
V_MESG_TEXT_SEGMENT VARCHAR2(1000);
V_TEXT VARCHAR2(1000);
V_MSG_TEXT VARCHAR2(4000);
V_MSG_FINAL_TEXT VARCHAR2(4000);
V_MSG_PROC VARCHAR2(1000);
V_SQL VARCHAR2(4000);
V_CNT NUMBER;
L_STATUS VARCHAR2(100);
L_REASON VARCHAR2(1000);
[code]...
I get an error saying that "wrong number or types of arguments in call to ||"..Not sure how to pass record type dynamically...
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.
I have two cursors like
cursor_A IS select * from table_a where condition_A;
cursor_B IS select * from table_a where condition_B;
record_A is a recorded of cursor_A%ROWTYPE
record_B is a recorded of cursor_B%ROWTYPE
I define a procedure like pro_A(record_in cursor_A%ROWTYPE) can I overload this procedure by defining pro_B(record_in cursor_B%ROWTYPE)?
If I can't, Can I call pro_A by passing record_B as the parameter to it?
I'm trying to execute a dynamic sql that calls a function. But that function has inserts and deletes inside and this way it can't be called through a select statement. And to be worst, it has an other problem, my function uses a record type as parameter.
My code (sample):
-----------------
DECLARE
type r_parameters is record
(cd_query cons_query_param.cd_query%type,
cd_usuario cons_query_user.cd_usuario%type,
nr_param cons_query_param.nr_param%type,
vl_param varchar2(2000),
[code].....
Getting error when using record type as in parameter.
PLS-00306: wrong number or types of arguments in call to 'SAL_UPDATE_PROC'
PLS-00302: component 'ENAME' must be declared
PLS-00302: component 'SAL' must be declared
CREATE OR REPLACE PACKAGE emp_details_proc
[code]....
I am not getting any error.
Is there a way we could define a record or a nestedtable with a type based on weak refursor i.e
TYPE RC IS REF CURSOR;
C2 RC;
Type t is table of c2%rowtype;
Following is some more explanation of what I am trying to do.
I have a table T with column A and B. Column A is a primary key with number 1,2,3,4,5,6, Column B has diffrent sql stmts stored. i.e 'Select * from emp', Select count(1) from dept' and so on. So table will look like
1 Select * from emp
2 Select count(1) from dept
Now I want to select statements stored in table T one by one and execute them by using cursor. Problem arises as i need to fetch the cursor into some variable but the outcome of each statment is diffrent and oracle does not allow to use cursorname%rowtype for a weak ref cursor.
How do i insert or select date data type value in am or pm Eg 10:30:00 am or 10:35:00 pm
View 3 Replies View RelatedI want to write select query to get sum of quantity for each type of item ,in the table I have itemnum,itemtype and quantity ,type have value(A,B,C,....)
the result will be like that ex:
item.No A B C D ....
10 50 60 80 20
--- 50 will be sum(quantity) for item type A
how can I do that, I mean how can I retrieve many column from the same field?
I have a table Table_xml_tmp which has a column MESSAGECONTENTS as XMLType data which contains data as follows
"<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<xclimSchemaMain xmlns="http://clones.telcordia.com">
<response>
<clHeader>
<msgName>queryPlace</msgName>
[code]......
I need to get all rows where node <operationStatus> value as SUCCESS. I am running following query but it is returning null value insted of data of columns
SELECT a.MESSAGECONTENTS.extract('/clHeader/msgName/object/msgType/sender/senderInstance/operationStatus/text()').getStringVal()
FROM Table_xml_tmp a
WHERE a.MESSAGECONTENTS.extract('/clHeader/msgName/object/msgType/sender/senderInstance/operationStatus/text()').getStringVal()= 'SUCCESS'
How to call a function with a row type return in an Oracle select statement.
For e.g. :
If I had this function with a rowtype return:
------------------------------
create function abc
return xyz%rowtype
is
rec xyz%rowtype;
begin
select * into rec from xyz where col1 = n;
return rec;
end;
--------------------------------
How could I use this in a select clause, as there is a multi column return by the function ?
Based on ACCOUNT_NUMBER column we have to check the GUID data in test table.
for example ACCOUNT_NUMBER =11 as duplicate it has 2 values then only, we need to check the corresponding GUID if any occurence ("9f680174-cb87-4f71-887a-92" and "9f680174-cb87-4f71-887a-91"),
we should select,if not leave it.
CREATE TABLE test
(
GUID VARCHAR2(32 BYTE),
ACCOUNT_NUMBER NUMBER(30),
INDIVIDUAL_ID NUMBER(13)
)
[Code]...
it tried as like as below it's working.
select t2.* from (select distinct t1.guid
from test t1,( select ACCOUNT_NUMBER from test
group by ACCOUNT_NUMBER having count(ACCOUNT_NUMBER
[Code]...
I am unable to select only record for 07Nov06. if i use between i get records e.g. :07 November ,2006-0941
Select
TO_CHAR(session_START_time,'dd month,yyyy-hh24mi') LOG_IN_TIME,
TO_CHAR(session_END_time,'dd month,yyyy-hh24mi') LOG_OUT_TIME
from SESSION_LOG
where SESSION_LOG.SESSION_START_TIME
between '06Nov06' AND '07Nov06';
I have a UNION query having 3 parts, 1st gets date, 2nd data and 3rd displays the formatted data count :WHERE clause of 2nd and 3rd queries are same.
Problem is that I an getting different record counts when I select the overall count of records given by the whole UNION query and when I run to count the records given by each query individually.First count. Here I am selecting the overall count of records given by the query :
select count(1)
from (
SELECT SUBSTR ( '0'
|| TO_CHAR (SYSDATE, 'MM/DD/YYYY')
|| TO_CHAR (SYSDATE, 'HH:MI:SS')
|| LPAD (' ', 180)
[code]...
This count is : 1751525 Second count. Now when I run to count the records given by each query individually, here is the result
select count(1) a
from (
SELECT SUBSTR ( '0'|| TO_CHAR (SYSDATE, 'MM/DD/YYYY') || TO_CHAR (SYSDATE, 'HH:MI:SS')
|| LPAD (' ', 180)|| chr(13), 1, 200 ) dtl_record from dual
select count(1) b from (
SELECT '1'
[code]...
why there is difference of 1 (1751526 - 1751526) in the count results.
I have a table of below structure:
CREATE TABLE Emp_addrs
(
EMP_ID NUMBER(15) NOT NULL,
ADDRESS_ID NUMBER(15) NOT NULL,
SITE_USE_ID NUMBER(15) NOT NULL,
SITE_USE_STATUS VARCHAR2(1 BYTE) NOT NULL,
SITE_USE_CODE VARCHAR2(30 BYTE) NOT NULL)
Insert Script code :
insert into Emp_addrs values ( '1207' , '1846', '2342','A');
insert into Emp_addrs values ( '1207' , '1846', '2343','I');
insert into Emp_addrs values ( '61618' , '165200', '261449','A');
[Code]...
A combination of emp_id & address_id can have multiple site_use_id's. I want to select the max(site_use_id) where site_use_status ='A'.
Now Site_use_status can have either = 'I' or 'A' value.
For a combination of emp_id and address_id , there could be cases when there is no record with site_use_status ='A'. In such cases I need to select the max(site_use_id) (and obviously site_use_status ='I').
Just to clear my requirements, out of the above records I want the following records:
'1207' , '1846', '2342','A'
'61618' , '165200', '261449','A'
'1003' , '1004', '1007','A'
'1005' , '1010', '2002','I'
'2005' , '2010', '3002','A'
I have a sql query where I need to select only records with an 18 month gap between max(date) and previous date( no dates between max(date)and 18 month gap date), when I run the below query it should only select supid 130, not 120 (even though 120 does contain an 18 month gap date it also has a date that is less then the 18 month gap( '25-NOV-2012','DD-MON-YYYY'). how would get the query to look back 18 months for the next date and evaluate the month_between.
. example:
create table supply(supID number(8), supply varchar2(20), supdate Date,supamount number(13,2));
insert into supply values(100,'Tapes',to_date('01-AUG-2013','DD-MON-YYYY'),50.00);
insert into supply values(100,'TV',to_date('01-APR-2013','DD-MON-YYYY'),250.00);
insert into supply values(100,'Discs',to_date('25-DEC-2012','DD-MON-YYYY'),25.00);
insert into supply values(120,'Tablets',to_date('25-AUG-2013','DD-MON-YYYY'),15.00);
[Code]....
and p.supid in(select s.supid from supply s where months_between
(s.supdate,p.supdate)<-18) SUPID SUPPLY SUPDATE SUPAMOUNT---------- -------------------- --------- ---------- 120 Tablets 25-AUG-13 15 130 Discs 25-JUL-13 75
I have 3 main tables as projects, tasks, clients. Then I have a history table for each. I created a trigger on update/delete that takes the old records and inserts into the history.
Now, I need to retrieve the history but I have no clue how to do so. I need to join these 3 history tables with:
1. select from projects_history with projectID = 1
2. if no records, then go back to projects and get projectID = 1
3. select from task_history with taskID = 1
4. if no records, then go back to tasks with taskID = 1
5. select from client_history with clientID = 1
6. if no records, then go back to client with clientID = 1
where client.taskID = task.taskId and task.projectID = project.projectID.
I have a collection of objects built in a PL/SQL program.
I want to keep only the distinct elements of this collection for the rest of the program.
I failed to find the correct way to do it
For instance (the actual object is far more complex containing object attributes itself), with the following types:
drop type t1b
/
drop type t1a
/
create or replace type t1a as object (
v1 integer,
v2 integer
)
/
create or replace type t1b as table of t1a
/
I have the following variable:
v t1b := t1b(t1a(1,2),t1a(1,2));
And I want to get only one "t1a(1,2)" in my collection.
My first idea (actually the second one but it does not matter) was to use DISTINCT:
SQL> declare
2 v t1b := t1b(t1a(1,2),t1a(1,2));
3 begin
4 select distinct t1a(v1,v2) bulk collect into v from table(v);
5 dbms_output.put_line(v.count);
6 end;
7 /
declare
*
ERROR at line 1:
ORA-22950: cannot ORDER objects without MAP or ORDER method ORA-06512: at line 4
As I said the object is far more complex and builting a MAP function is quite tedious (but I will do it if there is no other way).
The next idea was to use multiset operators:
SQL> declare
2 v t1b := t1b(t1a(1,2),t1a(1,2));
3 begin
4 select v multiset intersect distinct v into v from dual;
5 dbms_output.put_line(v.count);
6 end;
7 /
1
PL/SQL procedure successfully completed.
This works well but I suspect this is not the correct way and there is one to do it in PL/SQL but currently failed to find it.