Overload PL / SQL Procedure Based On Different Record Type With Same Structure

Apr 8, 2004

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?

View 3 Replies

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 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.

View 3 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 :: 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)
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...

View 10 Replies View Related

SQL & PL/SQL :: Converting Record Type In Table Type

Jul 12, 2010

how to convert a record type to a table type?

View 4 Replies View Related

PL/SQL :: Selecting Single Record From Multiple Record Based On Date?

Aug 26, 2013

I have a table which contains the multiple records for single ID No. Now i have to select single record which contains the latest date. here is the structure Name

Null Type ------ ---- ------------ ID_P NUMBER NAME_P VARCHAR2(12) DATE_P TIMESTAMP(6) Records---------------------1 loosi 22-AUG-13 01.27.48.000000000 PM1 nammi 26-AUG-13 01.28.10.000000000 PM2 kk 22-AUG-13 01.28.26.000000000 PM2 thej 26-AUG-13 01.28.42.000000000 PM

now i have to select below 2 rows how can write select qurie for this?

1 loosi 26-AUG-13 01.27.48.000000000 PM2 thej 26-AUG-13 01.28.42.000000000 PM

View 4 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

Precompilers, OCI & OCCI :: Passing Array Of Structure To Oracle Procedure And Returning Back?

Apr 29, 2010

I created a table and some type definitions in oracle 10.2.0.3 DB as follows. There is also a procedure that I defined and is shown below.

SQL> create table test_proc
(
test_name varchar2(10),
test_ver number (3,3),
active_flag number(2)
);

Table created.

SQL> select * from test_proc;
TEST_NAME TEST_VER ACTIVE_FLAG
---------- ---------- -----------
AFP 1.5 2
PSA 11.89 0
EHIV 99.5 3
aHAVM 1.45 9

[code]....

Now from Pro*C function I want to pass a similar array of structure to this procedure and return it via the out parameter of the procedure back to Pro*C. How do I do it?

I am using the attached program but its giving me compiler error as follows..

Error at line 31, column 1 in file sample.pc
proc_modify_tdefs (:in_tdefs,:out_tdefs);
1
PLS-S-00306, wrong number or types of arguments in call to 'PROC_MODIFY_TDEFS'
Error at line 31, column 1 in file sample.pc
proc_modify_tdefs (:in_tdefs,:out_tdefs);

[code]....

View 2 Replies View Related

Forms :: Show Organization Structure In Top Down Organizational Structure?

Sep 3, 2012

I am struck in a requirement to show the organization structure in a Top Down Organizational structure.

Is there any way we can make the well structured report OR form?

View 4 Replies View Related

SQL & PL/SQL :: Exception In Procedure / Created One Procedure Based On One Table Item Master

Mar 6, 2010

I have created one procedure based on one table item master which has a field called item stock or non stock based on this i will fetch data from one of two tables .If its a stock item data will be retrieved from wip_main_acnt table and if its non stock it will pick from ns_main_acnt.my procedure is working fine but all i need is i just want to put an exception that if data is not found in one of the table based on the item selected.I am confused which one to be used whether no_data_found or notfound%.

CREATE OR REPLACE PROCEDURE dflt_pr_acnt (
l_item_code IN VARCHAR2,
l_main_acnt_code OUT VARCHAR2
)
[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 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 ?

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_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.

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 :: Out Ref Cursor Based On Record Set

Sep 24, 2010

I have this record set coming from two tables.

cursor c1 is select cust_id from tab1 where login=in_login
union
select cust_id from tab2 where login_id = in_login;

in second step; I need to process and filter these ids based on some conditions.The final filtered record set I need to return as Out cursor;

How can I use the processed data to built the output ref cursor ?

View 4 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 :: Declaring Record Type In Package Spec?

Mar 29, 2013

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;

View 16 Replies View Related

SQL & PL/SQL :: Functional Difference Between Type %rowtype And Record

May 17, 2010

What is functional diffrence between type, %rowtype and record.

View 3 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;

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].......

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

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 declared
PLS-00302: component 'SAL' must be declared
CREATE OR REPLACE PACKAGE emp_details_proc

[code]....

I am not getting any error.

View 16 Replies View Related

SQL & PL/SQL :: Count Number Of Record Based On Some Condition

Jun 28, 2013

i have two tables one as test_master and other one limit master.

test master table structure

ct_id ct_a ct_b ct_C ct_d ct_e...... etc
1 -- -- -- -- --
1 -- -- -- -- --
2 -- -- -- -- --
limit master structure

limit_id ct_a_limit ct_b_limit ct_c_limit ct_d_limit ct_e_limt ..... etc
1 -- -- -- -- -- -- -- --
2 -- -- -- -- -- -- -- --

ct_id and limit_id is match column used for mapping.

now i want the total count of no of records in test_master in which test_master column value not exceed limit master column limit value if any one column exceeds its limit then the same row will be in my count criteria.

how to achieve the same using sql/oracle query.

View 7 Replies View Related

SQL & PL/SQL :: Bulk Insert Based On Record Count

Jun 19, 2012

Is there any defined record count range for the following ways of bulk insert :

INSERT INTO ABCTEMP SELECT * FROM DEFTEMP;

OR

through a cursor, bulk fetch and bulk insert under a loop.

View 6 Replies View Related

SQL & PL/SQL :: Create External Table Based On Record Identifier?

Oct 21, 2011

I have a csv file which is in this format -

qwerty SCHEMATIC FILE
; Version 2007.7.1
Project,qwerty Project,,1,,7,1.5,1.5,1,1,0,,0,1,0,0,0,0,3,1,1,0,1,0,0,0,0,2,3,1,0,1,0,0,0,0,3,3,1,1,1,0,0,0,0,0,0,0,0,
abc,25150-28407.dat,,102,192,42,12632256,1,1,102,192,42,1,12632256,0,6,1,0,32896,1,0,0,0,0,,,-1,-1,-1,-1,0,0,0,-1,-1,-1
xyz, , , 0, 102, 0, 0, 0, 0, 0, 0, 0, 0, 0, , , , , , , , , , , 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
Product,00094416505678,19133,"24-36""X80"" FOLD DR VIA MEH",,4.5,81,3, 8421504,,0,,L.T.L. WHOLESALE.,abc,,0,0,0,
Product,00094416502345,37154,"24-36""X80"" xyz WHITE",,5,81,3, 8421504,,0,,L.T.L. WHOLESALE.,abc,,0,0,
Product,00094416501111,83120,"24-36""X80"" abc WH",,5,81,3, 8421504,,0,,L.T.L. WHOLESALE.,abc,,0

Can I create an external table for loading this file based on selection of fields driven by the record identifier which is the first field of each record, except for the firt two?

For e.g In the above file, for records which say 'abc' in it's first field, I want to load the 2,3,6,8 fields delimited by a comma (the values may as well be null in those fields) and for records which say 'Product' in their first fields, I want to load 5,8,9 and 10 fields from the same file. Basically, I want to know if we can use instr function while choosing the fields line by line based on a search criteria in the file. There will be less columns in my table than the number of fields in the csv file, so I guess I got to mention 'MISSING FIELD VALUES ARE NULL' option. There is another challenge too - I have to skip loading the first two lines from the file to the table.

I have written a big pl/sql proc for doing the same, using utl_file.get_line option, but it is still untested and I would be extremely happy to believe that this can be achieved by creating an external table too.

View 11 Replies View Related

SQL & PL/SQL :: How To Execute Type Variable Procedure

Apr 26, 2013

I have created the following procedure. Since I am using this first time I don't know how to execute this.

CREATE OR REPLACE PACKAGE GAFT_PROG_DIT.ram_package
IS
TYPE type_ots IS TABLE OF ORDER_TREND_SCORE%ROWTYPE INDEX BY PLS_INTEGER;
PROCEDURE InsertTrend( P_TYPE_OTS_REC IN type_ots );
END;
/

[Code]...

View 2 Replies View Related

Forms :: Fetch Single Record From Multiple Records Based On Condition

Jan 27, 2012

I have made a travel booking system which comprises of 3 forms

1)Travel Booking form
2)Reservation Form
3)Cancellation Form

Under one booking number i can add multiple users in which they can have there multiple travels.

Users can cancel there individual travels under a prescribe booking number which on doing the Cancel flag turns to 'Y'.

What i want is, If a user is cancelling his/her travel under any booking number then while retriving the records in Travel Booking form, the travels which are cancelled should not be in enable mode.

For one user there can be 4 travels out of which 2 are cancelled, how can i track only those records whoes cancel flag is set to Y. some logic to find it out. Else can i use :system.cursor_record. If yes, How to use it for this system.

View 9 Replies View Related

SQL & PL/SQL :: Granting Permissions For Create Type / Procedure?

May 18, 2011

I have a question regarding how to grant create type and create procedure roles correctly.Currently if I do this:

create user vackar_temp identified by "123456789";
grant create type to vackar_temp ;

Then as vackar_temp:

create type temp_col as table of number;

I get the following error:

ORA-01031: insufficient privileges

I've also tried:

grant resource to vackar_temp ;

But that doesn't work either If I run this:

select
lpad(' ', 2*level) || granted_role "User, his roles and privileges"
from
(
/* THE USERS */
select
null grantee,

[code]...

I can see that the user does have the create type role:

User, his roles and privileges
------------------------------
VACKAR_TEMP
CREATE TYPE
RESOURCE
CREATE CLUSTER
CREATE INDEXTYPE

[code]...

View 8 Replies View Related

SQL & PL/SQL :: How To Pass Type Object As A Parameter To Stored Procedure

May 9, 2012

I am fairly new to oracle, here's what I am doing.

Create or replace type csc_info as object( source_code varchar2(10),
Docno varchar2(10),
Key_value_1 varchar2(10),
Key_value_2 varchar2(10));

[Code]....

I need to test the procedure how do I pass the inputs for the type object csc_info?

View 2 Replies View Related