Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2003
    Location
    In your thoughts
    Posts
    195

    Unanswered: Stored Procedure Help Needed

    Here is the code:

    Code:
    CREATE OR REPLACE PROCEDURE All_Cust
    AS
    BEGIN
       SELECT DISTINCT Cust FROM (
       SELECT DISTINCT QUOTE_COMP Cust FROM QUOTES
       UNION
       SELECT CU_NAME Cust FROM CU@QSDB_PK1)
       ORDER BY Cust
    END
    I keep getting the warning that it was created but with compilation errors. Can someone see why? I'm new to stored procedures. Thanks, Jeremy
    Nothing better than a good ride.

  2. #2
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    You can't select like that in PL/SQL - you have to select into "something" of a known type. I guess you want to return the recordset to your vb app? I don't know if VB can handle ref cursors, if so....

    CREATE OR REPLACE PACKAGE MyPackage IS
    TYPE tCurType IS REF CURSOR;
    PROCEDURE SelectSomeRecords( outList IN OUT tCurType );
    END MyPackage;
    /
    CREATE OR REPLACE PACKAGE BODY MyPackage AS
    PROCEDURE SelectSomeRecords( outList IN OUT tCurType ) IS
    BEGIN
    OPEN outList FOR
    SELECT DISTINCT Cust FROM (
    SELECT DISTINCT QUOTE_COMP Cust FROM QUOTES
    UNION
    SELECT CU_NAME Cust FROM CU@QSDB_PK1)
    ORDER BY Cust;
    EXCEPTION WHEN OTHERS THEN
    -- Some error, log it
    END SelectSomeRecords;

    END MyPackage;
    /

    As I say I don't know if VB will handle a ref cursor returned in this way, you'll have to check that. The SQL to execute this client side is usually something like

    begin
    MyPackage.SelectSomeRecords( <your ref cursor type> );
    end;

    Hth
    Bill

  3. #3
    Join Date
    Feb 2003
    Location
    In your thoughts
    Posts
    195
    Bill, what about a view? Could I create a view and populate a recordset by that? Thanks, Jeremy
    Nothing better than a good ride.

  4. #4
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Yes, you could create a view - but I'd hesitate to do that purely because of syntactical problems etc.... you could end up creating hundreds!

    CREATE OR REPLACE VIEW MyView AS
    SELECT .....;

    If you can get the ref cursors sorted, they are an ideal way to deal with select statements from a client side application. Primarily it moves all of your SQL onto the database itself - if you change a table and the SQL is invalidated, your package is also invalid. Oracle can also perform *much* better optimisation of cursors, particulary weak cursors as it knows they can't be updated and so internally atleast, one cursor can be shared amongst many users running the same (or even similar types of) queries. Vastly reduced overhead on the server, vastly improved client side performance.

    Hth
    Bill

  5. #5
    Join Date
    Feb 2003
    Location
    In your thoughts
    Posts
    195
    I'm new to cursors as well. When I create a view and SELECT * FROM View, I get the ORA-02041 error. It doesn't make sense. If I do this from SQL*Plus, it works but from VB, it doesn't. Do you know why this would happen? Here are my steps:

    Created View in SQL*Plus
    Tested View in SQL*Plus
    Altered code to SELECT * FROM View
    Ran
    Got ORA-02041 error

    If you need code, I can give it to you. Thanks, Jeremy
    Nothing better than a good ride.

  6. #6
    Join Date
    Jun 2003
    Posts
    34

    ORA-02041

    Could u post the whole code ?

  7. #7
    Join Date
    Feb 2003
    Location
    In your thoughts
    Posts
    195
    Here you go:

    Code:
    Private Sub Form_Load()
        Call DB_Connect
        Call PopulateCompanyComboBox
        'cmbCustomer.Text = "            Select Customer"
        cmbCustomer.Text = cmbCustomer.List(0)
        cmbBuyer.Text = "            Select Buyer"
        cmbSeller.Text = "            Select Sales/Rep"
        txtEstimator.Text = gbtxtUserName
        Call Get_Quote_Num
        Call Form_Activate
    End Sub
    Private Sub DB_Connect()
        'Create Connection Object
        Set OraCon = CreateObject("ADODB.Connection")
        OraCon.ConnectionString = "Provider=OraOLEDB.Oracle;" & _
                              "Data Source=PK1;" & _
                              "User ID=pk1;" & _
                              "Password=pk1;"
        'Open Connection
        OraCon.Open
        'Create Connection Object
        Set OraCon1 = CreateObject("ADODB.Connection")
        OraCon1.ConnectionString = "Provider=OraOLEDB.Oracle;" & _
                              "Data Source=QSDB;" & _
                              "User ID=QSDB_USER;" & _
                              "Password=5star5;"
        'Open Connection
        OraCon1.Open
    End Sub
    Private Sub PopulateCompanyComboBox()
        'Create Recordset Selecting all Customer Names
        SQLQuery = "SELECT DISTINCT Cust FROM (" & _
                   "SELECT DISTINCT QUOTE_COMP Cust FROM QUOTES " & _
                   "UNION " & _
                   "SELECT CU_NAME Cust FROM CU@QSDB_PK1) " & _
                   "ORDER BY Cust"
        'MsgBox SQLQuery
        Set OraRec = New ADODB.Recordset
        OraRec.Open SQLQuery, OraCon1, adOpenStatic, adLockOptimistic, adCmdText
        If OraRec.RecordCount = 0 Then
            Exit Sub
            OraRec.Close
            Set OraRec = Nothing
        Else
            OraRec.MoveFirst
            cmbCustomer.Clear
            
            Do While Not OraRec.EOF
                cmbCustomer.AddItem OraRec!CU_NAME
                OraRec.MoveNext
            Loop
            OraRec.Close
            Set OraRec = Nothing
        End If
    End Sub
    This is the first attempt without Procedure or View. The next one is with the View

    Code:
    Private Sub Form_Load()
        Call DB_Connect
        Call PopulateCompanyComboBox
        'cmbCustomer.Text = "            Select Customer"
        cmbCustomer.Text = cmbCustomer.List(0)
        cmbBuyer.Text = "            Select Buyer"
        cmbSeller.Text = "            Select Sales/Rep"
        txtEstimator.Text = gbtxtUserName
        Call Get_Quote_Num
        Call Form_Activate
    End Sub
    Private Sub DB_Connect()
        'Create Connection Object
        Set OraCon = CreateObject("ADODB.Connection")
        OraCon.ConnectionString = "Provider=OraOLEDB.Oracle;" & _
                              "Data Source=PK1;" & _
                              "User ID=pk1;" & _
                              "Password=pk1;"
        'Open Connection
        OraCon.Open
        'Create Connection Object
        Set OraCon1 = CreateObject("ADODB.Connection")
        OraCon1.ConnectionString = "Provider=OraOLEDB.Oracle;" & _
                              "Data Source=QSDB;" & _
                              "User ID=QSDB_USER;" & _
                              "Password=5star5;"
        'Open Connection
        OraCon1.Open
    End Sub
    Private Sub PopulateCompanyComboBox()
        'Create Recordset Selecting all Customer Names
        SQLQuery = "SELECT DISTINCT Cust FROM (" & _
                   "SELECT * FROM AllCust" 'AllCust is the View name
        'MsgBox SQLQuery
        Set OraRec = New ADODB.Recordset
        OraRec.Open SQLQuery, OraCon1, adOpenStatic, adLockOptimistic, adCmdText
        If OraRec.RecordCount = 0 Then
            Exit Sub
            OraRec.Close
            Set OraRec = Nothing
        Else
            OraRec.MoveFirst
            cmbCustomer.Clear
            
            Do While Not OraRec.EOF
                cmbCustomer.AddItem OraRec!CU_NAME
                OraRec.MoveNext
            Loop
            OraRec.Close
            Set OraRec = Nothing
        End If
    End Sub
    Both result in ORA-02041. There is a database link being used in the View and in the first piece of code named "QSDB_PK1". Thanks for your help, Jeremy
    Nothing better than a good ride.

  8. #8
    Join Date
    Jun 2003
    Posts
    34
    First create a synonym like :
    CREATE PUBLIC SYNONYM Customer FOR CU@QSDB_PK1;

    Then Create a view like :

    CREATE VIEW MY_VW AS (
    SELECT DISTINCT Cust FROM (
    SELECT DISTINCT QUOTE_COMP Cust FROM QUOTES
    UNION
    SELECT CU_NAME Cust FROM Customer)
    ORDER BY Cust)
    and then use :
    Select * from MY_VW in ur code

  9. #9
    Join Date
    Feb 2003
    Location
    In your thoughts
    Posts
    195
    Didn't work. Got same error. This is crazy. I just don't understand this. It SHOULD work but it isn't. Well, thanks for your help and if you have anymore ideas, let me know, Jeremy
    Nothing better than a good ride.

  10. #10
    Join Date
    Feb 2003
    Location
    In your thoughts
    Posts
    195
    I just heard that you couldn't use a DBLink in VB? I don't understand that one. Is this true? Thanks, Jeremy
    Nothing better than a good ride.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •