Home » SQL & PL/SQL » SQL & PL/SQL » Usage of package on a dynamically provided table name. (oracle 9i)
Usage of package on a dynamically provided table name. [message #304995] Fri, 07 March 2008 07:56 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #305012 is a reply to message #304995] Fri, 07 March 2008 08:58 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
>or some other approach for achieving the same.
CREATE VIEW .......
Re: Usage of package on a dynamically provided table name. [message #305014 is a reply to message #305009] Fri, 07 March 2008 09:01 Go to previous messageGo to next message
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 #305141 is a reply to message #305014] Sun, 09 March 2008 03:45 Go to previous messageGo to next message
orahelp007
Messages: 4
Registered: March 2008
Location: bangalore
Junior Member
Hi raj,

I googled the syntax for sysrefcursor, even then for creating a dynamic cursor the table name %rowtype should be acheived for performing validation on those columns.

If you think its possible, can you guide me my providing pseudo code.

Regards
Arjun G
Re: Usage of package on a dynamically provided table name. [message #305142 is a reply to message #305012] Sun, 09 March 2008 03:47 Go to previous messageGo to next message
orahelp007
Messages: 4
Registered: March 2008
Location: bangalore
Junior Member
Creating a view should be a seperate script,latter that pkg body can be compiled,is there any way to handle it in a single pkg.

Regards
Arjun G
Re: Usage of package on a dynamically provided table name. [message #305160 is a reply to message #305009] Sun, 09 March 2008 12:27 Go to previous message
Barbara Boehmer
Messages: 8633
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;
Previous Topic: help with PL SQL - error "invalid datatype"
Next Topic: Prioritizing oracle scheduled jobs
Goto Forum:
  


Current Time: Tue Dec 06 13:57:02 CST 2016

Total time taken to generate the page: 0.09954 seconds