| Usage of package on a dynamically provided table name. [message #304995] |
Fri, 07 March 2008 07:56  |
orahelp007
Messages: 4
Registered: March 2008
Location: bangalore
|
Junior Member |
|
|
Hi all,
Requirement:
Generic package body for validating the columns of a specific module in oracle applications.let us take HR module(assignments).
step1:
take data from staging table -> validate for mandatory conditions.
problem:
no static staging table.every time staging table name is provided as an IN parameter.
required:
fetch all the records one by one from staging table to validation.
my try:
------
I tried using ref_cursor, but I am not able to store the fetched records in a rowtype variable unless I mention the staging table name.
please find my efforts and request for an solution;
pkg spec:
---------
CREATE OR REPLACE PACKAGE CHECK_MYPACK IS
PROCEDURE RET_STR(table_name VARCHAR2,table_name1 VARCHAR2);
END CHECK_MYPACK;
pkg body:
--------
CREATE OR REPLACE PACKAGE BODY CHECK_MYPACK IS
PROCEDURE RET_STR(table_name VARCHAR2,table_name1 VARCHAR2)
IS
TYPE cur_typ IS REF CURSOR;
MY_CUR cur_typ;
query_str VARCHAR2(200);
MAKE_STR table_name%ROWTYPE; -- error out
MAKE_STR1 DEPT%ROWTYPE;
empname varchar2(70);
--make_str2 table_name%rowtype;
BEGIN
QUERY_STR := 'SELECT ' || '*' || ' FROM '||table_name ;
DBMS_OUTPUT.PUT_LINE(QUERY_STR);
OPEN MY_CUR FOR QUERY_STR;
LOOP
FETCH MY_CUR INTO MAKE_STR;
EXIT WHEN MY_CUR%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(MAKE_STR.empname||MAKE_STR.salary);
END LOOP;
CLOSE MY_CUR;
QUERY_STR := 'SELECT ' || '*' || ' FROM '||table_name1 ;
DBMS_OUTPUT.PUT_LINE(QUERY_STR);
OPEN MY_CUR FOR QUERY_STR;
LOOP
FETCH MY_CUR INTO MAKE_STR1;
EXIT WHEN MY_CUR%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(MAKE_STR1.deptname);
END LOOP;
CLOSE MY_CUR;
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END RET_STR;
END CHECK_MYPACK;
if you cannot understand :
select * from <table_name> here dynamically table name would be provided and the records should be fetched.
thanks in advance. please give some approaches.
regards
Arjun G
|
|
|
|
| Re: Usage of package on a dynamically provided table name. [message #305007 is a reply to message #304995] |
Fri, 07 March 2008 08:44  |
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
|
Senior Member |
|
|
Are you trying to write some utility like getting a count or dumping into csv or something generic it makes sense to write a subroutine and do it. Otherwise you are making things complicated.
| Quote: | take data from staging table -> validate for mandatory conditions.
|
Definitely the mandatory conditions will be different between different tables. How are you planning to achieve that ? So if you ask me it has to be specific to a package otherwise the code has to be extremely generic. If the code is more generic and if it is not properly documented even you cannot understand it if you look at the same piece of code after few months.
Anyways for what you needs look for dbms_sql package in the pl/sql packages and utilities manual.
Regards
Raj
|
|
|
|
| Re: Usage of package on a dynamically provided table name. [message #305009 is a reply to message #305007] |
Fri, 07 March 2008 08:52 |
orahelp007
Messages: 4
Registered: March 2008
Location: bangalore
|
Junior Member |
|
|
Hi raj,
As I had mentioned the staging table name alone changes, but not the structure of the table.
for example table xx has a,b,c as mandatory columns.
every time the table structure remains same i.e (a,b,c are mandatory). but the name of the table will change as 'YY'.
the dynamic table name should be stored into a variable using rowtype
vaiable a <dynamic table>%rowtype;
or some other approach for achieving the same.
Thanks,
Arjun G
|
|
|
|
|
|
| Re: Usage of package on a dynamically provided table name. [message #305014 is a reply to message #305009] |
Fri, 07 March 2008 09:01 |
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
|
Senior Member |
|
|
If the tablestructure is going to be the same but the table name changes,
a) accept table_name as a parameter
b) use sysrefcursor
c) create a cursor dynamically
d) process it.
For more details about sysrefcursor check in oracle sql reference manual or google it.
Regards
raj
|
|
|
|
|
|
|
|
| Re: Usage of package on a dynamically provided table name. [message #305160 is a reply to message #305009] |
Sun, 09 March 2008 12:27 |
 |
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
| orahelp007 wrote on Fri, 07 March 2008 06:52 | Hi raj,
As I had mentioned the staging table name alone changes, but not the structure of the table.
for example table xx has a,b,c as mandatory columns.
every time the table structure remains same i.e (a,b,c are mandatory). but the name of the table will change as 'YY'.
the dynamic table name should be stored into a variable using rowtype
vaiable a <dynamic table>%rowtype;
or some other approach for achieving the same.
Thanks,
Arjun G
|
If the structure is the same, then use a static table or create one so that you can use the name and instead of:
vaiable a <dynamic table>%rowtype;
use:
variable static_table_name%ROWTYPE;
for example:
make_str emp%ROWTYPE;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
