Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Dynamic "select into" from a record

Posted on 2009-12-29
9
Medium Priority
?
612 Views
Last Modified: 2013-12-18
Hello to dear experts,
I need help with the following issue:
This procedure runs successfully:

CREATE OR REPLACE PROCEDURE get_value( p_record  IN  some_table%ROWTYPE ) IS

  v_value NUMBER;

BEGIN

  select p_record.column1
  into v_value
  from dual;

END;

But I need to dynamically change the column and I try the following:

CREATE OR REPLACE PROCEDURE get_value(p_column IN VARCHAR2,
                                                                           p_record  IN  some_table%ROWTYPE ) IS

  v_value NUMBER;
  v_sql VARCHAR2(500);

BEGIN

  v_sql := ' select p_record.' || v_column ||  '  from dual ';
  EXECUTE IMMEDIATE v_sql INTO v_value;

END;

But this concept doesn't work,
How should I implement this task ??

Thank you


0
Comment
Question by:OraDeveloper
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 9

Expert Comment

by:joaoalmeida
ID: 26138022
In the syntax

v_sql := ' select p_record.' || v_column ||  '  from dual ';


the v_column should be p_column (this is the name of your input variable)

regards,

Joao
0
 
LVL 15

Expert Comment

by:Shaju Kumbalath
ID: 26138398
this concept doesn't work.
please use
FOR eg:
if p_column='ID' THEN
v_value:=p_record.id
elsIF P_COLUMN='NAME'  THEN
V_VALUE:=P_RECORD.NAME
.............
or if u have more conditions use CASE STATEMENT
0
 
LVL 32

Expert Comment

by:awking00
ID: 26138896
See attached.
comments.txt
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 14

Expert Comment

by:shru_0409
ID: 26143485
as u said it doesn't  work . im agree with Shajukg i am providing some information about ROWTYPE
and as awking said i will give the error like PLS-00306: wrong number or types of arguments in call to '||'

Selecting multiple rows from a single table using a table%ROWTYPE:
When the cursor selects all columns there is no difference between using table%ROWTYPE and a cursor%ROWTYPE.  
For example:

CURSOR c1 IS SELECT * FROM EMP;

REC C1%ROWTYPE; or REC EMP%ROWTYPE;      -- either will work

OPEN c1;
   WHILE c1%FOUND LOOP

      FETCH c1 into REC;

   END LOOP;
   CLOSE c1;

A table%ROWTYPE declares a structure with a component for each column. Usually the table%ROWTYPE is only used when selecting all columns. Otherwise, use the cursor%ROWTYPE. When selecting most components from tables with many columns you might choose a table%ROWTYPE with a SELECT * query, rather than code each distinct column in the SELECT statement.

0
 

Author Comment

by:OraDeveloper
ID: 26143962
Hi all,
Answer to shajukg:
I cant use this code because I don't know an exact columns of a table, so I can't write:

if p_column='ID' THEN
  v_value:=p_record.id

Because I don't know if there will be such a column ID. - I can't write column ID hard-coded !!!
I get a column name as a parameter.

Answer to awking00:
Doesn't work, I get a message: wrong number or types of arguments in call to '||'

Answer to shru_0409:
I did'nt understand your answer, what is the solution ??

Thank you
0
 
LVL 15

Expert Comment

by:Shaju Kumbalath
ID: 26144278
we can not use
select p_rercord.first_name from dual as is a pl/sql record set,
i think  the only way is to get the value is , get it from procedure/code where u open record set.
0
 

Accepted Solution

by:
OraDeveloper earned 0 total points
ID: 26145158
It was so simple, you just need to declare record in package declaration, and the following will work:

CREATE OR REPLACE PACKAGE some_package IS
 
  some_record some_table%ROWTYPE;

END;


CREATE OR REPLACE PROCEDURE get_value(p_column IN VARCHAR2,
                                                                           p_record  IN  some_table%ROWTYPE ) IS

  v_value NUMBER;
  v_sql VARCHAR2(500);

BEGIN

  some_record := p_record;

  EXECUTE IMMEDIATE 'BEGIN
                                        :x :=  some_package.some_record .' || p_column ;
                                      END;'   USING OUT v_value;

END;

If you see mistakes in syntax or variables just skip it. I didn't copy paste this code, I just wrote it to show the concept.

Here again,  I think to cancel my subscription to this site, because It seems that I get answers from amateurs.

Sorry, no points
0
 
LVL 32

Expert Comment

by:awking00
ID: 26145409
Technically, since no one is getting paid to do this, I guess we're all amateurs.
0
 

Author Comment

by:OraDeveloper
ID: 26145459
I would like to get answers from people who are interested to get payed for this, because the answers will be more accurate and responses will be quick.
Are there such options for this in this site ?

Thanks
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question