General SQL Parser Java v0.4 release
In this version, we can easily fetch table and column from stored procedures(packages) of Oracle and SQL Server.
Let’s take this create trigger statement(Oracle) for example:
CREATE TRIGGER hr.emp_permit_changes
BEFORE DELETE OR INSERT OR UPDATE ON hr.employees
BEGIN
INSERT INTO customers
(customer_id,
cust_last_name,
cust_first_name)
VALUES ( :new.customer_id,
:new.cust_last_name,
:new.cust_first_name);
INSERT INTO orders
(order_id,
order_date,
customer_id)
VALUES ( :new.order_id,
:new.order_date,
:new.customer_id);
EXCEPTION
WHEN duplicate_info THEN
Raise_application_error (num => –20107, msg => ‘Duplicate customer or order ID’);
END;
/
Here are table and column in above SQL Statement fetched by demo gettablecolumns shipped together with this component:
Tables:
customers
hr.employees
orders
Fields:
customers.cust_first_name
customers.cust_last_name
customers.customer_id
hr.employees.cust_first_name
hr.employees.cust_last_name
hr.employees.customer_id
hr.employees.order_date
hr.employees.order_id
orders.customer_id
orders.order_date
orders.order_id
Below is another create procedure SQL sample of SQL Server:
CREATE PROCEDURE Purchasing.uspVendorAllInfo
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON;
SELECT v.Name AS Vendor, p.Name AS ‘Product name’,
v.CreditRating AS ‘Credit Rating’,
v.ActiveFlag AS Availability
FROM Purchasing.Vendor v
INNER JOIN Purchasing.ProductVendor pv
ON v.VendorID = pv.VendorID
INNER JOIN Production.Product p
ON pv.ProductID = p.ProductID
ORDER BY v.Name ASC;
GO
Here are table and column in above SQL Statement fetched by demo gettablecolumns shipped together with this component:
Tables:
production.product
purchasing.productvendor
purchasing.vendor
Fields:
production.product.name
production.product.productid
purchasing.productvendor.productid
purchasing.productvendor.vendorid
purchasing.vendor.activeflag
purchasing.vendor.creditrating
purchasing.vendor.name
purchasing.vendor.vendorid
You can download General SQL Parser Java version here:
http://www.sqlparser.com/download.php
If you have any questions or comments about this SQL Parser, feel free to contact me: info@sqlparser.com