Script stores procedure creating in single line [duplicate]












0
















This question already has an answer here:




  • How to enter newline character in Oracle?

    2 answers




I have written VBA code in excel to create a SP in oracle and it working fine.
But when I opened SP in oracle it showing entire code in single line. I have tried known ways to give line breaks but nothing works.



My actual VB code:



If sp_exist_chk_flag > 0 Then

Else

   cmd_meta.CommandText = "create or replace PROCEDURE TEST_SP " & Chr(13) & _

    "As" & Chr(13) & _

    "REP DATA_REP.REP_ID%TYPE;" & Chr(13) & _

    "SELECT REP_ID INTO REP FROM DATA_REP WHERE REP_NAME='xyz';" & Chr(13) & _

    "COMMIT;" & Chr(13) & _

    "END;"



   cmd_meta.Execute

   MsgBox "SP Created"

End If


In Oracle I can see SP is created as below



create or replace PROCEDURE TEST_SP As REP DATA_REP.REP_ID%TYPE; SELECT REP_ID INTO REP FROM DATA_REP WHERE REP_NAME='xyz'; COMMIT; END;


My actual SP is too long, so my vb code making it very long line.
Please help me on this with VB code or oracle command to look like an actual SP with proper line breaks in oracle.






excel vba oracle excel-vba stored-procedures







share|improve this question















marked as duplicate by Pᴇʜ, Comintern vba
Users with the  vba badge can single-handedly close vba questions as duplicates and reopen them as needed.

StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;

$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');

$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
 Nov 21 '18 at 1:52


This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
















  • @ Peh - I clearly mentioned I am using excel and it is a VBA code

    – Scales
    Nov 20 '18 at 13:06
















0
















This question already has an answer here:




  • How to enter newline character in Oracle?

    2 answers




I have written VBA code in excel to create a SP in oracle and it working fine.
But when I opened SP in oracle it showing entire code in single line. I have tried known ways to give line breaks but nothing works.



My actual VB code:



If sp_exist_chk_flag > 0 Then

Else

   cmd_meta.CommandText = "create or replace PROCEDURE TEST_SP " & Chr(13) & _

    "As" & Chr(13) & _

    "REP DATA_REP.REP_ID%TYPE;" & Chr(13) & _

    "SELECT REP_ID INTO REP FROM DATA_REP WHERE REP_NAME='xyz';" & Chr(13) & _

    "COMMIT;" & Chr(13) & _

    "END;"



   cmd_meta.Execute

   MsgBox "SP Created"

End If


In Oracle I can see SP is created as below



create or replace PROCEDURE TEST_SP As REP DATA_REP.REP_ID%TYPE; SELECT REP_ID INTO REP FROM DATA_REP WHERE REP_NAME='xyz'; COMMIT; END;


My actual SP is too long, so my vb code making it very long line.
Please help me on this with VB code or oracle command to look like an actual SP with proper line breaks in oracle.






excel vba oracle excel-vba stored-procedures







share|improve this question















marked as duplicate by Pᴇʜ, Comintern vba
Users with the  vba badge can single-handedly close vba questions as duplicates and reopen them as needed.

StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;

$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');

$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
 Nov 21 '18 at 1:52


This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
















  • @ Peh - I clearly mentioned I am using excel and it is a VBA code

    – Scales
    Nov 20 '18 at 13:06














0












0








0









This question already has an answer here:




  • How to enter newline character in Oracle?

    2 answers




I have written VBA code in excel to create a SP in oracle and it working fine.
But when I opened SP in oracle it showing entire code in single line. I have tried known ways to give line breaks but nothing works.



My actual VB code:



If sp_exist_chk_flag > 0 Then

Else

   cmd_meta.CommandText = "create or replace PROCEDURE TEST_SP " & Chr(13) & _

    "As" & Chr(13) & _

    "REP DATA_REP.REP_ID%TYPE;" & Chr(13) & _

    "SELECT REP_ID INTO REP FROM DATA_REP WHERE REP_NAME='xyz';" & Chr(13) & _

    "COMMIT;" & Chr(13) & _

    "END;"



   cmd_meta.Execute

   MsgBox "SP Created"

