Solved

Stored Procedure Table name as inpurt parameter

Posted on 2011-09-07
18
297 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
  • 10
  • 8
18 Comments
 
LVL 76

Accepted Solution

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

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

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

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 500 total points
Comment Utility
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
Comment Utility
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
 

Author Comment

by:vadicherla
Comment Utility
ca you please update
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 500 total points
Comment Utility
>>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
Comment Utility
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
Comment Utility
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
Comment Utility
do we need to update anything in procedure to make it work
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 500 total points
Comment Utility
>> 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:vadicherla
Comment Utility
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
Comment Utility
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 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 500 total points
Comment Utility
>>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
Comment Utility
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 76

Expert Comment

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

Author Comment

by:vadicherla
Comment Utility
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 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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
Comment Utility
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 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
Post what you have.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
PL/SQL Two statements 6 49
Bulk insert into global temporary table 2 39
oracle query help 36 64
Pl/SQL Query 31 60
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…
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…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
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.

728 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now