Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 839
  • Last Modified:

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

0
lulubell-b
Asked:
lulubell-b
  • 6
  • 2
1 Solution
 
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
 
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

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