Avatar of Java Developer
Java DeveloperFlag 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
Java Developer

8/22/2022 - Mon
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.
ASKER
Java Developer

Hi Netminder, You were right. Could you please help me?
slightwv (䄆 Netminder)

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.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER
Java Developer

@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
slightwv (䄆 Netminder)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
Java Developer

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