Solved

Stored Procedure Table name as inpurt parameter

Posted on 2011-09-07
18
302 Views
Last Modified: 2012-05-12
Following Procedure is not working.  I want to pass  one of tje table  as input paramet and other table name will be in select statements itself

Please find below
CREATE OR REPLACE PROCEDURE TEST (variableid in number,p_STARTDATE in DATE,p_ENDDATE in DATE,
v_table in varchar2,votes_recordset OUT sys_refcursor)
AS
BEGIN
OPEN votes_recordset FOR
'select a.nnc, b.variable_id, d.variable_name,trunc(b.LAST_MODIFIED_DATE) DATE_MODIFIED,
b.group_id,c.group_name group_name from website a,profilea d,profileb c, '||v_table||' b where b.variable_id = d.variable_id  and c.group_id = b.group_id and a.atc_id = b.atc_id and b.variable_id = variableid and trunc(b.LAST_MODIFIED_DATE ) between  p_STARTDATE and p_ENDDATE';
end TEST;
/
0
Comment
Question by:vadicherla
[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
  • 10
  • 8
18 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 36498349
You need to concat ALL input variables:

change:
...
and b.variable_id = variableid and
...

...
and b.variable_id = ' || variableid || ' and
...
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 500 total points
ID: 36498363
oops...

didn't see the date parameters.

Same goes for them.

change:
...
between  p_STARTDATE and p_ENDDATE'
...

to (untested.  Might have some quoting wrong):

...
between  to_date(''' || to_char(p_STARTDATE,'MM/DD/YYYY') || ''',''MM/DD/YYYY'') and ...


Basically you need to generate a 'string' that can be executed.
0
 

Author Comment

by:vadicherla
ID: 36498599
CREATE OR REPLACE PROCEDURE test (variableid in number,p_STARTDATE in DATE,p_ENDDATE in DATE,
v_table in varchar2,votes_recordset OUT sys_refcursor)
AS
BEGIN
OPEN votes_recordset FOR
'select a.nnc, b.variable_id, d.variable_name,trunc(a.LAST_MODIFIED_DATE) DATE_MODIFIED,
b.group_id,c.group_name group_name from website a,profilea d,profileb c, '||v_table||' b where b.variable_id = d.variable_id  and c.group_id = b.group_id and a.atc_id = b.atc_id and b.variable_id = '||variableid||' and trunc(b.LAST_MODIFIED_DATE ) between  to_date(''' || to_char(p_STARTDATE,'MM/DD/YYYY') || ''',''MM/DD/YYYY'') and to_date(''' || to_char(p_ENDDATE,'MM/DD/YYYY') || ''',''MM/DD/YYYY'')';
end test;
/

Execution
DECLARE
  VARIABLEID NUMBER;
  P_STARTDATE DATE;
  P_ENDDATE DATE;
  V_TABLE VARCHAR2(32767);
  VOTES_RECORDSET SYS_REFCURSOR;

BEGIN
  VARIABLEID := 116;
  P_STARTDATE := 01/01/2011;
  P_ENDDATE := 12/12/2011;
  V_TABLE := 'PROFILE_SMS';
  -- VOTES_RECORDSET := ;  Modify the code to initialize this parameter

  DX_FINT2.RETRIEVEVARBLEBYDATE ( VARIABLEID, P_STARTDATE, P_ENDDATE, V_TABLE, VOTES_RECORDSET );

  :rc0_VOTES_RECORDSET := VOTES_RECORDSET;

  DBMS_OUTPUT.Put_Line('');

  COMMIT;
END;

ORA-06550: line 10, column 18:
PLS-00382: expression is of wrong type
ORA-06550: line 10, column 3:
0
Independent Software Vendors: 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!

 

Author Comment

by:vadicherla
ID: 36498759
ca you please update
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 500 total points
ID: 36498803
>>ca you please update

Sorry.  Was in a meeting.

>>ORA-06550: line 10, column 18:

What is line 10?

try this:
P_STARTDATE := to_date('01/01/2011','MM/DD/YYYY');
P_ENDDATE := to_date('12/12/2011','MM/DD/YYYY');
0
 

Author Comment

by:vadicherla
ID: 36498970
that error  while exectuing. I feel something need to change in below procedure to make it work

CREATE OR REPLACE PROCEDURE test (variableid in number,p_STARTDATE in DATE,p_ENDDATE in DATE,
v_table in varchar2,votes_recordset OUT sys_refcursor)
AS
BEGIN
OPEN votes_recordset FOR
'select a.nnc, b.variable_id, d.variable_name,trunc(a.LAST_MODIFIED_DATE) DATE_MODIFIED,
b.group_id,c.group_name group_name from website a,profilea d,profileb c, '||v_table||' b where b.variable_id = d.variable_id  and c.group_id = b.group_id and a.atc_id = b.atc_id and b.variable_id = '||variableid||' and trunc(b.LAST_MODIFIED_DATE ) between  to_date(''' || to_char(p_STARTDATE,'MM/DD/YYYY') || ''',''MM/DD/YYYY'') and to_date(''' || to_char(p_ENDDATE,'MM/DD/YYYY') || ''',''MM/DD/YYYY'')';
end test;
/


0
 

Author Comment

by:vadicherla
ID: 36499183
DECLARE
  VARIABLEID NUMBER;
  P_STARTDATE DATE;
  P_ENDDATE DATE;
  V_TABLE VARCHAR2(32767);

BEGIN
  VARIABLEID := 116;
  P_STARTDATE := to_date('01/01/2011','MM/DD/YYYY');
  P_ENDDATE := to_date('12/12/2011','MM/DD/YYYY');
  V_TABLE := 'PROFILE_SMS';

  DX_FINT2.RETRIEVEVARBLEBYDATE ( VARIABLEID, P_STARTDATE, P_ENDDATE, V_TABLE );
  DBMS_OUTPUT.Put_Line('');
  COMMIT;
END;

ERROR at line 13:
ORA-06550: line 13, column 3:
PLS-00306: wrong number or types of arguments in call to 'RETRIEVEVARBLEBYDATE'
ORA-06550: line 13, column 3:
PL/SQL: Statement ignored
0
 

Author Comment

by:vadicherla
ID: 36499332
do we need to update anything in procedure to make it work
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 500 total points
ID: 36499416
>> DX_FINT2.RETRIEVEVARBLEBYDATE ( VARIABLEID, P_STARTDATE, P_ENDDATE, V_TABLE );

You didn't add the ref cursor variable to the call.

In the original you had in the declare:
VOTES_RECORDSET SYS_REFCURSOR;

Then the call would be:
DX_FINT2.RETRIEVEVARBLEBYDATE ( VARIABLEID, P_STARTDATE, P_ENDDATE, V_TABLE, VOTES_RECORDSET );

 
0
 

Author Comment

by:vadicherla
ID: 36499531
Its working but not returning any data from exectuion
DECLARE
  VARIABLEID NUMBER;
  P_STARTDATE DATE;
  P_ENDDATE DATE;
  V_TABLE VARCHAR2(32767);
  VOTES_RECORDSET SYS_REFCURSOR;
PL/SQL procedure successfully completed.
BEGIN
  VARIABLEID := 116;
P_STARTDATE := to_date('01/01/2011','MM/DD/YYYY');
  P_ENDDATE := to_date('12/12/2011','MM/DD/YYYY');
  V_TABLE := 'PROFILE_SMS';
  RETRIEVEVARBLEBYDATE ( VARIABLEID, P_STARTDATE, P_ENDDATE, V_TABLE, VOTES_RECORDSET);
  DBMS_OUTPUT.Put_Line('');
  COMMIT;
END;
0
 

Author Comment

by:vadicherla
ID: 36499544
do we need to convert into dynamic sql to make below work
CREATE OR REPLACE PROCEDURE retrievevarbleByDate (variableid in number,p_STARTDATE in DATE,p_ENDDATE in DATE,
v_table in varchar2,votes_recordset OUT sys_refcursor)
AS
BEGIN
OPEN votes_recordset FOR
'select a.ncc, b.variable_id, d.variable_name,trunc(a.LAST_MODIFIED_DATE) DATE_MODIFIED,
b.group_id,c.group_name group_name from ' || v_table || ' b ' ||', website_identity a,profile_variables d,profile_groups c  where b.variable_id = d.variable_id  and c.group_id = b.group_id and a.atc_id = b.atc_id and b.variable_id = '||variableid||' and trunc(b.LAST_MODIFIED_DATE) between   trunc(to_date(''' || to_char(p_STARTDATE,'MM/DD/YYYY') || ''',''MM/DD/YYYY'')) and trunc(to_date(''' || to_char(p_ENDDATE,'MM/DD/YYYY') || ''',''MM/DD/YYYY''))';
end retrievevarbleByDate;
/
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 500 total points
ID: 36499711
>>Its working but not returning any data from exectuion

I don't see where you do anything with VOTES_RECORDSET.

What were you expecting to happen in that code?

>>do we need to convert into dynamic sql to make below work

You are using dynamic SQL not sure what you are asking here.

On mobile right now and cannot get a good look at the code to see what you changed.
0
 

Author Comment

by:vadicherla
ID: 36499764
its working  with below code. Thanks for your help

DECLARE
  VARIABLEID NUMBER;
  P_STARTDATE DATE;
  P_ENDDATE DATE;
  V_TABLE VARCHAR2(32767);
  VOTES_RECORDSET SYS_REFCURSOR;

BEGIN
  VARIABLEID := 116;
P_STARTDATE := to_date('01/01/2011','MM/DD/YYYY');
  P_ENDDATE := to_date('12/12/2011','MM/DD/YYYY');
  V_TABLE := 'PROFILE_SMS';
  RETRIEVEVARBLEBYDATE ( VARIABLEID, P_STARTDATE, P_ENDDATE, V_TABLE, VOTES_RECORDSET);
:rc0_VOTES_RECORDSET := VOTES_RECORDSET;
  DBMS_OUTPUT.Put_Line('');
  COMMIT;
END;
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36499907
Glad to help.  Don't forget to close this out by accepting the answer or answers that helped.
0
 

Author Comment

by:vadicherla
ID: 36513861
Following code is working from Toad but from sql plus command prompt its gving an eror
See below

SQL> DECLARE
  2    VARIABLEID NUMBER;
  3    P_STARTDATE DATE;
  4    P_ENDDATE DATE;
  5    V_TABLE VARCHAR2(32767);
  6    VOTES_RECORDSET SYS_REFCURSOR;
  7
  8  BEGIN
  9    VARIABLEID := 116;
 10  P_STARTDATE := to_date('01/01/2011','MM/DD/YYYY');
 11    P_ENDDATE := to_date('12/12/2011','MM/DD/YYYY');
 12    V_TABLE := 'PROFILE_SMS';
 13    RETRIEVEVARBLEBYDATE ( VARIABLEID, P_STARTDATE, P_ENDDATE, V_TABLE
_RECORDSET);:rc0_VOTES_RECORDSET := VOTES_RECORDSET;
 14    DBMS_OUTPUT.Put_Line('');
 15    COMMIT;
 16  END;
 17  /
SP2-0552: Bind variable "RC0_VOTES_RECORDSET" not declared.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36513942
Error seems self explanatory.

You have the line:  :rc0_VOTES_RECORDSET := VOTES_RECORDSET;

But you never delared rc0_VOTES_RECORDSET in the declare section.
0
 

Author Comment

by:vadicherla
ID: 36549775
Even i declare its not executing. Giving me same error Do we have any other way to execute this procedure from command line ??
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36549991
Post what you have.
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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

690 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