Solved

Passing table name into parameter for stored procedure

Posted on 2011-02-24
8
828 Views
Last Modified: 2012-05-11
Hello,

I'm trying to create a procedure with a parameter for a table name. Please help... I though I could write this ref cursor procedure just Like I do for the others.

Thanks
create or replace
PROCEDURE                                           ALL_TBLES_PRC

/* NAME: TBLES   DESCRIPTION: 
      -- This is to select ALL data from Project tables. Parameters will be the TABLE NAME and PUB_ID
   
*/ 
(
  PARAM_0 IN NUMBER, --USER
  PARAM_SYSFLAG IN NUMBER, --NUMBER  PARAM_1 IN VARCHAR2, -- TABLE NAME
  PARAM_2 IN NUMBER, -- PUB ID
  P_RECORDSET OUT SYS_REFCURSOR
) 
AS 
BEGIN
  OPEN P_RECORDSET FOR
    SELECT * FROM 'AAD.'|| PARAM_1
    WHERE ID = PARAM_2;

  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      NULL;
END ALL_TBLES_PRC;

/*

*/

Open in new window

0
Comment
Question by:lulubell-b
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 2
8 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 34973201
OPEN P_RECORDSET FOR
    'SELECT * FROM AAD.'|| PARAM_1 || ' WHERE ID = PARAM_2';

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 34973204
when using dynamic sql,  the entire statement must be contained in the string
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 34973212
oops, just noticed the param2 part

make that a bind

OPEN P_RECORDSET FOR
    'SELECT * FROM AAD.'|| PARAM_1 || ' WHERE ID = :PARAM_2' using param_2;

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 74

Expert Comment

by:sdstuber
ID: 34973231
your exception handler doesn't apply,  the OPEN won't generate no_data_found, that exception only applies to implicit SELECT cursors and FETCH of explicit cursors
0
 

Author Comment

by:lulubell-b
ID: 34973270
So the entire statement will be

  OPEN P_RECORDSET FOR
    'SELECT * FROM AAD.'|| PARAM_1 || ' WHERE ID = :PARAM_2' USING PARAM_2;

Not

  OPEN P_RECORDSET FOR
    'SELECT * FROM AAD.'|| PARAM_1 || ' WHERE ID = :PARAM_2';

Correct?

Thank you about the exception statement. I start off with that clause as a template. I will remove it.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 34973335
yes,  you need the USING clause to pass the param_2 into the bind variable.

you "could" append the value directly into the sql statement
but your DBA will thank you if you DON'T do that.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 34973346
on the other hand,  if PARAM_2 won't change for each table, then you might want to consider embedding it as a literal since it's not really variable with respect to each statement
0
 

Author Comment

by:lulubell-b
ID: 34973418
Thank you, I putting together a ref cursor for the developer to select data from their stage tables

PARAM_2 will change based upon what the application passes.
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle Forms 10g: How-To Prevent a Commit of 1 Record in Master-Detail block. 3 129
ER Diagram 3 52
pivot rows to columns 1 60
oracle forms question 9 40
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

738 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