{"action":"closed","issue":{"url":"https://api.github.com/repos/darold/ora2pg/issues/103","labels_url":"https://api.github.com/repos/darold/ora2pg/issues/103/labels{/name}","comments_url":"https://api.github.com/repos/darold/ora2pg/issues/103/comments","events_url":"https://api.github.com/repos/darold/ora2pg/issues/103/events","html_url":"https://github.com/darold/ora2pg/issues/103","id":53684992,"number":103,"title":"package containing procedures without arguments converting with lines out of order","user":{"login":"hdeadman","id":1556777,"avatar_url":"https://avatars.githubusercontent.com/u/1556777?v=3","gravatar_id":"","url":"https://api.github.com/users/hdeadman","html_url":"https://github.com/hdeadman","followers_url":"https://api.github.com/users/hdeadman/followers","following_url":"https://api.github.com/users/hdeadman/following{/other_user}","gists_url":"https://api.github.com/users/hdeadman/gists{/gist_id}","starred_url":"https://api.github.com/users/hdeadman/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/hdeadman/subscriptions","organizations_url":"https://api.github.com/users/hdeadman/orgs","repos_url":"https://api.github.com/users/hdeadman/repos","events_url":"https://api.github.com/users/hdeadman/events{/privacy}","received_events_url":"https://api.github.com/users/hdeadman/received_events","type":"User","site_admin":false},"labels":[],"state":"closed","locked":false,"assignee":null,"milestone":null,"comments":6,"created_at":"2015-01-07T21:33:25Z","updated_at":"2015-01-09T20:37:27Z","closed_at":"2015-01-09T20:37:27Z","body":"I have an Oracle package with a spec like this:\r\n```sql\r\nPACKAGE         \"ORG_SEQUENCE_UTIL\" AS\r\nPROCEDURE annual_org_reset;\r\nFUNCTION fire_org_seq(org VARCHAR2) RETURN NUMBER;\r\nFUNCTION fire_seq_gen(SEQ_NAME VARCHAR2) RETURN NUMBER;\r\nPROCEDURE insert_org_seq(org VARCHAR2, startval NUMBER);\r\nPROCEDURE delete_org_seq(org VARCHAR2);\r\nPROCEDURE check_current_year;\r\nEND ORG_SEQUENCE_UTIL;\r\n```\r\nThe two procedures without arguments are converted to functions that start out like this:\r\n\r\n```sql\r\n\r\n  IS\r\nV_PREFIX VARCHAR2\r\nCREATE OR REPLACE FUNCTION ORG_SEQUENCE_UTIL_annual_org_reset (100) RETURNS VOID AS $body$\r\nBEGIN\r\n```\r\n(The \"(100)\" above came from the V_PREFIX VARCHAR2(100) so I guess ora2pg is looking for the  parenthesis after the procedure name but Oracle won't let you have empty parenthesis for procedures with no arguments.)\r\n\r\nand \r\n\r\n```sql\r\n  IS\r\nCURSOR GET_YEAR_IN_DB IS SELECT year FROM current_year;\r\nCURSOR GET_YEAR IS SELECT to_number\r\nCREATE OR REPLACE FUNCTION ORG_SEQUENCE_UTIL_check_current_year (to_char(sysdate,'yyyy') RETURNS VOID AS $body$\r\nDECLARE\r\n) ;\r\nV_DB_YEAR bigint;\r\nV_YEAR bigint;\r\n```\r\n\r\nI changed the two procedures without args in Oracle so that they had a single dummy varchar2 argument and then the lines were converted in the correct order: \r\n\r\n```sql\r\nCREATE OR REPLACE FUNCTION ORG_SEQUENCE_UTIL_check_current_year (org text)  RETURNS VOID AS $body$\r\nDECLARE\r\n\r\nGET_YEAR_IN_DB CURSOR IS PERFORM year FROM current_year;\r\nGET_YEAR CURSOR IS PERFORM to_char(LOCALTIMESTAMP, 'yyyy')::integer ;\r\nV_DB_YEAR bigint;\r\nV_YEAR bigint;\r\n\r\nBEGIN\r\n```\r\nLooking at those two cursors and just having read up on PERFORM, I don't think they should both be PERFORM. The GET_YEAR_IN_DB cursor is selecting the year column from a table with one row (basically a marker table so the code can reset the counter rows in a table every year). \r\n\r\nI am using the latest code from today.\r\n\r\n(deleted comment about TABLESPACE taking long time, it eventually finished when I left it alone to run)"}}