Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > Oracle
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
Oracle General Oracle database discussions.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Oracle section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
 
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old May 24th, 2004, 04:53 AM
Registered User
 
Join Date: May 2004
Location: Secbad, AP, India.
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default ORA-01036: illegal variable name/number

Hi
  Iam encountering a strange problem when i call an oracle stored procedure from visual basic 6.0 using ADO. Iam getting the following two errors for the same action. The error number and description are as follows:

-2147467259 Data type is not supported.
and sometimes this one:
-2147467259 ORA-01036: illegal variable name/number

The stored procedure definition is as follows:

Create or replace PACKAGE TestPack AS
   TYPE T1 IS REF CURSOR;
END TestPack;

PROCEDURE Testsp(lDocid Number,Ref_Cur_1 IN OUT TestPack.T1)
AS
BEGIN
        BEGIN
                OPEN Ref_Cur_1 FOR
                SELECT Doc_Id,Note_id,NoteDet from mytable where DOC_ID = lDocid
;
        END;
END Testsp;

The visual basic code calling the above oracle stored procedure is :

    On Error GoTo errtrap
    Dim adCon As ADODB.Connection
    Dim adRS As New ADODB.Recordset
    Dim adCmd As New ADODB.Command

    Dim iParameterCount As Integer
    Dim iCtr As Integer
    Dim tSql As String
    Dim MyPram As ADODB.Parameter
    Set adCon = New ADODB.Connection
    adCon.Open "File Name=c:\Oraudl.udl"

    adCmd.CommandText = "testsp"

    adCmd.ActiveConnection = adCon
    adCmd.CommandType = adCmdStoredProc

    Set MyPram = adCmd.CreateParameter("LDOCID", adInteger, adParamInput)

    MyPram.Value = 2
    adCmd.Parameters.Append MyPram
    Set adRS = adCmd.Execute ========>ERROR
    'adCmd.Execute
    'MsgBox adCmd.Parameters(2), vbInformation
    Do While adRS.EOF
        For iCtr = 1 To adRS.Fields.Count
            tSql = adRS(iCtr) & " "
        Next
        MsgBox tSql, vbInformation
    Loop

    adRS.Close
    adCon.Close

    Exit Sub
errtrap:
    MsgBox Err.Number & " " & Err.Description

The line indicated with "========>ERROR" is giving the error.

What is that iam doing wrong there. Can anybody please help. please mail the solution to "sri@fortuneit.com"

thanx in advance
Sridhar
  #2 (permalink)  
Old May 24th, 2004, 12:37 PM
Authorized User
 
Join Date: Apr 2004
Location: Boise, ID, USA.
Posts: 70
Thanks: 0
Thanked 0 Times in 0 Posts
Default

- Is the procedure defined in the package or outside?
- Is there a reason that the REF CURSOR parameter is defined as IN OUT rather than OUT?

Justin
Distributed Database Consulting, Inc.
http://www.ddbcinc.com/askDDBC
  #3 (permalink)  
Old May 25th, 2004, 01:33 AM
Registered User
 
Join Date: May 2004
Location: Secbad, AP, India.
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi
  Thanx for ur reply.
  The procedure is defined outside the package. only the refcursor variable is defined inside the package.
No specific reason for defining it as an IN OUT parameter.
My main aim is to return a recordset kind of data from the stored procedure. This stored procedure must be able to acept in and out parameters.
If there is any other way of achieving the this result, it would definitely serve the prurpose.

thanx
Sridhar
  #4 (permalink)  
Old May 25th, 2004, 01:44 AM
Registered User
 
Join Date: May 2004
Location: Secbad, AP, India.
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi
  this is in continuation to my above reply.
   tried with only OUT parameter for the ref cursor variable.
  but still the same error.
  #5 (permalink)  
Old May 25th, 2004, 02:14 AM
Authorized User
 
Join Date: Apr 2004
Location: Boise, ID, USA.
Posts: 70
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I believe you need to have the procedure as part of the package, not as a standalone.

Justin
Distributed Database Consulting, Inc.
http://www.ddbcinc.com/askDDBC
  #6 (permalink)  
Old May 25th, 2004, 06:37 AM
Registered User
 
Join Date: May 2004
Location: Secbad, AP, India.
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi
  I tried with the procedure declared in side the package, but still the same error is coming.
Now the error that is coming most often is "Data type is not supported"

thanx
Sridhar
  #7 (permalink)  
Old December 27th, 2005, 06:31 AM
Registered User
 
Join Date: Dec 2005
Location: vandans, montafon, Austria.
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi
a bite late ;)
It's not necessary to add the 2nd parameter for the recordset!

byoliver

 


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Test if variable is even or odd number Mikect Beginning VB 6 2 June 13th, 2016 08:17 AM
SELECT VARIABLE Number of columns drani Oracle 4 September 21st, 2008 01:12 AM
[Oracle][ODBC][Ora][ORA-01013 Corey Access 1 December 15th, 2006 10:29 AM
Number of table rows to a variable Paula222 Access VBA 2 February 7th, 2006 09:33 AM
variable number of fields in record manisha.anand Oracle 0 July 1st, 2004 05:00 PM



All times are GMT -4. The time now is 01:26 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.