StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POpass pl/sql record as arguement to procedure
primarykey
Id
19636714
data
AcceptedAnswerId
19637172
AnswerCount
2
ClosedDate
CommentCount
5
CommunityOwnedDate
CreationDate
2013-10-28T13:58:00.557
FavoriteCount
0
LastActivityDate
2016-09-02T13:27:08.877
LastEditDate
2013-10-28T16:58:47.363
LastEditorUserId
764216
OwnerUserId
764216
ParentId
0
PostTypeId
1
Score
2
ViewCount
8914
LastEditorDisplayName
text
Body
How to pass pl/sql record type to a procedure : <pre><code>CREATE OR REPLACE PACKAGE BODY PKGDeleteNumber AS PROCEDURE deleteNumber ( list_of_numbers IN List_Numbers ) IS i_write VARCHAR2(5); BEGIN --do something END deleteNumber; END PKGDeleteNumber; / </code></pre> In this procedure <code>deleteNumber</code> I have used <code>List_Numbers</code>, which is a record type. The package declaration for the same is : <pre><code>CREATE OR REPLACE PACKAGE PKGDeleteNumber AS TYPE List_Numbers IS RECORD ( IID NUMBER ); TYPE list_of_numbers IS TABLE OF List_Numbers; PROCEDURE deleteNumber ( list_of_numbers IN List_Numbers ); END PKGDeleteNumber; </code></pre> I have to execute the procedure <code>deleteNumber</code> passing a list of values. I inserted numbers in <code>temp_test</code> table, then using a cursor U fetched the data from it : <pre><code> SELECT * BULK COLLECT INTO test1 FROM temp_test; </code></pre> Now, to call the procedure I am using <pre><code>execute immediate 'begin PKGDELETENUMBER.DELETENUMBER(:1); end;' using test1; </code></pre> I have tried many other things as well(<code>for loop</code>, <code>dbms_binding</code>, etc). How do I pass a pl/sql record type as argument to the procedure? EDIT: Basically, I want to pass a list of numbers, using native dynamic sql only... adding the table temp_test defn (no index or constraint): <pre><code>create table test_temp ( IID number ); </code></pre> and then inserted 1,2,3,4,5 using normal <code>insert</code> statements. For this solution, <pre><code>In a package testproc CREATE TYPE num_tab_t IS TABLE OF NUMBER; CREATE OR REPLACE PROCEDURE my_dyn_proc_test (p_num_array IN num_tab_t) AS BEGIN dbms_output.put_line(p_num_array.COUNT); END; / </code></pre> this is called from sql prompt/toad DECLARE v_tab testproc.num_tab_t := testproc.num_tab_t(1, 10); BEGIN EXECUTE IMMEDIATE 'BEGIN testproc.my_dyn_proc_test(:1); END;' USING v_tab; END; this will not work.This shows error.I am not at my workstation so am not able to reproduce the issue now.
Tags
<oracle><plsql>
Title
pass pl/sql record as arguement to procedure
singulars
PostAcceptedAnswerId
1. PO
 singulars
 PostTypePostTypeId
 PTAnswer
PostParentId
1. This table or related slice is empty.
PostTypePostTypeId
1. PTQuestion
UserLastEditorUserId
1. USsubodh1989
UserOwnerUserId
1. USsubodh1989
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. This table or related slice is empty.
PostsParentIdCreationDate
1. PO
 singulars
 PostTypePostTypeId
 PTAnswer
VotesPostIdCreationDate
1. VO
 singulars
 PostPostId
 POpass pl/sql record as arguement to procedure
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
2. VO
 singulars
 PostPostId
 POpass pl/sql record as arguement to procedure
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
CommentsPostId

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.