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.
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.
I am unable to insert the result set of query into corresponding SQL Table type variable where as same functionality can be accomplished by PL/SQL table type variable. Can't we access the same by using SQL type variable?
Ex:
Step 1:
SQL Object type , Table type Objects creation : drop type sql_emp_tab_type ; drop type sql_emp_type ; create or replace type sql_emp_type as object ( empno number, ename varchar2(20),
[code]...
Step 2: Accessing the table type object from PL/SQL block
I will have to take the Employee Names and create a table structure. Number of employee names can vary from day to day. So, whenever I execute my procedure with Table type, I will have to build the table columns with the employee names.
i am trying to run a script in which a command tries to create or replace a type.
i get this error:
ORA-02303: cannot drop or replace a type with type or table dependents
SQL> SQL> --create a test user: SQL> SQL> create user tuser identified by tuser
[Code]....
Table created.
SQL> SQL> --then change the type: SQL> SQL> create or replace type t1 as object (obj_type number(15)) 2 / create or replace type t1 as object (obj_type number(15)) * ERROR at line 1: ORA-02303: cannot drop or replace a type with type or table dependents
SQL> SQL> --if i'll do FORCE action on the type - it'll corrupt my depandant table: SQL> SQL> drop type t1 FORCE 2 /
Type dropped.
SQL> SQL> SQL> SQL> SQL> desc dpntnt_table Name Null? Type ----------------------------------------- -------- ---------------------------- ID1 NUMBER(7)
SQL> SQL> SQL> SQL> --if i re-create it - my table is still corrupted: SQL> SQL> SQL> create or replace type t1 as object (obj_type number(15)) 2 /
Type created.
SQL> SQL> SQL> SQL> desc dpntnt_table Name Null? Type ----------------------------------------- -------- ---------------------------- ID1 NUMBER(7)
SQL>
--if i re-create it - my table is still corrupted:
create or replace type t1 as object (obj_type number(15)) / desc dpntnt_table [/code]
1. If i'll do drop type FORCE what will happen to the dependent object(might be a table for example) ?
2. I understand that this type is already assigned to some object, but i can't seem to find out which one.
how do i find out which object is depending on the type i want to create or replace?
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 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,
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 ?
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 need to create a query that returns record by record a field qty_progr with the cumulate qty considering previous records. The result should be the following:
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).
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 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?
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;
I'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 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].......
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),
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
i want to get table name, constraint name, constraint type with join processes in string type. this is what i want: alter table tablename add constraint constraintname constrainttype(columnname)
