Click here to Skip to main content
Click here to Skip to main content

Check the input Query syntax from VB.NET

, 25 Dec 2011
Rate this:
Please Sign up or sign in to vote.
If you build the Query executer tool in a VB.NET, you need to check the input query syntax before getting the results. This tip gives you a small idea of how to get the syntactic errors from the VB.NET front end screen by calling already created stored procedure.
I've created one procedure in that I've used the ref cursor which is defined in the DBK_REPORTS Package.
The input parameter for this procedure was my input query and the output parameters are queryresult as cursor and message. What I did is, within the try..catch block, I've opened this refcursor for my input query and if any syntactic errors occurs which will be Exception under the condition OTHERS, and it'll be stored to the output parameter message. It was done by assigning the result of SQLERRM function.
create or replace package DBK_REPORTS 
is
TYPE record_type IS REF CURSOR;
 
create or replace PROCEDURE DBP_QUERY_RESULT
       (  
          
          O_QUERY               IN   CLOB,
          O_QRY_RES             OUT  DBK_REPORTS.record_type,
          O_MESSAGE             OUT  VARCHAR2
      )IS
 
V_QUERY            CLOB;
BEGIN
V_QUERY := O_QUERY;                        
   
      BEGIN
           OPEN O_QRY_RES FOR TO_CHAR(V_QUERY);
          
        EXCEPTION
          WHEN NO_DATA_FOUND THEN
            O_MESSAGE := 'NO RECORDS FOUND';
          WHEN OTHERS THEN
            O_MESSAGE    := sqlcode || sqlerrm;
                     
       END;
                         
END DBP_QUERY_RESULT;

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

bluesathish
Software Developer (Senior)
India India
Bluesathish was his alice name, He has been Working with Microsoft Visual studio from the past 7 years and worked its versions like VS2003, VS2005, VS2008 and Present VS2010. And in his free times he likes to play cricket and videogames, sometimes surfing in codeproject.
 
He likes to share things/thoughts/ideas which he knew, I think thats what he subscribed in CP Wink | ;-)

Comments and Discussions

 
Generalhi Member 8345599, You can use this oracle procedure results... Pinmemberbluesathish27-Dec-11 17:03 
GeneralReason for my vote of 1 not visual basic code PinmemberMember 834559927-Dec-11 5:33 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140827.1 | Last Updated 25 Dec 2011
Article Copyright 2011 by bluesathish
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid