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 - 

  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!




  • Options



    Can you site with an example step you have used to access that data and what error message that you have received?



    Rahul Choudhary

  • Options

    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.

  • Options

    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?

  • Options
    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


Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file