Avatar of Steve Sperber
Steve SperberFlag for United States of America

asked on 

How to invoke a Stored Procedure with RECORDTYPE as parameter and pass the value into it

How to call a stored procedure that is inside a package and takes parameter as RECORD TYPE for testing purposes. The procedure is not exposed in the package. 

--1)Cursor has a query 2)Cursor fetches data in Row Type 3) into list of variables

--Part 1: Cursor declaration ---
cursor abc_cursor select col_name from table_name;

Open in new window

--Part 2 Cursor assigned to rowtype---
abc_record abc_cursor % ROWTYPE;

Open in new window

-- Part 3 record passes into parameter
PROCEDURE PROC_NAME (abc_info IN abc_record%type)
DBMS_OUTPUT.PUT_LINE(abc_info.variable1);
DBMS_OUTPUT.PUT_LINE(abc_info.variable2);
-- lots of code ---
End  PROC_NAME;

Open in new window

* Oracle PL/SQLOracle Database* Oracle12c

Avatar of undefined
Last Comment
Steve Sperber
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Your title and example code don't match:
er_record IN er_record %TYPE

Open in new window


That is not a rowtype.  It appears to be a record type.

How is er_record  declared in the package?  If it is exposed externally, you can create a pl/sql block that initializes a record of that type and then just call the procedure in the package.

If it isn't exposed externally, you can create your own version in the pl/sql block.

If you can provide the details of what er_record is, we can provide a quick example.
Avatar of Steve Sperber
Steve Sperber
Flag of United States of America image

ASKER

Hi Netminder, You were right. Could you please help me?
I would love to help.

To do so, I need the information to help.

If it is a record object, I need to have the definition to come up with a simple test.

If it is a ROWTYPE, need that information as well.
Avatar of Steve Sperber
Steve Sperber
Flag of United States of America image

ASKER

@Netminder

The cursor has a query - which fetches data into Row Types and from row type, it is passed into the procedure as an object.

Updated the Main question to show the changes.
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Steve Sperber
Steve Sperber
Flag of United States of America image

ASKER

cursor and record are default or nothing before their declaration. testing this piece of code.
Oracle Database
Oracle Database

Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.

81K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo