Passing table name into parameter for stored procedure

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

lulubell-bAsked:
Who is Participating?
 
sdstuberCommented:
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
 
sdstuberCommented:
OPEN P_RECORDSET FOR
    'SELECT * FROM AAD.'|| PARAM_1 || ' WHERE ID = PARAM_2';

0
 
sdstuberCommented:
when using dynamic sql,  the entire statement must be contained in the string
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
sdstuberCommented:
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
 
lulubell-bAuthor Commented:
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
 
sdstuberCommented:
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
 
sdstuberCommented:
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
 
lulubell-bAuthor Commented:
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
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.

All Courses

From novice to tech pro — start learning today.