Skip to main content

External Stored procedures usage

  • June 21, 2016
  • 4 replies
  • 25 views
SrikanthGutha

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 - 

  1. 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)

 

  1. 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

    4 replies

    Rahul_Choudhary
    Forum|alt.badge.img
    • Rahul_Choudhary
    • Participating Frequently
    • June 24, 2016

    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

    SrikanthGutha
    • SrikanthGutha
    • Author
    • New Participant
    • June 24, 2016

    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.

    K__
    Forum|alt.badge.img
    • K__
    • Participating Frequently
    • June 30, 2016

    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?

    eli_revach
    Forum|alt.badge.img+2
    • eli_revach
    • Participating Frequently
    • July 1, 2016
    Hi,
    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.
    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement