Options
External Stored procedures usage
Hi there
I have created a stored procedure using shell script and registered the stored procedure. I can invoke the stored proc but it doesn’t serve my purpose. I am using a global temp table inside the stored proc but stored proc doesn't have access to the data that is in global temp table.
Example query -
- Open a session in SQLWorkBench and execute the following query. It inserts one row into GlobalTempTable1
Insert Into GlobalTempTable1 (CompanyId, StartDate, EndDate, DataVendorID, SortOrder, ContributorID)
Values (23567, '6/20/2016', '6/21/2016', 10, 1, 256)
- Execute the following stored proc in the same session opened in step 1.
select test_proc();
But Test_proc() is not able to access the data inserted in step 1
Any ideas or working examples on how to use temp tables in Vertica external stored procedure?
Thanks in advance!
Srikanth
0
Comments
Hi
Can you site with an example step you have used to access that data and what error message that you have received?
Regards
Rahul Choudhary
Hi Rahul,
Thanks for the reply!
Sure let me give more details with a better example
Step 1 - Open a session in SQLWorkBench
I already have a global temp table created in database and the global temp table name is CompanyInput_tbl.
In SQLWorkBench, I insert two input rows in to CompanyInput_tbl with the following query
Insert Into CompanyInput_tbl (CompanyId, DataVendorID, ContributorID)
Values (23567, 10, 256)
Insert Into CompanyInput_tbl (CompanyId, DataVendorID, ContributorID)
Values (777812, 1, 14)
Step 2 - Execute the Test_Proc stored proc which was created already. Test_Proc is an external stored procedure installed and created through a shell script. Here is the query used inside the stored proc -
Select c.companyId, c.companyName, c.companyTicker, c.companyType
From Company_tbl c
Inner Join CompanyInput_tbl ci
On c.companyId = ci.companyId
and c.dataVendorId = ci.dataVendorId
and c.contributorId = ci.contributorId
Where ci.companyName is not null
Company_tbl is a physical data table.
It doesn’t give any error message nor it gives any rows from the stored proc even though there are rows in the data table. CompanyInput_tbl seems to empty inside the stored proc execution. It is not reading the input rows that I inserted in step 1.
Please let me know if any question.
From what I can tell it looks like the shell script is setting up another session.
Did you try with an actual table? Does that work?
You dont share your external procedure , i am assuming you running shall script that include vsql command .
If this true , it make sense you dont see data ,as your vsql session is opening new vertica session .
And your temp table data is available only the SQLworkbenk .
Hope i will fing helpful
Thanks.