SQL PL/SQL :: Declaring Record Type In Package Spec?

Time 2020-10-15 15:04:48

Web Name: SQL PL/SQL :: Declaring Record Type In Package Spec?

WebSite: http://oracledba.bigresource.com

ID:83101

Keywords:

Declaring,Record,SQL,

Description:

SQL PL/SQL :: Declaring Global Variable Inside Package? Sep 11, 2012 I want to declare global variable inside package. get the correct query. how to assign value for that variable.SQL create or replace2 PACKAGE new_pack3 AS4 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);1011 END new_pack;12 /Package created.SQL CREATE OR REPLACE2 PACKAGE body new_pack3 AS4 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 errorErrors for PACKAGE BODY NEW_PACK:LINE/COL ERROR-------- -----------------------------------------------------------------12/12 PLS-00103: Encountered the symbol = when expecting one of thefollowing:constant exceptiontable LONG_ double refchar time timestamp interval date binary national characternchar View 10 Replies View Related SQL PL/SQL :: Using Record Type And Object Type? Aug 17, 2013 I 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. View 20 Replies View Related SQL PL/SQL :: Function That Returns A Table Type Inside A Package Body Jul 26, 2011 CREATE 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? View 10 Replies View Related PL/SQL :: Assign Variables To User Defined Data Type Within Package Oct 16, 2013 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 View 4 Replies View Related PL/SQL :: ORA-22806 - Not Object Or REF When Using Record In Package? Jun 19, 2013 Oracle Database 11g Enterprise Edition Release 11.1.0.7.0I 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]... View 8 Replies View Related SQL PL/SQL :: Getting Data From Record Type Jul 19, 2010 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 isI 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 ? View 20 Replies View Related SQL PL/SQL :: Converting XML Message To Record Type Apr 8, 2010 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). View 4 Replies View Related SQL PL/SQL :: Select All The Elements Of A Record Type May 24, 2011 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? View 2 Replies View Related SQL PL/SQL :: Table Type Of RECORD In A Query? Apr 2, 2012 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_nameFROM 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. View 5 Replies View Related SQL PL/SQL :: Nested Loop Using Record Type? Jul 29, 2013 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; View 5 Replies View Related SQL PL/SQL :: Dynamic Procedure Name With Record Type I/p Parameter? Jun 22, 2012 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? View 3 Replies View Related SQL PL/SQL :: Create Record Type With Rowtype And One Field Oct 11, 2012 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. View 3 Replies View Related SQL PL/SQL :: Executing Dynamic Procedure With Record Type Jul 26, 2012 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)ISl_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... View 10 Replies View Related SQL PL/SQL :: How To Access The Record Type Elements Of Varray Jun 11, 2013 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;IDDESCRIPTION1a2b3cselect * from t2;ID1DESCRIPTION14aa5bb1ccdeclaretype 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]....... View 13 Replies View Related WebService - How To Create Record Type And PL/SQL Table Oct 7, 2010 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. View 1 Replies View Related Overload PL / SQL Procedure Based On Different Record Type With Same Structure Apr 8, 2004 I have two cursors likecursor_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%ROWTYPErecord_B is a recorded of cursor_B%ROWTYPEI 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? View 3 Replies View Related SQL PL/SQL :: Dynamic Calling Function With Type Record Parameter? Jul 23, 2010 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]..... View 5 Replies View Related SQL PL/SQL :: Getting Error When Record Type Is Used / PLS-00302 / Component (SAL) Must Be Declared Sep 3, 2013 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 declaredPLS-00302: component SAL must be declaredCREATE OR REPLACE PACKAGE emp_details_proc[code]....I am not getting any error. View 16 Replies View Related SQL PL/SQL :: How To Record / Nested Table Type Based On Row Returned By Week May 10, 2011 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 like1 Select * from emp2 Select count(1) from deptNow 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. View 3 Replies View Related Declaring Cursors With A Variable Jul 7, 2010 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:declaremy_company_id INTEGER := select c.company_id from company_table c where company_name= Wal-Mart cursor employees isselect e.employee_id from employees e where e.company_id = my_company_id;Any way to do this? View 4 Replies View Related SQL PL/SQL :: Declaring Arrays Inside Stored Procedure? Jun 17, 2010 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 . View 14 Replies View Related PL/SQL :: Declaring Variable Twice In A Block Is Redundant Or It Gives Any Benefit May 5, 2013 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].... View 3 Replies View Related SQL PL/SQL :: Declaring Variables In Exceptions Block Globally Aug 23, 2011 I have a PL SQL program block as shown below,create or replaceprocedure except_handleisv_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 View 6 Replies View Related PL/SQL :: Find Out In Database Package Where Package Is Installed May 28, 2013 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 View 8 Replies View Related SQL PL/SQL :: Global Exception In Package Available Outside Package? Jan 15, 2012 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? View 4 Replies View Related

TAGS:Declaring Record SQL 

<<< Thank you for your visit >>>

Hot Websites