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 want to know how we can declare a Global Variable in Package body(Not Spec), So that i can use it in any procedures or function(Defined in same package).
View 11 Replies View RelatedI want to declare global variable inside package. get the correct query. how to assign value for that variable.
SQL> create or replace
2 PACKAGE new_pack
3 AS
4 g_id employee_details1.employee_id%type;
5 PROCEDURE emp_project(
6 st_date IN DATE,
7 Prj_id out VARCHAR2,
8 prj_name out VARCHAR2,
9 Prj_location out VARCHAR2);
10
11 END new_pack;
12 /
Package created.
SQL> CREATE OR REPLACE
2 PACKAGE body new_pack
3 AS
4 PROCEDURE emp_project(
5 st_date IN DATE,
6 Prj_id OUT VARCHAR2,
7 prj_name OUT VARCHAR2,
8 Prj_location OUT VARCHAR2)
[Code] ..........
Warning: Package Body created with compilation errors.
SQL> show error
Errors for PACKAGE BODY NEW_PACK:
LINE/COL ERROR
-------- -----------------------------------------------------------------
12/12 PLS-00103: Encountered the symbol "=" when expecting one of the
following:
constant exception
table LONG_ double ref
char time timestamp interval date binary national character
nchar
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.
understand between Oracle pl/sql object type and package!
View 3 Replies View RelatedCREATE OR REPLACE TYPE TEST_OBJ_TYPE IS OBJECT
(
TEST_ID NUMBER(9),
TEST_DESC VARCHAR(30)
)
/
CREATE OR REPLACE TYPE TEST_TABTYPE AS TABLE OF TEST_OBJ_TYPE
/
[code]....
I need to include the above function in a plsql package. How I can declare a object type and table type in a pks file? the syntax to include the above code in a .pks and .pkb file?
I got this code snippet online when I was looking for function that returns a table type. what exactly that Exception block does? delete the table when there is an exception, otherwise return the table type?
I have this requirement, I have a following Record type within a package, instead of selecting datas into this i need to assign variables to this record type
TYPE xx_delivery_detail_rectype IS RECORD ( p_delivery_id NUMBER, p_ultimate_dropoff_location_id NUMBER, p_creation_date DATE, p_last_update_date DATE, p_container_name VARCHAR2 (30), p_inventory_item_id NUMBER, p_shipped_quantity NUMBER, p_shipment_line_id NUMBER ); TYPE xx_delivery_table_rectype IS TABLE OF xx_delivery_detail_rectype INDEX BY BINARY_INTEGER;
Say i have variables to assign to all the values within the record type, How do i do it within the package
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0
I have declared a record type in my package
create or replacePACKAGE MYPKG AS TYPE MYREC IS RECORD (VAL1 varchar2(20), val2 date); PROCEDURE display_error (pSQLERRM number); PROCEDURE P_LOAD_DATA (pStartDate Date, pEndDate Date); FUNCTION F_EPI(refno1 in NUMBER, refno2 in NUMBER) return
[code]...
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).
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 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.
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 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].......
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.
I'm currently looking for a way to declare a cursor in the 'declare' block using a previously defined variable that got its value from a query. For instance:
declare
my_company_id INTEGER := 'select c.company_id from company_table c where company_name='Wal-Mart';
cursor employees is
select e.employee_id from employees e where e.company_id = my_company_id;
Any way to do this?
how can i declare an array inside a stored procedure in Oracle. Right now, I have the following declaration.
procedure MarkLoanMappings(
p_AL_LA_ID in ACTIVE_LOAN.AL_LA_ID%TYPE,
p_AL_ASG_ID in ACTIVE_LOAN.AL_ASG_ID%TYPE,
p_AL_CFH_ID in ACTIVE_LOAN.AL_CFH_ID%TYPE,
p_Period in ACTIVE_LOAN.AL_PRCS_PERIOD%TYPE)
[code]....
When I try to compile it, I get the error "component EXISTS must be declared".
if table exists then, i whould like to delete it and re create it again.to do so i have two method pls tel me which one i should follow.
declaraion of variable v_strTN twoice in 1) query, does the twoice declaration gives any benefit or it is redundant?
1)
DECLARE
v_strTN VARCHAR2(30);
BEGIN
BEGIN
SELECT TABLE_NAME INTO v_strTN FROM USER_TABLES
WHERE UPPER(TABLE_NAME)='ABC';
[code]....
I have a PL SQL program block as shown below,
create or replace
procedure except_handle
is
v_errorcode VARCHAR2(10);
v_errormsg varchar2(200);
constr_violation Exception;
PRAGMA Exception_Init(constr_violation,-2292);
[code]..........
Here, I have assigned values to the variables 'v_errorcode' and 'v_errormsg' directly inside the exception block so that I can use them in both exception types 'constr_violation' and 'others'. I am getting an error message like,
Error(22,1): PLS-00103: Encountered the symbol "V_ERRORCODE" when expecting one of the following: pragma when
I need to find out in DB Package where this Package is installed (in which schema). The problem is this DB Package can be installed in various schemas. This means that I can't use select user from dual or system environment SYS_CONTEXT('USERENV', 'OS_USER').
What I would need is something like $$PLSQL_UNIT
I have a package with several procedures which raise and catch an error if a foreign key constraint has been violated. I put the the following code in my package body:
e_ouder_niet_gevonden EXCEPTION;
PRAGMA EXCEPTION_INIT(e_ouder_niet_gevonden,-2291);
Now all the procedures inside the package which catch this exception in the EXCEPTION block work fine. I would like to be able to use that exception outside of my package as well though, how would I do this?