End If


In Oracle I can see SP is created as below



create or replace PROCEDURE TEST_SP As REP DATA_REP.REP_ID%TYPE; SELECT REP_ID INTO REP FROM DATA_REP WHERE REP_NAME='xyz'; COMMIT; END;


My actual SP is too long, so my vb code making it very long line.
Please help me on this with VB code or oracle command to look like an actual SP with proper line breaks in oracle.






excel vba oracle excel-vba stored-procedures







share|improve this question

















This question already has an answer here:




  • How to enter newline character in Oracle?

    2 answers




I have written VBA code in excel to create a SP in oracle and it working fine.
But when I opened SP in oracle it showing entire code in single line. I have tried known ways to give line breaks but nothing works.



My actual VB code:



If sp_exist_chk_flag > 0 Then

Else

   cmd_meta.CommandText = "create or replace PROCEDURE TEST_SP " & Chr(13) & _

    "As" & Chr(13) & _

    "REP DATA_REP.REP_ID%TYPE;" & Chr(13) & _

    "SELECT REP_ID INTO REP FROM DATA_REP WHERE REP_NAME='xyz';" & Chr(13) & _

    "COMMIT;" & Chr(13) & _

    "END;"



   cmd_meta.Execute

   MsgBox "SP Created"

End If


In Oracle I can see SP is created as below



create or replace PROCEDURE TEST_SP As REP DATA_REP.REP_ID%TYPE; SELECT REP_ID INTO REP FROM DATA_REP WHERE REP_NAME='xyz'; COMMIT; END;


My actual SP is too long, so my vb code making it very long line.
Please help me on this with VB code or oracle command to look like an actual SP with proper line breaks in oracle.





This question already has an answer here:




  • How to enter newline character in Oracle?

    2 answers







excel vba oracle excel-vba stored-procedures




excel vba oracle excel-vba stored-procedures






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 20 '18 at 13:01









Pᴇʜ

21.2k42750




21.2k42750










asked Nov 20 '18 at 12:52









ScalesScales

82




82




marked as duplicate by Pᴇʜ, Comintern vba
Users with the  vba badge can single-handedly close vba questions as duplicates and reopen them as needed.

StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;

$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');

$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
 Nov 21 '18 at 1:52


This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.






marked as duplicate by Pᴇʜ, Comintern vba
Users with the  vba badge can single-handedly close vba questions as duplicates and reopen them as needed.

StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;

$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');

$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
 Nov 21 '18 at 1:52


This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.















  • @ Peh - I clearly mentioned I am using excel and it is a VBA code

    – Scales
    Nov 20 '18 at 13:06



















  • @ Peh - I clearly mentioned I am using excel and it is a VBA code

    – Scales
    Nov 20 '18 at 13:06

















@ Peh - I clearly mentioned I am using excel and it is a VBA code

– Scales
Nov 20 '18 at 13:06





@ Peh - I clearly mentioned I am using excel and it is a VBA code

– Scales
Nov 20 '18 at 13:06












1 Answer
1






active

oldest

votes


















2














Not sure if it will make a difference, but try using the vbCrLf constant instead of Chr(13).



If sp_exist_chk_flag > 0 Then

Else

   cmd_meta.CommandText = "create or replace PROCEDURE TEST_SP " & vbCrLf & _

    "As" & vbCrLf  & _

    "REP DATA_REP.REP_ID%TYPE;" & vbCrLf  & _

    "SELECT REP_ID INTO REP FROM DATA_REP WHERE REP_NAME='xyz';" & vbCrLf  & _

    "COMMIT;" & vbCrLf  & _

    "END;"



   cmd_meta.Execute

   MsgBox "SP Created"

End If





share|improve this answer
























  • If LinuxUnix is used try Chr(10) which is a vbLf instead of Chr(13).

    – Pᴇʜ
    Nov 20 '18 at 12:59













  • And it not that Chr(10)

    – Tom
    Nov 20 '18 at 12:59











  • @dcp - Thanks it working :)

    – Scales
    Nov 20 '18 at 13:05











  • @Scales - That's great! Please be sure to accept the answer if it solved your issue.

    – dcp
    Nov 20 '18 at 13:12


















