• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 617
  • Last Modified:

Dynamic "select into" from a record

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
OraDeveloper
Asked:
OraDeveloper
  • 3
  • 2
  • 2
  • +2
1 Solution
 
joaoalmeidaCommented:
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
 
Shaju KumbalathDeputy General Manager - ITCommented:
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
 
awking00Commented:
See attached.
comments.txt
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
shru_0409Commented:
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
 
OraDeveloperAuthor Commented:
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
 
Shaju KumbalathDeputy General Manager - ITCommented:
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
 
OraDeveloperAuthor Commented:
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
 
awking00Commented:
Technically, since no one is getting paid to do this, I guess we're all amateurs.
0
 
OraDeveloperAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now