Solved

Stored Procedure Table name as inpurt parameter

Posted on 2011-09-07
18
301 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
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!

 

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
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.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

730 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