1 Answer
1






active

oldest

votes








1 Answer
1






active

oldest

votes









active

oldest

votes






active

oldest

votes









2














Not sure if it will make a difference, but try using the vbCrLf constant instead of Chr(13).



If sp_exist_chk_flag > 0 Then

Else

   cmd_meta.CommandText = "create or replace PROCEDURE TEST_SP " & vbCrLf & _

    "As" & vbCrLf  & _

    "REP DATA_REP.REP_ID%TYPE;" & vbCrLf  & _

    "SELECT REP_ID INTO REP FROM DATA_REP WHERE REP_NAME='xyz';" & vbCrLf  & _

    "COMMIT;" & vbCrLf  & _

    "END;"



   cmd_meta.Execute

   MsgBox "SP Created"

End If





share|improve this answer
























  • If LinuxUnix is used try Chr(10) which is a vbLf instead of Chr(13).

    – Pᴇʜ
    Nov 20 '18 at 12:59













  • And it not that Chr(10)

    – Tom
    Nov 20 '18 at 12:59











  • @dcp - Thanks it working :)

    – Scales
    Nov 20 '18 at 13:05











  • @Scales - That's great! Please be sure to accept the answer if it solved your issue.

    – dcp
    Nov 20 '18 at 13:12
















2














Not sure if it will make a difference, but try using the vbCrLf constant instead of Chr(13).



If sp_exist_chk_flag > 0 Then

Else

   cmd_meta.CommandText = "create or replace PROCEDURE TEST_SP " & vbCrLf & _

    "As" & vbCrLf  & _

    "REP DATA_REP.REP_ID%TYPE;" & vbCrLf  & _

    "SELECT REP_ID INTO REP FROM DATA_REP WHERE REP_NAME='xyz';" & vbCrLf  & _

    "COMMIT;" & vbCrLf  & _

    "END;"



   cmd_meta.Execute

   MsgBox "SP Created"

End If





share|improve this answer
























  • If LinuxUnix is used try Chr(10) which is a vbLf instead of Chr(13).

    – Pᴇʜ
    Nov 20 '18 at 12:59













  • And it not that Chr(10)

    – Tom
    Nov 20 '18 at 12:59











  • @dcp - Thanks it working :)

    – Scales
    Nov 20 '18 at 13:05











  • @Scales - That's great! Please be sure to accept the answer if it solved your issue.

    – dcp
    Nov 20 '18 at 13:12














2












2








2







Not sure if it will make a difference, but try using the vbCrLf constant instead of Chr(13).



If sp_exist_chk_flag > 0 Then

Else

   cmd_meta.CommandText = "create or replace PROCEDURE TEST_SP " & vbCrLf & _

    "As" & vbCrLf  & _

    "REP DATA_REP.REP_ID%TYPE;" & vbCrLf  & _

    "SELECT REP_ID INTO REP FROM DATA_REP WHERE REP_NAME='xyz';" & vbCrLf  & _

    "COMMIT;" & vbCrLf  & _

    "END;"



   cmd_meta.Execute

   MsgBox "SP Created"

End If





share|improve this answer













Not sure if it will make a difference, but try using the vbCrLf constant instead of Chr(13).



If sp_exist_chk_flag > 0 Then

Else

   cmd_meta.CommandText = "create or replace PROCEDURE TEST_SP " & vbCrLf & _

    "As" & vbCrLf  & _

    "REP DATA_REP.REP_ID%TYPE;" & vbCrLf  & _

    "SELECT REP_ID INTO REP FROM DATA_REP WHERE REP_NAME='xyz';" & vbCrLf  & _

    "COMMIT;" & vbCrLf  & _

    "END;"



   cmd_meta.Execute

   MsgBox "SP Created"

End If






share|improve this answer












share|improve this answer



share|improve this answer










