ORACLE: Tabs used for indentation converted to spaces in stored procedure when executing through DSN connection

52 Views Asked by At

I have a functionality in vb6.0 application to execute stored procedures into oracle database. when doing so, The TABS present in the stored procedures for indentation gets removed and a single SPACE is being applied.

Oracle DB: 19c Connection Type: ODBC

VB6.0 Code:

Private Sub Command1_Click()
    Dim conn As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim rs As ADODB.Recordset
    Dim strSql As String

    Set conn = New ADODB.Connection
    Set cmd = New ADODB.Command
    
    conn.ConnectionString = "Data Source=MyDataBase; User ID=username; Password=password;"
    
    conn.Open
    
    If conn.State = adStateOpen Then
        Set cmd.ActiveConnection = conn
        'cmd.CommandType = adCmdText
        strSql = RichTextBox1.Text
        cmd.CommandText = strSql
        cmd.Execute
        '
    End If
    conn.Close
    
    Set cmd = Nothing
    Set conn = Nothing
    End
End Sub

My Sample script:

CREATE OR REPLACE PROCEDURE A_SELECT_PATIENTS
IS
    v_value VARCHAR2(100);
    BEGIN
        SELECT DISTINCT
            PAT.SURNAME
            INTO v_value
        FROM
            PATIENTS PAT
        WHERE
            PAT.PATNT_REFNO = 73917;
            
        DBMS_OUTPUT.PUT_LINE('Surname: ' || v_value);
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            DBMS_OUTPUT.PUT_LINE('No data found.');

    END;

script when viewed through sql developer

create or replace NONEDITIONABLE PROCEDURE A_SELECT_PATIENTS 
IS 
 v_value VARCHAR2(100); 
 BEGIN 
 SELECT DISTINCT 
 PAT.SURNAME 
 INTO v_value 
 FROM 
 PATIENTS PAT 
 WHERE 
 PAT.PATNT_REFNO = 73917; 
 
 DBMS_OUTPUT.PUT_LINE('Surname: ' || v_value); 
 EXCEPTION 
 WHEN NO_DATA_FOUND THEN 
 DBMS_OUTPUT.PUT_LINE('No data found.'); 
 
 END;

I would be very thankful if anyone share a solution to preserve the Tab indentation from the script when looking from sql developer at the time of executing script through the code execution itself.

Note: it would be good it the solution is provided without adding any Provider into the above given connection string

0

There are 0 best solutions below