Home » SQL & PL/SQL » SQL & PL/SQL » PLSQL Generic Packge Which creates package and package body based on table name
PLSQL Generic Packge Which creates package and package body based on table name [message #391683] Fri, 13 March 2009 01:02 Go to next message
ashreddy
Messages: 19
Registered: March 2009
Junior Member
Hi,

I have Package which does Insert,Update,Select and Delete operation on a database table.

I needs to write packages for all the tables(of course columns gets change) which does the same work which I think is waste of time as I need to write same package with different name for almost 50 tables.

Is it possible to write a generic Package which creates the package and package body based on the database table name as I have the same operations done on all the tables i.e.package which gets the table name from database and gets the work done.

Thanks,
Ashok

Re: PLSQL Generic Packge Which creates package and package body based on table name [message #391687 is a reply to message #391683] Fri, 13 March 2009 01:11 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Just being curious: why would you want to do that? What's wrong with an ordinary SQL (or PL/SQL or Forms or ...) statements/procedures/applications which do the job directly? You know,
INSERT INTO one_of_my_tables
  (<list of columns I'm interested in>
   VALUES
  (first_value, then_the_second_one, blabla);
Re: PLSQL Generic Packge Which creates package and package body based on table name [message #391688 is a reply to message #391683] Fri, 13 March 2009 01:12 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Step 1: Create a template containing so you know what each package should look like
Step 2: Filter out the parts that differ from table/package to table/package
Step 3: Write SQL to fetch those dynamic parts
Step 4: Merge the constant parts plus the dynamic parts (in SQL)

In short: use SQL to generate each package, based on user_tab_columns, for example.
Re: PLSQL Generic Packge Which creates package and package body based on table name [message #391696 is a reply to message #391687] Fri, 13 March 2009 01:48 Go to previous messageGo to next message
ashreddy
Messages: 19
Registered: March 2009
Junior Member
Actually I am being told to just transform the Procedures or code written in SQL-Server into Oracle Stored Procedures which does the above said operations on the spcified tables which should have input parameters and return the required data in the form of record set.

So I have written a package with procedures each for Insert,Delete,Update and return the row count and some coulmns in the record set by using REF cursor.

My need is instead of writing 30-40 packages which does the same operations on the different tables..why not a generic code which fetches the table name and columns and generate the package and package body.

And another problem is that the stored procedures has input parameters which differs according to the table...

Is it possible??

Please help..


Re: PLSQL Generic Packge Which creates package and package body based on table name [message #391697 is a reply to message #391688] Fri, 13 March 2009 01:50 Go to previous messageGo to next message
ashreddy
Messages: 19
Registered: March 2009
Junior Member
Hi Frank,

I was just doing the same.
But as I am new to SQL...just need asny sample code which does the same...

Thanks,
Ash
Re: PLSQL Generic Packge Which creates package and package body based on table name [message #391698 is a reply to message #391696] Fri, 13 March 2009 01:53 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Start with step 1.
Show us (in pseudo code) what such a generic procedure would look like.
Don't forget to show what input parameters it will have and how you think to handle those.
Re: PLSQL Generic Packge Which creates package and package body based on table name [message #391702 is a reply to message #391698] Fri, 13 March 2009 02:15 Go to previous messageGo to next message
ashreddy
Messages: 19
Registered: March 2009
Junior Member
My package looks like the below one:

Create Package tablename_pk IS

PROCEDURE sp_tablename_Insert(
Name VARCHAR2, ----Changes according to the table columns
Code VARCHAR2,
<input parameters to insert into the table>
ret_cursor OUT SYS_REFCURSOR);

PROCEDURE sp_tablename_update(
Id VARCHA2 DEFAULT NULL,
<input parameters with which the table gets updated based on the input parameter Id and returns the rowcount thru ref cursor >
ret_cursor OUT SYS_REFCURSOR);


PROCEDURE sp_tablename_Findall(
Id VARCHA2 DEFAULT NULL,
<input parameters based on which I select the data from the table and return thru ref cursor>
ret_cursor OUT SYS_REFCURSOR);

PROCEDURE sp_tablename_Delete(
Id VARCHA2 DEFAULT NULL,
<input parameter based on which delet the data from the table and return the rowcount thru ref cursor>
ret_cursor OUT SYS_REFCURSOR);

--End of package specification
END tablename_Pk;
/

And the subsequent transactions are done in the package body


Re: PLSQL Generic Packge Which creates package and package body based on table name [message #391706 is a reply to message #391702] Fri, 13 March 2009 02:23 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
ashreddy wrote on Fri, 13 March 2009 08:15

PROCEDURE sp_tablename_Insert(
Name VARCHAR2, ----Changes according to the table columns
Code VARCHAR2,
<input parameters to insert into the table>
ret_cursor OUT SYS_REFCURSOR);

PROCEDURE sp_tablename_update(
Id VARCHA2 DEFAULT NULL,
<input parameters with which the table gets updated based on the input parameter Id and returns the rowcount thru ref cursor >
ret_cursor OUT SYS_REFCURSOR);


But how would you define the <input parameters to insert into the table>?
That would probably be a collection of strings. In order to insert those into date or number columns, you'd want to convert them.
This, combined with the actual dml-statements themselves would require you a lot of fancy (and error-prone and non-performant) dynamic sql.
What's wrong with generating 50 packages?
Re: PLSQL Generic Packge Which creates package and package body based on table name [message #391707 is a reply to message #391706] Fri, 13 March 2009 02:25 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I agree with Frank: a generic example might look like a good idea but a separate table API package for each table is probably a better idea from a performance point of view.

In fact, you only need to write one script that queries a couple of dictionary tables and you're started.

MHE

[Updated on: Fri, 13 March 2009 02:25]

Report message to a moderator

Re: PLSQL Generic Packge Which creates package and package body based on table name [message #391721 is a reply to message #391706] Fri, 13 March 2009 03:40 Go to previous messageGo to next message
ashreddy
Messages: 19
Registered: March 2009
Junior Member
Frank,

Nothing wrong in generating 50 packages...but the thing is time consuming...I was told yesterday to write packages for the same and get it done by today at anycost...

So I was wondering is there any means which can be done without actually writing all of the packages but a generic package which consists of the stored procedures which does all the operations and another code which in turn creates all the packages based on the database tables.

And there are no conversions of the columns..but we need to pass different input paramters for each different table.

Anyhow thanks for your suggestions.
If any other alternative let me know...otherwise there is no other go but to write all 50 packages.

[Updated on: Fri, 13 March 2009 03:42]

Report message to a moderator

Re: PLSQL Generic Packge Which creates package and package body based on table name [message #391724 is a reply to message #391721] Fri, 13 March 2009 03:44 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Generating 50 or 500 packages is not more time-consuming than generating 1 package.
Quote:
And there are no conversions of the columns..but we need to pass different input paramters for each different table.

Too bad you only have a single procedure, so you only have a single (set of) input variables with predefined datatypes. This means that you do need conversion.

Quote:
If any other alternative let me know...otherwise there is no other go but to write all 50 packages.

I never said anything about WRITING 50 packages.

[Updated on: Fri, 13 March 2009 03:44]

Report message to a moderator

Previous Topic: Need help to write a SQL
Next Topic: NUMBER with One precision
Goto Forum:
  


Current Time: Wed Dec 07 04:45:08 CST 2016

Total time taken to generate the page: 0.05407 seconds