answered Nov 20 '18 at 12:57









dcpdcp

42.8k16119146




42.8k16119146













  • If LinuxUnix is used try Chr(10) which is a vbLf instead of Chr(13).

    – Pᴇʜ
    Nov 20 '18 at 12:59













  • And it not that Chr(10)

    – Tom
    Nov 20 '18 at 12:59











  • @dcp - Thanks it working :)

    – Scales
    Nov 20 '18 at 13:05











  • @Scales - That's great! Please be sure to accept the answer if it solved your issue.

    – dcp
    Nov 20 '18 at 13:12



















  • If LinuxUnix is used try Chr(10) which is a vbLf instead of Chr(13).

    – Pᴇʜ
    Nov 20 '18 at 12:59













  • And it not that Chr(10)

    – Tom
    Nov 20 '18 at 12:59











  • @dcp - Thanks it working :)

    – Scales
    Nov 20 '18 at 13:05











  • @Scales - That's great! Please be sure to accept the answer if it solved your issue.

    – dcp
    Nov 20 '18 at 13:12

















If LinuxUnix is used try Chr(10) which is a vbLf instead of Chr(13).

– Pᴇʜ
Nov 20 '18 at 12:59







If LinuxUnix is used try Chr(10) which is a vbLf instead of Chr(13).

– Pᴇʜ
Nov 20 '18 at 12:59















And it not that Chr(10)

– Tom
Nov 20 '18 at 12:59





And it not that Chr(10)

– Tom
Nov 20 '18 at 12:59













@dcp - Thanks it working :)

– Scales
Nov 20 '18 at 13:05





@dcp - Thanks it working :)

– Scales
Nov 20 '18 at 13:05













@Scales - That's great! Please be sure to accept the answer if it solved your issue.

– dcp
Nov 20 '18 at 13:12





@Scales - That's great! Please be sure to accept the answer if it solved your issue.

– dcp
Nov 20 '18 at 13:12



Popular posts from this blog

MongoDB - Not Authorized To Execute Command

Image
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box; } 0 I have successfully enabled authorization on MongoDB and I have created an account on the admin database and then I created an account for my database called test. The following connection string to connect to my test database works successfully: mongo --host 192.168.17.52 --port 27017 -u user1 -p password --authenticationDatabase test Only problem I have now is, I cannot execute commands such as: show dbs. I get the following error when I try to do so: "errmsg" : "not authorized on admin to execute command { listDatabases: 1.0, lsid: { id: UUID("a1d5bc0d-bc58-485e-b232-270758a89455") }, $db: "admin...
Read more

in spring boot 2.1 many test slices are not allowed anymore due to multiple @BootstrapWith

Image
1 I tried to upgrade a yummy sandwich made of two test slices (@JsonTest and @JdbcTest in my case, crunchy test code in between) adding spring boot 2.1 flavour to it. But it seems it was not much of a success. I cannot annotate my tests with many @...Test since they are now each bringing their own XxxTestContextBootstrapper. It used to work when they all used same SpringBootTestContextBootstrapper. @RunWith(SpringRunner.class) @JdbcTest @JsonTest public class Test { @Test public void test() { System.out.printn("Hello, World !"); } } The error I get from BootstrapUtils is illegalStateException : Configuration error: found multiple declarations of @BootstrapWith for test class I understand I might be doing something wrong here but is there an easy way I could load both Json and Jdbc contex...
Read more

How to fix TextFormField cause rebuild widget in Flutter

Image
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box; } 0 I've got the same issue at #9280. I tried all solutions from that topic with/without GlobalKey for Scaffold and Form but the problem wasn't fixed. I tried to build a authentication form with login and signup. But the keyboard doesn't show when I tap the TextFormField at the first time. Then I try to input some letter from physical keyboard, the widget is rebuilt. Like this: login screen Nothing to be log when I run flutter run --verbose, so I logged manually. Every tapping on field, the widget called dispose -> init -> build. [√] Flutter (Channel beta, v1.0.0, on Microsoft Windows [Version 10.0.17763.195], ...
Read more